MikeDavisSQL

When looking at a report and wanting to drill through to get more detail, we often create two reports for this. This can be accomplished by just one report by creating a self-referencing action that links to the same report. This can be done even if the report has parameters on it.

Above is a simplified example report. The list on the left contains the product ID’s. The table on the right shows the details for the product that the user clicks on. Also notice the different background color to show which item the user clicked. There is a parameter on the report for the product color. There is hidden parameter on the report for the product ID also.

There are two datasets on this report. The first contains the product ID’s. Here is that query.

SELECT ProductID

FROM Production.Product

Where Color = @Color

The second contains the Product details. Here is that query.

SELECT *

FROM Production.Product

Where Color = @Color and ProductId = @ProdId

Nothing special is done to the color parameter. The Product ID parameter needs a few adjustments. It needs to be set to allow blanks and nulls, and be hidden.

Add the two tables and use the details dataset on the details table and the product Id dataset on the product ID table.

Next you will create an action on the Product ID table. Right click on the data field and select textbox properties.

Click on the Action node. In here you will select “Go to Report” and select the report you are on as the drill down report. There are two parameters. The color parameter will map to itself. To do this click on the fx expression box next to the Color parameter and select the color parameter in the expression window. Select the product id from the drop down menu next to the product id parameter.

The last option is to highlight the background color of the product id that was clicked. The expression for that is below. You are comparing the selected product id with the product ID on the table and changing the background color if they match.

Now run the report and click on a product Id. Check the first image in the blog to see the results. Now you don’t have to leave the current report to see details.

There are some pretty charts out there and most of them can be done in Reporting Services. There is one type of chart I had someone ask for that is not natively built in to SSRS. But with a little manipulation you can get the look you want. Here is an example of the chart I am talking about.

This is a mirror chart showing the genders spread across age groups. The female chart is a basic chart with no extra work done to it.

Here is a sample of the data on the table I am querying. This is just a table I manually filled.

Here is the query and results I used for the Female dataset.

In the female dataset I am grouping by age range and getting a count of the gender column.

In the chart I set up the Gender as the value and the age range as the category.

The Male chart is does not turn out mirrored unless you do some trickery. Here are the steps you can take to get it mirrored.

First, add another column to the query, as you can see in the below image.

This “Diff” column gives you a number that is the difference between the gender count and the number 10. In your data you might need to use a bigger number.

Next you create a stacked bar chart.

In this chart you set the values to be diff and gender in that order. You set the category to age range.

Now for the trickery part, Right click on the Diff series and select series properties.

Click on the fill option on the left and set the color to no color.

The last step is to set the horizontal axis properties. Right click on the horizontal axis and select the properties.

Set the maximum to 10. In your data you might need a bigger number.

Then remove the vertical axis from the male chart if you desire. It should look like the image below.

You might need to manipulate your charts a little more to get them to look just right.

With the new Map control in SSRS 2008 R2 there are three different map types, Road, Aerial, and Hybrid. If you want to allow your users to change the map type you would think you could map a parameter to the property on the tile layer with these three types. If you map a parameter to the map type and try to change the type the report stays on the original map setting. But there is a trick to get this to work.

First create a parameter with the three map types in the available values.

Then create three tile layers on the map, one of each type, Road, Aerial, and Hybrid.

Then set the visibility property of these layers to a parameter with the values to compare them to.

The function in the map tile visibility property for hybrid will be.

=iif(Parameters!MapType.Value = “Hybrid”,”Visible”,”Hidden”)

Set the visibility for the other layer based on their names. This will cause your report to run slower due to the report pulling down three layers of maps.