on 10-30-2015 9:01 PM
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
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
You must be a registered user to add a comment. If you've already registered, sign in. Otherwise, register and sign in.
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
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
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
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
...... 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
You must be a registered user to add a comment. If you've already registered, sign in. Otherwise, register and sign in.
User | Count |
---|---|
90 | |
10 | |
10 | |
10 | |
7 | |
7 | |
6 | |
5 | |
4 | |
3 |
You must be a registered user to add a comment. If you've already registered, sign in. Otherwise, register and sign in.