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 –

VTD 2011 Presentations and Demos


Presentation and Source code for my VTD sessions can be downloaded from the links mentioned below.

Numbers, charts and Decisions using SSRS 2008 R2 PPT Demos
Integrating SSIS with external applications PPT Demos

 

Let me also mention (As a shameless self gratifying plug), that the SSIS session was the highest ranked session in the database track!!

%d bloggers like this: