#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)
)AS
BEGIN
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
END

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.

Advertisements

1 Comment »

RSS feed for comments on this post. TrackBack URI

  1. […] This article has been cross posted from pwmather.wordpress.com (original article) […]


Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s

Blog at WordPress.com.
Entries and comments feeds.

%d bloggers like this: