CR 10, questions about report grouping summary

[TedLiu]

Hi,

I'm working with Oracle 9i and CR 10. I have a report need to display data as below:

detail:
1st QTR : Num1, Num2
2nd QTR : Num3, Num4
3nd QTR : Num5, Num6

group1:
1st QTR : sum(Num1), sum(Num2)
2nd QTR : sum(Num3), sum(Num4)
3nd QTR : sum(Num5), sum(Num6)

group2:
1st QTR : sum(Num1), sum(Num2)
2nd QTR : sum(Num3), sum(Num4)
3nd QTR : sum(Num5), sum(Num6)

...

I implemented this report with subreports in each group to retreive summary data. I just wonder if there is a better way for it without using subreport because the subreport is meant to be slow especially you have lots of data and 3 or 4 level grouping.

I appreciate any suggestions.

Thanks,

Ted

 

[lbass]

I'm not sure this would improve the speed, but if you want to show all the detail information first for all groups, then you could show that in a subreport in the report header. Then in the main report, insert two groups, first your outer group (you don't identify this) and then on your date field, quarterly. Then insert summaries on your detail fields, suppress the details, and drag the group names into the group footers.

This assumes that by "Group 1" and "Group 2" above you mean two different instances of the same group. I made this assumption since, if these were separate groups, you would expect to see multiple instances of group #2 summaries nested within group #1.

Another approach might be to use no subreports, and instead of suppressing the details in the main report, hide them so that they are available upon drilldown. However, the details would be limited to those within each group.

-LB

 

[TedLiu]

Hi LB,

Thanks for your quick reply. I did exactly like what you said insert subreport to each group. The main report contains up to 7 level groups.

My data structure is :

row 1: company, program, ..., 1 QTR, num1, num2
row 2: company, program, ..., 2 QIR, num3, num4
row 3: company, program, ..., 3 QTR, num5, num6


The heirarchy of grouping in main report as below:
grand total,
company total,
program total,
risk state total,
city total,
county total

and each group needs to display each quarter summary data. The report will generate 4 different report outputs base on parameters. I did not know I have so many grouping levels when I used subreport to implement it at the beginning. As the legacy sample report i have, it may have 2000 pages for one report. I'm ok with it if subreport will run hundred times, but I really doubt and have no experience if subreports need to run thousand times.

Another way I can think is to have all the quarterly data in one row, like: 1 QTR, num1, num2, 2 QTR, num3, num4, 3 QTR, num5, num6,... I think I need to look around first before I jump into it because I have like 15 columns data for each QTR which means I may create a table to contain up to 80 columns.

Any suggestions?

Thanks,

Ted

 

[lbass]

I didn't suggest inserting subreports for each group. Can you verify whether you have to display the detail records at all? Did you really mean you want to show all details first in the report?

It looks to me like all you need to do for the summaries is to add a group on {table.quarter} and then right click on each field and insert summaries (sums) on each field, selecting "insert summaries for all groups" and "insert grand total". Then suppress the details section.

You also might see if a crosstab would meet your needs. You could use {table.quarter} for your column field, and then add company, program, risk state, county, city as your row fields. Then insert whatever fields you want to summarize as your summary fields. Place the crosstab in the report header or footer.

Your groups don't look like they are in the correct order (although maybe I'm missing something). Shouldn't the order of the geographical groups be state->county->city?

-LB

 

[TedLiu]

Thanks, LB

Quarter data need to be in detail section and it will be the lowest level group too. The report data is summarized already base on grouping levels and Quarter number.

Insert summary data in each group is easy, but how I can get quarterly summary data. for instance, right now, it's 4th Quarter, I need to display all 4 quarter summary data in each group level like the format from my initial question, the Quarter data in detail section is loop displayed but not physical columns. num1, num3, num5 and num2, num4, num6 are only 2 columns with loop, Maybe I made it mislead you about that.

Cross-tab can't get me the data too unless I made up columns for all detail section data.

State-County-city or State-city-county is not really important, which is only from user's requirement.

Thanks,

Ted

 

[lbass]

...the Quarter data in detail section is loop displayed but not physical columns. num1, num3, num5 and num2, num4, num6 are only 2 columns with loop...

That was really important information that you left out. I'm not sure I can help, but I think that before anyone can help, you need to share how you are creating the quarterly detail columns. You might want to also show samples of your raw data before creating these detail columns--maybe there's another approach.

-LB

 

[TedLiu]

Thanks LB,

The only way I can think is to have all the quarterly data in physical columns, and then I can do insert summary after that.

Creating quarterly data is pretty straightforward:

1. created a report table.

2. generate and insert quarterly summary data to the report table from daily transaction data base on required grouping level and quarter number.

3. generate and insert each quarterly records to the report table with 0 amount because I have to display a quarterly record although there is not transaction happened in that quarter.

4. create a ref_cursor to have the quarterly summary data from report table base on the grouping level.

the data will be like below:

row 1: company, program, ..., 1 QTR, num1, num2
row 2: company, program, ..., 2 QIR, num3, num4
row 3: company, program, ..., 3 QTR, num5, num6

I think I should consider union statement to regenerate report data.

Ted