Saturday, December 03, 2005

Quick SQL Reporting tip

I usually have to customize the data that's presented in a report in SQL Reporting services based on the currently logged in user. I'm guessing I'm not the only one.

The good news? It's really really easy!

What you can do is simply create a new String parameter (called something imaginative like 'CurrentUser' or something). When you create the parameter in the report designer, it'll give you a bunch of configuration options for the parameter. The main one you're interested in is the Default value.

Select 'Non-Queried' from the radio button list on the left, and then in the text-box that's enabled, enter the following function:
=Right(User!UserID, len(User!UserID)-instr(User!UserID, "\"))
Looks ugly, yeah? But it's quite logical, if you break it down a bit:
Right(string, length) gives you the right n characters of string. So if you entered Right("Monkey", 3) the resulting string would be 'key', the right 3 characters of the work 'Monkey'.

In this context, the string we're initial string is User!UserID. This is a report function that gets the login of the user viewing the report. The value is in the form DOMAIN/USER, which might be fine as is for your reporting. However, when I was using it, I just wanted the name part of the login, rather than both the domain and name. instr and len both combine to give me the user portion of the UserName. len(User!UserID) returns the length of the UserID string in its entirety. So for 'MARVIN\Ben' (my current login) it would return 10.
instr(String, StringToFind) returns the index of StringToFind within String. So taking my current login as an example, instr("MARVIN\Ben", "\") returns 7.
If you evaluate those 2 expressions, the outer expression then reads =Right(User!UserID, (10-3)), which is why the expression returns 'Ben' in my report.

That's not the clever bit, though.

The clever bit lies in the rest of the parameters.

If you have a datasource that requires parameters, say you're pulling results from a stored procedure, the report designer automatically adds those parameters to the parameters collection off the report. Since all the report parameters are evaluated in the order they appear in the list (Or are displayed in the order they are evaluated, if you like) you can out your CurrentUser parameter at the top of the list, and then if any of your query parameters need the current username, rather than repeat the function, you ca just refer to the CurrentUser parameter using Parameters!CurrentUser.Value. Just hide those parameters (more on that in a minute), set their defaults to the CurentUser parameter's value, and you're away!

The final stage is to hide the current user parameter. This differs depending on which version of SSRS you're using, but the 2 I know about are:

  1. the SSRS 2005 parameter dialog box has a 'Hide Parameter' check box. Just tick that and you're done
  2. In SSRS 2000 (which is what I'm using at home) if you clear the 'Prompt' value in the dialog, it'll automatically hide the parameter for you
But wait, there's more! Something that I was absolutely overjoyed to find out!

You set the default value of the CurrentUser parameter when the report fires up. Now normally, if the parameter is hidden, that value will never change. However if you un-hide that parameter, you can edit it and re-run the report using that value. This makes it an absolute doddle to debug and test. If you want to test the report output using several different users, you can just do it right there and then. And if you've set all the rest of the report parameters' defaults to refer to CurrentUser, then they'll all pick up the value you enter.

When you're done testing and are happy with the results that are being produced for your users, then you can just re-hide the parameter in the designer and publish the report. Once that's done, your report will produce custom output for each user, with no user intervention required or, in fact, allowed.

Clever eh? I love SQL Server Reporting Services. They're my friend.

Just wanted to share.