The difference between drill down and drill through reports.

During the design of reports and dashboards I have seen that a lot of people do not know the difference between drill down and drill through reports. In most situations people uses the term: Drill down for both situations. However, there is a difference between these reports.

Drill through reports:
A drill through report allows you to go from summary to detail. For instance: You have a financial application in which you can show the balance and profit and loss for a specific time frame. Every general ledger displays the total amount of all transactions for this specific period. By clicking (Drill through) on one of the general ledgers you will see the underlying transactions for this specific period.

Drill down reports:
A drill down reports allows you to look to the data in different levels. For instance, a report displays information on Country level if we drill down the report displays information on state level. Next drill down will display information on city level. So Countries contains States, States contains Cities. So we can view all levels through drill down.
Another examples is Year contains Months, Months contains Weeks, Weeks contains Days, Days contains Hours, Hours contains Minutes.

Picture: My drill down from the Kawarau Bridge, New Zealand Dec 2011

How to display a multi value parameter in a text box in SSRS.

In my previous blog I described how you can pass a multi-value parameter to another SSRS report via drill through. After doing this, sometimes you want to show the used multi-value parameters in a text box. The solution for this is very simple. This can be achieved by using the JOIN function. I will explain with an example @Hostname as multi-value parameter.
Open the text box and define next expression:

="My own text: " + Join(Parameters!Hostname.Label,", ")


Other blog post about multi-value parameters: How to pass NULL value to a multi-value parameter in SSRS?

Picture: Milford Sound, New Zealand Dec 2011.

Passing Multi-value parameter to drill through SSRS report.

Last week I was building Report A with a multi value parameter called @Hostname. The values selected for the @Hostname parameter should be passed to Report B with the same multi value parameter. This failed because the dataset expects a valid IN clause. I will explain what happens with an example:
Multi value Parameter: @Hostname
Dataset: Select col1, col2 col3 FROM TableX WHERE Col4 IN (@Hostname)

In this case reporting server tries to add multi values like:
Select col1, col2 col3 FROM TableX WHERE Col4 IN (Server1,Server2,Server3)

This query syntaxt is wrong. It should be
Select col1, col2 col3 FROM TableX WHERE Col4 IN ('Server1','Server2','Server3')

This can be achieved by using next expression in the action for the parameter field:

=SPLIT(JOIN(Parameters!Hostname.Value,","),",")

With this expression the correct TSql syntax will be generated.

Happy building your reports. Enjoy It.

Picture: Mount Cook, New Zealand Dec 2011.