#ProjectServer #SSRS Report with multivalued parameters #SQL #PS2010 #SP2010

March 17, 2013 at 6:03 pm | Posted in Configuration, Customisation, Functionality, Reporting, T-SQL | 1 Comment
Tags: , , , ,

A quick blog post to highlight the use of one of the Project Server Reporting database functions to resolve an issue when using an SSRS multi value parameter.

There are several methods to get multi value parameters working in SQL Server Reporting Services (SSRS) including dataset filters, joining parameters and custom SQL functions – other blogs / forum posts detail these. This post demonstrates using a function that is available in the Project Server Reporting database. The function is called MSP_FN_Utility_ConvertStringListToTable. An example SQL Stored Procedure that will allow multi values can be seen below:

CREATE PROCEDURE [dbo].[SP_ProjectData] (
        @ProjUID NVARCHAR (max)
select            P.ProjectName
           ,    T.TaskName
from            MSP_EPMProject_UserView P
INNER JOIN        MSP_EPMTask_UserView T
ON                P.ProjectUID = T.ProjectUID
INNER JOIN        MSP_FN_Utility_ConvertStringListToTable (@ProjUID) AS PU
On              P.ProjectUID IS NULL or P.ProjectUID like PU.TokenVal

Create the SQL query as normal but instead of using a where clause to filter the Project UIDs join on to the function as shown above.

#ProjectServer #PowerView report in #Excel 2013 #PS2010 #PS2013 #Office2013

March 1, 2013 at 11:51 pm | Posted in Administration, Configuration, Functionality, Reporting, T-SQL | 2 Comments
Tags: , , , , , , ,

This post will take a brief look at creating a map view of Project Server data – this does assume you tag your projects with a location!

For the purpose of this post I will use the example Excel file shown below – this pulls data from one of my test Project Server PWA instances, hence the project names!


In Excel 2013, click Insert > Power View Reports:


You will now see a Power View report:


To create a map with the projects plotted in the correct location by cost, see the steps below.

On the design tab, click Map and you will see the following:


Now click the map and modify the Power View fields shown below:


For this example, add ProjectCost to the size property, add Project Locations to the Locations property and set the colour property to ProjectName:


Increase the size of the map and add a title:


You can hover over the data circles and a tooltip will appear with the project details:


The data can be refreshed and the map updates.

A quick and simple report to show projects by location.

Blog at WordPress.com.
Entries and comments feeds.