Grouping On Count of Records

[ryanparks]

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:

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!

 

[pmax9999]

Hi ryanparks

Have a look a the Group Sort option. I think that will achieve what you need.

Hope this helps.

Cheers
Pete.

 

[ryanparks]

Thank you for the response. Unfortunately it looks as if all that allows me to do is sort the groups by the number of records but does not allow me to aggregate them together based on a number of records.

 

[pmax9999]

True, but you can specify the number of groups to show (Top N), and elect to combine the rest in one group or exclude them completely. I thought from your post you just wanted to combine those the groups beyond the top 3. Maybe I misunderstood.

The only other way to achieve what think it is that you are trying to do is to count the records within each group at the database level, ie by way of View, Stored Procedure, Command or SQL Expression so that the resultant counts are available to Crystal as another data field. These approaches are Database dependant and very difficult to deal with in a forum environment.

Maybe someone else can come up with a solution for you

Good luck.

Cheers

 

[lbass]

I have a solution, but it might depend upon the group level on which you want to do the combined summaries. If there are seven groups (NOT seven instances of one group), please identify which group you want to do the combinations in--Grp#1, Grp#7, or one of the intermediate groups?

-LB

 

[ryanparks]

pmax, the Top N almost worked but the N only specified the number of groups before combining, rather than specifying a quantity value from the field that counts the records. So close!

lbass, it is group #6 in the header that I am attempting to combine together. There are definitely 6 individual groups, not 7 instances of one group. Shoot, I can reorganize the report in the Footer, if that's how it has to be done...

Thanks guys! Really appreciate the responses.

 

[lbass]

I don't have time right now--will get back to you.

-LB

 

[lbass]

I'm assuming Group#6 is the innermost group, and that you have done a group sort on the grp#6 tab in the group sort expert on count of {table.grp#6field}, descending order.

Create a formula {@cnt} and place it in the group#6 header and suppress it:

whileprintingrecords;
numbervar grpno;
numbervar cnt;
numbervar sumr;
grpno := grpno + 1;
if count({table.grp#6field},{table.grp#6field})>=15 then //you could also use a parameter instead of 15 if you wanted the option to change your criterion
cnt := cnt + 1 else
cnt := 0;
if cnt =0 then
sumr := sumr + count({table.grp#6field},{table.grp#6field}) else
sumr := 0
;
cnt

Create a reset formula and place it in the Grp#5 Footer and suppress it:
whileprintingrecords;
numbervar grpno;
numbervar cnt;
numbervar sumr;
grpno := 0;
cnt := 0;
sumr := 0;

Go into the section expert and format Group #6 header to "suppress blank section". Then also for Group#6 header, go to suppress->x+2 and enter:

whileprintingrecords;
numbervar cnt;
numbervar grpno;
cnt=0 and
grpno<>distinctcount ({table.grp#6field},{table.grp#5field}) //be sure to notice the 'grp5' vs 'grp6' in this and following formulas

Then in preview mode, select {table.grp#6field}->right click->format->display string->x+2 and enter:

Whileprintingrecords;
numbervar grpno;
if grpno=distinctcount({table.grp#6field},{table.grp#5field}) then
"Others Combined" else
totext({table.grp#6field},0,"")

Then select the count field (in this example I inserted a count on the groupfield within the group by the same name, but maybe you are counting some other recurring field)->right click->format field->display string->x+2 and enter:

whileprintingrecords;
numbervar sumr;
numbervar grpno;
if grpno=distinctcount({table.grp#6field},{table.grp#5field}) then
totext(sumr,0,"") else
totext(count({table.grp#6field},{table.grp#6field}),0,"")

-LB

 

[ryanparks]

This is amazing!

Thank you soooo much....I feel like it's right there!

I am getting an error on this section:

Then in preview mode, select {table.grp#6field}->right click->format->display string->x+2 and enter:

Whileprintingrecords;
numbervar grpno;
if grpno=distinctcount({table.grp#6field},{table.grp#5field}) then
"Others Combined" else
totext({table.grp#6field}[highlight #FCE94F],0,""[/highlight])

"Too many arguments have been given to this function"


Any ideas as to why?

 

[ryanparks]

Okay, one step further. Now it's REALLY close! I had one of the formulas in a wrong area.

So it is properly combining the number of records based on >=15 per group, however the "combined" group totals are not adding up together....it appears as if it's taking the first combined group and displaying that specific group for the entire combined group. These original totals were using summary fields of the count of records and total revenue.

Your {@cnt} formula adds the total number of records correctly for the combined group, but the rest of the data is not following suit.

I'm guessing that I'm going to have to come up with another formula for a running total via "Whileprintingrecords" for the combined revenue total?

 

[lbass]

Regarding the first issue, the line that failed converts a number field to a text with 0 decimals (0) and no divider (""). If your table.grp#6field is NOT a number, but instead is text, you can remove totext() and the associated arguments.

On the second issue: If there are other fields to summarize in the combined row, yes, you would have to create additional variables and accumulate them. You would also need to add the new variables to your reset formula. You could change the {@cnt} formula, assuming revenue is a currency:

whileprintingrecords;
numbervar grpno;
numbervar cnt;
numbervar sumr;
currencyvar sumrev;
grpno := grpno + 1;
if count({table.grp#6field},{table.grp#6field})>=15 then //you could also use a parameter instead of 15 if you wanted the option to change your criterion
cnt := cnt + 1 else
cnt := 0;
if cnt =0 then
sumr := sumr + count({table.grp#6field},{table.grp#6field}) else
sumr := 0
;
if cnt =0 then
sumrev := sumrev + sum({table.revenue,{table.grp#6field}) else
sumrev := 0
;
cnt

Change reset formula to:

whileprintingrecords;
numbervar grpno;
numbervar cnt;
numbervar sumr;
currencyvar sumrev;
grpno := 0;
cnt := 0;
sumr := 0;
sumrev := 0;

Select the sum of revenue in the grp#6 header->right click->format field->display string and enter:

whileprintingrecords;
currencyvar sumrev;
numbervar grpno;
if grpno=distinctcount({table.grp#6field},{grp#5field}) then
totext(sumrev,2) else
totext(sum({table.revenue},{table.grp#6field}),2)

-LB