CODEWITHSUNDEEP
reporting-servicesssrs-2008ssrs-2008-r2
M_66
M_66

Reputation: 299

How to group by and loop through sub-group values in SSRS report builder

I am using SSRS report builder with SharePoint lists. I've created a Matrix in report builder that groups site evaluation status by organization. I would like to be able to set the background color of the cell of holding the organization name, depending on the overall evaluation status of the sites that belong to the organization.

My site evaluation status values are "not started", "in progress", "complete". If any of the organization's sites have a status of "not started", then the background color of the organization's cell is red. As long as all of the organization's sites have a status of "in progress", then the organization's background cell is yellow. Finally as long as all of the organizations sites have a value of "complete", then the organizations background color is green.

Example in Excel Example using Excel

I know how to change the background color of the cell using an expression to call code, but not sure how to pass the values I need or loop through those values. I think I have to write something in VB in the custom code section, but not sure how to loop in within a group with Report Builder

Upvotes: 0

Views: 2058

Answers (1)

alejandro zuleta
alejandro zuleta

Reputation: 14108

Try using this expression in the background color property of Organization:

=Switch(
Array.IndexOf(LookupSet(Fields!Organization.Value,Fields!Organization.Value,Fields!Status.Value,"DataSet2"),"Not Started")>-1,"Red",
Array.IndexOf(LookupSet(Fields!Organization.Value,Fields!Organization.Value,Fields!Status.Value,"DataSet2"),"In Progress")>-1,"Yellow",
Array.IndexOf(LookupSet(Fields!Organization.Value,Fields!Organization.Value,Fields!Status.Value,"DataSet2"),"Complete")>-1,"Green"
)

It produces this:

enter image description here

UPDATE 1:

The Switch function evaluates every case:

  1. Status column contains Not Started values
  2. Status column contains In Progress values.
  3. Status column contains Complete values).

In order to check if there is a Not Started value in the Status column, I used IndexOf() and LookupSet() functions. LookupSet will give me an array containing every Organization status, so using IndexOf I search Not Started, In Progress or Complete values. In case a value is found it returns the index where it is stored in the array otherwise it returns -1.

UPDATE 2: Edition based on user comments.

First you have to understand how Switch function works in SSRS. In fact, it is really simple, every row is evaluated against every case expression of the Switch function. When a row match the condition, there is a Not Started value i.e. the next row is evaluated against every condition in the Switch function and so on.

Note the cases are evaluated in the order they were written. So in our scenario every row is evaluated firstly against the condition where it contains Not Started values if the condition is false, it continues to the next condition, if it is false it continues to the next condition and so on, until a condition is true or all conditions are evaluated.

Let me know if this helps.

Upvotes: 2

Related Questions