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
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+%'))