Hi everybody,

It's been more than a month since my last blog-confession.  Life is totally getting in the way here, in the form of new job responsibilities and yet-another-move.

I have promised three nice people in the last 24 hours that I would address their SQL RS questions here as soon as I can. I hope they don't get annoyed, I'm bumping another question up to the head of the line… and I really will post more regularly, soon, I promise. 

A co-worker here at EC|Wise had a question, and his question pointed out to me something that, while it should be obvious in RS, and while it actually is easy, just… isn't… obvious at all…

… so it's a good item to highlight here.  (With any luck at least one of the three nice people from the past 24 hours' worth of questions will be helped, too!)

Here's the scenario:

You use integrated authentication in your intranet site, and you create RS reports to analyze the data on that site. 

Normally, your ASP.NET code passes a parameter with the current user identity to your stored procedures, to limit the data displayed to any individual.  Your database maintains tables of user privilege and role.  So how do you make sure that the user sees only the correct data in your RS reports?

The simple answer is "Same way as you do in your ASP.NET application.  You pass a parameter with the user identity, which Reporting Services exposes very nicely for you in the form of the global variable User!UserID".

I suppose it's possible that some people don't even know that the Globals exist at all.  Hence the screenshot above. 

But it's more likely that the not-obvious part is how to supply those globals as arguments to your stored procedure code, just as you would from ASP.NET.  Hence the walkthrough below.

Ready?

1. The sample report setup

My co-worker's actual report involves some cascading report parameters, each one of which is fed by a stored procedure.  Each stored procedure shows up as a separate dataset for the report, and these datasets supply the picklists for limiting report data. 

What he needs to do is pass the user identity to the stored procedure that populates the first  picklist in the cascade, which in turn will limit the data in all the other picklists. 

There are many other possible scenarios — he might need to pass the user id to every stored procedure, to filter each picklist separately.  That's fine, they would all work the same way.

In my example, I'll use a simple report from AdventureWorks data, and I'll drive the main body of the report by a stored procedure, which gets a list of cities by StateProvinceID.  While this procedure happens to use a picklist parameter as well, I'll actually pass User!UserID to the main stored procedure, driving the report data.  This will make the whole thing easy to see.

So let's consider the "main" stored procedure, shown in the following screenshot:

 

… yeah, it's not much to look at. 

This sproc has one parameter that is tied to a report parameter (StateProvinceID) and the other is tied to our user's identity, even though the procedure doesn't do much with the user except send it back for display purposes.

We have a second dataset feeding our picklist parameter in this report.  We fill this dataset using a simple SELECT  Name, StateProvinceID FROM  Person.StateProvince,  nothing fancy.

OK so far?

Quick review: driving the picklist with a parameter

Here's our parameter setup; you can see that it has a NULL default value, meaning that the user has to pick a StateProvince (by Name, in this case) to actually run the report.

Report layout

Our report layout really has one simple job: demonstrate what the current value of User!UserID happens to be, and that the value sent to the stored procedure is correct (the same as User!UserID.

… still not very exciting.  You can see that, in the header, I show the report-global variable, and in the table, I show the value coming back from the stored procedure.

Whew. that was a long wind-up.  Where's the beef?

2. The "meat" of the work: passing a report variable to the stored procedure

I've set my main data set for the report to use my stored procedure (note Command type):

Wait just a gosh-darned moment. 

How did I get to the dialog in the last screen shot?  Oh, yes.  Another not-obvious thing — use the ellipsis button on the Report Designer data tab:

Back to configuring the stored procedure 

Now go to the Parameters tab in this dialog.  See?  

You can even use intellisense (sort of) to build up an expression.  You'll be using the same dialog as you see in the first screen shot in this post.

3. The result

That's it.  Stand back and watch it work… whether in your IDE for testing or in Report Manager (or otherwise requested from ReportServer)…

As they used to say on the cereal commercials, "Stays crunchy, even in millk".