Search code examples
reporting-servicesmicrosoft-reporting

Reporting Services group with a condition

I have this source data :

source data (image)

First I want to group by "Name" (I already did this part), after this I want to create the"category" column, if a "Name" group have any row type1 = A, then the Category is "X" else category = "Z"

This is the result I want:

result I need(image)

How can I get the category group-column in reporting services?

Solution

  • My defacto method for this would be to edit the SQL query feeding the report and add the logic to add the group there. I assume that's not possible or you don't have access to the SQL layer, so here a way to do it completely in SSRS:

    1. Insert another column to the right within your Name group

    2. For the field expression, do a lookupset on the name field value and check if A exists in the results

      =IIF(Array.IndexOf(Lookupset(Fields!Name.Value, Fields!Name.Value, Fields!Type1.Value, "DatasetName"), A) > -1, "X", "Z")

    If you build the expression piece-meal, use the following expression to first check that your lookupset is working:

    =JOIN(LookupSet(...), ",")

    Then add the IIF(Array.IndexOf(...,A) > -1, X, Z) to see if the returned array contains your "A" value.