Storing and retrieving session variables in Reporting Services 2008

Question

Is there a way to set session variables during login that are then available to reports?

We have a reporting services server where we move and denormalize data from our transaction system for reporting purposes. In order to allow our users to login with the same user name and password from the transactional system we have set up custom forms authentication and after much trial and error it is finally working. In addition we have the authorization accessing our transactional system so that any changes in user authority is immediately reflected in Reporting Services.

Our problem now is that we would like to add some additional features such as locking down parameters depending on user authority/groups in our transactional system. We have found a way to do it but it's inefficient, basically we have stored procedures that will query our transactional system to check for access. The problem is that these queries will often be run for every report request even though the answer is unlikely to change. It would be nice to have access to session level data that can be set once during log in and then accessed from the reports.

Answer 1

There are no direct access from SSRS to session. My suggestions:

1. create table in database to host all authority/group related information, pass userID as a parameter into stored procedure. Issue - to trigger changes in this table.
2. use ASP.NET as a wrapper for SSRS, so there will be no troubles with session variables. Starting with SQL Reporting Services in ASP.NET Application

Answer 2

Using reflection, you can get access to the HTTPContext of the running job, from where you can possibly get the Session object (depending on your set-up). We've had to use this method for a slightly different scenario related to security: if you have a report that uses multiple datasets, only the first one gets the proper user context. This hack allows you to get the HTTPContext yourself (which we use in a custom data layer).

Excerpt from MSDN forum article here :

using System.Reflection;
using Microsoft.ReportingServices.Diagnostics;

// This is declared outside of a method body for improved performance,
// since this reflection bit only needs to run once

private static FieldInfo contextField = 
  typeof(RunningJobContext).GetField("m_optionalWebCtx", 
                   BindingFlags.Instance | BindingFlags.NonPublic);

// Call this in your method
HttpContext context = (HttpContext)contextField.GetValue(Globals.JobContext);

Answer 3

You definitely have a lot more flexibility moving the access into an asp.net site and using the reportviewer control.

Make sure you have the same version of the control on both your dev machine and on the IIS machine, Version 9.0.0.0 for VS 2008.

You then have to get the permissions / access between IIS and the reporting server setup but that is not too hard.

At that point you have more control for setting, hiding and controlling all the parameters or providing your own parameter controls.