It is not an uncommon requirement to be able to publish an SSRS report to SharePoint and then use it in a Web part filtered by the user viewing it. You include the domainuserid in your main report query and filter the query on this value using a report parameter where the user value can by typed in.
However, in many cases the report does double duty as a report stored in a Report library, where users could choose multiple or ALL users. In that case you want to have a list of Available Values in your parameter so that someone viewing the report in the library can pick from a list of users rather than have to know everyone’s user id. The tricky part is formatting the domainuserid in your parameter so SharePoint can use it, and adding yourself to the list of available values so you can test that the Web part works as expected.
These are easy to do, but it took me a little bit of time to figure it out, so I’m blogging it here.
CREATE THE LIST OF AVAILABLE VALUES FOR YOUR PARAMETER
The SharePoint Current User Filter expects the domainuserid in the parameter list of available values to be all lowercase. So when creating the SQL Query for need to convert them to lower case. You also want to add yourself to the list for testing purposes, otherwise you will get an error when looking at the Web part in SharePoint. You can do this using a UNION clause. Your query for the list of available values should look like this:
SELECT DISTINCT
‘mydomain’ + LOWER(myuserid) AS UserAccount,
myUserName as UserName
FROM myTable
UNION
SELECT
‘ ALL’ AS UserAccount,
‘ ALL’ AS UserName
UNION
SELECT
‘mydomainmydevuserid’ AS UserAccount,
‘mydevusername’ AS UserName
I won’t go into the details here of how to use ALL in your report, but you can read more here http://thedataqueenblog.azurewebsites.net/2011/06/how-to-default-to-all-in-an-ssrs-multi-select-parameter/
Configure your user parameter as follows:
Publish your report to the relevant SharePoint library, and navigate to the library and test that the report and the parameter is working as expected.
ADD THE REPORT TO A WEB PART
Add the report to a web part as you normally would. You would add a web page, and configure a web part to be a SQL Server Reporting Services Report Viewer. Edit the web part and navigate to the report you created. Open the Parameters section and click on the Load Parameters button. You can leave the parameter default as “Use Report Default Value”. Click Apply and OK.
ADD THE CURRENT USER FILTER TO A WEB PART
Add a web part and choose the Current User Filter type from the Filters section. It will say that it is not connected.
CONNECT THE FILTER TO THE SSRS REPORT VIEWER
Go back to your Report Viewer web part and from the drop down choose Connections –> Get Report Parameters From –> Current User Filter
A dialog box will pop up where you can choose your User parameter and click on Finish.
Check in your changes and view the results. You will be able to see your web page with the report filtered on your user name. As a developer, if you do not have any values in this report you should see the report with no values returned, rather than getting an error. This is because you added your userid to the list of available values at the beginning of this exercise. You might want to remove yourself from the list once you have tested that the web part is working correctly.