Make Summary Report from Existing Subreports

[Freefall27]

I am running CR XI and pulling data by Oracle ODBC and am relatively new to the report writer. Here is what I am trying to accomplish:

I have a report that consists of 6 subreports that show detail of six different stages of orders based on select expert criteria. Each report list the following fields with totals by each category.

Report Layout Example (Detail):

Report 1

PH Name Type Date ID ID_Name Location

GH Support

D WO126 Net 11/01/05 123 Comm1 NY
D WO128 Net 11/11/05 123 Comm2 NY

RF Total 2

GH Contact

D WO129 Net 11/21/05 123 Comm3 NY
D WO121 Net 11/19/05 123 Comm3 NY
D WO122 Net 11/20/05 123 Comm3 NY

RF Total 3

Report 2

PH Name Type Date ID ID_Name Location

GH Support

D WO126 Net 11/01/05 123 Comm1 NY
D WO128 Net 11/11/05 123 Comm2 NY
D WO130 Net 11/11/05 123 Comm4 NY

RF Total 3

GH Contact

D WO129 Net 11/21/05 123 Comm3 NY
D WO121 Net 11/19/05 123 Comm3 NY
D WO122 Net 11/20/05 123 Comm3 NY

RF Total 3

Etc……


Can I pull the total field from each report by category and place on a summary page?

Report Layout Example (Summary):

Report 1

Support 2
Contact 3

Report 1 Total 5


Report 2

Support 3
Contact 3

Report 2 Total 6

 

[synapsevampire]

You can pass the values back to the main report for display in the report footer:

whileprintingrecords;
shared variable Sub1num:=sum({table.field});

Then in the report footer use a formula to display:

whileprintingrecords;
shared variable Sub1num

I would question why you need so many subreports, note that you could accomplish this SQL in a SP or View on the database, or use a Crystal Command Object to get around using subreports, they're very slow.

-k

 

[Freefall27]

I have to distribute the sub reports separately to a listing of recipients and that is why they are all needed I guess? As I said I am new to the report writer and not sure of the best ways to accomplish things yet. If there is a better way than all the sub reports let me know.

Can you please expand on your solution to incorporate into a summary report? Do I have to place a new report into the main report where all the sub reports are located? I created this report based on reference of books because I was just not sure how to construct. Right now I have a report, which I placed 6 sub reports into, and print all at once. Should I just do another sub report with your fields and formula above? Thanks for the help.

 

[synapsevampire]

Place the shared variables in your subreports, and then in the MAIN report footer display the summaries.

In general posts are best served by icluding technical information if you intend optimal performance:

Example data (show tables and columns and data)
Expected output

You showed existing output from the subreports as example data.

For optimizing you might use a UNION ALL query to pull everything into one report to provide the summary.

As long as the reports are all in the report footers, then your performance isn't horrendous, it would just be faster to use a single query.

-k

 

[lbass]

To add to SV's comments, for your subreport formulas, you would need to write formulas like:

//{@support}:
whileprintingrecords;
shared numbervar support;

if {table.groupfield} = "Support" then
support := count({table.name},{table.groupfield});

//{@contact}:
whileprintingrecords;
shared numbervar contact;

if {table.groupfield} = "Contact" then
contact := count({table.name},{table.groupfield});

You would place these on your subreport.

Then in the main report footer, use formulas like:
//{@displsupport}:
whileprintingrecords;
shared numbervar support;

//{@displcontact}:
whileprintingrecords;
shared numbervar contact;

//{@displsum}:
whileprintingrecords;
shared numbervar support;
shared numbervar contact;
support + contact

//etc.

-LB

 

[Freefall27]

I may need a little more support on this one. I am new to the program and this is not sinking in all the way. I will provide some fields and then maybe I can understand where they all fit into the formula as provided.

Summarize each from subreports:

Group Total = Distinct Count of Orders
(May be 5 or 6 different groups, which are separated and need to be displayed)

Group A 2
Group B 3
Group C 1


Overall Total = Distinct Count of All Orders
(Total of all group Types)

Total 6

Take these results for each report and place on to 1 summary report. I need clarification on what to put in formula whileprinting records and if to place in both summary and subreports? Lost at this point and need to finish this up as soon as I can. Please let me know if additional information is needed.

 

[lbass]

Can we back up a little? I'm not sure you should be using subreports either. What distinguishes the reports from each other? If each subreport is simply collecting the same information for different IDs, for example, then instead of using subreports, insert a Group #1 on ID, Group #2 on {table.stage}, and then use {table.OrderID} in the detail section. You can then right click on {table.OrderID} and insert summaries at each group level. Then you can go into the section expert and hgihlight the ID group footer -> "New Page After"->x+2 and enter:

not onlastrecord

This will give separate sections for each ID. No need for all the shared variables.

Then you can insert a crosstab in the report footer where you use ID and stage as the row field, and distinctcount of Order ID as the summary. This will give you a quick summary across all IDs.

-LB