How to display a subtotal on a report footer ?

[alpha_sow]

Hello

  I have a report that has

report header

page header

Group header #1 a, on this (group header # 1a) it's grouping by a customer no and on this group i have a formula which has some text(it's a letter) and I display the letter then below the letter I have

Group Header #2 a. where I display one line that has an address information then

Group Header #2 b. this has the header information things like, invoice_no,invoice_date,amount, currency. then

Group Header #3. this has the currency(no show) then

Group Header #4. this display the currency code either, USD or CHF or EUR then below this is

the Details : this has the detail of what is on Group Header #2 b, the, invoice_no,invoice_date,amount,currency then I have

Group footer #4

Group footer #3: this is the currency where I have a total by currency

Group footer #2

Group footer #1 : this is the customer no

Now the question, how can I display a summary of all subtotals at the button of the report, most of the time I will have 2 types of currency on the report per customer.

    I want display  a summary for both , for example

        Total in USD is :

        Total in EUR is :

        Total in CHF is :

Thank you

[abhilash_kumar · Accepted solution]

Hi Alpha,

Insert a Crosstab and place it on the Report Footer.

Use the Currency field as the 'Row' and the field you wish to summarize under 'Field to summarize'.

You can then 'format' the crosstab if you do not wish to see Gridlines et al. This is customized by going to the Crosstab Expert > Customize tab.

-Abhilash

[alpha_sow · Accepted solution]

Thank you so much for the reply, I did try the cross tab and this seems to work but the only issue I am running is that I have another formula which fee_charges and that returns either 0.00, 10 or 30 dollars , now I need to add that result to the Total of in the currency(see screen shot), in this example I have only 2 currencies

USD and EUR

I need to add 30 to the EUR which will give me 290.25 and 0.00 to the CHF which will remain as but I still need to show something like this

Total  EUR 260.25

fee_charges 30.00

Total in EUR 290.25

Total CHF 122.31

fee_charges  0.00

Total in CHF 122.31

Thank you

Note: Please note that fee_charges is a formula where based on a max number of a field I return something the amount that need to added

[abhilash_kumar · Accepted solution]

What you're left with to do, is not going to be easy to implement on a crosstab!

You'll need to add a Calculated Member and use the 'Display string' formula on this cell to display the formula field's value (the formula that outputs the max value).

You'll also need a Calculated Row for the Grand Total to add up the fee_charges amount to the final total.

At this point, I'd much prefer Ted's approach on using the three formula approach as you can easily add the fee_charges amount to the currency values.

-Abhilash

[alpha_sow · Accepted solution]

Ok, Thank you,  now I am trying to use formulas but when I add the formula on the page footer I am only getting the result of one currency only, what am I missing, the formula is like this below

Sum ({table.AMOUNT_DUE}, {table.CURRENCY})

what can I do on this formula so that I can get all currencies that's on the group by ?

alpha

[abhilash_kumar · Accepted solution]

Hi Alpa,

1) Create a formula with this code and place this on GH:

If {table.currency} = 'CHF'

Then

Shared Numbervar chf :=

Sum ({table.AMOUNT_DUE}, {table.CURRENCY})

Else if {table.currency} = 'SFX'

Then

Shared Numbervar sfx :=Sum ({table.AMOUNT_DUE}, {table.CURRENCY})

2) Create two separate formulas one for each currency:

Place them on the report footer to display the value:

Shared numbervar chf;

You can also add the fee charge value to this variable.

-Abhilash

[alpha_sow · Accepted solution]

Thank you, will give it a try and then update it later on, Thank you again

[Former Member]

......  or you could use 3 formula fields in the page footer.      This gives you the added ability to do the currency conversion in the formula if needed, and to be able to use another formula to suppress the field for those instances where you only want to display 2 of the 3 different currency's.   

Ted

[alpha_sow]

Hi Ted

Could you see my update above, I tried using formulas but I am returning the total for one currency only not the others, what am I missing on the formula. Thank you guys for your help