Posts Tagged ‘SSRS’

SSRS #75 – Use RunningValue() Function and VB Script to capture discrete dates

November 11, 2014 Leave a comment

Drill-down report is a common design

In SSRS, it’s a common design to allow users to drill down to more granular data in a same report.

In the following snapshot, users will see a summary line that shows the Call Handled 130. The two days, 11/4 and 11/5 will not be immediately visible. Only when the user clicks the + sign in the All Dates field, the two detail lines will become visible. All this is happening in the same report.









Result set can contain only discrete dates

In this particular report, I had two date filters, as shown below.



The date filters ask for data in a continuous date range, starting from 11/1 to 11/10. However, the report is only showing some discrete dates per employee. This is because other filters suppress certain dates.

The point here is that although the date filters ask for data in a continuous date range, the result set contains only discrete dates within the date range.

Now the users ask you to create a hyperlink to go to a separate report, to show data for that employee, and for those discrete dates only.

The challenge becomes how do we capture those discrete dates within a group (The report is grouped by employee).

Capture discrete dates in SSRS

You might attempt to create a stored procedure to run the same query for the current report again so you can capture those discrete dates in your code, and then pass them to your second query to get the new report.

This will not be my first choice though, because it requires a lot of coding, not mention that the query performance might suffer because I will need to jam too much and redundant logics into one query.

Instead, I want to accomplish it entirely in the SSRS report.

Before I create the hyperlink on the summary line, I created a new column “Running Values” to just test my idea of capturing those discrete dates per employee.










I named this column “Running Values”, because I know that the RunningValue() VB function is what I need to use. A while ago I blogged about this wonderful aggregate function.

SSRS #72 – Use RunningValue() Function to Concatenate Row Values

As a matter of fact, all the aggregate functions in the following screenshot allow you to do aggregation within a group in SSRS.









RunningValue() function needs an expression, an aggregate function and a group name

To use the RunningValue() function, I’ll need three parameters.

  • An expression, which normally is a field name, such as Yearly Income.
  • An aggregate function, such as MAX, SUM.
  • An group name, which is called GROUP_EMP in my report.

It is quite obvious that using the RunningValue() function out-of-box will not work for me, because I am not doing any direct aggregation on dates per employee.

It’s time to go back to my old blog, SSRS #72 – Use RunningValue() Function to Concatenate Row Values.

A VB script that simply concatenate the dates per GROUP_EMP

What I need is a VB script that allows me to simply concatenate the dates per GROUP_EMP. Every time the script is called, it keeps the prior dates, and adds the new date to the concatenated string.

This function will do exactly that. In Report –> Report Properties window, write the following code, making sure that the two strings aStr and tempStr are defined outside the Public Function/End Function block. This will make sure that both aStr and tempStr are saved per GROUP_EMP.


















Here is the code in text.

Dim aStr as String = “”
Dim tempStr as String = “”

Public Function ConcatenateString_Dates (ByVal inEmployee_ID As String, ByVal inDays As String) As String

If inEmployee_ID = aStr Then
tempStr = tempStr + “,” + inDays
aStr = inEmployee_ID
tempStr = inDays
End If

Return tempStr

End Function


Calling the RunningValue() function from the test column

Now it’s time to use the RunningValue() function.

In this test column RunningValues, I need to call the RunningValue() function.












Here is the actual code I used.

=RunningValue(Code.ConcatenateString_Dates(Fields!EMPLOYEE_ID.Value, Fields!CAL_DAY_LOCAL.Value), Max, “Group_Banker”)

The trick is in the first parameter, the expression.

Code.ConcatenateString_Dates(Fields!EMPLOYEE_ID.Value, Fields!CAL_DAY_LOCAL.Value)

This expression is not a simple field, it is the VB script I just created to concatenate the dates per employee.

The aggregation function I used is MAX. This allows me to get the last concatenated string, which is all the dates within one GROUP_EMP.

URL encoding allows more robust drill-through design

Now I am ready to just pass the following as a dates parameter to my next report.

RunningValue(Code.ConcatenateString_Dates(Fields!EMPLOYEE_ID.Value, Fields!CAL_DAY_LOCAL.Value), Max, “Group_Banker”)

Calling another SSRS report, or creating a drill-through report is normally pretty straightforward. SSRS has an out-of-box feature called Action for textboxes that can do that perfectly. But to make your reports more robust, and lasting long after the developer has moved on, there is something called URL encoding that VB scripts are good at. That can be another blog topic.

SSRS #74 – 2008 Cascading bug when you have a middle parameter

May 9, 2014 2 comments

Some SSRS reports have a large number of parameters

I recently had a chance to help a co-worker to modify an existing SSRS+Cube report. The first thing that caught my eye is the large number of parameters in the report.

Although I never really developed any reports that have more than, let’s say, 6 parameters, I can understand why developers sometimes need to use, let’s say more than 20 parameters. This happened most likely because, the report developer is lack of authority to re-design the cube, or because the report has unnecessarily complex design. Another possibility is that the developer had made conscious decision to avoid writing MDX queries and shifting the coding to the Reporting Services.

A bug in SSRS 2008

Here is a scenario where developers sometimes might need to resolve to using a hidden parameter, that sits in the middle of two parameters. In the diagram below, the middle parameter will take the user input from parameter 1, and have some IIF statement to transform the input, and then the dynamically set value will be the input for the dataset 3 which feeds parameters 3.


I’ve tested this type of "cascading" parameters (with a middle parameter that has a dynamically set default value). The short conclusion is this: in SSRS 2008, the parameter 3 failed to reflect user’s section in parameters 1; in SSRS 2012, all is good.

So I had to say that this is a bug in SSRS 2008.

Remove the middle parameter to work around the bug

To work around the bug, fortunately, we have a pretty simple solution. Simply remove the middle parameters, AND then code the IIF logic in the query parameter for dataset 3 (which feeds parameter 3).


The only drawback I can see in this workaround is that if the transformation logic needs to be used for another dataset, then you have no choice by repeating it.

Longer version of the solution

The following is the longer version of the solution.

My Original Suggestion
  • Use a middle parameter to do the transformation, with nested IIF statement in both Available Values and Default Values.
  • Then pass the middle parameter to the cascading dataset; the middle parameter is passed directly to the dataset without the IIF statement.
  • The solution failed in SSRS 2008: the cascading dataset DataSet_Employees would never get refreshed even when the middle parameter is refreshed correctly when ever a new Department is selected by the user.
  • Reason for the failure: it might have to do with how the value for the middle parameter is set programmatically with the nested IIF statement, rather than by user selection.
  • Good news: this solution worked in SSRS 2012 in my test.

Here are two screen shots from our failed solution.



New Solution That Worked
  • Remove the middle parameter entirely.
  • Pass the parameter Departments to the cascading dataset, DataSet_Employees, instead of the middle parameter.
  • The Departments parameter needs to be modified to perform the transformation; use the same nested IIF statement we used in the middle parameter.
  • Voila. It works.
  • Reason for the success: by removing the middle parameter and coding the nested IIF in the parameter that is passed to the cascading dataset, Reporting Services made no mistake, but had to refresh the cascading dataset based on the user selection.

Here are a few screen shots from our successful solution.


The nested IIF statement is now coded in the parameter expression.


SSRS #73 – Marlon Ribunal and Mickey Stuewe’s New Book: Reporting Services 2012 Blueprints

January 24, 2014 Leave a comment

The book’s link is here, SQL Server 2012 Reporting Services Blueprints.

This book is a step-by-step, task-driven tutorial that goes straight to the practical development of reporting skills, explaining actions as they are taken. If you perform the role of report development using SSRS in your job and you already have a basic knowledge of how data source and datasets in SSRS work, this book will advance your reporting skill to the next level.

In order to accomplish all the steps discussed in this book, including the steps on how to integrate SharePoint 2010/2013, PowerPivot, and Power View with SSRS, it is recommended that you use SQL Server 2012 Enterprise or Business Intelligence Edition. You can still use the Standard Edition to accomplish most of the steps in the book but it doesn’t support the advanced BI features, such as PowerPivot and Power View.

To create SSRS 2012 reports, you have two options:

  • You can use the SQL Server Data Tools (SSDT). SSDT is Visual Studio shell which is an integrated environment used to build SQL Server database objects. You can install SSDT
    from the SQL Server installation media.
  • You can also use Visual Studio 2012. If you use Visual Studio, you must install the SSDTBI templates. SSDTBI (SQL Server Data Tools-Business Intelligence) is a component that contains templates for SQL Server Analysis Services (SSAS), SQL Server Integration Services (SSIS), and SQL Server Reporting Services (SSRS) for Visual Studio 2012.

To try the examples in this book, you will need to have 2012 AdventureWorks Sample database.

In Chapter one you will learn right away different tricks on how to implement parameters for  
different scenarios, ability to exclude one or more parameters at runtime, drop-down parameter, multivalued parameter, cascading parameter and cascading multivalued parameter.

I really appreciate Chapter 2 in which a section is devoted to create custom report template. Report templates not only save time but also creates visual consistency that suit your organization branding. Number crunching is really all about data summaries, aggregates, and groupings. Through practical examples in Chapter 2, you will be able to pick up the skills very quickly.

Another essential skills in creating SSRS reports is to be able to add interactive elements, such as Actions, Tool tips, Document Map, Sorting and Bookmark to a report. In SSRS, one report can also interact with another report through Subreport, Drillthrough report or linked report. You will absolutely love Chapter 3 which focuses on adding actions to SSRS reports. 

Have you ever wondered if SSRS is also a good tool for data visualization? In SSRS 2012, in the report designer toolbox, you will find a few report items that are new, they are, Data Bar, Sparkline, Indicator and Map. Chart and Gauge exist in the prior versions.

Chapter 4 will show you how to create reports in SSRS 2012 with data bars, sparklines, gauges, and indicators. Next time if you need to meet the common data visualization requirements, you will find that this book can make your data visualization work seem effortless.

I particularly like Chapter 5 which goes right into map visualization. It not only shows how to use the built-in maps for the United States, but also how to import and use commercial shapefiles (map files) to create world map report.

If you are tasked to develop reports based on the cubes in the OLAP, Chapter 6 will show you how to survive simple implementation just with the query designer in SSRS.

The last chapter in this book goes beyond just report development. It will show you how to get your SSRS reports ready for production. You will find practical information on deployment configurations, user security, shared schedules, cached reports, and snapshots. It also dives into report subscriptions with e-mail delivery / scheduled delivery and data-driven subscription.

Overall, this SSRS 2012 blueprints book is a well written book and worth buying if you just start out developing SSRS reports in 2012. If you started with the prior versions and already are an experienced report developer, then this book might not be right for you. However, if you are like me and new to the data/spatial visualization features in SSRS 2012, you will find very useful information in this book on how to use the data bars, sparklines, gauges, and indicators and map objects.

Congratulations to Marlon Ribunal and Mickey Stuewe on their first SSRS book. And to reviewers Basit A. Masood-Al-Farooq, Varinder Sandhu, SIVAKUMAR VELLINGIRI and Valentino Vranken. Their combined experience in SQL Server Reporting Services report development makes this book a valuable addition to every SSRS developer’s library.

MDX+SSRS #33 – Retrieve manager’s key and fully-qualified member name

November 8, 2013 Leave a comment

A co-worker recently worked on a SSRS report and wanted to use a specific manager’s team for testing. The Manager is a filter for the SSRS report and can take a fully-qualified member name as the default value.

Using the Employee dimension in Adventure Works cube as an example, a fully-qualified member name can take two forms, key-based and name-based:

  • [Employee].[Employees].&[77]: key-based
  • [Employee].[Employees].[Douglas B. Hite]: name-based

The Manager parameter in the SSRS report is designed to take the key-based fully-qualified member name as input, and the member name as the label.

It is common for us to know only the partial spelling of names. So what would be the quickest way to get a member’s key-based fully-qualified member name (I’ll also refer it as member unique name) when we only know the partial spelling of names?

Retrieve member properties not as data in the result set

Member unique name is one of the many member properties exposed by dimensions in SSAS. I went ahead and showed my co-worker how to write a query using the CurrentMember function to return member properties as data in a result set.

On the second thought, I should have showed him another easier way. That is to use the PROPERTIES keyword to retrieve member properties, including the member unique name. Properties returned this way will not have the property data in the result set.

In SSMS, you can run this query to return each employee’s unique name, i.e. the key-based fully-qualified member name.

SELECT    {} on 0,
             ON 1
FROM    [Adventure Works]

This is part of the results.


Now double-click on an employee name Amy E. Alberts. Here is the Member Properties window you will see.


You notice that the unique name appeared twice. As a matter of fact, by default SSMS query result will return the member properties that are shown in the above window, from Caption to ChildCount.

So by simply running the following query without the PROPERTIES keyword you will still be able to get the member’s unique name by double-clicking on the employee name.

SELECT    {} on 0,
        [Employee].[Employees].MEMBERS  ON 1
FROM    [Adventure Works]

The Copy button is also can be very convenient. It will copy the entire Property and Value list to the clipboard.

Query Editor in SSRS will not return UNIQUE_NAME property by default

Let’s just first run this simple query in the Query Editor (in the text mode rather than the graphical mode) in SSRS to see what it will return.

SELECT    {} on 0,
        [Employee].[Employees].MEMBERS  ON 1
FROM    [Adventure Works]


We see employee names returned in the result set. It actually only returns the member property MEMBER_CAPTION by default.

The Query Editor in SSRS behaves quite differently. It will not return member properties other than MEMBER_CAPTION by default. If you want to return more than just the MEMBER_CAPTION, you will need to use the PROPERTIES keyword.

SELECT    {} on 0,
             ON 1
FROM    [Adventure Works]


Interestingly, if you don’t include the property MEMBER_CAPTION, the first column is now empty.


Another different behavior from the query editor in SSMS is that it actually includes the member properties as data in the result set.

Here is a short list of the behaviors of the Query Editor in SSRS when using the PROPERTIES keyword.

  • It only returns MEMBER_CAPTION property by default (default means without using the PROPERTIES keyword).
  • It includes the member properties as data in the result set.
  • When using the PROPERTIES keyword, the MEMBER_CAPTION should be included in the property list (but does not need to be the first one in the list).


Using the PROPERTIES keyword is a simple way to get members’ unique names in the Query Editor in both SSMS and SSRS. We also see that the Query Editor in SSRS behaves quite differently from how the Query Editor behaves in SSMS.

We need to be careful when retrieving the entire members from a very large dimension without filtering. It might cause performance issue.

This might not work very well either when retrieving the entire members from a hierarchy that has multiple levels. It will be very difficult to look for what you want to see without proper sorting.

These member properties, MEMBER_CAPTION, UNIQUE_NAME, LEVEL_UNIQUE_NAME, MEMBER_KEY, are only part of the intrinsic member properties exposed by SSAS. To see all the intrinsic member properties, check out this link on SQL Server Books Online for more details:

Using Member Properties (MDX)

In the query below, I am including quite a long list of intrinsic member properties.

SELECT    {} on 0,
             ON 1
FROM    [Adventure Works]

In all the above queries, I used an empty set on the COLUMNS axis. For more details on this technique, please see the recipe “Skipping axis“ in Chapter 1 of the book “MDX with SSAS 2012 Cookbook”).

Notes: all the examples and screenshots are from 2012 version of the SQL Server platform.

MDX+SSRS #31– Query Designer in SSRS only allows the Measures dimension in the first axis

September 28, 2013 Leave a comment

In Chapter 1 of the book MDX with SSAS 2012 Cookbook, in the first recipe “Putting data on x and y axes”, I have given a simple example to show how easy it is to use the CROSSJOIN function to "combine" more than one hierarchy into the COLUMNS and ROWS axes.

SQL Server Reporting Services is a report design and information delivery tool, and has been adopted by many companies for their Business Intelligence reporting needs. However, building reports in SSRS accessing OLAP cubes in Analysis Services is not without frustration.

The graphical MDX Query Designer in Reporting Services allows you to retrieve data from any BI semantic model through the technique of drag and drop, without you actually needing to understand and write MDX queries.

If you have some experience building reports in SSRS with Analysis Services data, you have already discovered that the graphical MDX Query Designer serves its purpose very well to  graphically generate well-formed and efficient MDX queries. Very quickly, however, you also will find that the you will need to cross a bridge. This bridge will lead you to the generic MDX query editor where you can edit the MDX query that is built by the graphical designer.

The following is a screenshot of the Query Designer and the toggle button for switching between the graphical designer and the editor.


Don’t expect the MDX query editor in SSRS works the same way as the MDX query editor in SSMS.

This MDX query simply puts two measures on the COLUMNS, and the CROSSJOIN (all possible combinations) of the sales territory country and product category on ROWS.

    { [Measures].[Internet Sales Amount],
      [Measures].[Internet Gross Profit]
    } ON 0,
    { [Sales Territory].[Sales Territory Country].[Sales Territory Country] *
      [Product].[Product Categories].[Category]
    } ON 1
    [Adventure Works]

In SSMS, the previous query will produce the following result.


If we copy the same query to the query editor in SSRS, we do get the same number of rows back, and the measures match perfectly for every combination of the sales territory country and product category. However, we see some noticeable differences, comparing the previous screenshot with the following screenshot.


1. The measures are not formatted in the query editor in SSRS.

2. We get four columns in the query editor in SSRS. In addition to the two measures that we put on the X axis, the two hierarchies from our CROSSJOIN function on the Y axis have also appeared as two separate columns.

Now, let’s change the previous MDX query slightly, by switching the measures to the ROWS, and the CROSSJOIN of the sales territory country and product category to COLUMNS.

    { [Sales Territory].[Sales Territory Country].[Sales Territory Country] *
      [Product].[Product Categories].[Category]
    } ON 0,
    { [Measures].[Internet Sales Amount],
      [Measures].[Internet Gross Profit]
    } ON 1
    [Adventure Works]


In SSMS, we would expect to see the following results.


Let’s copy the same query to the query editor in SSRS. This time we would get an error.


The message is actually very clear. I’d translate the error message into the following two rules. The query editor (and the graphical Query Designer) in SSRS:

1. does not allow CROSSJOIN in the COLUMNS (or 0-axis)

2. only allows the Measures dimension in the COLUMNS (or 0-axis).

What we have put on the COLUMNS clearly violated both of the rules.

     { [Sales Territory].[Sales Territory Country].[Sales Territory Country] *
      [Product].[Product Categories].[Category]
    } ON 0


In my experience, knowing what to expect from the graphical MDX Query Designer and the MDX query editor in SSRS will put you half way through the learning curve.

SSRS #73 – Use Calculated Field to Dynamically Set Default for Parameters

January 4, 2013 3 comments

Calculated Field has existed in Reporting Services since version 2005. SSRS 2005 did not have the most attractive user interface design, so this pretty useful feature stayed hidden from me until version 2008.

Even in SSRS 2008, I did not pay much attention to it until I started to write reports in MDX queries on SSAS cubes.


Once I started using them, I find myself like it more and more.

One of the challenges in SSRS reports is to set parameter defaults dynamically. It’s an even bigger challenge if you want to dynamically set parameter defaults to multiple values, such as (Select All). Don’t read this statement wrong. Setting parameter defaults to multiple values, such as (Select All), is not hard. What is hard is to do it dynamically, meaning the situation where you want to set the default or not depends on the selection of another parameter.

I recently ran into this issue when working on a Data-Driven Subscription project. The idea is that we’d use just one SSRS report for both online ad-hoc reporting, and for a daily data extraction that can be sent to users via E-Mail.

For the online ad-hoc reporting, we certainly do not want to default all parameters to (Select All). For the daily data extraction though, that’s exactly what we’d like to do.

Since I am using Data-Driven Subscription and a SSIS package to trigger the subscription, you would think that I can manipulate the parameter defaults in either SQL code and/or in SSIS scripting. But I highly recommend against that if your goals are

1) To use just one SSRS report for both online ad-hoc reporting, and for a daily data extraction

2) Not to replicate all the queries you already have in the SSRS report

3) Not to waste your valuable time to wrestle with Reporting Services. When it comes to parameters with defaults or cascading parameters, Reporting Services is not very forgiving in terms of dynamic settings. If you have tried that before, you probably know what I meant.

Using Calculated Field feature in SSRS 2005 and above is what I’d recommend.

Suppose you have a hidden parameter, Subscription.


Parameter Subscription has two values, 1 and 0.


Its default is 0 (not the 1 shown).


When Subscription is 1, we want to set the default of parameter Geography to (Select All).

We’d create a Calculated Field for DataSet_Geography. Call it GeographyKey_Dynamic.


In the Expression Editor for GeographyKey_Dynamic, enter an IIF() expression. This expression will basically use the GeographyKey value if the Subscription is 1, otherwise, it will be Nothing.


Now we are ready to use this new Calculated Field GeographyKey_Dynamic as the default value for parameter Geography.


DataSet_Geography is the same dataset for Available Values. The only difference is that in Available Values, we’d always use GeographyKey, which is a SQL column. For the Default Values, we’d use GeographyKey_Dynamic instead.



1) Watch out when you like the Calculated Fields too much and use them excessively. SSRS reports will typically go through Retrieval, Processing and Rendering phases during run time. If you have queried a Report Server database, you will find that the Processing and Rendering phases take more time than you’d like. Having too many Calculated Fields will no doubt increase the Processing time. So think about distributing the burden across queries on servers and calculations in memories.

2) For data-driven subscription, see my blog at

SSRS #60 – Steps to implement a data-driven subscription

SSRS #72 – Use RunningValue() Function to Concatenate Row Values

August 13, 2012 2 comments

I had a post quite a while ago on how to use FOR XML clause with PATH mode, which is available since SQL 2005, to concatenate row values into columns.

T-SQL – Concatenate row values in Transact-SQL

We can achieve the same in SSRS with a wonderful aggregate function, RunningValue() function.


But to make it work, it can take more than a few tries.

In this example, I am going to use the MDX query from my post MDX #10 – Top 5 Resellers in Each Country.


Once I have the MDX query ready, I want to concatenate the top 5 resellers into a column, and the result will look like this:


A great post on has inspired this post. I forgot the link, but still want to attribute to the original blogger.

First add some custom code to the report

The first we need to do is to add some custom code to the report by going to the Report Properties.


In the Code section, create a custom function ConcatenateString() to concatenate resellers for each country. It will take Country and Reseller as 2 parameters.


Next create a row group

Next we need to create a row group, named “Group_Country”. In the group property, make sure that the group by column is [Sales_Territory_Country].


Use RunningValue in the expression

In the Top 5 Resellers expression, enter:

=RunningValue(Code.ConcatenateString(Fields!Sales_Territory_Country.Value, Fields!Reseller.Value), Max, "Group_Country")

  • The ConcatenateString() function take 2 parameters, Fields!Sales_Territory_Country.Value and Fields!Reseller.Value.
  • The aggregate function Max is the second parameter for the RunningValue() function. 
  • The last parameter is the name of the row group.
  • The RunningValue() function iterates through each [Sales_Territory_Country] in the row group, finds each reseller, concatenates them, until it finds all the resellers in each [Sales_Territory_Country].

Concatenating row values into column can be easily done in TSQL, and also in SSRS.

%d bloggers like this: