Summary of a Summary

[CPK6104]

I'm using product version 11.5

I've read thread after thread about how CR will not calculate a summary of a summary. But I'm still hopeful there is a solution to my problem be it a table, running total, sub report, or something else.

This is how the data appears on my report:

PR1949

IN19717​

IN19638​

IN20056​

PR1950

IN19687​

PR1951

IN19708​

The PR numbers are groups. So, group PR1949 has 3 records while the other two groups have 1 record each.
In the header of the report, I'd like to produce a summary of IN records that looks like this:

PRs with 1 IN: 2
PRs with 3 IN: 1

 

[BettyJ]

If IN numbers are records, try the following:

Insert a summary (count) of the IN numbers and place it in the group header of PR group.
[ul]
[li]Create a copy of the current report you have. (To be used as a subreport. So rename it - say Subreport.rpt)[/li]
[li]Suppress all sections in this report except the header section that has the PR numbers and the summary(count) and save it.[/li]
[li]Go to the first report(main report) and Insert -->Subreport and choose the existing Subreport.rpt and click the OK button. Place it in the Report Header.[/li]
[/ul]
You can format the subreports to get rid of the Borders if needed.

I believe I didn't miss anything.

Hope this helps.

 

[CPK6104]

Thank for the response BattyJ. I appreciate it. I followed your steps but didn't get my exact desired results. All I get at this point with the new subreport in the header of the main report is a huge list of PR numbers with counts for each group. It's prob my fault for not clearly explaining my desired end result.

In the header of the main report, I want users to know how many PR numbers (regardless of the actual numbers) have 1 IN record, 2 IN records, 3 IN records, etc. Going back to my original post.. the header would tally the results and look something like this:

Number of PRs with 1 IN Record: 2
Number of PRs with 2 IN Records: 0
Number of PRs with 3 IN Record: 1

Based on this data:

PR1949
...IN19717
...IN19638
...IN20056
PR1950
...IN19687
PR1951
...IN19708

You are correct about the levels:
PR - group
IN - record level

 

[hilfy]

This is going to be a challenge, no matter what route you take. If it were me, I would put a subreport in the report header for this. I would then use a command (SQL Select statement) to pre-count all of the data, using the same data as the main report. This command might look something like this:

Select
pr.prNumber,
count(distinct in.InvoiceNumber) as inCount
from PurchaseRequest pr
inner join Invoice in
on pr.prNumber = in.prNumber
where pr.date >= '12/1/2015' and pr.date < '1/1/2016'
group by pr.prNumber

You could then create a crosstab in the subreport that would have the inCount field as the rows and a single total column that counts the number of purchase requests. Once the crosstab is set up, you can modify the "group name" for the rows so that it will display your text.

-Dell


DecisionFirst Technologies - Seven-time SAP BusinessObjects Solution Partner of the Year

http://www.decisionfirst.com

 

[CPK6104]

The challenge seems so simple on the surface. I'm sure others have been sucked into this before. Thank you for the reply! I appreciate your help.

 

[hilfy]

Oh, and if you've never used commands before, see here for more info about how to work with them to get the best results:

http://scn.sap.com/community/crysta...ices-when-using-commands-with-crystal-reports

-Dell

DecisionFirst Technologies - Seven-time SAP BusinessObjects Solution Partner of the Year

http://www.decisionfirst.com

 

[BettyJ]

Sorry, I missed the last portion of your post that says:

In the header of the report, I'd like to produce a summary of IN records that looks like this:

PRs with 1 IN: 2
PRs with 3 IN: 1

If the data is
PR1949
...IN19717
...IN19638
...IN20056
PR1950
...IN19687
PR1951
...IN19708
then my previous post will display only

PR1949 3
PR1950 1
PR1951 1
in the report header.

I agree with hilfy's solution of using a command to generate the data for the subreport. Other than using cross-tab in the subreport,I believe grouping by count and then inserting a summary(count) of the PR numbers (and suppressing all other sections in the subreport except GH or GF where you are inserting the summary) will also generate the result you are looking for.