July 6, 2006 at 4:09 am
Hi,
I have some data pulled from a report – here’s a snip from it:
Transaction Type Quantity Total
DEPOSIT 72 1530.49
WITHDRAW 27 3982.47
OPEN_ACCOUNT 1 0
DEPOSIT 11 705.04
WITHDRAW 1 71
If I add a footer and use =SUM(Fields!TRANSACTION_COUNT.Value) as an expression, it'll total up all the values.. but some are are deposits, some are withdrawals etc...
So I want to add three rows in the footer and then have Quantity and Total totals for each of DEPOSIT, WITHDRAW and OPEN_ACCOUNT transaction type.
The expression I’m using is wrong but I can’t see where:
=SUM(Fields!TRANSACTION_COUNT.Value)
And (Fields!TRANSACTION_TYPE.Value)='DEPOSIT'
The =’DEPOSIT’ is underlined in a red squiggle, so I think that’s where the problem lies? What I’m try to do is for QUANTITY get the expression to add up the total where the transaction type is DEPOSIT (for example)
Does this make sense?
I know I'm missing something quite simple here, so any help would be greatly appreciated.
Thanks in advance,
David
July 6, 2006 at 5:38 am
David,
I would use a case statement in the report dataset and create columns for each value to be totalled e.g.
SELECT TRANSACTION_TYPE,
CASE
WHEN TRANSACTION_TYPE = 'DEPOSIT' THEN TRANSACTION_COUNT
ELSE 0
END AS Deposit_Count,
CASE
WHEN TRANSACTION_TYPE = 'WITHDRAW' THEN TRANSACTION_COUNT
ELSE 0
END AS Withdraw_Count
etc, etc...
Then on the report you can use SUM(Fields!Deposit_Count), SUM(Fields!Withdraw_Count) to display the various count totals ... Set up addtional columns for each item you want to total in the report footer
This will only work if the transaction_types are known and constant though
Cheers,
Mal
July 6, 2006 at 6:16 am
Hi Mal.
Thanks for the reply and I gave it a go... seems to throw some other errors at me though. Here's the actual original SQL Statement too.. maybe it'll throw nsome light on it?
SELECT APPLICATION, TRANSACTION_TYPE,
Count(TID) as TRANSACTION_COUNT,
sum(AMOUNT) as TOTAL from
(
SELECT APPLICATION, TRANSACTION_TYPE, TID, AMOUNT from
LOG_TRANSACTIONS WHERE TRUNC(LOG_DATE,'DDD')=TRUNC(SYSDATE - 1,
'DDD')
)
GROUP BY APPLICATION, TRANSACTION_TYPE
Thanks,
David
July 7, 2006 at 4:12 am
I went back to basics, and the answer was staring at me in the face all time... very simple:
I created a new dataset, executing this query:
SELECT TRANSACTION_TYPE,
COUNT(*) as Quantity,
SUM(AMOUNT) as Total
FROM
LOG_TRANSACTIONS
WHERE TRUNC(LOG_DATE,'DDD')=TRUNC(SYSDATE - 1,'DDD')
GROUP BY TRANSACTION_TYPE
And then simply added a table directly underneath the original one showing the output - which works a charm!
Thanks for the replies though, it got me to where I needed to be!
David
Viewing 4 posts - 1 through 3 (of 3 total)
You must be logged in to reply to this topic. Login to reply