Recursive Reporting with SSRS
In this blog I will walk you through an example of how to use SSRS to report on recursive data. If you have a self referencing table or even a Parent-Child hierarchy setup in an SSAS multidimensional cube you could find these techniques useful. In our example below we want to display the data within a multi-level hierarchy. We will be working with HR data and we want to see the CEO at the top of hierarchy and then see who reports to who after that.
In this example I will be using the Adventure Works sample data warehouse (AWDW) so that you can follow along step by step. Let’s assume that we already have a blank report created and that we also have a shared data source that’s connected back to the AWDW.
We will be working with the data from the DimEmployee table. You can use the following query to return the columns that we need.
Select
[EmployeeKey]
,[ParentEmployeeKey]
,[FirstName] + ‘ ‘ + [LastName] AS FullName
,[Title]
From [dbo].[DimEmployee]
Now let’s get to the fun stuff. The first thing that we need to do is to build out a table. Insert a table into the design tab and then drag over the FullName and Title fields into the table/report.
To further show you what’s happening here we will use an SSRS expression for a third column. I labeled this new column as Level. The label of this new column probably gave it away but we will be using the Level function within the SSRS expression. The Level function will return a zero-based integer representing the current depth level of a recursive hierarchy.
Here’s the expression, =Level()
What your report should look like up to this point
Next we then need to navigate to the to the group properties of our Detail Group.
On the general tab of the group properties we need to add a grouping on EmployeeKey as shown below.
Pretty easy but we’re not done yet. On the Advanced tab of the group properties you will notice a section labeled as Recursive Parent. In this section we need to select the ParentEmployeeKey as shown below.
Go ahead and preview the report and you will notice that the report will work. However, if you look closer it’s still less than desirable to look at because it’s still somewhat challenging to see the parent child relationship. Notice the third column we created earlier with the Level function.
Let’s clean it up a bit by using some additional SSRS expressions.
The first one will add some additional padding so that it looks more like a natural hierarchy. Right click on the FullName column in the detail row group and go to Text Box properties -> Alignment. Locate the Left Padding property and use the following expression. As you can see this expression is using the same Level function like we did earlier to dynamically set the padding based on what level in the hierarchy it lands.
=CStr(2 + (Level()*20)) + “pt”
Next we will set the font style of the parent values in the hierarchy using the following SSRS expression. This expression will count the number of rows returned for each group, if it’s greater than 1 it will have bold font.
=IIF(Count(Fields!FullName.Value, “Details”, Recursive) > 1, “Bold”, “Normal”)
Now preview the report again and you will notice the additional formatting we just applied. With this new formatting applied we can quickly and easily see how the data and the different levels are related to each other.
Do you need to add drilldown functionality for each level of the hierarchy? If so navigate back into the Details row group properties. On the visibility tab set the group to be hidden and toggle this based on the FullName field within the group.
Preview the report now and you can see the drilldown that we just implemented.
We can even adjust the row background color based on the Level as well.
Use an expression like this and pick the colors of your choice. The row property that needs to be set is BackgroundColor.
=SWITCH(Level()=0, “Tomato”,Level()=1, “Silver”,Level()=2, “LightGrey” ,Level()=3, “Gainsboro”,Level()=4, “Khaki”)
I hope you enjoyed this post, thanks.
SSRS Bookmarks and Go to Bookmark Actions
SSRS bookmarks are essentially customized navigational links in the report. Follow along with the steps below to see how they work.
In SSRS create 3 rectangles as shown below. Within each of the three rectangle properties add a page break after. Within the first rectangle add two text boxes. One labeled Page 1 and the other labeled Page 2.
In the middle rectangle add another text box and label that one as Page 1 as well. Select the text box for Page 1 and press F4 for the properties. The property that you’re looking for is called Bookmark. Type in a label for this first bookmark.
Do the same thing for the third rectangle and label the text box as Page 2.
Then go up to the first rectangle and select the text box with Page 1. Go to the text box properties and set up an action that goes to a bookmark. If you don’t see anything in the dropdown just type out the bookmark name that you created in a previous step.
Do the same thing for the Page 2 textbox within the first rectangle and follow the previous steps of creating the Go to Bookmark action.
Now all you have to do is test it. Preview the report and click on one of the text boxes in the first rectangle. When you click on Page 1 it takes you to the second rectangle on Page 2. And from the first page/rectangle when you select the Page 2 in takes you to the third page labeled Page 2.
Changing the SSRS Site Name
Within the native installation of SSRS you have the ability to change the site name. The default name for the site is SQL Server Reporting Services as shown below.
As an SSRS developer or administrator I have the ability to change this.
Step 1: Log into the SSRS instance through SQL Server Management Studio (SSMS). Right click on the server name and select properties.
Step 2: Navigate to the advanced tab on the left and look for the property labeled, SiteName. By default its set to SQL Server Reporting Services as mentioned above.
Step 3: Once the SiteName property has been set go back to the report manager to confirm that the site name has been updated. In the below image you can see that I changed the site name to Report Portal.
By the way, you can also do this through the site settings within the report manager as well.
Also, this link has a listing of all of the advanced server properties for SSRS.
Turning Off Report Builder
In some cases you might need to turn off Report Builder within SSRS. It’s an easy process as you will see below.
Step 1: Log into the SSRS instance through SQL Server Management Studio (SSMS). Right click on the server name and select properties.
Step 2: Navigate to the Advanced tab on the left and look for the property labeled, EnableReportDesignClientDownload. By default its set to True but you can turn it to False.
Step 3: Once the EnableReportDesignClientDownload property has been set to False go back to the report manager to confirm that the report builder option is no longer available.
Here’s an MSDN article with more information on this.
https://msdn.microsoft.com/en-us/library/ms365173.aspx
Also, this link has a listing of all of the advanced server properties for SSRS.
Auto Refresh an SSRS Dashboard or Report
You might be working on a new SSRS dashboard for a top level executive. He or She likes the dashboard so much they want to have it displayed on a giant 70 inch monitor outside his/her office. The one last request that they have is for you to setup the SSRS dashboard to update every X number of seconds.
This is an image of a sample dashboard using SSRS and the AdventureWorksDW2012 sample database.
In order for this to refresh to work, open the report and go into the report properties or press F4.
The property that you want to change is called AutoRefresh, go figure :).
Make the change and go preview the report in SSDT. It will refresh every 5 seconds without having to click on the view report button. You should also validate this works once it’s been deployed to the report manager or SharePoint. By the way this property setting is calculated in seconds.
Deploying a DLL for SSRS to the Global Assembly Cache (GAC)
With SSRS you can create your own custom assemblies to help extend SSRS with your own custom code. You can also use it to externalize and reuse some other code from outside the scope of a report. This is out of scope for this blog but if you want to see how this is done go check out this free webinar from my friend Devin Knight, http://pragmaticworks.com/Training/FreeTraining/ViewWebinar/WebinarID/393.
The focus of this blog is to help you with the deployment of the DLL once the .NET development is complete.
Once the DLL has been created it needs to be deployed to two different locations in order for SSRS to use/reference it.
1. The first location that it needs to be deployed to is SSRS. It should resemble the following unless you changed the default during the installation. C:\Program Files\Microsoft SQL Server\MSRS11.MSSQLSERVER\Reporting Services\ReportServer\bin.
Usually you can just paste the DLL into the above directory.
2. The second location is the GAC. The GAC is located here, C:\Windows\assembly. In order to deploy the DLL you will need admin access to do this.
Deploying the DLL to the GAC can be done in a couple different ways. If you try to paste the DLL into the GAC it will not work. So if you can’t paste the DLL you can try to drag and drop it into the GAC. This option should work in most cases.
However, if this option doesn’t work for you can try the following by using the Visual Studio command prompt ( https://msdn.microsoft.com/en-us/library/ms229859(v=vs.110).aspx ).
Run this as an admin, C:\ProgramData\Microsoft\Windows\Start Menu\Programs\Microsoft Visual Studio 2012\Visual Studio Tools
As you can see in the image below, I changed the command prompt directory to the location of the DLL that was created during the visual studio development. This is highlighted in yellow below.
Once that’s done hit enter.
I then use the gacutil ( https://msdn.microsoft.com/en-us/library/ex0ss12c(v=vs.110).aspx ) and reference the DLL I created for my SSRS reports, shown below in the red square/box.
Hit enter and if you see the successful message your DLL should have been deployed to the GAC.
This blog has been tailored for SSRS however the previous steps can be used to deploy any type of DLL to the GAC.
April 2015 Speaking Engagements
This month I will be teaching one session on SSRS. This isn’t a beginner session for someone new to SSRS. This is a more advanced class to take your SSRS levels to the next level. These sessions are designed for someone who has some experience with SSRS already. However, I will be teaching the intro class a couple more times before the end of the year. Check back for future post about upcoming dates.
Overview: Master Report Developers must hold unique traits to set themselves and their work apart from others. This two day class is designed to take you to the next level in your understanding of SQL Server 2008R2/2012/2014 Reporting Services (SSRS). You will learn how to create complex reports that use Analysis Services as a data source and custom code assemblies. This course will cover all the advanced topics of Reporting Services like scaled-out deployment, .Net integration and monitoring Reporting Services usage. This class is built to work for students using one of the following versions of SQL Server: 2008R2/2012/2014.
For more details or to register please go to the link below.
March 2015 Speaking Engagements
Next month I will be teaching two different sessions on SSRS. One will be in a workshop setting where you can learn some advanced SQL Server Reporting Services (SSRS) skills. This will be in Atlanta Georgia that I will be co-teaching with Devin Knight. These sessions are designed for someone who already has some experience with SSRS. This isn’t a beginner session for someone new to SSRS. However, the other will be a virtual class that will focus on ramping up your SQL Server Reporting Services (SSRS) skills for anyone new to SSRS.
The first one is an in person workshop at the Microsoft office in Atlanta, GA. The workshop is only 2 days long (3/10/2015 – 3/11/2015) at a very affordable price, $399.
Overview: Master Report Developers must hold unique traits to set themselves and their work apart from others. This two day class is designed to take you to the next level in your understanding of SQL Server 2008R2/2012 Reporting Services (SSRS). You will learn how to create complex reports that use Analysis Services as a data source and custom code assemblies. This course will cover all the advanced topics of Reporting Services like scaled-out deployment, .Net integration and monitoring Reporting Services usage. This class is built to work for students using one of the following versions of SQL Server: 2008R2/2012/2014. Audience participation is encouraged so bring your laptop ready to go with the system requirements below.
This facility can only hold a maximum of 60 people. So sign up ASAP before it’s full. For more details or to register please go to the link below.
The other session is a virtual session that can be attended from anywhere in the world. I have had people from Asia, Africa, and Europe attend this class virtually without any issues. This is a 4 day class (only half days) that will be on 3/16/2015 – 3/19/2015 from 2-5 EST. The great thing about this class is that you have access to the recordings for up to 7 days after the class for reference and 2 hours of our BI Virtual Mentor service for assistance with YOUR project work.
Overview: This is a four day class designed to help you understand all aspects of SQL Server Reporting Services (SSRS), the reporting tool of choice for SQL Server developers. We will use a variety of lab exercises and small projects to ensure you gain familiarity in how the design and execution environments work and to prepare you to build your own real world applications using this SQL Server subsystem. With the hands-on labs, you will learn how to create complex reports. This course will cover all the key concepts of Reporting Services from basic reporting, parameters, deploying, and scheduling.
For more details or to register please go to the link below.
SSRS Repeating Page Headers: Option 3
This is the third post in this series where I have shown you have to resolve this issue in SSRS. Check out my other 2 blogs on this same subject, you can find those links posted below for additional workarounds.
This third option here is probably the easiest to work with or at least start with. If you don’t like altering the XML of a report or going into the advanced group settings you also can start the report using the SSRS report wizard. That’s’ right, if you create a report using the SSRS report wizard it will automatically setup the report to use repeating page headers. If you don’t believe me go give it a try.
After you have done this you can go look at the XML of the report and you will see the XML settings and how it has been setup to get these feature working. Go check out my second blog in this series to how this can be done.
Option 1 https://chrisalbrektson.wordpress.com/2015/01/15/ssrs-repeating-page-headers-option-1/
Option 2 https://chrisalbrektson.wordpress.com/2015/01/26/ssrs-repeating-page-headers-option-2/
SSRS Repeating Page Headers: Option 2
If you missed the first part of this blog, please go checkout this other blog for an additional workaround.
In my first blog on this subject we reviewed how you could open the advanced mode of the groupings pane to fix this issue by adjusting a few properties. Well those properties we adjusted in the first blog are going to be used again here in this blog.
In this workaround you will need to adjust the XML of the SSRS report you’re working on. Before you do this make a quick copy of the report (Ctrl + C and Ctrl + V) before you proceed with this option. The reason for this is that you could easily corrupt the report you’re working on and it’s always nice to have a backup in case something doesn’t work out here. It only takes 2 extra seconds to do this and could save you a lot of time from having to re-create a report, especially if you don’t have source control.
Locate a report you need to apply repeating page hears to. In my example below I will be doing this on a copy of the original report I used in my first blog. In this example it’s a report with a couple row groupings.
Right click on the report and select View Code
After you have done this another tab will open in SSDT and you will be able to see the XML that’s running behind the scenes. Don’t worry too much, this can be a daunting task to look through but I will show you below what you need to look for and what you need to change.
Probably the easiest item to search on here is based on the following image below. Locate the KeepWithGroup property under the TablixRowHierarchy. This section of XML can be very long and if you notice in the image below I have it collapsed quite a bit. You could also locate this be looking for the first row group name (CalendarYear) and then move up to locate the KeepWithGroup property
Once this has been located add the following to lines of code as shown below.
<RepeatOnNewPage>true</RepeatOnNewPage>
<KeepTogether>true</KeepTogether>
And that’s it. Save the XML file and go back and open the report. If you get an error saying that you can’t open the report then you added the code to the wrong area. Go back and delete the code you added or go back to the original report and start over.
Run the report and you should now see the page headers repeating on every page.
Notice in the image above I’m on page 8 and you can still see the page headers with a report with a few row groups.
Again this is just one option. Check back because I will be adding 1 additional blog on how you can overcome this in the next few weeks. You can also go back and checkout my first blog on how to overcome this problem.
Option 1, https://chrisalbrektson.wordpress.com/2015/01/15/ssrs-repeating-page-headers-option-1/
Option 3, https://chrisalbrektson.wordpress.com/2015/02/09/ssrs-repeating-page-headers-option-3/
ChrisAlbrektson 