Ok, so this is not your everyday run-of-the-mill post which talks about how to repeat the same chart for each value selected in the report parameters. I know you have been listening to countless posts instructing how you can do the same with Lists in SSRS (in case you haven’t, this would be a nice place to start). But the subtle issue with just using a list is that they grow only downwards. Now this would mean that in the case of small charts, we would not be using the page efficiently as there would be lots of horizontal space left. So the ideal situation would be that the list grows horizontally and at the end of the page, it just breaks to the next line, just as we would expect our typewriter to behave. Actually speaking, this is not that hard as it looks to be. All it needs is a little tweak in your dataset query and some changes to your layout as I am going to show you in this post. To illustrate this, I am using the following scenario from AdventureWorks database:- a) Report parameter for subcategory b) The report should display charts (Products of the selected subcategory v/s Reseller Amount) for each of the subcategory selected Now that the requirement is crystal clear, follow the steps detailed below:- 1) Edit the dataset query to include a rank for each subcategory. For e.g., in my query, I have the Subcategory, Products and Reseller amount. Create a calculated member using the rank() which would return the rank of the subcategory. 2) Once you have the rank, proceed to the Layout. Calculate the max no of charts that can be displayed horizontally. This is done by using the formula Integer part of (Page Width/Chart Width) In this example, I am taking 3 as the max number of charts displayed in one row. 3) Make the chart with Product on Category and measure on Data Value. Make the title of the chart as [First(Subcategory)] 4) Now create a matrix and add Subcategory on the rows and embed the above chart in the data section of the matrix. This will ensure that the chart will appear once for each subcategory appearing in the matrix. Make the column containing subcategory as hidden and borders of the matrix as none, so that only the chart is visible. Copy this 2 more times so that we have the layout as shown below in the figure Notice that the column for subcategory has been made very small. The reason is because anyways they are hidden and we would not want to waste precious real estate (read it as the space in the layout) on hidden things. 5) Now right click on the first chart and select tablix properties. 6) Go to filters tab and click Add button. Enter =Fields!Prod_Rank.Value mod 3 for Expression. Operator should be = and Value should be 1. Also make sure that the data type is integer. This would make sure that only subcategories whose Rank mod 3 is 1 would be displayed in the first matrix. E.g. subcategories with rank 1,4,7,10, etc 7) Repeat step 5 and 6 for second matrix with Value=2 8) Repeat step 5 and 6 for third matrix with Value=0 9) You can preview the report and see your chart growing horizontally first and then vertically as required based on your selections. This technique would work beautifully on both the 2005 as well as the 2008 versions of SSRS.

The MSDN forum has been the inspiration for most of my blogs, but in all those cases, I was sure that I could attain the end result and my blogs were usually a graphical step by step depiction of what needs to be done, so that I could post it as an answer to the question. But this blog is quite different, I almost wrote a no saying that it is not possible. And that was when I decided to give it a try before replying No 🙂 To give a background of the problem, the user wants to display the list of reports in a dropdown list and on pressing the view report button, the selected report must be displayed. On reading this question, I started typing – no, it is not supported by design in SSRS and suddenly I thought whether I am doing any value-addition to the post by typing this answer, I might as well as not write it and wait for some expert to confirm it. So I just thought of trying it while some expert gives his answer, and by chance, I stumbled upon a workaround. To implement this, follow the steps below:- 1) Create a report parameter (say RP) and in the report parameter properties, specify the default values. The labels should contain the report names and in the values, use integers as shown in the figure below 2) Create a matrix in the layout and for each report add a row. In this example there are 3 reports hence add 3 rows. 3) For each of the row, drag and drop a subreport  from the toolbox into the data cell. Then right click and select the subreport properties. In the General tab, select the required report from the dropdown list which contains the list of reports (as shown in the figure above). Press OK. 4) Once you have modified the properties of all the subreports, select the entire row and right click on it. Select Row Visibility option. 5) Select the Show or hide based on an expression radio button For the first row, enter the following expression:- =iif(Parameters!RP.Value="1",False,True) This expression will ensure that the first row will only display if the parameter value is 1, else the row will be hidden. 6) Repeat steps 4 and 5 for each row. Substitute the number 1 by the appropriate value in the expression specified in the 5th step. 7) In the matrix column header, use the below expression to display the name of the report =Parameters!RP.label 8) Save and deploy the reports. Now you can preview the report in internet explorer, and when you select the report name from the parameter dropdown and click on view report button, the appropriate report will be displayed. This technique can be extended to pass other report parameters also by passing the required parameters to the subreports. Don’t you think I deserve a pat on the back for this? 😉

Ok, this post is dedicated to all those zillion questioners asking how to use embedded images on your report based on a condition. Yet again, I will be making use of my favourite dataset from AdventureWorks – Order Count by Product (people who are following me would understand that this is the same dataset that has been featuring in many of my blogs. I admit it guys, I am too lazy to make a new dataset. On second thoughts, this doesn’t look  that bad, considering I have created a re-usable component in the form of my dataset. Woah, high fives!) For making this report, I am going to make use of three images mainly:-

a) Home                  b) Thumbs Up        c) Thumbs Down

The home image would just be used in the top left of the report, which could be used for hyper linking to the home page. The other 2 images would be used in a matrix, and would be displayed based on a condition. (P.S. : This report is made in SSRS 2008, although the steps are quite similar in SSRS 2005 also) To make the report, follow the steps below:-

1) On the Report Data panel, go to Images and right click on it. Click on Add Image and select the images you need to embed by giving the source path.

2) Once you have uploaded the 3 images, drag and drop Home image from the report data to the body of the report and press ok on the popup that appears (This is one method to bring embedded images in your layout)

3) Make a matrix underneath the Home image like shown in the figure below

4) Now the matrix will be having Products on it’s rows and order counts on it’s columns. Now let us add the Thumbs up image for the row which has the highest value of Order Count and the Thumb down image for the row which has the least value for Order Count. For this, go to the toolbox and drag and drop the Image tool to the vacant column in the right end of the matrix (This is the second method to bring embedded images in your layout). You would be getting the Image Properties popup on doing this. Click on the expression symbol in the image name.

5) Once you get the Expression editor open, enter the following expression:-

=iif(Fields!Order_Count.Value=max(Fields!Order_Count.Value,”DataSet1″),”thumbsup”,

iif(Fields!Order_Count.Value=min(Fields!Order_Count.Value, “DataSet1″),”thumbs_down”,””))

where thumbsup and thumbs_down are the names of the images embedded in the report. Notice that they are enclosed in double quotes. Also notice that if none of the conditions are true, empty string would be displayed.

6) Save the report and deploy it. Now you should be able to preview the report in internet explorer.

This is all it takes to embed images in your reports and use them. You can use them for a wide variety of purposes, though I usually use them for showing indicators or for using them as hyperlinks. However, if you want to store images in your database and use them in your reports, you might want to check out this post.

In one of my projects, I was asked to implement a drillthrough report, which had a depth of around 6 pages. After the development, I was sitting with the user who was testing the reports and I noticed that the user was frequently shifting between the sixth and the third report to change the values. As most of us, she was also just using the back button of the internet explorer 3 times to reach the third report from the sixth report. That was when I quizzed her whether this was going to be a common requirement and suggested the idea of using breadcrumbs as a navigation aid. Though I can’t show you the original report, I will illustrate the idea here by making some sample reports. Here, I have created three chart reports using the AdventureWorks database to simulate the drillthrough effect – a) Order by Category b) Order by SubCategory c) Order by Product The Order by Category will be used as the starting point and on clicking a particular category in the chart, it will drill down to the subcategories of that category. Similarly, on clicking a particular subcategory, the chart will drill down to the products of that subcategory. Now, follow the steps detailed below to add the breadcrumbs:- 1) Now in the first report, add a textbox with the text – Order by Category as shown below 2) In the second report, copy the same textbox and just to the right, make another textbox with the text – Order by Subcategory and do the formatting as shown below 3) Right click on the first textbox, select the textbox properties and click on the action property. Now click on the Go to URL radio button and enter the following expression =”javascript:history.go(-1)” This particular javascript will retrieve one page back in history. 4) Similarly, in the third report, add the textboxes in the format shown below. 5) Edit the action property of the first textbox (as shown in step 3) and enter the following expression =”javascript:history.go(-2)” For the second textbox, enter the following expression =”javascript:history.go(-1)” 6) Save and deploy all the three reports and view them in internet explorer. As you can see from the images below, the breadcrumbs would be displayed. The active links would be displayed in blue while the inactive (the present page) would be displayed in black. You can click on the breadcrumbs directly to navigate between pages. This makes the drillthrough reports more user friendly and professional. The user for whom I designed this was extremely happy with the end result and gave me a BIG hug for making her life easier. As for me, I have included this trick in my list of best practices when designing drillthrough reports, who knows whether I would get another hug some other day by implementing this again 😉