I have a total invoice amount that can appear in several sections of my report but I only want to sum (grand total) for the single instance of the invoice in the entire report. I cannot seem to get the context right to do this. Any ideas?
ckh009 
(BOB member since 2006-03-14)
Why would your ātotalā appear in several sections? Are you refering to WEBI āSectionsā or are you really saying ādifferent areasā?
Sections in WEBI are a sub-set of your original data, filtered on a particular dimension.
We need more information to point you in the right directionā¦ but the big question in my mind is whether or not that grand total thatās appearing is in the raw data output from the SQL as a row, a column, or not at all.
JPetlev (BOB member since 2006-11-01)
Each section is a grouping by a catetgory.
Each category is summed up and matches nicely.
But the total we want is not the sum of the categories because we are only looking to sum up the single instance of the invoice.
In other words Invoice 1234 can appear in section 1, section 2, and section 3. The invoice amount is included in each of these totals. But the complete sum of the report should only equal one instance of Invoice 1234 for $100 not as it appears in each section for a total of $300.
I hope this helps you understand what I am trying to achieve.
ckh009 (BOB member since 2006-03-14)
Hi:
If you need to sum by the Invoice, you can do it through context:
SUM(amount) Foreach ([Invoice])
or if you need to place the grand total for all the categories outside the section ā¦you can use following variable.
SUM(Amount) where (field_value = userresponse(prompt))
Regards
Ramesh Vasudevan
rameshbalajiv (BOB member since 2008-03-25)
Ok it sounds like your invoice amount is actually in the raw data more than onceā¦
Something like:
CategoryA - Invoice1 - Amount 1111
CategoryB - Invoice2 - Amount 2222
CategoryC - Invoice1 - Amount 1111
CategoryC - Invoice2 - Amount 2222
CategoryC - Invoice3 - Amount 3333
etcā¦
I believe what you want to use is the āFOREACHā option with your sumā¦
Something like Sum(Amount) ForEach Invoice
I donāt have BOBJ access at the moment so this is off the top of my headā¦ let me know if it works and if not Iāll double check tommorrow when I get in the office.
JPetlev (BOB member since 2006-11-01)
Seems logical that it should work but it does not.
ckh009 (BOB member since 2006-03-14)
Opps Ram and I had the same idea.
Ckh, did you put that sum statement OUTSIDE of your section break? If so and itās still not working, dump a few lines from your data into Excel and post them if you can so we can get an idea of the underlying data.
If you make a formula outside of your section break we donāt have to worry about any special filters, sections or block restrictions.
It should work so Iām thinking the data you have isnāt what is in our heads.
JPetlev (BOB member since 2006-11-01)
I canāt really export it to Excel but I have generated a small sample and attached it. You will see in the example that the Grand Total is showing 1050 but should be only 750.
Let me know any ideas you have.
Sample 1.xls (18.0 KB)
ckh009 
(BOB member since 2006-03-14)
Hmm it should work, thatās how I thought it was laid outā¦
Double check that your total is outside of any sections and perhaps even include another piece to the formula to be sureā¦InReport should do the trickā¦ though it should be functioning correctly without itā¦
Sum([Amount]) ForEach([Invoice]) InReport
JPetlev (BOB member since 2006-11-01)
This does not make a difference at all in the totalā¦ I donāt know why not.
The total is outside of the section. I have entered the formula exactly as you have suggested.
ckh009 (BOB member since 2006-03-14)
Have you tried / are you able to create a second query returning invoice id and the sum total? So a distinct record of the singular amount. I presume invoice no would work as a linking dimension and maybe enable you to get to that distinct value in your report?
SteveD 
(BOB member since 2009-11-02)