2

This seems to be a common issue, but I could find no solution which worked.

I have an SSRS report which shows employee Vacation balances.

I want to make a linked report which will only display information for the current user.

The built in field User!UserID, returns the Login of the User, however that Login is generally not used in our DW, so I cannot filter off of it alone. We have a stored procedure which will convert that login to the matching UserID, which I can then filter the report on.

My issue there is that although I have a secondary dataset which returns the current users EmployeeID, that dataset cannot be used in the filter of my primary data set, nor in any parameters.

Because of the need to identify current user, I do not know of a way to do this within SSMS, but am stuck trying to make it work in SSRS

To clarify the linked report idea, I was planning to have a Boolean where true/false indicated whether to display data for the current user or just return all. The linked report was an effort to not replicate the RDL with this minor change

Any help will be greatly appreciated, this is the sort of issue I am sure I will come across again in the future

gruff
  • 411
  • 1
  • 9
  • 25
  • 1
    Why are you using an SP to get the userID? I used a lookup table that was generated nightly, with a simple inner join to add it to my main dataset. – Eric Hauenstein May 22 '15 at 16:07
  • 1
    Somebody else wrote the SP, and I just thought I could recycle it. Although if I had a lookup table, which I made into a data set, isn't that going to result in the same issue? i.e. I can't use one dataset to filter another – gruff May 22 '15 at 16:16
  • 2
    Is there something in particular preventing you? Usually I do this by creating a new parameter and selecting the option to use a query to define the available values. – TPhe May 22 '15 at 16:21
  • 1
    You're right I don't know why this was confusing me. I had to create two additional parameters within my SP (my current employee param searches by name, not ID) and then I made the Boolean to toggle between my linked and primary report. Please write this as an answer and i'll vote you up – gruff May 22 '15 at 16:59

1 Answers1

1

I ended up solving this thanks to the mental jumpstart if got from TPhe..

What was needed:

In my TSQL Procedure I created two new variables. One, a Boolean which toggles whether to filter on the person or not. This Boolean is flipped between the SuperUser and Individual report versions. And the second was an EmpID filter (previously we only had a free-text name filter)

If the Boolean is set to view only individual then the report filters to only Current user, which I acquire through a dataset generated by the SP which I mention in the original question.

The key is the Boolean parameter. I titled it viewALL; Defaulted to True for superusers. I then created the linked report, and altered parameters so that viewAll was set to False, meaning show only the current user information.

I had to add these parameters to the SSRS so that I could Manage the report on the ReportServer and flip the Boolean, and of course they were needed in the SQL for use in my where:

Where ((viewALL = 'False' AND EmployeeID = @CurrentEmployeeID) OR (viewALL = 'True' AND EmployeeName like '%+@EmployeeName+%'))

gruff
  • 411
  • 1
  • 9
  • 25