SSRS – Transposing a RowSet into a Cell


I was recently called upon by a customer to help him tide over some tricky issues in their quest to convert some 200 odd reports from iReports to SSRS.

One of the scenarios involved taking a serious of name value pairs and transposing them from a regular rowset layout into a single cell.

So in plain and simple English words, instead of having a report layout like this –

We want something like this –

I knew that we would need to write custom code in order to achieve this result. Furthermore, the custom code would need to iterate thru all the rows containing the product categories and the sales quantity and concatenate them in a temporary string variable. Finally, by adding a row outside the Details section, and accessing the final value of the string variable, I was able to show the resultant value.

The problem with this approach is that I am calling the Concatenate function from a cell in the Details section. And expressions get evaluated for a row only if the row is visible (great from a performance perspective)

The final objective was to hide the detail rows and just show the final concatenated value. As soon as I changed the visibility of the detail section to Hidden, the expression was not evaluated and that made a mess of the final result.

So the modified solution was to split up this requirement into two reports. The Main Report would only show a list of countries and regions. A subreport would perform the concatenation and would be invoked for every row in the detail section.

The main report would pass the TerritoryID to the subreport.

The DataSet in the subreport would return the name value pairs (Product Category Name and Order Quantity in this case) for a particular territory.

However since Custom Code in an SSRS report cannot iterate thru rows and columns returned by a DataSet, we have to create a multi-valued report parameter and assign the rows returned by the DataSet to it. When we assign dataset as the Default Value of a multi-valued report parameter, all the rows returned by the dataset get populated in the array that is created for the parameter values. By iterating thru these array elements in our code, we can perform the desired concatenation.

So we create a report parameter called DataSet as follows –

Add custom code to the report

Public Function GetStr() As String

    Dim Str as String = “”

    Dim i as Integer = 0


    For i = 0 to Report.Parameters!DataSet.Count – 1

        If i > 0

            Str = Str + ” , ”

        End if

        Str = Str + Report.Parameters!DataSet.Label(i) + ” ” + Report.Parameters!DataSet.Value(i)

    Next

    Return Str

End Function

In the Details section of the subreport, add an expression to call the custom code. Delete the header rows and trim the size of the report as shown

Integrate the subreport with the Main Report and voila –

Advertisements

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s

%d bloggers like this: