@WonderLaura

You have all of this data in SharePoint, and you have all of this information about the site users, such as their department names, phone numbers, and much more. Instead of asking for site users to fill out all of their pertinent information every time they fill out a list item, form or survey in SharePoint, wouldn’t it be nice to just use the information that is already there?

A while back, I wrote an article on how to report off of this data using Microsoft Access SharePoint: Find out About Site Users within Microsoft Access (Video). This method is flawed because once you create the report, there isn’t a way to display it back in SharePoint… it’s only in Access. I also wrote this post Pass Default Value From a Web Part Page to a New Item, which works okay, but you’re really copying all that user data into some fields, which is really inefficient.

This week, I discovered a much better way than those other two, and it entails creating a SQL Server Reporting Services (SSRS) report. This solution can be used with all versions of SharePoint (not just enterprise). Before getting started, the prerequisites are that SSRS is already installed on a SharePoint web front end server, and it’s all set up and integrated with SharePoint. Also, make sure that you are using SQL 2008 R2, or steps 19 and 20 won’t work. You’ll need the Report Builder Report and the Report Data Source content types added to a document library (I called my library “Reports”). For more information about SSRS, I did a presentation recently: Easy Reporting off of SharePoint Data.

In this example, I’m going to use a regular SharePoint tasks list, and the goal is to display the list of tasks, showing the “Assigned To” column, and also showing more information about each user that has a task assigned to them. (Note that part of these instructions are easier if you are working at the root level of the site collection, as opposed to a sub-site.)
Here’s the final result above. Those last two columns come directly from the SharePoint user information list.

1. In your Reports library, click the New button and choose Report Data Source.
2. Name it TeamSite, data source is Microsoft SharePoint List,and use your own SharePoint site URL as the connection string. Credentials has to be Windows authentication. Click OK.
   
3. In the library, click the New button again, and choose Report Builder Report. (If you’ve never used it before, you will be prompted to install it)
   Note: if you get the error “To use Report Builder, you must install .Net Framework 3.5 on this computer”, here is the resolution.
4. In the wizard, choose New Report. Click Table or Matrix Wizard.
   
5. Choose Create a Dataset, and click Next.
6. Choose to browse to find a data source, and navigate to the current library, which is where you already created the TeamSite data source, which is the one you want to select. Click Next.
7. Check the box next to your Tasks list on the left. (Note to make sure that there are some items in your list. It just helps to have test data.) Click Next.
8. Add a few fields to the Values section, by dragging them over. Click Next.
   
9. Since I didn’t select any grouping on the Arrange Fields screen, on the Choose the Layout screen, all the options are grayed out. Click Next.
10. Pick a color preference on the Choose a Style screen, and click Finish.
11. If you’d like to preview your creation so far, click the Run button at the top, then click Design to close the preview. Go ahead and click the Save button at the top left, and drill down to your Reports library. Save this as TaskReport.rdl.
12. So far, we haven’t done anything extraordinary, that’s just a pretty report. Now it’s time to add those user information columns that I promised you. Right click on Datasets on the left, and choose Add Dataset.
13. Call this new dataset UserInfo, and choose to use a dataset embedded in the report. Choose your TeamSite data source from the drop-down.
    
14. Click the Query Designer Button.
15. Check the box next to the User Information List on the left, and click OK.
    (Note that only if you do NOT see the user information list, this is because it only exists at the root level of the site collection. You’ll have to create a separate data source, call it “TopSite”. Repeat steps 1 and 2, using the URL of the root site in your site collection. In the screenshot above, you’ll be selecting the other, “TopSite”.)
16. On the Dataset Properties screen, click OK.
17. A couple of columns can now be added. Again, the goal is to show extra information about each of the “Assigned To” people. Click to select the last column in the report, and choose Insert Column -> Right. Do this twice.
    
18. In the first new empty cell (not header), right click and choose Expression.
    
19. Paste this formula, click OK, and I’ll explain it all in a minute.
    =Lookup(Fields!Assigned_To.Value,Fields!Name.Value,Fields!Department.Value, “UserInfo”)
20. Right click the next empty cell next to that one, pick expression again, and this one will have the job title:
    =Lookup(Fields!Assigned_To.Value,Fields!Name.Value,Fields!Title.Value, “UserInfo”)
21. In the column header cells, type titles for the new columns, “Assignee Department”, and “Assignee Title”.
22. Save. Close Report Builder. Now in your reports library, you can click to open this report (RDL file) and see how lovely it is.

Here’s how that lookup formula really works…

The Lookup function has four sections, separated by commas:

• Fields!Assigned_To.Value – This is the field in the current (tasks) dataset that is going to exactly match up to a field in the other (UserInfo) dataset. You can click Fields (DataSet1) (in the screenshot under Category) to pick from the list of all the other fields.
• Fields!Name.Value – This is the field in the other dataset (UserInfo) that is going to exactly match with the Assigned_To field. In the user information list, the Name field is “Firstname Lastname”.
• Fields!Department.Value – this third part is the name of the field in the other dataset (UserInfo) that you want to display in this cell. To see a list of the names of the fields in that dataset, in the Category section of the above screenshot, you can click Datasets and then click UserInfo.
• “UserInfo” – This fourth part of the formula is the name of the second dataset that you’re looking up to. This is what we named it at step 13.

Anyway, that’s it, I hope you have fun with this. I absolutely love SSRS. Don’t forget that there’s the Report Viewer web part, which will let you display your report on any page in SharePoint. Another side note that I may blog about separately is the fact that you can cache the data in your dataset, so that it doesn’t take so long to render the report. It has to be a shared dataset to be able to do that, and we didn’t do any shared datasets in these instructions, just for simplicity’s sake.

Note, if you got stuck at step 15 and you still don’t see the user information list, just paste this in the big white box called Query:

<RSSharePointList xmlns:xsi=”http://www.w3.org/2001/XMLSchema-instance&#8221; xmlns:xsd=”http://www.w3.org/2001/XMLSchema”&gt;
<ListName>UserInfo</ListName>
</RSSharePointList>

Here’s the reference for the Lookup Function.