Filtered Group Totals and Grand Total
-
I have a report that lists customers' transactions over 90 days,
grouping and sub-totalling by customer to give balances over 90 days.
Some of the balances / group totals are credits, i.e., 'negative' and I
want to exclude these from my report. I have used a filter at the group
level to do this which works well. However, the report 'grand' total
held in the report footer still shows the total of all balances, not
just the 'positive' ones I want report on.
Can anyone tell me the best way to deal with this to get the correct
total.
-
Use something like...
=Sum(iif(CustomerAmt<0, 0, CustomerAmt))
-
Thanks for the reply David. I've tried that, it doesn't work. My
group filter is there to prevent groups with a sub-total (i.e. balance)
of >0 appearing; however, groups/sub-totals can be made up of
debit/positive and credit/negative transactions, giving an overall
positive sub-total. Using your formula just removes all the negative
transactions from the datset. So the grand total doesn't reconcile to
the sum of the sub-totals.
The fundamental problem seems to be that table footers can only add-up
all the transaction in the full dataset/table. There is no way to add
up just the 'visible' sub-totals. Crystal can do this though so maybe I'll
have to re-think.
One partial work-around I have is to use running totals; the last
running total in the table does give the sum of the 'visible' positive
balances. Is there any way to carry this value to the table footer?
Alternatively, I could push the problem back to the SQL query (if my SQL was better) and create a separate dataset to work out the total. I am working with a view of a table and already filter out transactions <90 days old - easy enough. Unfortunately I'm working with a DB that has all the transaction values as positive, and uses a transaction 'type' to tell me if it is +ve/debit or -ve/credit.
I can get as far as follows to get the 3 fields I would need using this query:
SELECT ST_COPYCUST, ST_TRANTYPE, ST_UNALLOCATED
FROM PMVIEW_STATEMENTS
WHERE (DATEDIFF(d, ST_DATE, { fn NOW() }) >= 90) AND (ST_COPYCUST LIKE 'D%')
This gives me results that look like this:
ST_COPYCUST
S__TRANTYPE
ST_UNALLOCATED
DA001
INV
1000
DA001
INV
1500
DA002
ADR
10
DB123
CRN
1000
DB123
INV
900
DZ999
CRN
5
First I need to create an 'alias' column saying if the ST_TRANTYPE is 'INV' or 'ADR' the, use ST_UNALLOCATED otherwise mulitply ST_UNALLOCATED x -1 (I'm currently doing this in calc'd field in my report).
Next I need to group by ST_COPYCUST and sum the new alias field by ST_COPYCUST.
Then I need to filter to return only where the group sums are > 0.
Can anyone help with that?
Cheers...
-
Yup
This is achieved by using a group by and a case statement.
You could also augment this query with subqueries so you get
columns with the amount of invoices, adjustments and payments etc.
Post another reply if you want this.
Heres the code:
SELECT
ST_COPYCUST,
SUM(CASE
When ST_TRANTYPE in ('INV','ADR') then ST_UNALLOCATED
When ST_TRANTYPE NOT in ('INV','ADR') then ST_UNALLOCATED * -1
Else 0
End) AS ST_ALLOCATED
FROM PMVIEW_STATEMENTS
WHERE (DATEDIFF(d, ST_DATE, { fn NOW() }) >= 90) AND (ST_COPYCUST LIKE 'D%')
GROUP BY ST_COPYCUST
HAVING SUM(CASE
When ST_TRANTYPE in ('INV','ADR') then ST_UNALLOCATED
When ST_TRANTYPE NOT in ('INV','ADR') then ST_UNALLOCATED X - 1
Else 0
End) > 0
-
Simon, many thanks, that worked a treat. 3 days of messing aroud trying to get RS to work and the simple solution was just to get SQL to do the work...
New years resolution for me: I must improve my SQL.
Thanks again, Merry Christmas.
Viewing 5 posts - 1 through 4 (of 4 total)
You must be logged in to reply to this topic. Login to reply