Secondary (Additional but different) Group Count in Group Footer CR X1

[PWD]

How do I do this? I need to list ALL the ‘Service Groups’ in a report so I can have 3 different selections in a row and filter according to various criteria and have all the rows displayed for easy comparison purposes by my user.

So Column 1 will show all the ‘Service Groups’. Column 2 will show a Count of data based on various filter options. Column 3 will have different filter options and Column 4 will provide a Sum of the rows in Columns 2 & 3.

I started by creating a Group #1 based on the ‘Service Groups’ field from the database and inserting on the report. Check.

I created a Formula Field – ‘DistinctServiceGrp’ based on the required selection criteria – only records that are ‘Active’ and within certain dates. I put this in the ‘Details’ section but hid it. Check.

I created a second Group #2 based on this Formula Field ‘DistinctServiceGrp’ and hide/Suppress the Group Header #2: Check.

I created a Formula Field – ‘FilteredRecords’ based on the required selection criteria – only records that are ‘Active’ and within certain dates. Check.

I created a Summary – DistinctCount - based on the ‘FilteredRecords’ and placed this in Group Footer #2: Check.

When I preview the report I get 2 lines for the Group Footer #2: - ‘DistinctCount of @FilteredRecords (Number); I don’t know where the first value comes from (it is always 1 except for one Service Group that also has another extra line of 0!!) but the second looks correct – if I show the ‘Details’ this is the number of lines displayed. The first ‘DistinctCount’ is displayed before the ‘Details’ and the second (correct) comes after the ‘Details’. They are one and the same field as to change the font changes both figures.

I suspect the first ‘DistinctCount’ is something to do with the ‘Service Group’ Group #1 as it appears even when there are no ‘Details’ to show as that particular ‘Service Group’ has no data for the selected criteria.

If I place the ‘DistinctCount’ in Group Footer #1 it aggregates the two figures into one displayed figure.

All I want my user to see is the correct figure. Has anyone come across this before & know a sensible solution? Again, is it possible to do want I want to do?

Many thanks,

Des.

 

[Madawc]

Running totals can include formulas that mean they ignore some records. If you take time to learn what they can do, you'll find them very useful.

The use of Crystal's automated totals is outlined at FAQ767-6524.


Madawc Williams (East Anglia, UK). Using Crystal 11.5 with SQL and Windows XP

 

[lbass]

You don't need that inner group. After grouping on service groups, create conditional formulas, e.g.:

if {table.status} = "active" and
{table.date} in {?start} to {?end} then 1

Place formulas like these in the suppressed detail section and then right click on them and insert summaries on them at the group level. Drag the groupname into the group footer and suppress the group header.

If you have repeating records, you would need to use running totals instead, with a conditional formula in the evaluation section.

-LB