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 – ‘FilteredRecords’ 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 based on this Formula Field ‘FilteredRecords’ and hide/Suppress the Group Header #2: 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.
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 – ‘FilteredRecords’ 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 based on this Formula Field ‘FilteredRecords’ and hide/Suppress the Group Header #2: 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.