Reporting Services - dynamically pick right data source / dynamically change WHERE clause?

Question

I have a brain-teaser for all you SQL Server 2008 Reporting gurus out there :-)

I have a report which displays a bunch of data, including the UserID and OrgUnitID for users. It's a server report which I'm rendering on the server, from within an ASP.NET application.

My requirement is that

• a "normal" user can only see his own data rows (basically
  SELECT (fields) FROM dbo.DataView WHERE UserID = @UserID)

• a privileged user can see all his subordinates; I have a table-valued function which delivers that functionality (basically SELECT (fields) FROM dbo.DataView
INNER JOIN dbo.OrgHierarchy ON DataView.OrgUnitID = OrgHierarchy.OrgUnitID)

Both scenarios in themselves work just fine - trouble is: I should combine those into a single report.

How can I do this?? How can I have a report that dynamically, based on a report parameter I pass in (@UserLevel; 0 = normal user, 1 = privileged) uses one query or the other??

I don't really feel like getting into dynamically rewriting the SQL query.... any other ideas??

• I was thinking about having two separate distinct datasets and then just activating the "right" one based on the UserLevel parameter - but I can't seem to find out how to define a data set on a subreport or a textbox......

• Then I was hoping I might be able to add a filter to my dataset on my server report dynamically, from my client app - but that doesn't seem to be possible, either..... could I create a filter for the "UserID = @UserID" setting and somehow enable/disable it as needed, in embedded VB Code on the report itself?

Any takers? Wild outside-the-box thoughts? Let me know!

Answer 1

can you try to make one query that works for everyone? here is just a guess based on the table/columns you include in your question:

--always returns the data for the one given @UserID
SELECT
    (fields) 
    FROM dbo.DataView 
    WHERE UserID = @UserID
UNION
--only returns data for the subordinates of @UserID, nothing if there are none
SELECT
    (d2.fields)
    FROM dbo.DataView               d1 
        INNER JOIN dbo.DataView     d2 ON d1.OrgUnitID = d2.OrgUnitID 
        INNER JOIN dbo.OrgHierarchy  h ON d1.OrgUnitID = h.OrgUnitID
    WHERE d1.UserID = @UserID and d1.UserLevel=1