Tags: Project 2010, Project Server 2010, PS2010, SQL, SSRS
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)
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.
Tags: EPM, Office 2013, Project 2010, Project 2013, Project Server 2010, Project Server 2013, PS2010, PS2013
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.