sql-serverreporting-servicesexpressionssrs-2012daxstudio

Calculate the count and put in the same matrix table

Data here:

enter image description here

What I expect to see: I want to sum the value for each member and approving person to put in the same row. For example: Maria was the an approving person who has 2 paperworks under review and 1 paperwork under in the office. I want to put combine the value that Maria had in the same table as the staff.

enter image description here

I would like to know how I write the query to support my work for SSRS. I tried:

=COUNT(IIF(Fields!Staff.Value \<\> Fields!Approving_Person.Value, Fields!Staff.Value, Nothing))

I know the query wasn't right, would you please help me with this? I am not sure I need to have public code to make the table I need works.

Solution

  • This will be much easier in your dataset query.

    If you use something like this

    SELECT Staff, CurrentStatus 
    FROM myTable
UNION ALL
SELECT ApprovingPerson, CurrentStatus 
    FROM myTable
    WHERE ApprovingPerson IS NOT NULL

    Then you will get a single list of Staff and Status which you can then easily build a matrix from.