Totalling and Expressions

• David Clothier

  Right there with Babe

  Points: 771

  More actions

  July 6, 2006 at 4:08 am

  #169460

  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

• Jens Süßmeyer

  SSC Enthusiast

  Points: 165

  More actions

  July 7, 2006 at 1:07 am

  #647887

  Should be something like this here:

  =SUM(IIF(Fields!Transaction_Type= 'DEPOSIT',Fields!TRANSACTION_COUNT.Value,0))

  or use a Field within the report which sums up (and might be invisible) and reference that on the footer.

  HTH, jens Suessmeyer.

• David Clothier

  Right there with Babe

  Points: 771

  More actions

  July 7, 2006 at 4:11 am

  #647910

  Thanks Jens.

  Tried it and it kinda worked, but not for what i was looking for.

  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