Aggregate of  an aggregate function in SSRS

Aggregate of an aggregate function in SSRS

I am worse than a child when it comes to colours and no wonder, spend a lot of time on the aesthetics whenever I am preparing an Excel sheet or a PowerPoint presentation. For me, this is rather a way to indulge the child within me than to make it more presentable to the audience. The joy that you get when you arrive on that perfect colour for your headers or that perfect font to suit the mood or that perfect design template for your slides is something that can’t be expressed. Recently, I was preparing the sample data for a report and playing around with the colours in excel as usual when I noticed that there was an unexpected aggregation that would be required. Beads of sweat broke from my forehead as I contemplated going back to the user and saying that I might need some time to think whether this is possible. Let me give you an overview of the requirement. The measure (Order Count) comes in at a sub category level and the client wanted to see the average of the subcategories within a category, as well as an average of average as the grand total (refer below image for calculations) Aggregate CustomCode  I straightaway opened BIDS and decided to try it out for myself. I quickly made the dataset and designed the layout as shown below. Design mode - layout On previewing the report, I got the following output Preview I was almost giving that smug grin of mine thinking everything is fine, when I spotted that the Grand Total doesn’t match with the requirements. The totals were coming properly at the category level (which were just an average of the sub categories) but at the Grand Total level, instead of taking an average of the Categories [ (7585 + 7857 + 4082) / 3 ], it was still taking an average of the sub-categories [ (5212 + 7512 + 10030 + 6185 + 9528 + 3382 + 4781) / 7 ]. That is when I realized that what we need here is an average of an average at the grand total level. But SSRS 2008 and below versions doesn’t allow us to define an aggregate of an aggregate function. This was confirmed when I got the following error on replacing Avg(Fields!Order_Count.Value) by Avg(Avg(Fields!Order_Count.Value)) [rsAggregateofAggregate] The Value expression for the textrun ‘Order_Count9.Paragraphs[0].TextRuns[0]’ contains an aggregate function (or RunningValue or RowNumber functions) in the argument to another aggregate function (or RunningValue). Aggregate functions cannot be nested inside other aggregate functions. My next approach was to try using the Avg function on a textbox which would already have the value Avg(Fields!Order_Count.Value), something like Avg (ReportItems!Textbox21.Value). The result was again an error, not much different from the previous error [rsAggregateReportItemInBody] The Value expression for the textrun ‘Order_Count9.Paragraphs[0].TextRuns[0]’ uses an aggregate function on a report item. Aggregate functions can be used only on report items contained in page headers and footers. That is when I decided to dig into the treasure troves of the MSDN forum, and unearthed this post by Jin Chen (MSDN Moderator) where he gives a sample custom code to achieve the functionality. Armed with a minorly tweaked version of this custom code, I followed the steps below to achieve my requirement:- 1) Go to design mode and right click on the area outside the report body. Select Report Properties Report Properties 2) Select the Code tab and paste the following code there Dim public totalBalance as Integer
Dim public Cnt as Integer
Public Function AddTotal(ByVal balance AS Integer ) AS Integer
                totalBalance = totalBalance + balance
                return balance
End Function
Public Function GetTotal()
                return totalBalance/Cnt
End Function
  The AddTotal function basically adds all the values of a textbox and sets it to totalBalance variable. This function also counts the number of values. The GetTotal function returns the sum of the values by the count of the values which is basically the average of the textbox values. Once you have pasted the code, you can click OK. Custom code window 3) Replace the expression at the Total level from =Avg(Fields!Order_Count.Value) to =Code.AddTotal(Avg(Fields!Order_Count.Value)) This is done so that when the average is being calculated at this textbox during report rendering, at the same time the sum and count of these averages will also be calculated. 4) Replace the expression at the Grand Total level from =Avg(Fields!Order_Count.Value) to =Code.GetTotal() This function will return the computed value, which would be the average of the average as we required. 5) Preview the report to confirm that the output matches with the requirement. Preview result Guess who is grinning smugly now? 😀

Posted by SQLJason, 13 comments
Calculating visit length by first and last visit time in SSRS

Calculating visit length by first and last visit time in SSRS

Writing a blog is an exhausting experience, most of the times you would be writing your blogs at the expense of your sleep or after a hard day’s work. But then there is a very fulfilling feeling, a joy that you get when you come to know that your work is helping people all around the world. I am sure most of the authors get a huge kick by the number of hits their blog receives and by the number of different countries visitors come from. For collecting such statistics, I use StatCounter and I rate it one of the best free add-ons for getting real-time web stats. There are a lot of features and one among the many is the Visit Length stat. Let me quote the definition of Visit Length from the StatCounter site itself – “ The time between when a visitor accesses your first webpage of their visit, and when they access the last is what we call the ‘Visit Length’. Unfortunately it is not possible to detect with the Standard StatCounter Project when they left your website for a different website. Instead we take the time they accessed your last webpage of their visit as their ‘exit time’. Although this isn’t 100% accurate it is very close and nonetheless provides a valuable insight into your visitors. Based on this information you can see how much ‘pull’ and ‘interest’ your website is generating for your visitors. If you have a low ‘Visit Length’ you may want to think up more ways to encourage your visitors to stay around for longer “. Now you might be wondering why I am advertising StatCounter here ( nopes, I don’t get paid by them, I am just a happy customer ). The actual reason for all this introduction was to introduce you to a scenario where we have data of the visits stored and we need to calculate the visit length in a SSRS report. Let me first get the data before I explain the scenario. For getting the data having the blog visitor information, I will be using a random page of my blog’s StatCounter and then importing the data into the database. A sample page from StatCounter is given below:- Visitor stats  After importing the data, I created a report which will show the visitor info as shown below:- Report showing Visitor data Problem Now that the source data is ready, I will explain the requirement. We need to find the first visit time and last visit time for the same visitor from the above data. A visitor is considered to be the same if he has the same Country, Place, Browser, Resolution and back to back entries. For eg, the first 2 entries for Coimbatore, India is considered to be the same visitor as it has back to back entries and also all the other fields except time is the same. Using the same logic, Mountain View, USA is not considered to be the same visitor in the last and third last rows as it has no back to back entries even though all the other columns are same. If there is no back to back entry, the last visit time would be the same as the first visit time. The required output would look like shown below:- Expected Output Solution The very first thing that comes to our mind on seeing such a problem is to group by all the fields except time and then get the first and last Time for that group. However, we can’t do that here as a) we need to consider visitors that do not have back to back visits as separate visitors. A Group By clause will group all the visitors together even if it doesn’t have back to back entries. In the above example, it will group the visitor from Mountain View as one entry even though we require it as two. b) it will group entries from different dates also as one entry. So suppose one visitor came on the first and last day of the month, he would be grouped as one entry and his visit length would become 1 month which would be incorrect. After brainstorming and googling upon this for a lot of time, I stumbled upon a valuable post in the MSDN forums by Raymond Lee (moderator) in which he posts a custom code for achieving this type of grouping. Follow the steps below to apply his solution in our problem:- 1) Go to the Design section of the report and press Alt+R. Select Report properties from the menu and select the code tab. Paste the code given below there Dim Address As System.Collections.Hashtable
Dim sumGroup as Integer
Function MyFunc(ByVal _Address  As Object) As integer
Dim flag as integer
If (Address Is Nothing) Then
Address = New System.Collections.Hashtable
End If
If (Not Address .Contains(_Address)) Then
Address .clear()
sumGroup = sumGroup + 1
Address .Add(_Address, nothing)
sumGroup  = sumGroup
End If
MyFunc = sumGroup
End Function Paste vb code in Code section 2) Drag and drop a table into the design and add all the required fields except time into the table. Make a group for the fields with group expression as given below =Code.MyFunc(Fields!Place.Value+Fields!Country.Value+Fields!Browser.Value+Fields!Resolution.Value) Group Expression The argument for the function would be all the fields on the basis of which we need to check if there is a back to back entry. In our case, it is all the fields except time and we are concatenating it as one field and sending it as input to the function. 3) Once this is done, we should be having the table in the design section looking as shown below Table structure in Design  Notice that there is only one group and the group name is Group2. 4) Now all we need to do is to get the first and last visit time for each entry which is easily done with the below expression =first(Fields!Time.Value,"Group2")+" – "+last(Fields!Time.Value,"Group2") Take care to substitute Group2 in the above expression with the group name of your table. 5) With a little bit of tidying up and colouring, we should be able to attain the results shown below Final Output 

Posted by SQLJason, 2 comments
Display Total on top of Stacked Chart

Display Total on top of Stacked Chart

It is a normal phenomenon that human beings take most of the simple things for granted. Take this case, one of my blog readers sent me a mail asking how to display the total on top of a stacked chart in SSRS. The first time I read it, I was almost going to blurt – just enable some property to display subtotals. But the more I thought about it, the more elusive the property became and that is when I realized there is no such property. So for the benefit of all, I thought about posting the workaround that I devised for this. Suppose I have a simple stacked column chart with Month on Categories, Product Category on Series and Sales as the measure. The requirement is to display the total of all Product Categories for a month on top of the stacked column chart. The required output is shown in the below image.

Stacked Column Chart - Total

For this, follow the steps below:-

1) The original query was

SELECT  Month,
FROM    Sales
Modify it as follows SELECT   Month,
FROM     Sales
SELECT   Month,
             ‘Total’ AS Category,
             0.1 * SUM(Sales) AS Sales
FROM     Sales

This is done to include an extra row called Total for Product Categories. The value for Sales for this row would be 10% of the total Sales of that month.

2) Go to the Design tab, right click on the chart and enable the Show Data Labels option. Then select the series label properties by right clicking on the data labels.

Show Data Labels

3) Edit the Label data and enter the following expression


Label Data Expression

The above expression displays blank if the Product Category is not “Total” and displays the sum of the entire Product Categories for that month (including the value for Total) – sum of the Total field. P.S. : “Chart1_CategoryGroup” would have to be replaced with the category group name of your stacked column chart. To find this, click on the Month (category) button in the chart and press F4. You will find the Name of the group in the property panel that opens up as shown below.

Find category group name

4) Now we should be getting the following output when we click on the Preview tab.

Statcked column chart with Total

Go back to the Design tab and click on Product Category (series) in the chart and press F4 to open the property panel. Enter the below expression in the Label property =iif(Fields!Category.Value=”Total”,” “,Fields!Category.Value)

Edit Label expression for series

This is done so that the Total value will not appear in the legend. 5) Right click on the stacked columns and select series properties.

Series properties

6) Go to the Fill tab and enter the following expression for the color


Color expression

This is done so that the Total value will be transparent and hence, would look like it is not present.

7) Preview the report and you should be getting the required output

Final result

This report has been done with SSRS 2008 R2 but the concept should work in SSRS 2008 more or less the same way.


For SSRS 2008 users, it has been noticed that all the bars become black when the above fill expression is used. That is because the Automatic colour can not be used in expressions. So as a workaround, follow the step below instead of the 6th step above

1) In BIDS, press Alt+R to open the Report Tab and select Report Properties. Then go to the code section and paste the following code

Private colorPalette As String() = {“Green”, “Blue”, “Red”, “Orange”, “Aqua”, “Teal”, “Gold”, “RoyalBlue”, “#A59D93”, “#B8341B”, “#352F26”, “#F1E7D6”, “#E16C56”, “#CFBA9B”}
Private count As Integer = 0
Private mapping As New System.Collections.Hashtable()
Public Function GetColor(ByVal groupingValue As String) As String
  If mapping.ContainsKey(groupingValue) Then
    Return mapping(groupingValue)
  End If
  Dim c As String = colorPalette(count Mod colorPalette.Length)
  count = count + 1
  mapping.Add(groupingValue, c)
  Return c
End Function

Rep properties

Right now, there are only 14 colours in the palette but more colours can be added if you have more than 14 values in the series.

2) Use the following expression for colour in the fill tab of series properties.

=iif(Fields!Category.Value=”Total”, “Transparent”,Code.GetColor(Fields!Category.Value))

This should do the trick 🙂

Posted by SQLJason, 22 comments
Auto-update of parameter datasets in SSRS 2008

Auto-update of parameter datasets in SSRS 2008

The times I have spent with SSRS 2008 can be likened to that of a married couple’s life story. The moment I first laid my eyes on her during the Microsoft seminars and community previews, I knew that she was going to be the one. I secretly started collecting details about her, as much as possible and awaited her arrival. Needless to say, I was one of the very first to have the trial version installed and in the dates that followed, I was just astounded at her potential – she seemed so full and promising. I even started writing sonnets in her praise (read as reviews), love struck that I was. Eventually, the marriage took place (yups, we got the license too!) and we revelled in the honeymoon period. But once the honeymoon period got over, I started noticing some irritating things about her that I had not noticed before – the story of every married couple’s life. And one of those irritating features was the auto update of report parameter datasets. Everyone who has worked with SSRS 2008 must have experienced this sometime or the other. You use the query builder to make a query with report parameters in them, and then you decide to modify the parameter dataset queries. After that, you play around with the main dataset, make those few important changes that the query builder doesn’t support and then you execute the query. You wouldn’t find anything missing till you actually preview the report and notice that the report parameters are not reflecting the changes that you just did. You double check and yups, the parameter queries have indeed changed and all the hard work that you did to change them has gone for a toss. Well, with this post, this issue is going to be history. For illustrating the problem, I have made a simple report which has a Year and a Quarter report parameter, and the category and Quantity for the selected values in a table. Report Parameter Suppression Now when I expand the parameter list, I can see that there is an option for All Periods which I would not like to display. Parameter - with All Periods displayed So I go back and change the query to remove the all period option. Altering the rep parameter dataset query Now I can preview the report and the All Periods option is not there anymore. Parameter without All Periods Looks good huh? Well it does as long as you don’t touch the main dataset query. The moment I decide to go and display only the top 2 categories instead of all the categories by modifying the query, things change. Main Dataset query change Now when I preview the report, I get the following result Changed Report The report now displays only the top 2 categories but then the changes that I had done to the parameter is gone and All Periods is back to haunt me. There are 2 methods to solve this issue – Method 1 Make sure that wherever the particular parameter (whose dataset query you would not like to be auto updated) is referenced in a query, the default value is specified using the fully qualified name or the unique name rather than selecting the dimension, hierarchy and name. An example is shown in the image below Modifying parameter properties Please note that you might have to delete the old parameter from the Query Parameters window and then create a new one else you would not be able to leave the Dimension, Hierarchy columns empty as shown above. Method 2 I owe this solution to a blog that I read from Teo Lachev (MVP). Follow the steps below to implement the solution:- 1) Right click on your report and select the View Code option Select View Code 2) Find the code section which will have the required dataset name (where you want to prevent the auto update). Go to the line just above the </Query> and paste <rd:SuppressAutoUpdate>true</rd:SuppressAutoUpdate> An example is shown in the image below SuppressAutoUpdate Code Now that you have got the solution that you had been looking for, why are you still reading on? Oh are you waiting for me to complete the story that I was telling you at the start? 🙂 Ok here it goes. Now that I have spent quality time with SSRS 2008, I have understood her more and have learned to work my way around her when she throws her tantrums. No girl is perfect, and I don’t claim SSRS 2008 to be perfect but for me, she is the best one around and I just love her a lot – the story of every happy married couple’s life 🙂

Posted by SQLJason, 1 comment
Creating navigation panel for reports

Creating navigation panel for reports

Recently, I was asked to design a set of reports for a dashboard in SQL Server 2008 which would all have the same report parameters passed to them. Also, the users wanted to switch between the reports frequently and hence needed a navigation panel on the left hand side preferably. The moment I heard about navigation panel on the left side, the first thing that came to my mind was document maps (yes, this is the post that I said I would come up with in my previous post). But then the conservative within me was trying to stifle out this idea saying that it can not be used, and even if you are able to use, it would be a big performance issue as all the reports would have to be rendered within the same report at the same time. Another five minutes of brainstorming and I shut the <beep> out of the conservative in me. Before we start, it is good to know that the main concepts we are going to apply in this post are Document Maps and On Demand Report Processing. So if you are not familiar with these concepts, please go through How to add a Document Map to your reports and On-Demand Report Processing in Reporting Services 2008. Once you are set, follow the below instructions:- 1) Suppose you have 3 reports and you want to have a welcome page with a navigation panel. First, design your home page in the layout of the report. 1 Home Page section  2) Then, make a matrix below and drag & drop a subreport from the toolbox to the cell section of the matrix. Right click on the subreport and select subreport properties. 2 Subreport Properties  3) Select the subreport name that you want to display from the drop down list. 3 Add the subreprot name  Do steps 2 and 3 for each of the report that you need to display in the navigation panel. 4) Right click on each of the tablix, select the tablix properties and tick the ‘add a page break before’ option. Also make sure that there is a dataset name associated with the tablix, else it would throw an error when we preview. Just make a dummy dataset for this purpose if it is not already there. 4 Tablix properties Note that I have deleted the header row of the matrix. Even though this is not necessary, I did it to reduce the spacing between the start of the page and the subreport.  5) Make sure that border lines of the tablix have been made invisible. 6) Select each tablix and press F4 to open the properties panel. Write the name that you want to see in the navigation panel to the DocumentMapLabel property 5 Adding document map label to tablix  7) Now we are all set to preview the report. 6 Home Page  On clicking the first report, we get the following page Order Count by Cat Report  On clicking the second report, we get the following page Rich text report  On clicking the third report, we get the following page Subcat sales by Prod report  This technique can be extended to passing the same report parameters to all the subreports and hence, creating a feeling of having a single parameter toolbar. And there would be no nasty green processing symbol also 🙂 P.S. : This report has been done in SQL Server 2008 and even though it can be replicated in SQL Server 2005, it is not advisable unless the reports are very small. The reason is that SSRS 2008 has a new feature called On Demand Report Processing, because of which processing can be delayed until a user navigates to a particular page showing that data region or hitting an expression that references information from those other datasets. Hence, in effect, the time taken to display the report would be equal to the time taken to display the subreport in that page (as there is only one subreport in one page). But in 2005, it would be equal to the sum of the times of all the subreports and the initial load time of the report can become too high. Anyways, it is worth a try in SSRS 2005, and if it works for you, give me a party 😉

Posted by SQLJason, 2 comments
How to add a Document Map to your reports

How to add a Document Map to your reports

One of the most common things I do during taking interviews is to give real life scenarios and assess the person on how he tackles the situation. Most of the times, what I really look for is how he goes forward in solving the problem – the approach and the way of thinking rather than the answer itself. Another thing that I look for is whether he tries to put some extra effort to implement the solution (especially the reporting part) in an aesthetic and user-friendly way. Most of the candidates would know the answer to some simple question like how to add data labels to their charts but they stutter when I ask what are smart labels or how can we prevent the cluttering of data labels. I would easily give brownie points to someone who details his solution with some extra attention to details. It must be my frequent rendezvous with creating big reports that make me extra fond of document maps in SSRS and an essential part of my SSRS interview questions. It is hard to imagine going through a big book that has no table of contents. But many users and developers have no problem at all in designing big reports without any navigational aid (although at some point of their wretched life, the users are going to curse the person who developed those reports), even though Microsoft has provided this feature. Document Map is a navigational feature in SSRS that, when implemented, allows the user to navigate through the document and its hierarchies. With this feature, you can add a panel to the left of the reports where you can have the list of “contents” of the report. What makes it more special is that on clicking the “content” in the list, you will be directly taken to the page where the content is present. Cool feature, huh? Requirement Suppose you already have a matrix or a table with some groupings as shown below Matrix - Subcategory grouping Suppose the number of subcategories are very large and spans into multiple pages and the requirement is such that each user has to specifically search for a set of subcategories at a particular time of the day. It would make his life a lot easier if there was a document map listing the subcategories on clicking which he would be directly taken to the clicked subcategory Solution 1) Go to the design mode of the report and click on the group properties of subcategory. Subcategory group properties 2) Go to the advanced tab of the properties and set the Document map as the field Subcategory (you can select the appropriate field name from the drop-down). Advanced group properties 3) Save the report, deploy it and then preview it . Report DM Preview Already you can see the panel at the left hand side which contains the list of subcategories. To hide/unhide this panel, you can click on the icon that has been highlighted in red in the above image. Notice that the “Document Map” text highlighted in blue is the report name. 4) Now you can click on any of the subcategories, say Handlebars and you would be taken to the page where the clicked subcategory is. On clicking Handlebars 5) (a) You can also make document map labels for each of your report item. For eg, if you have 2 tables, you can click on the table and press F4 to see the properties. Table document map label 5) (b) You can give the required name in the DocumentMapLabel property as shown above and then see the required result when you preview the report. DM Tables 6) Now that you have seen the toggle symbol, you would have already guessed that it is possible to create hierarchies also in the document map label, for e.g., Subcategory—>Product. For that, all you need to do is to enable Document Map property for the Product group also. Subcategory - Product Hierarchy DM Note Quoting a section from Packtub Learning SQL Server 2008 Reporting Services – “ Document Map is mostly for HTML rendering. Other renderers render differently.

  • PDF: Uses Bookmarks.
  • Excel: Uses named worksheet with hierarchical links. Report sections appear in other sheets.
  • Word document also has a document map like the table of contents.
  • Tiff, CSV, and XML ignore this setting.
  • Recursive data is related to the idea of self joins where the relationship between parent and child is represented by fields in the dataset. “

So there ends the part 3 of my interview series. In my next blog, I would be taking a momentary break from my interview question series and presenting a very interesting application of Document Maps, wait for it 🙂

Posted by SQLJason, 6 comments
Better way of repeating charts based on parameters

Better way of repeating charts based on parameters

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. Query Designer - Add calculated member for rank 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)] Making the Chart 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 Final Layout 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. 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. Adding the filter 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. Final Result This technique would work beautifully on both the 2005 as well as the 2008 versions of SSRS.

Posted by SQLJason, 8 comments
Report selection in parameter toolbar

Report selection in parameter toolbar

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 RP Properties 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. 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. Row visibility Select Row Visibility option. 5) Select the Show or hide based on an expression radio button Setting row visibility expression 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. order count by product Image visibility Rich text 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? 😉

Posted by SQLJason, 4 comments
SSRS reports using embedded images

SSRS reports using embedded images

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

Home                 thumbsup               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.

Add Image

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.

Image Properties

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


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.

Image Visibility report

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.

Posted by SQLJason, 24 comments
Adding breadcrumbs to your drillthrough reports

Adding breadcrumbs to your drillthrough reports

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 Design Mode - Order by Category b) Order by SubCategory Design Mode - Order by SubCategory c) Order by Product Design Mode - 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 Text in Order by Category report 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 Text in Order by SubCategory report 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)” Setting the action property for breadcrumbs This particular javascript will retrieve one page back in history. 4) Similarly, in the third report, add the textboxes in the format shown below. Text in Order by Product report 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. Order by Category Order by SubCategory Order by Product 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 😉 Title

Posted by SQLJason, 1 comment