Problem with multiple groupings and summary totals

[Former Member]

Hello . .

I am new to this forum and could use some help.  I was asked to update a report with new SQL.  I updated the command file and checked the output.

I found that the report is grouping at three levels (Code, Incidents and Tracking Number).  The person who developed this report used a summary report and calculated the total distinct tracking numbers for the incidents per the code.

In doing this, the distinct count for the rows are correct.  However, the total records for the Incidents and Codes are incorrect.  The user then went down one more level and added percentages of the record total for the incident for the overall total for the Codes.

The problem with the report is the summary totals for the codes is incorrect and the percentage for each row of the codes / incidents are incorrect.

In reading some articles on-line, I found that distinct counts in crystal can cause issues. So to eliminate the count problem, I created formulas for the following:

- Row Totals for each unique Code / Incident based on the distinct tracking numbers.

- Group totals for each Code

- Grand Totals for all the records on the report.

These are all working fine now.

The formulas have been put in the group footer and report footers of the report.  The report is basically a summary report without and true detail.

Now I have been asked to put the percentage of the line totals for the group.

Reading the Crystal guide, it tells me that Crystal does it calculation in order of sections;  Report Header, Page Header, Group Header 1, Group header 2, Details, Group Footer 2, Group Footer 1, report footer for example.

With that said, the totals are being calculated for the row at a time and then the group total is created after.  To do the percentage of each row, would be kind of an after fact and I am stuck on how it could be done.

The following is a sample of what the report looks like:

Code      Incident     Counts     Percentage (trying to do)

100      Accident       75     Should be:  75%

           Injury             25     "               25%

           Final Check   10     "               10%

          No Issues       10     "               10%

100     Group Total   100

The codes and row counts are fine, I just need to figure a way IF I can do the percentages per row.

I would appreciate any feedback / help in this.

Jay

[abhilash_kumar · Accepted solution]

Hi Jay,

For the percentages you can create a formula like this:

(distinctcount({database_field}, {Group Field}) / Sum({database_field}, {Group Field})) * 100

Replace {Group Field} with the Name of the field used to group the report. I guess in this case this would be the Incidents group?

Similarly the {Group Field} in the denominator part of the formula would be a higher level group.

-Abhilash

[Former Member · Accepted solution]

Abhilash:

Thanks for the response . .

I have tried the following:

(DistinctCount({Command.TRACKING_NUMBER},GroupName ({Command.INCIDENT_HEADER_TYPE_CD})) / Count({Command.TRACKING_NUMBER},GroupName ({Command.INCIDENT_HEADER_TYPE_CD})))*100

When I try to validate the new formula Row Percentage, I get the following error message:

This field can not be used as a group condition field.

Let me know what I am doing wrong.

Thanks !

Jay

Crystal Fields.txt.zip

[Former Member · Accepted solution]

Sorry . .

The following field is the one pointed to:

GroupName ({Command.INCIDENT_HEADER_TYPE_CD}

Jay

[abhilash_kumar · Accepted solution]

Try this:

(DistinctCount({Command.TRACKING_NUMBER},{Command.INCIDENT_HEADER_TYPE_CD}) / Count({Command.TRACKING_NUMBER},{Command.INCIDENT_HEADER_TYPE_CD}))*100

P.S: You can't use "GroupName ({Command.INCIDENT_HEADER_TYPE_CD}" inside an Aggregate function

-Abhilash

[Former Member · Accepted solution]

Abhilash:

Thanks for the update . .

I changed the first part:

DistinctCount({Command.TRACKING_NUMBER},{Command.REPORT_LABEL})

That is giving me the correct totals for the row.

I am having troubles with the group total still.

I have tried count and distinctcount on a number of combinations.  The first group should be based on Incident Header Type Cd, Report Label Distinct (tracking numbers).

Let know if you have any other ideas.  I will play more and see what I can come up with.

[abhilash_kumar · Accepted solution]

Hi Jay,

To get the Subtotal of the Group above the {Report_Label} group, try this formula:

Count({Command.TRACKING_NUMBER},{Group_above_report_label})

Place this formula in the Group Header/Footer of the Group and see if you get the right counts for each Group. Once you know the counts are right, use it in the formula to divide and find the percents.

P.S: Would you be able to attach the report with saved data in case you're having trouble with it? To attach the report, change the extension of the report from .rpt to .txt and then click on the 'Use advanced editor' option to attach. Make sure you save the report 'With data'.

-Abhilash

[Former Member · Accepted solution]

Abhilash:

I tried that and the group total I would expect is 18,810, the total that came back was 30,401

I left the total in the group footer line to show you.

I really . .appreciate the help.

I have saved the report and renamed it to .txt for you with data.

Look forward in hearing from you again.

Thanks !

Jay

Test - Copy.txt.zip

[abhilash_kumar · Accepted solution]

OK. I see what's happening!

The 18,810 is actually the 'Sum of Distinct Count' and as you may have already realized, you can calculate this only using a formula. And then this is the value that we need to access in the inner groups, however we cannot because the 'evaluation times' of the Distinct Count and the formula (you have created) are different.

In certain situations though, the Distinct Count shows up correctly at all levels when there are no repeating values in any of the groups.

What you need to do is, find out a way to access the value that is evaluated 'later' and use it to divide each distinct count. One way to do that is to use a Subreport. I've updated your report and attached it for your reference.

Here's what has been done:

1) The same report was added as a Subreport. The job of this Subreport is to calculated the Sum of Distinct Count of Tracking Number for each group

2) The values are passed back to the Main report in an array. The array variable increments for each group and uses the value at the same index to find the percent.

3) There is also another formula at the Group Header 1 to initialize a variable I use inside the percentage formula

4) The Subreport itself is hidden from sight. It is placed in the Report Header a is as big as a dot.

Hope this helps!

P.S: The report is now unfortunately too large to be attached to this forum. If you don't mind to provide your e-mail address, I can e-mail it to you.

-Abhilash

[Former Member · Accepted solution]

Abhilash:

Wow . .

I never would have thought about the subreport for this.

I look forward in seeing the report.

My e-mail is jayjorgensen@maximus.com

I will check it out once you send it to me and I most likely will have questions since I haven't done subreports before.

Thanks and look forward in seeing the updated report.

Jay

[abhilash_kumar · Accepted solution]

I sent the file yesterday. I hope the solution works for you.

-Abhilash

[Former Member · Accepted solution]

Abhilash:

Thank you so much for the help!  It works great and you gave me the insight to subreports.

I have been doing Crystal for a while now, but never ran into the need for subreports.  I will be reading and learning more about them, since they could become very useful, especially in this instance when I am doing running totals and need to create other calculations based on the numbers that are totaled later on, that I need sooner.  I hope that makes sense?

I really do appreciate all the help and the quick follow-up.

Have a great day and nice weekend!

Jay

[abhilash_kumar · Accepted solution]

Glad I could help Jay!

And, you're spot on with what you just said - "numbers that are totaled later on, that I need sooner"; nice way to put it accross!

Have a great day.

-Abhilash