I am running across an issue that I cannot figure out. Basically, I have 7 grouping levels that have certain suppression criteria, but one of my groups needs to be consolidated based on the number of records found in that group. For instance:
Product----Insurance----State----# Records
E0676----Travelers----CA--------83
--------------------------CT--------26
--------------------------MD--------16
--------------------------NH---------8
--------------------------NJ---------3
--------------------------WI---------2
I would like to group the states in the header based on the numbers of records 15 or greater. If it is 14 or less, then group it into a group called "Combined" (instead of using the state abbreviation). It should look like this:
Product----Insurance----State-------# Records
E0676----Travelers------CA-------------83
----------------------------CT------------26
----------------------------MD------------16
----------------------------Combined----13
The problem is that the formula I used is using the Group as a reference to get the counts, and it cannot reference itself....meaning the formula is not showing up as a group-able option. For example:
I've read about running totals as the only way to do this, but I am not able to get it working (they only work in the footers).
In a nutshell, I need to get the state field to display the abbreviation if the count of that group is 15 or more, or display "Combined" if it is 14 or less.
Is there a way to do this?
Thanks for any help!
Product----Insurance----State----# Records
E0676----Travelers----CA--------83
--------------------------CT--------26
--------------------------MD--------16
--------------------------NH---------8
--------------------------NJ---------3
--------------------------WI---------2
I would like to group the states in the header based on the numbers of records 15 or greater. If it is 14 or less, then group it into a group called "Combined" (instead of using the state abbreviation). It should look like this:
Product----Insurance----State-------# Records
E0676----Travelers------CA-------------83
----------------------------CT------------26
----------------------------MD------------16
----------------------------Combined----13
The problem is that the formula I used is using the Group as a reference to get the counts, and it cannot reference itself....meaning the formula is not showing up as a group-able option. For example:
Code:
If Sum ({@If MC and $0}, {@State Combined}) < 15 //Think of "[u]Sum ({@If MC and $0}, {@State Combined})[/u]" as the count/sum of the total number of records in the group
Then "Combined"
Else {DPR_DME_Patient_Records.State_of_Service}
I've read about running totals as the only way to do this, but I am not able to get it working (they only work in the footers).
In a nutshell, I need to get the state field to display the abbreviation if the count of that group is 15 or more, or display "Combined" if it is 14 or less.
Is there a way to do this?
Thanks for any help!