Populate a cell in SSRS report by testing value conditions from other column value

Question

Populate a cell in SSRS report by testing value conditions from other column values

I have 3 columns (category, Month, Amount) in my dataset, with values similar to these below:

Category   Month   Amount
A          Jan       20
A          Feb       25
A          Mar       10
R          Jan       15
R          Feb       50
R          Mar       55

On the report I need:

    Jan   Feb   Mar
A   20    25    10
R   15    50    55

I have tried placing this expression in each group row column, for example, in the "Feb" column it would be:

=IIF(Fields!Category.Value = "A" and Fields!Month.Value = "Feb", Fields!Amount.Value, 13)

Using IIF, which does not short-circuit, is not evaluating the conditions properly. The "Month" value being a string, it displays always the first row found for the conditions, in this case it would display value 20, instead of 25. If I change the "Month" value to int, it displays the false(13); How can I populate my cell correctly, using IIF or something other way?

Any help on this is deeply appreciated.

Alan's answer is the way to go but to make your theory work, you'd want a Row Group based on the Category and then SUM to the Amount fields if the month is a match =SUM(IIF(Fields!Month.Value = "Feb", Fields!Amount.Value, 0)). Not sure what 13 was for tho. — Hannover Fist

Alan Schofield Alan Schofield · Accepted Answer · 2018-11-13T15:59:51

You don't need to do anything like that.

Just use a matrix instead of a table.

Add the matrix to the report, you'll see three placeholder names, (from memory) something like Rows, Column and Data. Drag your Category field to the rows cell, your month field to the columns cell and your Amount field to the data cell. That's it....

The only problem you will have is the columns would be ordered alphabetically by default. It would be better to have the Month number in your dataset too so you can order by that. You set the column order by right-clicking the column group name in the row/column group panel which is under your main report design area.

Populate a cell in SSRS report by testing value conditions from other column value

1 Answers