Reporting Services Adding VB code / Getting user login whilst still allowing subscriptions

The User!UserID global allows you to determine the active directory user who is running your report. Very handy for customising and security. A big problem is that subscriptions will fail because report server cannot idetify the user from the subscription.

Here is a work around using a custom code function.

If you have really complex tasks - or ones like our problem - then VB code can be embedded in your report server report.

1. In Design view, right-click the design surface outside the border of the report and click Report Properties.
2. Click Code.
3. In Custom code, type the code.
4. Public Function UserName()
   Try
   Return Report.User!UserID
   Catch
   Return "System"
   End Try
   End Function

Now we need to make the user name available as a parameter in the report so we can use it in our underlying SQL.

1. Click on the Report Menu -> Parameters
2. Add a new parameter
3. In the default values use a non queried value of =Code.UserName() to call the custom code we entered above.
4. This particular parameter is needed to determine the list of Market parameters used in this report. It needs to be moved (using up and down arrows) above any parameters dependant on it - in this case Mkt.

The @UserName parameter is now available for us to build our SQL in the data section of the report.