saseow
Posts: 744
|
| 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.
|
 |
 |
MichaelMcDonald
Posts: 640
|
| 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.
|
|
|
|
saseow
Posts: 744
|
| 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.
|
|
|
|
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 |
|
|
|
saseow
Posts: 744
|
| 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.
|
|
|
|
saseow
Posts: 744
|
| 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!
|
|
|
|
MichaelMcDonald
Posts: 640
|
| 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.
|
|
|
|
saseow
Posts: 744
|
| 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.
|
|
|
|
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 |
|
|
|
saseow
Posts: 744
|
| 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!
|
|
|
|
