Data Driven Subscriptions and Row-level Security

Scenario: A customer wants to have report row-level security and data-driven subscriptions. If you have used SSRS for a while, you will now the cornerstone of report row-level security is User!UserID which returns the user identity. But here is the catch – SSRS will refuse creating a data driven subscription if a report has an expression with User!UserID.

Solution: How do we solve this horrible issue? A great hack is in order. We will disguise the User!UserID in an embedded function inside the report or in an external assembly so SSRS can’t see it. (The reason why this function is named GetHashedUser although no hashing is in place will become obvious in a moment)

Public Function GetHashedUser() as String

return Report.User!UserID.ToLower()

End Function


Believe it or not, this lets the data-driven subscription going. But there is another catch. A data-driven subscription runs in unattended mode and you need to pass the user identity as a parameter. So, when the report is executed live we want to pass the internal User!UserID to the report query or stored procedure. But when the report is subscribed to, we want to pass the user identity as a parameter. A simple expression for the query parameter will do the trick.

=iif(Parameters!UserID.Value =“”, Code.GetHashedUser(), Parameters!UserID.Value)

If the parameter is empty (live delivery), we will call the GetHashedUser function, otherwise, we will pass the parameter value to the database. So far so good, but we now have a security hole. There is nothing stopping the end user from spoofing the report parameter and passing another user’s identity to it to force the report take the data-driven subscription path. To fix this, we will need some level of encryption. I suggest a solution that encrypts the user identity with SHA1 hash both in the report and the database. This should keep spoofy users at bay.

You can download the complete code with an AdventureWorks sample report here (requires registration).

Note: The above solution assumes a report connection string with stored credentials which is what you would typically have for T-SQL (relational) reports. What about SSAS with Windows and dimension security? Not a good story and no changes in Denali unless you vote for the following suggestions on connect:

  • Enabling Subscriptions for non-stored credentials reports only has 3 votes on Connect (see link)
  • Fixing the SSAS connection string issue has only 4 votes on Connect (see link)
  • Enabling Shared Data Sources to have Expression Based Connection strings has only 4 votes on Connect (see link)
  • Can’t create a subscription if data source is set to impersonate the authenticated user (see link)


SSAS and data-driven subscriptions has been the bane of my existence. Greg Galloway has an excellent writeup here. In a nutshell, clumsy workarounds include duplicating the report definitions and using an embedded data source with expression-based connection strings, or implementing the row-level security in the report (as you would do with relational reports). So, please vote! Let’s see if can convince Microsoft that we do use SSAS and this is a wide-spread issue that deserves to be fixed ASAP.

SSRS Query Performance Issues with Parameters

I recall seeing “fast queries in SSMS but slow in SSRS” issues reported a few times in the public discussion list but I’ve always dismissed them as unrelated to SSRS. Alas, I happened to see one today when a report would take a very long time (7 min) to execute. As usual, the first stop was to take a look at the SSRS Execution Log which showed that all the time was spent in data retrieval. However, executing the report query in SSMS would take a few seconds. In this case, the report had a complex free-text T-SQL SELECT statement that referenced a single report parameter several times in the query.

The workaround we found at the end was to declare a variable at the beginning of the query that referenced the parameter once, as follows:

DECLARE @invoice_number as int

SET      @invoice_number = @inv_parameter

Then, we referenced the variable in the report query instead of the query parameter. This solved the mysterious query performance issue which appears to be a bug with parameter passing in the SQL Server dataset extension. In this case, the customer had SQL Server 2008. The issue might be fixed in SQL Server 2008 R2, I haven’t checked.

Presenting at TechEd North America 2011

031611_0213_Presentinga1I’ll be presenting two breakout sessions at TechEd North America 2011 which will be held this year in my home city of Atlanta. The tentative titles of the sessions are:

  • DBI205: Reporting Services Are We There Yet? – I’ll discuss top requested SSRS features, give solutions and workarounds, share more tips with lots of demos, and present reasons to upgrade to SSRS 2008 or R2.
  • DBI330: Can Your BI Solution Scale? – Present practical load testing methodology, show how to load test SSRS and SSAS, analyze results and performance bottlenecks.

The sessions are scheduled back to back on Monday, May 16th, from 3 PM to 6 PM. Please join me if you plan to attend TechEd. See ya’ll in Hotlanta!

Applied PowerPivot Online Training Class

Self-service BI is on the rise. Join me this month for the PowerPivot online class. Learn how self-service BI can help your users build their own BI solutions with minimum involvement from IT. No travel, no hotel expenses, just 100% content delivered right to your desktop! Our intensive online classes teach you the skills to master Microsoft BI to its fullest. Use the opportunity to ask questions and learn best practices.

Applied Business Intelligence Training in March


Applied PowerPivot Online Training Class – Only $599
Date: March 29 – March 30, 2010
Time: Noon – 4:00 pm EDT; 9 am – 1:00 pm PDT
8 hours for only $599
You can request custom dates for groups of five or more students.

For more information or to register click here!

Dundas Analytical Dashboard and Analysis Services

In my Dundas Dashboard and MS PerformancePoint Comparison article, I introduced you to Dundas Dashboard and explained how you can use it to rapidly assemble compelling performance dashboards. With Dundas Dashboard, you can also build analytical dashboards to let users create interactive, web-based reports from OLAP databases, such as Microsoft Analysis Services, as I will show you in this article.

Drillthroughs Going Wild

Got bit by an unfortunate bug which has been around for four years since it got reported on with Analysis Services OLAP drillthrough. It caused me almost a day to figure out. Drillthrough actions (both default and explicit drillthrough) won’t return any rows on some cells or would return repeating rows for other cells. As I posted on connect the bug springs to action when:

  1. The measure group has IgnoreUnrelatedDimensions=False. Bryan Smith provides a great coverage of IgnoreUnrelatedDimensions in this blog.
  2. A dimension attribute has IsAggregatable set to False. Setting the dimension default member either using the DefaultMember attribute property or via script doesn’t make a difference.

In my case, I had a Time Calculations dimension which was a free floater and it had IsAggregatable=False. Excluding the unrelated dimensions from the drillthrough action doesn’t make a difference either. The only workaround I’ve found is to set IgnoreUnrelatedDimensions back to True. Vote for a due fix, please!