SharePoint Integrated SSRS Report Filtered on Logged-in User

I ran into a requirement for some charting on a SharePoint site that the various chart parts out there just were not solving.  I had already created some integrated SSRS reports for the site, so I figured I would just keep going down that path and keep things consistent.  However, this chart posed a new problem for me... pass through the current user and filter the report based on that.  In other words, I'm logged in as Cory Swartz.  I only wanted to see Cory Swartz's results on the chart.  After a few days of searching, tweets on #SPHelp, etc., I finally figured it out.

First, I started out with the Data Source on the SharePoint Report Library:

The key with the data source is that it is using Windows authentication.  This will allow us to match up the IDs later on.














Once we have the data source configured, we can build our report.  I used Report Builder for this.  One thing that I learned a while back with IE9 is that you need to change your Browser Mode to launch Report Builder from the browser.  Just a tidbit for those new to this.  To switch the Browser Mode, hit F12 and then change over to Internet Explorer 8.  Then you'll be able to open Report Builder directly from the browser.

Ok, so back to the report. 

You'll need to create your Data Source.  For this, you will select the Use a shared connection or report model option.  You will need to browse out to your SharePoint site via the Data Source dialog box and select the Data Source we created in the Report Library (or other location).

Next up is where the rubber meets the road.  Create a new Dataset.  You can name it whatever you'd like.  I used the Use a dataset embedded in my report option.  Select the Data source we created in the above step.  Then I used the Text Query type.

Here you can see the details.  The real key for this report is in the Query.  I highlighted the starting point of the <Query> region.  This is what's going to determine the User-filtered results.












Here's the detail of the entire Query:

In this example, my Assigned To field is a user lookup in SharePoint.  Using LookupId='TRUE' it will find the ID of that user.  Then, using the <Eq> operator, it will match it to the <UserID/> and only return those results.

Then I was able to build my chart all nice and pretty in Report Builder and publish it like any other SSRS report in SharePoint.  Now that I know what steps to take, I'll be able to meet their report needs very quickly and produce nice looking charts and graphs.

Thanks to @VanVlaenderenP for taking time to answer all of my #SPHelp tweets!