SharePoint (and Project Server) Shenanigans

I am a Project Server and SharePoint consultant but my main focus currently is around Project Server. I have been working with Project Server for nearly five years since 2007 for a Microsoft Gold Certified Partner in the UK, I have also been awared with the Microsoft Community Contributor Award 2011. I am also a certified Prince2 Practitioner. This article has been cross posted from pwmather.wordpress.com (original article)

Following from my previous post where I briefly introduced a new product from CPS called SharePoint ReportLink, as promised, this post demonstrates the ease of use and ease of reporting on SharePoint data when using SharePoint ReportLink.

For the purpose of this blog post, I have already deployed the solution to my test VM. Worth noting at this point, the version I have installed / used for this blog post is not the final release so you will see CPS DataMart referenced as this was the original product name. Functionally the version I have installed on my VM is probably 99% identical. Firstly I need to activate the feature on the site collection, in this case it is one of my PWA site collections. Once the feature is activated a new heading / menu appears on the Site Settings page for that site collection:

You will see CPS DataMart with four links, Application Settings, List Configuration, List Column Mappings and Logging. We will take a look at each page in turn, firstly the Application Settings page:

I didn’t set any database server or database name, as this is has been activated against a PWA site collection the feature automatically populates the database server with the Project Server Reporting SQL Server name and the name of the Project Server reporting database – pretty cool! You can of course update the settings to use a different database server and custom database. If this feature is activated against a normal SharePoint site collection you will see the following on the Application Settings page:

As you can see there is also a logging level, by default this is set to Information but can be updated:

The SharePoint ReportLink will log events to a custom list to ease diagnostics.

Let’s take a look at the next two page pages together, List Configuration and List Column Mappings.

List Configuration:

List Column Mappings:

As you can see these are just standard SharePoint lists, at this point you need to know what lists and what columns on those list you wish to report on. For the purpose of this blog post I will report on the Issues, Risks and Lessons Learned from my projects sites:

See below the populated / configured List Configuration and List Column Mapping lists:

Once the CPS SharePoint ReportLink is activated and configured on the site collection, any items on the lists configured will be synchronised to the SharePoint ReportLink database tables in real time. This includes, new items created, existing items modified and items deleted from those configured lists across all webs within that site collection.

After setting the List Column Mappings, custom tables will be created in the specified SharePoint ReportLink database, in this case the Project Server Reporting database:

Tables:

Issues list columns:

If those lists already contain items, after setting the column mappings the existing SharePoint items will be created in the SharePoint ReportLink tables.

I then created a few new items on the lists (on PM 1 and PM 2 sites, other items already existed), as you can see from below, the data is available in the custom tables in the reporting database:

Issues:

Lessons Learned:

As you can see, you can quickly and easily access the SharePoint list data from multiple sites using T-SQL. Also notice the Project_UID, this enables you to easily link the SharePoint ReportLink data to your Project Server data. If SharePoint ReportLink reads data from a non Project Server project site, the Project_UID column will contain an empty GUID.

The final menu we haven’t seen yet is the logging list, this can be seen below:

The warnings are logged above as the new custom list, Lessons Learned, doesn’t exist on the majority of my test project sites.

Now that the SharePoint data is easily accessible using T-SQL, reporting could not be simpler using your preferred reporting tool, SSRS, Excel etc. SharePoint BI made easy!

In case you didn’t see the first post, I have included links to the product below:

http://www.cps.co.uk/What-We-Do/Pages/SharePoint-ReportLink.aspx

http://www.cps.co.uk/What-We-Do/Documents/SharePoint%20ReportLink.pdf

I will follow up with a third post and create an SSRS report including the SharePoint ReportLink data in the near future.