Subtotal on every group code

[Former Member]

Morning all,

I have created a report which is grouped by Reason Code (A numbering system 01- 999). This report produces Total Jobs, Total Lenses and their averages.

There is one slight addition I would like to add into the report.

I would like to create a formula so that report sub totals each record according to the code range.

For example:

When producing report for all codes (01- 999) It should produce results like this.

01 to 99

Sub Total

100 to 199

Sub total

200 - 299

Subtotal

and so on (upto 999).

Then Grand total of everything.

Any ideas how to achieve something like this?

Report Layout:

Grouped by - Reason code 01- 999

Group Header = Reason Code and Reason Description

Group Footer = Total Jobs, Total Lenses, Total Average

Report Footer = Total of Total Jobs, Total of Total Lenses and avg

Currently report when produced, it produces results like this

01 - 999

Total lenses, jobs and averages.

Hence there is no sub division between code ranges.

Could someone advise me on this please?

Many thanks

Regards

Jehanzeb

[Former Member · Accepted solution]

Hi Jehanzeb,

This can be done using a formula :

If Reason Code >= 0 and Reason Code <=99

Then "0 - 99"

Else If Reason Code >= 100 and Reason Code <=199

Then "100 - 199"

Else If Reason Code >=200 and Reason Code <=299

Then "200 - 299"

Else.........so on

Insert group based on this formula then you will get the required results.

Then you can insert group totals.

Hope this will resolve the issue.

Thanks,

Sastry

[Former Member · Accepted solution]

Thanks for your quick reply Sastry,

I have tried your method but it says a string is required here. I suspect that I am using Group Name and that is the reason why it is showing that message. Here is how I designed the formula

I named the formula "grouping"

Then entered the following code:

If GroupName ({lab_reasons.reason_code}) >= 1 and
GroupName({lab_reasons.reason_code}) <=99
then "01 - 99"
Else If GroupName ({lab_reasons.reason_code}) >= 100 and
GroupName ({lab_reasons.reason_code}) <=199
Then "100 - 199"
Else If GroupName ({lab_reasons.reason_code}) >=200 and
GroupName ({lab_reasons.reason_code}) <=299
Then "200 - 299"
Else If GroupName ({lab_reasons.reason_code}) >= 300 and
GroupName ({lab_reasons.reason_code}) <=399
Then "300 - 399"
Else If GroupName ({lab_reasons.reason_code}) >= 400 and
GroupName ({lab_reasons.reason_code}) <=499
Then "400 - 499"
Else If GroupName ({lab_reasons.reason_code}) >=500 and
GroupName ({lab_reasons.reason_code}) <=599
Then "500 - 599"
Else If GroupName ({lab_reasons.reason_code}) >= 600 and
GroupName ({lab_reasons.reason_code}) <=699
Then "600 - 699"
Else If GroupName ({lab_reasons.reason_code}) >= 700 and
GroupName ({lab_reasons.reason_code}) <=799
Then "700 - 799"
Else If GroupName ({lab_reasons.reason_code}) >=800 and
GroupName ({lab_reasons.reason_code}) <=899
Then "800 - 899"
Else If GroupName ({lab_reasons.reason_code}) >= 900 and
GroupName ({lab_reasons.reason_code}) <=999
Then "900 - 999";

I think I am doing it incorrectly. Could you please put some light on this.

Regards

jehanzeb

[Former Member · Accepted solution]

Don't reference the group name in the formula, just the field itself. I also like to use the Select..Case structure when I can as it makes the code much easier to follow and debug:

select {lab_reasons.reason_code}
 case is <= 99	: "01 - 99"
 case is <= 199	: "100 - 199"
 case is <= 299	: "200 - 299"
 case is <= 399	: "300 - 399"
 case is <= 499	: "400 - 499"
 case is <= 599	: "500 - 599"
 case is <= 699	: "600 - 699"
 case is <= 799	: "700 - 799"
 case is <= 899	: "800 - 899"
 case is <= 999	: "900 - 999";

[Former Member · Accepted solution]

Brian,

This is great and it works too however my question stays the same.

I can now show that the following code range is within x code range however how can I do the subtotal of them.

Initially what I wanted is to do subtotal on each reason code. So if the report produces reason code from 1-99 then it should show

Reason Code - Description - Jobs - Lenses - Averages
01                   xxxxx            xx          xx           xx
02                   xxxxx            xx          xx           xx
03
100
123
134
199                xxxxxxx         xx          xx           xx

SubTotal ----                         300  -      400      -   23

200
201
222
234
253
299

Subtotal ------                     400         600

So the whole report shows the subtotal on each group name.

I think I make more sense now?

Regards

Jehanzeb

[Former Member · Accepted solution]

So if I am understanding this correctly, you are going to need to add a second grouping.

Group 1 is on the formula that we already worked on. Now, insert a new group on the reason code field itself. That should give you the next level that you want to show subtotals on.

[Former Member · Accepted solution]

Insert new group and insert summary to get sub totals.

Thanks,

Sastry

[Former Member · Accepted solution]

I got it now.

[Former Member · Accepted solution]

Thats exactly what I did.

It is working now, all I have to do is to create 8 new formulas which will count the sub total's total.

I have a grand total sorted as well.

Thanks all of you!

Regards

Jehanzeb