Search code examples
reporting-servicesdatasetssrs-expression

Display value of dataset on matrix columns based on value on another dataset in SSRS

I have a matrix table report with a few datasets - days, status(the status can be Open or Invoiced) and ord_total. I want to display the days on one column and then on two other columns I want to display the ord_total for Open and for Invoiced.

Currently, in the open column I am using the expression: =IIF(Fields!status.Value = "Open", Fields!order_total.Value, "") and in the Invoiced column I am using the expression =IIF(Fields!status.Value = "Invoiced", Fields!order_total.Value, "").

I get the expected data in the Open column however, the Invoiced column returns nothing for the various days. I have validated that there are several invoiced totals that should have been returned. Does anyone have any suggestions on this issue? Thanks.

Solution

  • Thanks for the response(s). Indeed I had to use column group. I created two column groups, grouped by status and then filtered each group by Status = Open and Status = Invoiced. And then in the data section, I used the expression: =IIF(Fields!status.Value = "Open", Fields!order_total.Value, 0) and =IIF(Fields!status.Value = "Invoiced", Fields!order_total.Value, 0) respectively. That gave me the results I was looking for.