Drill-down report is a common design
In SSRS, it’s a common design to allow users to drill down to more granular data in a same report.
In the following snapshot, users will see a summary line that shows the Call Handled 130. The two days, 11/4 and 11/5 will not be immediately visible. Only when the user clicks the + sign in the All Dates field, the two detail lines will become visible. All this is happening in the same report.
Result set can contain only discrete dates
In this particular report, I had two date filters, as shown below.
The date filters ask for data in a continuous date range, starting from 11/1 to 11/10. However, the report is only showing some discrete dates per employee. This is because other filters suppress certain dates.
The point here is that although the date filters ask for data in a continuous date range, the result set contains only discrete dates within the date range.
Now the users ask you to create a hyperlink to go to a separate report, to show data for that employee, and for those discrete dates only.
The challenge becomes how do we capture those discrete dates within a group (The report is grouped by employee).
Capture discrete dates in SSRS
You might attempt to create a stored procedure to run the same query for the current report again so you can capture those discrete dates in your code, and then pass them to your second query to get the new report.
This will not be my first choice though, because it requires a lot of coding, not mention that the query performance might suffer because I will need to jam too much and redundant logics into one query.
Instead, I want to accomplish it entirely in the SSRS report.
Before I create the hyperlink on the summary line, I created a new column “Running Values” to just test my idea of capturing those discrete dates per employee.
I named this column “Running Values”, because I know that the RunningValue() VB function is what I need to use. A while ago I blogged about this wonderful aggregate function.
As a matter of fact, all the aggregate functions in the following screenshot allow you to do aggregation within a group in SSRS.
RunningValue() function needs an expression, an aggregate function and a group name
To use the RunningValue() function, I’ll need three parameters.
- An expression, which normally is a field name, such as Yearly Income.
- An aggregate function, such as MAX, SUM.
- An group name, which is called GROUP_EMP in my report.
It is quite obvious that using the RunningValue() function out-of-box will not work for me, because I am not doing any direct aggregation on dates per employee.
It’s time to go back to my old blog, SSRS #72 – Use RunningValue() Function to Concatenate Row Values.
A VB script that simply concatenate the dates per GROUP_EMP
What I need is a VB script that allows me to simply concatenate the dates per GROUP_EMP. Every time the script is called, it keeps the prior dates, and adds the new date to the concatenated string.
This function will do exactly that. In Report –> Report Properties window, write the following code, making sure that the two strings aStr and tempStr are defined outside the Public Function/End Function block. This will make sure that both aStr and tempStr are saved per GROUP_EMP.
Here is the code in text.
Dim aStr as String = “”
Dim tempStr as String = “”
Public Function ConcatenateString_Dates (ByVal inEmployee_ID As String, ByVal inDays As String) As String
If inEmployee_ID = aStr Then
tempStr = tempStr + “,” + inDays
aStr = inEmployee_ID
tempStr = inDays
Calling the RunningValue() function from the test column
Now it’s time to use the RunningValue() function.
In this test column RunningValues, I need to call the RunningValue() function.
Here is the actual code I used.
=RunningValue(Code.ConcatenateString_Dates(Fields!EMPLOYEE_ID.Value, Fields!CAL_DAY_LOCAL.Value), Max, “Group_Banker”)
The trick is in the first parameter, the expression.
This expression is not a simple field, it is the VB script I just created to concatenate the dates per employee.
The aggregation function I used is MAX. This allows me to get the last concatenated string, which is all the dates within one GROUP_EMP.
URL encoding allows more robust drill-through design
Now I am ready to just pass the following as a dates parameter to my next report.
RunningValue(Code.ConcatenateString_Dates(Fields!EMPLOYEE_ID.Value, Fields!CAL_DAY_LOCAL.Value), Max, “Group_Banker”)
Calling another SSRS report, or creating a drill-through report is normally pretty straightforward. SSRS has an out-of-box feature called Action for textboxes that can do that perfectly. But to make your reports more robust, and lasting long after the developer has moved on, there is something called URL encoding that VB scripts are good at. That can be another blog topic.
I had a post quite a while ago on how to use FOR XML clause with PATH mode, which is available since SQL 2005, to concatenate row values into columns.
We can achieve the same in SSRS with a wonderful aggregate function, RunningValue() function.
But to make it work, it can take more than a few tries.
In this example, I am going to use the MDX query from my post MDX #10 – Top 5 Resellers in Each Country.
Once I have the MDX query ready, I want to concatenate the top 5 resellers into a column, and the result will look like this:
A great post on beyondrelational.com has inspired this post. I forgot the link, but still want to attribute to the original blogger.
First add some custom code to the report
The first we need to do is to add some custom code to the report by going to the Report Properties.
In the Code section, create a custom function ConcatenateString() to concatenate resellers for each country. It will take Country and Reseller as 2 parameters.
Next create a row group
Next we need to create a row group, named “Group_Country”. In the group property, make sure that the group by column is [Sales_Territory_Country].
Use RunningValue in the expression
In the Top 5 Resellers expression, enter:
=RunningValue(Code.ConcatenateString(Fields!Sales_Territory_Country.Value, Fields!Reseller.Value), Max, "Group_Country")
- The ConcatenateString() function take 2 parameters, Fields!Sales_Territory_Country.Value and Fields!Reseller.Value.
- The aggregate function Max is the second parameter for the RunningValue() function.
- The last parameter is the name of the row group.
- The RunningValue() function iterates through each [Sales_Territory_Country] in the row group, finds each reseller, concatenates them, until it finds all the resellers in each [Sales_Territory_Country].
Concatenating row values into column can be easily done in TSQL, and also in SSRS.