Announcements
 Crystal Reports Forum : General Information : Announcements
Message Icon Topic: How to un-group aggregate data Post Reply Post New Topic
Author Message
Jenny
Newbie
Newbie


Joined: 11 Jun 2009
Online Status: Offline
Posts: 15
Quote Jenny Replybullet Topic: How to un-group aggregate data
    Posted: 25 Sep 2012 at 5:03am
I have a county report that has 5 subreports. Each report uses the same parameters (frommonth, fromyear, tomonth, toyear). The report produces an aggregate total of data.

I need the report to run each county's data broken out showing only their information on the page, and then the next county on the next page, etc.

Based on a date range, I get grand totals of all county info,   Example, # of County tickets = 9,000. (this 9,000 is total of all counties added together). This information is in a subreport.

How can I break out the 9,000? Example Bexar County = 450, Cooke County = 1,000, Harris County = 8,550.

The remaining subreports bring in other informtion for the report, again as an aggregate (the sum).
So, I'll need to break out the date per county based on the same data range.

Any assistance is appreciated.
IP IP Logged
hilfy
Admin Group
Admin Group
Avatar

Joined: 20 Nov 2006
Online Status: Offline
Posts: 3701
Quote hilfy Replybullet Posted: 27 Sep 2012 at 3:31am
So, if I understand you correctly, you have a different number in each subreport that is aggregated by adding together the numbers for all of the counties.  Is that correct? 
Ultimately, how do you want to see the data?  All of the numbers for each county and then move to the next county?  Or all of the counties for each number and then move to the next number?
 
-Dell
IP IP Logged
Jenny
Newbie
Newbie


Joined: 11 Jun 2009
Online Status: Offline
Posts: 15
Quote Jenny Replybullet Posted: 30 Sep 2012 at 8:15pm
I want to see all the data for one county and then move on to the next county.

I want each subreport to produce the data together as one report of data per county.
Right now subreport 1 brings in county population, subreport 2 brings in county courthouses, subreport 3 brings in county tickets, and etc though the 5h subreport. But the data in each subreport is one grand total for all counties combined.

How do I have the data in each subreport come together instead of separately, so that I have County #1: 45,000 population, 2 courthouses and 1,00 county tickets.
County #2: 32,000 population, 1 courthouse and 1,200 county tickets,
County #3 -------
County #4 ---------
etc.

Any assistance is appreceiated. Thanks
IP IP Logged
hilfy
Admin Group
Admin Group
Avatar

Joined: 20 Nov 2006
Online Status: Offline
Posts: 3701
Quote hilfy Replybullet Posted: 01 Oct 2012 at 3:12am
1. Group your main report by county. 
2. Put each of the subreports in either a county group header or footer.
3. Suppress the details section in the main report.
4. Set up the subreport links for each subreport so that you're passing in the county from the main report and the subreport is filtered based on that.
 
Depending on the number of counties included, this report may be slow to process - each time a subreport is called, it opens a separate connection to the database and then runs its query.  So, where your original report ran each subreport once, the new version will run each subreport times the number of counties on the report. 
 
-Dell
IP IP Logged
Jenny
Newbie
Newbie


Joined: 11 Jun 2009
Online Status: Offline
Posts: 15
Quote Jenny Replybullet Posted: 01 Oct 2012 at 4:15am
This is great, I can't wait to try it. However, I'm in Texas and we have 254 counties, I may need 24hrs to run this report.

Is there a way to break up the report, say run county_Id < 100 and then county_id >100? I use county_id because each county has a county_id in the database.



Thanks so much!
IP IP Logged
hilfy
Admin Group
Admin Group
Avatar

Joined: 20 Nov 2006
Online Status: Offline
Posts: 3701
Quote hilfy Replybullet Posted: 01 Oct 2012 at 4:25am
I would test with a smaller number of counties first - you can either hard-code a filter into the main report  or use a parameter in the main report to set the filter for testing.
 
Another option would be to create a stored procedure in the database or a command (SQL Select statement) in the report and use that instead of individual tables to pull all of the numbers for your report instead of using subreport.  You would have to have fairly good SQL skills for either of these options, though.
 
-Dell
IP IP Logged
Jenny
Newbie
Newbie


Joined: 11 Jun 2009
Online Status: Offline
Posts: 15
Quote Jenny Replybullet Posted: 01 Oct 2012 at 11:27am
My skills are not up to par for either the stored procedure or the sql statement.

And unfortunately, linking on the group isn't working. When I group the main report, it is fine,but when I link the subreports to the county_id, I'm not getting all the data.
Do you know of any direct support I could explore? Getting desparate, deadline has passed!
thanks.
IP IP Logged
hilfy
Admin Group
Admin Group
Avatar

Joined: 20 Nov 2006
Online Status: Offline
Posts: 3701
Quote hilfy Replybullet Posted: 03 Oct 2012 at 3:38am

I would check the links in the subreports - in each subreport, go to the Select Expert and see how the "pm_" parameter is connected to the data.

 
-Dell
IP IP Logged
Post Reply Post New Topic
Printable version Printable version

Forum Jump
You cannot post new topics in this forum
You cannot reply to topics in this forum
You cannot delete your posts in this forum
You cannot edit your posts in this forum
You cannot create polls in this forum
You cannot vote in polls in this forum



This page was generated in 0.047 seconds.