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.

Solution 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.

Solution 2

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.

Solution 3

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.

Comments

[strattonn]

Thank you for the Nothing False part of the iif. In my report, I had a 0 and was getting #Error.

[Mark]

that "Nothing" part helped me too.:)

[NateJ]

This should be the accepted answer. The question, IMO, is way too specific, but the title is nearly perfect, and this answer answers that question posed by the title.