In the BI world, working with lots of clients implies dealing with lots of requests. While some of those requests might be reasonable and straightforward, some may require you to think outside the box and use unconventional methods to solve them.

Recently, we had to build a complex portfolio report to evaluate the project status from a risk and issue perspective. Our client had over 3000 projects, so reading the data by issuing separate queries for each project was not feasible. SSIS, PowerShell scripts, or custom code were also not an option, since we’d been asked to provide a solution using only SSRS and a custom database table.

To fully appreciate the challenge, one must understand how Microsoft Project Server provisions the website for a newly created project upon the first “publish” operation, based on the SharePoint site template associated with that project’s Enterprise Project Type (EPT.)

Only the information for the out-of-the-box project site lists (issues, risks, and deliverables) is available in a consolidated form in the Project Server reporting tables. Additional lists, and even additional columns added to the out-of-the-box lists, are not available as part of the reporting data. SSRS allows querying list data using the SharePoint built-in data source type, but only one list instance can be queried at a time.

To overcome this limitation, we came up with a scheduled SSRS report containing two sub-reports that iterate through all the projects, query each project list and insert the data in a table. We’d like to share the step-by-process below.

1. Creating the main report
First, you need to create the main report which will contain a data connection to the Project Server database and a dataset that will retrieve all the projects along with the project site link.

A table in this report will contain a row with a sub-report embedded and grouped on ProjectUID, or any field within your dataset which will identify a row unique. This is a really important step because it will ensure the level 1 sub-report will loop through each project site, based on the row grouping.

When you’re done with this step the main report should look similar with the below report, an empty table with a data set associated to it and a row group:

2. Creating the level 1 sub-report

Create a new report, which will be the first level sub-report, and add a data connection for the SharePoint List, and use a valid project site URL. Also create a parameter which will hold the Project Site URL, at this point this will have no available or default values and will be set to Hidden.

Report Parameter Properties

Data Source Properties

Then, create a data set and use the SharePoint List as a data source. You should be able to browse through the lists available in the project site. Here you can select and filter the data you need.

Query Designer

Go to the report created in the previous step, the main report, and insert in the table row the report you created in this step, and pass the Project Site URL:

Mail Report.rdl

3. Creating the level 2 sub-report

Create another report, which will actually insert data into the database for each item in the list. Before starting with this you should make sure you have created the destination table in the database. Create a data connection for the destination database, and a dataset which will contain an insert statement as well as a select statement. For each column that you need to insert in the database you’ll create a parameter; set them to hidden, and set no default or available values. If you like/need you can insert an object in the report design area to show the records inserted or maybe other information, but this is not mandatory. The final look of this sub-report:

In the level 1 sub-report insert in the table row the newly created level 2 sub-report, and set the parameters accordingly:

Subreport Level2

Once all the parameters are connecting, you need to go back to the level 1 sub-report and change the data connection to use the Project Site URL parameter, instead of the hard-coded value.

Data Source Properties

When the report runs, it will insert the data in the database table.

Finally, publish the report and set a schedule. Of course, data liveliness depends on how often the report is scheduled.