SSRS Sum Expression with Condition

Question

I have a problem here, Have an SSRS report for Bank Transfer see attached

I would like to add a row expression which will Sum the total amount of the same bank, i.e, 03001 - Standard Chartered Bank Ltd BJL, 03002 - Standard Chartered Bank Ltd sk and 03002 - Standard Chartered Bank Base are all standard charters, i would like to get a total of all standard charters GMD figures. if need more clarification, please ask.

NB: Banks which are together e.g Standard charters above have a common field called BankAName. So the sum condition can be set to check if BankAName is the same.

Answer 1

Have ended up creating a new group the groups bank branches by Banks then create a sum per group. Thank you guys, your answers gave me a new perspective.

Answer 2

Modify your SQL query and add the new column showing the value you want

SELECT *, SUM(Amount) OVER(Partition By BankAName) AS BankANameSum
FROM myTable
Where Cond1 = Cond2

BankANameSum is the data field you can use in report design as it is. No need to apply any logic.

HTH.

Answer 3

You'll need something like this:

=Sum(IIf(Fields!BankAName.Value = "Standard Chartered Bank"
    , Fields!Amount.Value
    , Nothing)
  , "DataSet1")

This checks for a certain field (e.g. BankAName), and if it's a certain value that row's Amount value will be added to the total - this seems to be what you're after. You may have to modify for your field names/values.

By setting the Scope of the aggregate to the Dataset this will apply to all rows in the table; you can modify this as required.