Reporting on Lists from Project Workspaces (SharePoint Sites)

Posted By Posted by: Laith Adel on July 14, 2011

It has been always a challenge to report on custom SharePoint lists in project workspaces, lists like Issues and Risks have its own special reporting techniques (I won’t go through this here), but if you create other lists in your project workspace, like “Changes’ . ‘Purchase Orders’, Lessons Learned’, etc. it was always hard to report on them in say a SSRS Project Status Report, well not any more with Business Intelligence Development Studio 2008R2, and here is how you do it:

 

(Assumption: in your SSRS, thee is a Parameter for the “Project Name” that comes from a dataset that queries all the projects that are in the Reporting Database)

 

  • Create a new data source, under ‘Embedded connection:’ Type: choose “Microsoft SharePoint List”

  • Under Connection string, click on (fx) then type in your PWA site address between quotations for example “http://server/pwa/“ the a + sign then from parameters select Project Name Parameter

  • This will set the right data source for you, however not very useful to build the query when you build the data set, so what I do I actually create a new data source for an existing site, to build my data set, so create a new data source following the same instructions as above, but instead on clicking on the (fx) for your connection string, just type in the address, like http://server/pwa/project1 , let’s say that we call this data source is “TestChanges”

  • Then create a new dataset, choose ‘Use a dataset embedded in my report. , from Data source list choose the one you created for the existing site (i.e. TestChanges in my example here).

  • Click on ‘Query Designer’ , and the magic will happen .. a list of all your SharePoint lists on that site appears, you can then select from these lists, expand the one (or the ones) you want to report on and select the fields you want to show in the report. You can also ‘Run Query’ to see the results. Then click Ok (only ONCE)

  • Before clicking Ok again, make sure that you copy the Query to somewhere, you can then cancel if you don’t want to keep this data set

  • Then Create a new data set, choose ‘Use a dataset embedded in my report. , from Data source list choose the one you created initially (i.e. the one with Parameter) then paste the query into the query area

  • Before you click ok, click on ‘Fields’ and map manually to your fields that come from the query

  • Click Ok and you are done!! You can then use this data set anywhere in your SSRS ‘Project Status Report’

 

 


Blog Posted In 

Leave a Reply

Your email address will not be published. Required fields are marked *