CodeCharge Studio
search Register Login  

Visual Web Reporting

Visually create Web Reports in PHP, ASP, .NET, Java, Perl and ColdFusion.
CodeCharge.com

YesSoftware Forums -> CodeCharge Studio -> PHP

 Sorting report groups (Resolved)

Print topic Send  topic

Author Message
saseow

Posts: 735
Posted: 08/16/2014, 3:08 AM

Has anyone come up with a way of sorting groups in a report?
I don't think that it is possible but some guru may have found a way.
View profile  Send private message
MichaelMcDonald

Posts: 639
Posted: 08/19/2014, 2:30 PM

Hey Trevor - can you be a little more specific?

I did something once in the before show event where I created arrays and then exploded them into labels and used the gallery builder without a style and unlimited records per page to create a multi-column report layout. Doesn't have the native CCS function to render as printable but doesn't matter a whole lot and can always port through a pdf generator anyway....
_________________
Central Coast, NSW, Australia.

View profile  Send private message
saseow

Posts: 735
Posted: 08/19/2014, 7:03 PM

Hi Michael,
Thanks for the reply!
I have a company that supplies different types of vending machines to different locations. Each machine has a meter that records monies put in and a driver collects this cash weekly and enters the amount of $1, $5, credit card amounts etc. etc.
From this data I have a report that is grouped by location and then machine type that has totals for each machine and each location that shows fields for each of the money totals and also variences etc. between what the machine says was paid and what the driver said was paid. All good so far.
Now, the company wants to be able to sort the locations by the variences, coin amounts etc. and this is where the difficulty comes in.
I know that in SQL you can sort groups if you do a sort order first e.g.:
select * from (select * from sometable order by somefield) group by somefield but this does not seem to work in CCS report builder. I contacted support and they said use SQL as the data source which I had already tried but had no success.
In a nutshell, that is the problem.
View profile  Send private message
eratech


Posts: 513
Posted: 08/19/2014, 10:40 PM

saseow - it usually depends on the SQL server (ie: MS SQL, MySQL etc) but I have had queries that use the GROUP BY and the ORDER BY

I *think* in MSSQL (like 2005) it was a problem unless you did a 'SELECT TOP 100 PERCENT * FROM...' so your milage might vary.

Alternatively, I have used views to get the basic data and then done queries on the views, with the ordering there.

Eric


_________________
CCS 3/4/5 ASP Classic, VB.NET, PHP
Melbourne, Victoria, Australia
View profile  Send private message
saseow

Posts: 735
Posted: 08/19/2014, 11:41 PM

Thanks for the replies. I am using MySQL and there is definitely a problem using the report builder. I am going to try once more wigth a select of sub query but if that does not solve it, I have also going to resort to views or change the structure of one of the tables.
I believe that there also was a problem with one of the MySQL version but I can't remember which.
Anyway, thank you both for the input. I will post the solution here when/if I succeed.
View profile  Send private message
saseow

Posts: 735
Posted: 08/20/2014, 3:19 AM

I had to resort to creating views. There was just no other way to do this.
Thanks for all the suggestions Michael and Eric!
View profile  Send private message
MichaelMcDonald

Posts: 639
Posted: 08/20/2014, 3:41 AM

Yes there are limitations in the report builder.

Where I have had many tables with joins there seems to be a limit on how many tables can be queried that is unrelated to MySQL.

I have also generated reports where the SQL generated did not capture the ORDER BY or GROUP By as per the visual query builder.




_________________
Central Coast, NSW, Australia.

View profile  Send private message
saseow

Posts: 735
Posted: 08/20/2014, 3:50 AM

Yes, you are right Michael.
This particular setup has tons of joins, three of which are the same table for three different users. The report builder just had it uphill all the way and I guess that was a bit too much.
View profile  Send private message
eratech


Posts: 513
Posted: 08/21/2014, 5:26 AM

As much as software puts query builders in and abstracts away the database, I still find some hand-crafted SQL really handy to know. I love CASE statements for doing IF/THEN inside the query.

E
_________________
CCS 3/4/5 ASP Classic, VB.NET, PHP
Melbourne, Victoria, Australia
View profile  Send private message
saseow

Posts: 735
Posted: 08/21/2014, 6:29 AM

Yup, I agree Eric.
Like most of us I use Navicat for data admin and do nearly all SQL using that. Lots and lots of powerful stuff in straight SQL.
b.t.w., I don't know if any of you have to backup remote databases but instead of writing scripts and running them via a cron I have started using MySQLbackupftp and just let the machine handle the backups daily. Some of my clients have very sensitive data and will not rely on the hosts standard backup provided with their plan and are loath to let the data stay on a server that the host backs up to. I provide this service for them and backup encrypted DBs to shared folders in the cloud and they seem happy with that.
Thank you for all the feedback!
View profile  Send private message

Add new topic Subscribe to topic   


These are Community Forums for users to exchange information.
If you would like to obtain technical product help please visit http://support.yessoftware.com.

Internet Database

Visually create Web enabled database applications in minutes.
CodeCharge.com

Home   |    Search   |    Members   |    Register   |    Login


Powered by UltraApps Forum created with CodeCharge Studio
Copyright 2003-2004 by UltraApps.com  and YesSoftware, Inc.