Group Supression based on detailed running total

[ramblinrose]

I am using CRXI and have a question regarding supression of a group based on a comparison running total in the detail line.

The data I have on the report that I am analyzing is a number field ({BI_DTL.PAY_MNTH}) that represents year month and is as follows:

BI_DTL.PAY_MNTH
200608
200609
200610
200611
200612
200701
200702
200705
200706

My goal is to analyze each detail record to see if their are 6 consectutive paymonths. I have created a running total counting each record based on the following formula: {BI_DTL.PAY_MNTH} - previous ({BI_DTL.PAY_MNTH}) in [1, 89].

Reset is based on the formula: {BI_DTL.PAY_MNTH} - previous ({BI_DTL.PAY_MNTH}) <> [1, 89]

So my detail results are as follows:

BI_DTL.PAY_MNTH RT
200608 0
200609 1
200610 2
200611 3
200612 4
200701 5
200704 0
200705 1
200706 2

I want to supress the groups based on the maximum RT total for the lowest group < 5.

Of course I am getting the message that the RT field can not be evaulated.

Is there another way I can accomplish this.

Thank you.

 

[lbass]

What field are you grouping on? It is unclear what you mean by

the maximum RT total for the lowest group < 5

. Please clarify.

-LB

 

[ramblinrose]

I actually have 3 groups.

Group 1 = Office
Group 2 = Caseload Name (employee of the office)
Group 3 = Case ID (client of the employee)

The details capture each month that a client receives benefits. The focus is to only include Groups that include Clients that have received 6 consecutive benefits. So if a client does not have a running total of at least 6 then suppress the entire group. I have the running total resetting if a month is skipped. For example if a client receives benefits Jan through May and then skips June and then receives benefits in July, the running total for July would start over and be 0.

Thank you for looking at this.

 

[lbass]

Suppress the entire client group?

-LB

 

[ramblinrose]

Yes, that is correct. We are not interested in seeing clients that have received less than 6 consecutive payments.

 

[lbass]

You will have to save the main report as a subreport, and then insert it in a client group header #3a section, with the regular group header info in GH#3b. The sub should be linked on each of the three group fields. In the main report, create a formula:

//{@reset} to be placed in the GF#3:
whileprintingrecords;
shared numbervar maxrt := 0;

In the sub, create a formula:

//{@accum} to be placed in the sub detail section:
whileprintingrecords;
shared numbervar maxrt;
if {#yourrt} > maxrt then
maxrt := {#yourrt};

//{@finalresult} to be placed in the sub report footer:
whileprintingrecords;
shared numbervar maxrt;

Then suppress all sections within the subreport, remove the subreport borders, and format the subreport (format subreport->subreport tab->suppress blank subreport), and also go into the section expert of the main report->GH#3a->suppress blank section. Section GH#3a will now not appear. Then still in the main report section expert, select the GH#3b (and then repeat for details and GF#3)->suppress->x+2 and enter:

whileprintingrecords;
shared numbervar maxrt;
maxrt < 5

-LB