Column Visibility Based on Group Value

Overview

I recently received a question on how to hide the column of a matrix based on the value of a group.  To illustrate this, I'll use a picture:

The First Attempt 

The logical approach for any developer is to create a table with the appropriate groupings like so: 

Then the developer thinks to go to the 'RAM' column and set its visibility like so:

The formula for hiding/showing the column is set to: =Iif(Fields!Category.Value = "Laptop",False,True). This should work, right? WRONG!!

My example shows the following when I run the report:

Notice how the table now hides the 'RAM' column no matter what the value for Category is.  The reason is because my first record in the data set happens to be a Television category, so SSRS pulls that first value, and evaluates the Show/Hide expression and determines to hide the whole column.  If my first record were a Laptop, then the 'RAM' column would permanently be shown.

The reason why SSRS behaves this way is because of the way the report is scoped.  In order to get the results we want, we have to place the Column we want to control within the scope of the Category.  As it stands now, the 'RAM' column of the table lies outside of the scope of the Category grouping.

The Second Attempt

To achieve the desired results, we need to scope the controls appropriately.  I'll demonstrate one way to do this.  The way I'll manage the scoping in this example is with a List Control.  I'll place a list control on the report, and then place my table inside of that List Control as follows:

The table inside of the list needs to be modified.  We need to scope the Category grouping above the table so we can control the columns of the table with the value of the Category group.  To do this, I remove the category grouping from the table and its associated labels.  I go to the List control and add the Category grouping to the List Control, the same way I had it in the table.  Then, I go inside of the list control and add a text box above the table to display the Category group.  When it is all done, the final design looks like this:

  

When I run my report, I see the magic!  Notice how the 'RAM' column is hidden in my Television group. 

The lesson to learn here is to remember that controls must be scoped properly if we want to manage them based on dynamic values.  Happy reporting!

SSRS Recursive Parent

Overview

In this article I'm going to explain how the 'Recursive Parent' grouping works in SSRS.  This grouping feature was very tricky for me to grasp (mostly due to the lack of documentation and examples available), so my hope is that I can help lower the learning curve for others.

Background

I explored the 'Recursive Parent' option in a recent assignment to create a management report for our SSRS instance.  We wanted to see the entire reporting structure and be able to see key statistics for each report. In this case, we were using SSRS 2012 integrated in SharePoint 2010.  We only had one SSRS instance on one SharePoint server.  

The most difficult part of the assignment was structuring the data so that the report could reflect each folder and its contents.  The structure was certainly hierarchical, but with unknown depth.  Some reports were several layers deep, and others were only 1 or 2.  This type of hierarchy is called a jagged hierarchy.  The 'Recursive Parent' property makes reporting on jagged hierarchies possible.

Getting Started

To begin, I knew that I needed a function in my database to help me split up the report path into its various folders and parts.  I created a function to help me do that.  The function takes a report path, and report name, and returns a table with each row representing the folder structure to get to that report.  The code for my function is below. (It is probably easier to read the code if you copy and paste it into Management Studio or a text editor)

CREATE FUNCTION [dbo].[fnReportPathBreakDown]

(

@reportPath nvarchar(425)

,@reportName nvarchar(425)

)

RETURNS 

@Results TABLE 

(

FolderLevel tinyint

,FolderName nvarchar(100)

,RelativePath nvarchar(425)

,ReportLevelFlag bit

,ParentFolderPath nvarchar(425)

)

AS

BEGIN

declare @workingPath nvarchar(425)

declare @currentIndex int = 0

declare @nextIndex int = 0

declare @currentFolderLevel tinyint = 0

declare @maxLoop int = 1

declare @parentFolderPath nvarchar(425) = ''

declare @currentFolderName nvarchar(425)

set @workingPath = substring(@reportPath,0,charindex(@reportName,@reportPath))--strip off the report portion of the report

if CHARINDEX('/',@workingPath,@currentIndex) = 1

begin

set @workingPath = substring(@workingPath,2,len(@workingPath) - 1) --strip off leading slashes

end

while CHARINDEX('/',@workingPath,@currentIndex) > 0 and @maxLoop < 100--loop through the path and populate our table with the folder levels

begin

set @nextIndex = CHARINDEX('/',@workingPath,@currentIndex) --location of the next slash in the string (should be the end point of the folder name we want)

select @currentFolderName = substring(@workingPath,@currentIndex,@nextIndex - @currentIndex)

insert into @Results (FolderLevel,FolderName,RelativePath,ReportLevelFlag,ParentFolderPath)

select @currentFolderLevel

,@currentFolderName

,substring(@workingPath,0,@nextIndex)

,0

,@parentFolderPath

set @currentFolderLevel = @currentFolderLevel + 1 --advance currentFolderLevel

set @currentIndex = @nextIndex + 1--advance the currentIndex

set @maxLoop = @maxLoop + 1

set @parentFolderPath = substring(@workingPath,0,@nextIndex) --set parent to current relative path

end

declare @rootFolder varchar(50) --the root folder is a GUID representing the SharePoint server

select @rootFolder = FolderName

from @Results

where FolderLevel = 0

update @Results

set RelativePath = Replace(RelativePath,@rootFolder,'//') --replace the root folder GUID with a leading // for better readablity

,ParentFolderPath =  Replace(ParentFolderPath,@rootFolder,'//')

update @Results

set ReportLevelFlag = 1

where FolderLevel = @currentFolderLevel - 1 --the last folder level has the reports

return

END

Passing a path and report name into the function as follows

select * 

from dbo.BAF__ReportPathBreakDown2('/{759a08db-dcfd-4ef5-b7e9-e654ede733fd}/test/BITesting/Documents/Report1.rdl','Report1.rdl')

returns the following result set:

The function breaks each folder into its own row with the specific folder level, the name of the folder, its
relative path, a flag indicating if we are at the level where the report is located, and the parent folder path.  Structuring the data vertically like this is key to making the report work the way we want it to.  Keeping the data vertically allows us to deal with the jagged hierarchy because some reports will return 4 folder levels, others 2, 6, 12...etc.  This structure keeps things flexible so as folders are nested in folders inside the SSRS instance, it doesn't break the report, and we capture the information we need.  Notice that ParentFolderPath has values that are contained in the RelativePath column.  This is very important, and I'll explain this essential point later in the article.

The Data Set

The code for this procedure is quite lengthy, but relatively simple in concept, so I'll explain what it does instead of posting the code.  I built a stored procedure that opens up a cursor and loops through all of the reports in my ReportServer database.  I look in the Catalog table and pass in the 'Path' and 'Name' columns to my function and build up a table with my result set from each call. (I delete all rows with FolderLevel 0 since everything rolls up to that level)  This stored proc returns a select statement that my report can now consume.  The data set looks like this:

The Report

Now that I have my data set set up and structured vertically, I'm ready to build my report.  I set up my report so that I display one group showing each of the folders, and a child group which will display the reports.  My report looks like this:

The Folder group has groupings set on the RelativePath, and the Recursive Parent set to the ParentFolderPath as follows:

Here's the important lesson to learn about using Recursive Parent, which I alluded to in my "very important" point above:  The values in the Recursive parent column (in our case ParentFolderPath) must be found in the Child group (in our case RelativePath).  The way the Recursive parent property works is it looks up the value of the Recursive parent value in the child column recursively until it gets to the top of the hierarchy.

I will try to explain this using some pictures from the data set.  If we isolate the Report1 data in our data set, we have the following 3 rows for that report:

The red square shows the inner grouping of the ReportName, so SSRS knows that these lines all belong to the same child group.  Now, to figure out  the Folder grouping, SSRS looks at the values in the ParentFolderPath (the recursive parent), shown in the green box, and looks for that value in the RelativePath column (child group), shown in in the orange box.

It continues that process recursively as follows:

Now SSRS is on the green box, "///test/BITesing" and so it looks for that member's look-up value, which is shown in the purple box.  It goes back to the RelativePath column to find the row with that value.  It continues this process until it gets to the brown box "//".  There is no value in the RelativePath column for that value, and so it stops the recursion.  SSRS follows the path from green, to purple to orange and then to brown to build the hierarchy.  

This recursive look-up is the reason why the values in the recursive parent column must have the same values as the child group, otherwise the chain will not link together properly.

Formatting

I add a little formatting to my report to hide the report detail lines for all rows except where the ReportLevelFlag = 1 (so the report shows up at the right place in the reporting structure), and I also indent the folder and report according to what level it is.

My padding looks like this:

The formula looks like this:  

=CStr((Parameters!PaddingConstant.Value * (First(Fields!FolderLevel.Value) - 1))+2) & "pt"

I have a parameter called PaddingConstant that I have defaulted to 15. 

Here's an example of what the report looks like.  Notice how the dark blue upper-case headers represent the folder structure, properly indented to reflect the nested folders, and the reports show up under their parent folder.

The Recursive parent property in SSRS is a very powerful feature that makes reporting on jagged hierarchies much easier.  The concept is pretty straight forward once it is understood.  Hopefully this article helps fill in the huge void out there as to how this property works and how to structure the data set to take advantage of this feature.

Happy reporting!

SSRS Exporting Options for Excel

When exporting to excel, sometimes SSRS can behave in a very unpredictable ways.  This little post has a few pointers when configuring excel-friendly SSRS reports.

One frequently requested feature is remove the report headers when exporting to excel.  To do that, you must add some code to the SSRS configuration file to tell SSRS to not export headers.  To do that, locate the rsreportserver.config file by navigating to C:/Program Files/Microsoft SQL Server/MSRS[Version].[Instance]/Reporting Services/ReportServer.

Then add the following code to the <Render> tag.

<Extension Name="EXCEL (No Header)" Type="Microsoft.ReportingServices.Rendering.ExcelRenderer.ExcelRenderer,Microsoft.ReportingServices.ExcelRendering">
<OverrideNames>
   <Name Language="en-AU">Excel (No Header)</Name>
</OverrideNames>
<Configuration> 
<DeviceInfo>
   <SimplePageHeaders>True</SimplePageHeaders> 
</DeviceInfo> 
</Configuration> 
</Extension>

To get SSRS to pick up the changes, you'll have to go into report server configuration tool and restart the service.

When you navigate to a report on that server, and you click on the export icon, you should see the new option of "Excel (No Header)".

As you save the file off, you'll notice that the headers are not exported.  If you have blank rows or columns before your data begins, then you need to align your tablix of data such that there is no empty space between the tablix and the header or report edge. I find the best way to accomplish this is to open the tablix properties and set the location to 0,0.

Data Driven Subscription using SharePoint List

In this article I will walk through the steps to create a data-driven subscription using a SharePoint list as a data source.  I'm using SSRS 2012 integrated in SharePoint 2010.

In the data driven subscription pane, choose a Description, set the Data Source Type to Microsoft SharePoint List, enter the url to the SharePoint site (obviously replacing the {SharepointDomain}/{SPSite}/ with the appropriate values, and entering in the credentials to be used by the subscription to access the list and execute the report.

The Next part is building the Query to be used.  I find it easiest to build the query by opening up SSRS Report Builder or Visual Studio and using the Query Designer inside of SSRS to help me.  In the example below, I've set up a SharePoint Data Source in my report called "SharePoint" which has the same same connection string as above:  http://{SharepointDomain}/{SPSite}

Opening up the Query Designer will take you to a page that looks like this.  In this example, I'm going to use the "subscription test" list for my data-driven values for the subscription.  I select the fields I need, and in this case, I put a filter in it to restrict my list to only list items that have a BeginDate less than 10/1/2013.  You may test your data by pressing the "Run Query" button as shown in the green box below.  This allow you to see exactly how the data will be returned.

Pressing "OK" takes me back to the original window and gives me my query syntax.

I copy the selected syntax and then go back to the data-driven subscription window and paste that code into the Query section.  Pressing the "Validate" button will check to make sure the syntax is correct.  At this point, the hard part is done...the rest of the subscription should be pretty straight forward.

The next page lets us wire up the parameters with the BeginDate and EndDate we retrieved from the SharePoint list query.

The next page lets us wire up the email address to the one in our SharePoint list.  It also requires us to specify a rendering type and subject.

The remainder of the subscription is setting the schedule just like any other subscription, so I won't provide any details on that screen.  Pressing "Finish" gives you a data driven subscription using a SharePoint list as your data source!

The hardest part for me is getting that query.  I've found that using the SSRS query designer to help me write the query is much easier than typing it all in myself.  The query designer is usually where I go to get the "stub" of my query, and then I manually change the bits and pieces in it that I need for the particular situation.  Good luck in your next SharePoint driven Data-Driven subscription!

Custom Subscription Description SSRS

One major limitation in SSRS is the inability to set the description property of a normal subscription.  Data-driven subscriptions allow for custom descriptions, but normal subscriptions do not.  Here's an image of what I'm talking about.

The description property is auto-generated for you.  In the example above, this report has 3 different subscriptions, each one with a different parameter setting.  Granted, this subscription could be turned into a data-driven subscription if none of the parameters are multi-select parameters.  In the case where the report requires multi-select parameters, data-driven subscriptions will not work.  Managing these subscriptions is a huge pain because the descriptions are so useless.

Background Story

Not long ago, I worked for a company that managed a daily regional report to managers through a subscription.  The parameter in the report was a multi-select State parameter that allowed one report to be used for all regions in the US.  For example, the Western region had California, Oregon, Washington, and Hawaii selected.  The Mid-Western region had Nevada, Utah, Idaho, and Wyoming, etc.
Each subscription emailed the report to the regional management team with the appropriate states selected in the parameter (because the parameter was a multi-select, using a data-driven subscription would not work!). After getting the 12 or so unique subscriptions set up on the report, the subscription management page looked a lot like the one above, with completely useless descriptions.  It wasn't long before I would get calls saying, "Please remove Roger from the Mid-West Region 2 report and add Ann".  How did I locate the Mid-West Region 2 subscription?  By going through the list and opening up each subscription detail and looking at the subject line.  I was at least smart enough to put the region name in the subject line of the email delivery options.  Needless to say, it wasn't long before I reached the end of my patience in this extremely cumbersome task of managing these subscriptions.

The Solution

The solution I came up with was to hijack the Subscriptions table in the ReportServer database.  I needed to over write my own subscription names in the [Description] field of that table.

Step 1

Create an override table with the names of the custom subscription names you want.  I chose to create my table in the ReportServer database.  Here's my code:

create table tblSubscriptionDescriptionOverride

(

SubscriptionID uniqueidentifier not null primary key

,[Description] nvarchar(512) not null

)

Step 2

Populate the override table with the SubscriptionID and custom descriptions.  To find the SubscriptionID, I made a simple modification to the Subscription, then immediatly ran this code on the ReportServer database.

select *  from [ReportServer].[dbo].[Subscriptions]

  order by ModifiedDate desc

This query will return all the subscriptions in the order in which they were last modified, so the subscription that was just changed will be on top, as shown below.

 Once I had the SubscriptionID, I used it to populate the insert statement into my override table as follows:

insert into tblSubscriptionDescriptionOverride (SubscriptionID,[Description])

values ('4B1BE0ED-72C3-45AB-B366-B2EEB400B7BF',N'North East Region')

Step 3

Create a trigger on the Subscriptions table so that any future updates to the subscription will always re-populate the custom description.  In a nutshell, this trigger will take the subscription being updated, compare it to any values in the override table and use the value in the override table as its description instead of the auto-generated one.  If no values are in the override table for that subscription, then it will use the default, auto-generated description as normal.

create trigger trgCustomSubscriptionDescriptionUpdate on dbo.Subscriptions

after update

as

update subs

set subs.[Description] = coalesce(ovr.[Description],subs.[Description])

from

Subscriptions subs

join inserted i

on subs.SubscriptionID = i.SubscriptionID

left join tblSubscriptionDescriptionOverride ovr

on subs.SubscriptionID = ovr.SubscriptionID

Step 4

Test it out to make sure it is working.  Open up each subscription and save it again.  You should see that the Description property is now being populated with the values in the override table!

SSRS 2012 Custom Indicator

In this demonstration, I will show how I used custom indicator graphics to create this report.

I'm using SSRS 2012 in SharePoint 2010, however, I believe the content will work with 2008 R2 and above versions of Reporting Services either in native or SharePoint mode.

Background Story

A week or two ago, I was given the assignment to use SSRS to mimic a report the executive management team used in Roambi.  Roambi is a mobile app that displays data in very rich, graphical ways.  All the executives use this report on their iPads and they love it.  Since Roambi only has a mobile platform, the executives cannot access the report on their desktops—hence the reason for me to mimic the report in SSRS.

The Unique Chart Image

Roambi has many more chart types in its interface than SSRS.  One that was used heavily in this report was the horizontal bar chart with a vertical goal.  The images below show what the charts look like.

75% of goal

125% of goal

These two images represent two different states.  The one on the left shows a value that is under target.  The one on the right is slightly above target.  These graphs visually display one metric compared to a goal or target value (think sales vs a sales goal).  If the metric is under the goal, the bar is displayed in red, and if it is at or above the goal, then the bar is green. The closest thing SSRS has to this chart type is a bar chart—however, there is no way (at least one that I could see) to display the vertical goal line, and change its bar color based on a calculation.  The SSRS Bar chart is simply a column chart flipped horizontally.

Realizing that the report will require custom graphics, I went to work using GIMP to create the following images to represent the various scenarios/states (creating these images is a tutorial in an of itself, but here is a link to a great article that helped me in creating these).  By the way, if you like my graphics, you're welcome to download them and use them yourself--or if you want the entire GIMP file that I used to create them, send me an email and I'll be happy to send you my project file.

I created images to map the range from 0-300+% of the goal.  I added the images to my SSRS project. The images folder in my SSRS project looks like this (there are more images in my folder than just these custom graphics).  I labeled all of the custom graph images as "BarToGoal_".

Add the Indicator Control Type

In the cell that I wanted to display the graph, I selected the indicator control type from the report toolbox or from the ribbon if using Report Builder 3.0

Visual Studio--Tool box

Report Builder 3.0

Configure the Indicator

Once the Indicator is added, it pops up a window to select an indicator type.  It doesn't matter which one is chosen since it will be customized.  For this example, I selected the "5 Quarters" one.

I open up the configuration options by right clicking on the indicator and selecting "Indicator Properties".

When the Indicator properties window opens, I select the "Values and States" option from the left-hand menu of the properties window.

Set the Value

In my example I want to compare one field to another.  I open up the value box and type in my expression. A simple example could be something like =Fields!SalesDollars.Value/Fields!SalesGoal.Value

Set the Measurement Unit

I change the "States Measurement Unit" drop down to "Numeric".

At this point, my properties window looks like this:

Set the Image

In this example, the indicator has 5 defaulted images and values to use. I want to override those with my own images and values.  I click the drop down in the Icon column and select "Image".

The image source is "Embedded" and the name of the image I want is BarToGoal_10.  I set the Start value to 0.0 and the End value to 0.10. What this means is that if the value of my metric is between 0 and 10% of the goal, then this image will be displayed.

I continue to configure the remaining indicator states, adding additional ones as needed.

Note:  I made sure to include an indicator for all values ranges.  If the value evaluates to something not within a specified indicator Start and End value, no indicator will display.

That is how to configure custom indicator graphics in an SSRS 2012 report.  You may also find Microsoft's documentation helpful.

Introduction

Hello,

My name is Jayson Seaver.  I'm a BI (Business Intelligence) developer.  I use the Microsoft BI Stack, and I've done quite a bit of web-searching in my career to help me figure out what I'm doing.  I appreciate how well we are able to communicate with one another through blogs, forums, and articles.  Much of what I call "experience" has come from reading some stranger's blog or forum post--and then a lot of trial and error.  In that spirit, I wish to now post my own thoughts and knowledge in the things that I'm learning.  My hope is that someone may find my articles useful.  In addition, writing my thoughts down, in a way to teach someone else, has the greatest impact on my ability to learn and retain new things. Here's to my new BI Blog!