Skip to Content
author's profile photo Former Member
Former Member

Problem with multiple groupings and summary totals

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

Add a comment
10|10000 characters needed characters exceeded

Assigned Tags

Related questions

1 Answer

  • Best Answer
    Posted on May 22, 2013 at 04:46 PM

    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

    Add a comment
    10|10000 characters needed characters exceeded

Before answering

You should only submit an answer when you are proposing a solution to the poster's problem. If you want the poster to clarify the question or provide more information, please leave a comment instead, requesting additional details. When answering, please include specifics, such as step-by-step instructions, context for the solution, and links to useful resources. Also, please make sure that you answer complies with our Rules of Engagement.
You must be Logged in to submit an answer.

Up to 10 attachments (including images) can be used with a maximum of 1.0 MB each and 10.5 MB total.