How to un-group aggregate data
Printed From: Crystal Reports Book
Category: General Information
Forum Name: Announcements
Forum Discription: Please check this section for the latest announcements from Crystal Reports Forum
URL: http://www.crystalreportsbook.com/forum/forum_posts.asp?TID=17619
Printed Date: 05 May 2024 at 3:49am
Topic: How to un-group aggregate data
Posted By: Jenny
Subject: How to un-group aggregate data
Date 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. |
Replies:
Posted By: hilfy
Date 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 ------------- Proviti, Data & Analytics Practice http://www.protiviti.com/US-en/data-management-advanced-analytics - www.protiviti.com/US-en/data-management-advanced-analytics |
Posted By: Jenny
Date 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 |
Posted By: hilfy
Date 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 ------------- Proviti, Data & Analytics Practice http://www.protiviti.com/US-en/data-management-advanced-analytics - www.protiviti.com/US-en/data-management-advanced-analytics |
Posted By: Jenny
Date 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! |
Posted By: hilfy
Date 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 ------------- Proviti, Data & Analytics Practice http://www.protiviti.com/US-en/data-management-advanced-analytics - www.protiviti.com/US-en/data-management-advanced-analytics |
Posted By: Jenny
Date 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. |
Posted By: hilfy
Date 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 ------------- Proviti, Data & Analytics Practice http://www.protiviti.com/US-en/data-management-advanced-analytics - www.protiviti.com/US-en/data-management-advanced-analytics |