6 Replies Latest reply: Aug 5, 2015 6:39 PM by Jean-Pierre Clermont

# How to do subtotal in a report

Hi

I have a a Excel file we produce. The user wants us to had a subtotal by Gl Code. The file as 5 tabs. Same fields in every tabs just different filters. I know how to had grand totals but how do you do for every value of the gl code.

I have an other question. Is there any way to do running totals in Monarch. It would be nice to have a function that give you access to the previous value of a field with date you could do running totals.

Thank you .

JP

• ###### Re: How to do subtotal in a report

Hi JP, you can add subtotals by checking the option "Insert subtotal line" from Summary Design > Keys(GI Code) > Layout > Insert subtotal line.

Regarding running totals in Monarch, can you give more information of what function you needed?

Regards,

• ###### Re: How to do subtotal in a report

I have attached a response I gave to Lovely :

That is what I mean. We  would  like do that for any of the sort field that is resting the sum. You sum for example by company and by department.

We have Monarch 11.5. I do not see Function CUM() it. I have found a convoluted way of doing it as follow:

You could have a field define using the record number (File in order needed). Then you have a field with record number + 1 (Fieldp1) an another with record number – 1(Fieldm1). Then you do a look up to fieldp1 and an another using FieldM1 to the record number field.

You get access to the previous and next value of whatever field you want in the host lookup file.

It’s a sort of recursive join.

Thank You

JP

Jean-Pierre Clermont

SOFTWARE ENGINEER

BANK OPS&TRANSACTIONAL BANKING

MUFG Union Bank, N.A.

Monterey Park, CA 91755

T: +1-323-720-2903

JeanPierre.Clermont@Unionbank.com

A member of MUFG, a global financial group

• ###### Re: How to do subtotal in a report

Hi JP,

If I understood you correctly, you want something like this:

Row Date Value RunningTotal

1      July1  20    20

2      July2  40    60

3      July3  60    120

Is this right? If yes, then you can use the Cumulative function of Monarch, Cum().

Hope this helps.

Thanks,

Lovely Gonzaga

• ###### Re: How to do subtotal in a report

Lovely

That is what I mean. We  would  like do that for any of the sort field that is resting the sum. You sum for example by company and by department.

We have Monarch 11.5. I do not see Function CUM() it. I have found a convoluted way of doing it as follow:

You could have a field define using the record number (File in order needed). Then you have a field with record number + 1 (Fieldp1) an another with record number – 1(Fieldm1). Then you do a look up to fieldp1 and an another using FieldM1 to the record number field and you get the previous and next value of whatever field you want in the lookup file that is the file working from.

It’s a sort of recursive join.

Thank You

JP

I believe it should work.

Thank you

JP

Jean-Pierre Clermont

SOFTWARE ENGINEER

BANK OPS&TRANSACTIONAL BANKING

MUFG Union Bank, N.A.

Monterey Park, CA 91755

T: +1-323-720-2903

JeanPierre.Clermont@Unionbank.com

A member of MUFG, a global financial group

• ###### Re: How to do subtotal in a report

I use subtotals quite often.  The CUM function is in Summaries.  I use version 10.5 and 12 and both have it.  You can certainly have subtotals by multiple keys like in your example by company and by department.  There are multiple aggregation functions that are available in Summaries.

• ###### Re: How to do subtotal in a report

Joe

Thank you

JP

Jean-Pierre Clermont

SOFTWARE ENGINEER

BANK OPS&TRANSACTIONAL BANKING

MUFG Union Bank, N.A.

Monterey Park, CA 91755

T: +1-323-720-2903

JeanPierre.Clermont@Unionbank.com

A member of MUFG, a global financial group