CRM Dynamics 2015 “Error Uploading Report” caused by using Visual Studio 2015

When I was trying to upload a SSRS report to replace an existing report to CRM Dynamics 2015 solution, I got the error

Error Uploading Report

An error occurred while trying to add the report to Microsoft Dynamics CRM.

erroruploadingreport

The error message does not say much.

I decided to use Visual Studio 2015 to edit the CRM report which has caused the issue.

I compared the code behind of the previous report and new report and found that there are new XML elements in the report generated by visual studio 2015 (SQL Server Data Tools for Visual Studio 2015). For example, “reportsections” xml tag has been inserted.

 <ReportSections>
 <ReportSection>
 <Body>
 <ReportItems>
 <Tablix Name="Tablix4">
 <TablixBody>
 <TablixColumns>
 <TablixColumn>
 <Width>3in</Width>
 </TablixColumn>
 <TablixColumn>
 <Width>1.5in</Width>
 </TablixColumn>
 <TablixColumn>
 <Width>1.5in</Width>
 </TablixColumn>
 <TablixColumn>
 <Width>1.5in</Width>
 </TablixColumn>
 <TablixColumn>
 <Width>2.0455in</Width>
 </TablixColumn>
 </TablixColumns>
 <TablixRows>
 <TablixRow>

The only option for me was to rework the report in Visual Studio 2013 (SQL Server Data Tools for Visual Studio 2013) before uploading the report to CRM dynamics solution successfully.

Drilling Down to a Report in a new tab SSRS Integrated Mode

There are two ways to navigate to another SSRS from a main Report

  1. Action “Go to Report”
  • Right-click on a TextBox, select Text Box Properties, select Action tab and select option “Go to report”.

GoToReport

  • Click on “Browse” and select the SSRS Report from the report library.
  • If the selected SSRS Report has parameters, click on “Add” to add parameter. From the “Name” dropdown select the parameter name and from the “Value” dropdown select the value to pass to the parameter.
  1. Action “Go to URL”: This option is ideal to open a SSRS report in a new tab
  • Right-click on a TextBox, select Text Box Properties, select Action tab and select option “Go to URL”.

GoToURL

  • Click on “fx” button under “Select URL”
  • Type the URL of the report in the following format

= “<PWASiteURL>/_vti_bin/reportserver? <PWASiteURL>/ <relativeSSRSURL>&<ParameterName>=<ParameterValue>”

Example of a link to a report named “Project Overall Status”

=LCase(Globals!ReportServerUrl) & “?” & Replace(LCase(Globals!ReportServerUrl),”/_vti_bin/reportserver”,””) & “/ProjectBICenter/SSRS Reports Library/Project Overall Status.rdl&ParmProjectName=” & LCase(Fields!ProjectUID_STR.Value)

The built in field Globals!ReportServerUrl returns the URL where the report is rendered.

  • If you want the report to open in a new tab, add the JavaScript function window.open

Type the URL of the report in the following format

=”javascript:void(window.open(‘<PWASiteURL>/_vti_bin/reportserver? <PWASiteURL>/ <relativeSSRSURL>&<ParameterName>=<ParameterValue> ‘,’_blank’));”

Example of a link to a report “Project Overall Status” in DEV environment with the JavaScript code

=”javascript:void(window.open(‘” & LCase(Globals!ReportServerUrl) & “?” & Replace(LCase(Globals!ReportServerUrl),”/_vti_bin/reportserver”,””)  & “/ProjectBICenter/SSRS Reports Library/Project Overall Status.rdl&ParmProjectName=” & LCase(Fields!ProjectUID_STR.Value) & “‘,’_blank’));”

Add SSRS report to Project Detail Page Project Server

Often clients want to be able to drill down to SSRS reports with the project pre selected when they are viewing project information from project detail pages. This post describes how to embed SSRS report in a project detail page.

Add SSRS Report to Project Detail Page in Project Server

1. Navigate to a PDP (Project Detail Page) , e.g. Schedule PDP.
2. Edit Page
3. Click “Add a Web Part”. Select the “SQL Server Reporting Services Report Viewer” web part.

SQLReportViewerWebPart

4. Click “Add a Web Part”. Select the “SQL Server Reporting Services Report Viewer” web part.

ToolPane_SelectReport

5. Click “Click here to open the tool pane”.

SelectReport

6. Click on the “…” button to select a SSRS report and click apply. In the example below the “Project Overall Status” has been selected with one parameter capable of accepting ProjectUID.

Report_ProjectOverallStatus_NoParam_Passed_edited.png

7. Click “Add a Web Part” to add “Query String (URL) filter”.

QueryString(URL)Filter

8. Click “Open the tool pane” to configure “Query String (URL) filter”.

QueryString(URL)Filter_OpenTheToolPane

9. Add “projuid” into the “Query String Parameter Name” property.

QueryString(URL)Filter_ProjUid

10. Click on the “Arrow Down” on the webpart and click on “Connections”-”Send Filter Values To” – .

SendFilterTo

11. Click on the “Arrow Down” on the webpart and click on “Connections”-”Send Filter Values To” – .
Pick the parameter defined in the SSRS report into “Filtered Parameter”.

FilteredParam

12. Click on “Stop Editing”.

StopEditing

13. The report is rendered displaying only information of the project

SSRS SharePoint Integrated Mode – Cells grow vertically to fit content

I have come across this issue several times that even the “Can Grow” property  of the cell is set to false, the cell grows vertically to fit contents forcing the same row to stretch to the height of the cell distorting the rendering of the report on the browser though it might display correctly in Report Builder or SQL data tools in Visual Studio.

Can Grow is set to False from the cell property.

CanGrow_False

Cell displayed correctly Report Builder

DisplayCorrectly_ReportBuilder

Report does not display correctly from report library in SharePoint 2013

DisplayIncorrectly_Browser

Fix

  1. Clear the cell, i.e. delete the textbox and right click on the cell to add a rectangle.

Insert_Rectangle

2. After adding the rectangle control, right click to add a textbox

Insert_TextBox

3. Select the field to display in the textbox and set writingmode to 270.

TextBoxInsideRectangleSetTo270

4. Save the report from Report Builder and run the report from browser.

fixed_Resize

 

 

Restricting SSRS Report to show only projects users are allowed to see in Project Server 2010/2013 On Premises

To restrict the projects users can see from SSRS, there are several options

1. Use the published database.

However Microsoft does not recommend querying directly published database. This approach can be used at your own risk.


CREATE FUNCTION [dbo].[PWA_FN_UTILITY_GetProjectsUserCanViewProjectDetails]
(
@WRES_NT_ACCOUNT nvarchar(255)
)
RETURNS @OUTPUT TABLE
(
ProjectUID uniqueidentifier
)
AS
BEGIN
DECLARE @RES_UID uniqueidentifier
-- get the user account from the published database, also available in reporting database
-- but in reporting database no indication if user is just a resource or an actual user.
-- REVISIT if necessary.
SELECT @RES_UID = res.RES_UID
FROM PUB.[dbo].MSP_RESOURCES res
WHERE res.WRES_ACCOUNT = @WRES_NT_ACCOUNT
AND res.RES_IS_WINDOWS_USER = 1
IF (@RES_UID IS NOT NULL) -- if user exists and is recognised, filter projects accordingly
BEGIN
INSERT INTO @OUTPUT
SELECT p.ProjectUID
FROM [dbo].MSP_EpmPROJECT p
INNER JOIN MO_Project_Server_Published.[dbo].MSP_WEB_FN_SEC_GetAllProjectsResCanViewByViewID(@RES_UID, 'E98573C8-7EA6-41AB-8EA4-FF8DA9730D0A', NULL, 3) AS p1
ON p1.PROJ_UID = p.ProjectUID
END
-- else return empty table
RETURN
END

2. Using the PSI

2.1 Create a XML data connection pointing to the PSI API

The PSI can be accessed from <PWA URL>/_vti_bin/psi/project.asmx

Create a Shared Data Source of type XML.

Enter the URL of the PSI into “Connection string”

PSIDataSource_XML2.2 Create DataSet to get standard Project

The method  “ReadProjectStatus” from PSI can be used to query projects the users have access to view. However only one particular project type can be passed as parameter. The different project types are explained from

https://msdn.microsoft.com/en-us/library/microsoft.office.project.server.library.project.projecttype_di_pj14mref.aspx

  • Create a Blank Report pointing to the Shared Data Source created in previous step.
  • Create an embedded dataset using the shared data source with the following query to get standard projects. Standard Projects have [Project Type] 0.


<Query>
<Method Namespace="http://schemas.microsoft.com/office/project/server/webservices/Project/" Name="ReadProjectStatus">
<Parameters>
<Parameter Name="projType"><DefaultValue>0</DefaultValue></Parameter>
</Parameters>
</Method>
<SoapAction>http://schemas.microsoft.com/office/project/server/webservices/Project/ReadProjectStatus</SoapAction&gt;
<ElementPath IgnoreNamespaces="true">ReadProjectStatusResponse/ReadProjectStatusResult/diffgram/ProjectDataSet/Project{PROJ_NAME,PROJ_UID,PROJ_TYPE}</ElementPath>
</Query>

  • Update Name to ProjectListDataSet_0

DataSet_Properties_ProjectList

2.3 Create DataSet to get Proposal Project

Follow the same steps as 2.2 to create an embedded dataset.  Name to ProjectListDataSet_4. Proposal Projects have [Project Type] 4

Enter Query

<Query>
<Method Namespace=”http://schemas.microsoft.com/office/project/server/webservices/Project/&#8221; Name=”ReadProjectStatus”>
<Parameters>
<Parameter Name=”projType”><DefaultValue>4</DefaultValue></Parameter>
</Parameters>
</Method>
<SoapAction>http://schemas.microsoft.com/office/project/server/webservices/Project/ReadProjectStatus</SoapAction&gt;
<ElementPath IgnoreNamespaces=”true”>ReadProjectStatusResponse/ReadProjectStatusResult/diffgram/ProjectDataSet/Project{PROJ_NAME,PROJ_UID,PROJ_TYPE}</ElementPath>
</Query>

 

2.4 Create DataSet to get Sub Project

Follow the same steps as 2.2 to create an embedded dataset.  Name to ProjectListDataSet_5. Sub Projects have [Project Type] 5

Enter Query

<Query>
<Method Namespace=”http://schemas.microsoft.com/office/project/server/webservices/Project/&#8221; Name=”ReadProjectStatus”>
<Parameters>
<Parameter Name=”projType”><DefaultValue>5</DefaultValue></Parameter>
</Parameters>
</Method>
<SoapAction>http://schemas.microsoft.com/office/project/server/webservices/Project/ReadProjectStatus</SoapAction&gt;
<ElementPath IgnoreNamespaces=”true”>ReadProjectStatusResponse/ReadProjectStatusResult/diffgram/ProjectDataSet/Project{PROJ_NAME,PROJ_UID,PROJ_TYPE}</ElementPath>
</Query>

 

2.5 Create DataSet to get Master Project

Follow the same steps as 2.2 to create an embedded dataset.  Name to ProjectListDataSet_6. Sub Projects have [Project Type] 6

Enter Query

<Query>
<Method Namespace=”http://schemas.microsoft.com/office/project/server/webservices/Project/&#8221; Name=”ReadProjectStatus”>
<Parameters>
<Parameter Name=”projType”><DefaultValue>6</DefaultValue></Parameter>
</Parameters>
</Method>
<SoapAction>http://schemas.microsoft.com/office/project/server/webservices/Project/ReadProjectStatus</SoapAction&gt;
<ElementPath IgnoreNamespaces=”true”>ReadProjectStatusResponse/ReadProjectStatusResult/diffgram/ProjectDataSet/Project{PROJ_NAME,PROJ_UID,PROJ_TYPE}</ElementPath>
</Query>

2.6 Create hidden Parameter for Standard Projects

  • Create a Parameter named HiddenProjectUID_0.
  • Check “Allow multiple values”
  • Select “Hidden” under “Select parameter visibility”

 

  • Click tab “Available Values”, select option “Get values from a query” and pick DataSet “ProjectListPSIDataSet_1”.
  • Select PROJ_UID for Value field and PROJ_Name for Label field.

AvailableValues_HiddenProjectUID_0

  • Click tab “Default Values”

Select option “Get values from a query” and pick same dataset selected for “Available Values”. Select DataSet “ProjectListPSIDataSet_1” and pick “PROJ_UID” from value field.

DefaultValues_HiddenProjectUID_0

Click on OK to create the dataset.

2.7 Create hidden Parameter for Proposal Projects

Repeat the same steps as 2.6 but pick DataSet ProjectListPSIDataSet_4 instead of DataSet ProjectListPSIDataSet_1. Rename the parameter to HiddenProjectUID_4.

2.7 Create hidden Parameter for SubProject Projects

Repeat the same steps as 2.6 but pick DataSet ProjectListPSIDataSet_5 instead of DataSet ProjectListPSIDataSet_1. Rename the parameter to HiddenProjectUID_5.

2.8 Create hidden Parameter for Master Projects

Repeat the same steps as 2.6 but pick DataSet ProjectListPSIDataSet_6 instead of DataSet ProjectListPSIDataSet_1. Rename the parameter to HiddenProjectUID_6.

2.9 Create hidden parameter that will join the 4 parameters created above.

  • Create an embedded dataset. Update Name to “TempProjetUID”.
  • Check “Allow multiple values”.
  • Select Option “Hidden”.

TempProjectUID

  • Click on tab “Available Values”. Select option “Specify values”.

Under Label column, click the fx button and enter

=Split(Join(Parameters!HiddenProjectUID_0.Value,",")
+ iif(Parameters!HiddenProjectUID_4 is nothing,"", "," + Join(Parameters!HiddenProjectUID_4.Value,","))
+ iif(Parameters!HiddenProjectUID_5 is nothing,"", "," + Join(Parameters!HiddenProjectUID_5.Value,","))
+ iif(Parameters!HiddenProjectUID_6 is nothing,"", "," + Join(Parameters!HiddenProjectUID_6.Value,","))
,",")

Under Value column, click the fx button and enter

=Split(Join(Parameters!HiddenProjectUID_0.Value,",")
+ iif(Parameters!HiddenProjectUID_4 is nothing,"", "," + Join(Parameters!HiddenProjectUID_4.Value,","))
+ iif(Parameters!HiddenProjectUID_5 is nothing,"", "," + Join(Parameters!HiddenProjectUID_5.Value,","))
+ iif(Parameters!HiddenProjectUID_6 is nothing,"", "," + Join(Parameters!HiddenProjectUID_6.Value,","))
,",")

AvailableValues_TempProjectUID

 

Click on tab “Default Values”. Select option “Specify values” and enter after clicking on fx button

=Split(Join(Parameters!HiddenProjectUID_0.Value,”,”)
+ iif(Parameters!HiddenProjectUID_4 is nothing,””, “,” + Join(Parameters!HiddenProjectUID_4.Value,”,”))
+ iif(Parameters!HiddenProjectUID_5 is nothing,””, “,” + Join(Parameters!HiddenProjectUID_5.Value,”,”))
+ iif(Parameters!HiddenProjectUID_6 is nothing,””, “,” + Join(Parameters!HiddenProjectUID_6.Value,”,”))
,”,”)

DefaultValues_TempProjectUID

2.9 Create a Shared Data Source pointing the Reporting Database.

2.10 Create dataset to return ProjectName and ProjectUID using parameter TmpProjectUID

Create a dataset using the data source pointing to the reporting database.

Rename the Dataset to “ProjectListDataSet.

Select Option “Text”.

Enter the SQL query


SELECT ProjectUID, ProjectName
FROM dbo.MSP_EpmProject_Userview
WHERE ProjectUID in (@ProjectUID)
ORDER BY ProjectName
ProjectListDataSet_Prop_1

Click on Parameters tab. Map the parameter @ProjectUID to @TempProjectUID.

Parameters_ProjectListDataSet_Prop_1.png

2.11 Create visible parameter  to display list of projects user has access to.

Create parameter . Rename it to “Projects”.

Click on tab “Available Values” . Pick option “Get values from a query”.

Pick the dataset “ProjectListDataSet” created in step 2.10 . Pick ProjectUID from Value field and ProjectName from Label field

AvailableValu_ProjectUID_Param

2.12 Run the report

The parameter Project display only projects the user can view.

3. Use REST or CSOM in managed code

Though I have not tried it, CSOM or REST (http://ServerName/pwaName/_api/ProjectServer/Projects) can be used in C# Managed Code to return list of projects as an array object which can be bound to a parameter.

Create a TimeLine View for Milestones and Project Duration using Matrix in SSRS

A range chart in SSRS can be used to produce a Gantt chart.of project. Below is a link to a great post that explains how to do it.

http://pnarayanaswamy.blogspot.co.uk/2010/09/range-bar-chart-gantt-chart-using-ssrs.html

Unfortunately I have found the following limitations

  1. The width and height of the range chart does not grow dynamically when run.
  2. If too much data is displayed, the chart is unreadable

To counteract the above limitations, I  have used the matrix control which can grow dynamically in terms of width and height to fit the data which remains readable.

The article describes how to display the timeline of projects with milestones using the Matrix control in SSRS. At the end of step by step guide you will end up with a report which looks like

MilestonesTimeLineView_Report

The diamond shapes represent milestones while the blue color represents the duration of the project.

 Create Milestones Timeline View Report

1. In your Web browser, type the URL for your report library in the address bar.

2. Click ‘New Document’> ‘Report Builder Report’. (Please note if it is the first time you are trying to access report builder, it might prompt to download report builder).

ReportBuilderReport

(Please note if it is the first time you are trying to access report builder, it might prompt to download report builder).
3. Click “Blank Report”
4. Add reference to a Shared Data Source.

To create a Shared Data Source , Click “New Document”> ‘Report Data Source’

NewDataSource

Fill in the following details:
• Name
• Data Source Type: If source is SQL server database, select ‘Microsoft SQL Server’
Connection String: Example of connection string is
Data Source=<SERVERNAME>;Initial Catalog=<DATABASENAME>
Replace the SERVERNAME tag with the database server name instance and DATABASENAME tag with the database name in the server.
The final connection string will look like
“Data Source=SUPPORTSQL2K8R2;Initial Catalog=VM470_PWA_Reporting”
3. Click ‘Test Connection’. If the connection to the data source is successful, the message ‘Connection created successfully’ appears below the ‘Test Connection’ button.

DB_Conn_Success

4. Click ‘Apply’ to save the data source.

5. Right-Click on Datasets Folder , click “Add Dataset”
Update the Name to “MilestoneDataSet”.
Pick the Data Source created earlier.
Select “Text” Option under “Query type”

DataSetProperties

Under Query, copy and paste the following query

DECLARE @workingDate DateTime
SET @workingDate = @inputStartDate


-- create temporary table of unique days for given start date and end date
DECLARE @days TABLE (
DayByDay datetime,
MonthYr nvarchar(25),
WeekMonthYr nvarchar(25)
)


-- calculate each day within number of week range
WHILE (@workingDate <= @inputEndDate)
BEGIN
INSERT INTO @days (
DayByDay,
MonthYr,
WeekMonthYr
) VALUES (
@workingDate
,convert(char(3),(@workingDate),0) + ' ' + convert(nvarchar(6), year(@workingDate))
,convert(nvarchar(3), DATEPART(wk, @workingDate)) + ' ' + convert(char(3),(@workingDate),0) + '-' + convert(nvarchar(6), year(@workingDate))
)
SET @workingDate = DateAdd(d, 1, @workingDate)
END


--Create temporary table to store ProjectUID
DECLARE @TProject TABLE(
ProjectUID uniqueidentifier
)


INSERT INTO @TProject
SELECT DISTINCT PROJECTUID
FROM dbo.MSP_EpmProject_UserView P
INNER JOIN @days D ON CONVERT(DATE,P.ProjectStartDate) <= D.DayByDay AND CASE WHEN ISNULL(CONVERT(DATE,P.ProjectActualFinishDate),CONVERT(DATE,'1900-01-01')) > CONVERT(DATE,P.ProjectFinishDate) THEN CONVERT(DATE,P.ProjectActualFinishDate) ELSE CONVERT(DATE,P.ProjectFinishDate) END >= D.DayByDay
AND P.ProjectUID in (@ProjectUId)


-- CREATE INDEX TmpDelProj ON @TProject(ProjectUID)


--select * from #TProject
-- create temporary table of unique days for given start date and end date
DECLARE @PMilestoneTmp TABLE (
ProjectUID uniqueidentifier,
DayByDay datetime,
MonthYr nvarchar(25),
WeekMonthYr nvarchar(25),
MilestonesName nvarchar(max)
)
--in case there are two milestones finishing in same week
INSERT INTO @PMilestoneTmp
SELECT P.ProjectUID
, d.DayByDay
, d.[MonthYr]
, d.WeekMonthYr
,(STUFF((
SELECT ' ,' + convert(nvarchar(3), day(TaskFinishDate)) + ' ' +
+ convert(char(3), TaskFinishDate , 0) + ' ' +
+ convert(nvarchar(6), year(TaskFinishDate))
+ ' - ' + T.TaskName
FROM dbo.MSP_EpmTask_UserView T
WHERE T.ProjectUID = P.ProjectUID
AND convert(nvarchar(3), DATEPART(wk, T.TaskFinishDate)) + ' ' + convert(char(3),(T.TaskFinishDate),0) + '-' + convert(nvarchar(6), year(T.TaskFinishDate)) = d.WeekMonthYr
AND T.TaskIsMilestone = 1
ORDER BY T.TaskFinishDate ASC
FOR XML PATH(''), TYPE, ROOT).value('root[1]', 'nvarchar(max)'),1,3,'')
) AS MilestonesName
FROM @TProject P
CROSS JOIN @days d


--create table for milestones ending per day per project
SELECT MP.DayByDay
, MP.MonthYr MonthYear
, 'Wk' + MP.WeekMonthYr WeekMonthYr
, MP.MilestonesName
,P.ProjectName
,P.ProjectUID
,P.ProjectFinishDate
,P.ProjectStartDate
,P.ProjectActualFinishDate
,P.ProjectBaseline0FinishDate
,(CASE WHEN CONVERT(DATE,P.ProjectStartDate) <= MP.DayByDay AND CASE WHEN ISNULL(CONVERT(DATE,P.ProjectActualFinishDate),CONVERT(DATE,'1900-01-01')) > CONVERT(DATE,P.ProjectFinishDate) THEN CONVERT(DATE,P.ProjectActualFinishDate) ELSE CONVERT(DATE,P.ProjectFinishDate) END >= MP.DayByDay THEN 1
ELSE 0 END ) ProjectInProgress
FROM @PMilestoneTmp MP
LEFT OUTER JOIN dbo.MSP_EpmProject_UserView P ON P.ProjectUID = MP.ProjectUID
ORDER BY MP.DayByDay ASC

6. Click on “Ok”.
Three parameters are created. Expand the Parameters folder to view @inputStartDate,@inputEndDate and @ProjectUId parameters

Parameters

7. Right-click on @inputStartDate and select “Parameter Properties”.Update as follows
Change the “Prompt” to “Start Date”.
Update “Data type” to “Date/Time”.

inputStartDateProp

8. Right-click on @inputEndDate and select “Parameter Properties”.Update as follows
Change the “Prompt” to “End Date”.
Update “Data type” to “Date/Time”.

inputEndDateProp

9. Right-Click on Datasets Folder , click “Add Dataset”
Update the Name to “Param_ProjectList”.
Select “Data Source”
Select “Text” under “Query type”
Copy and paste the SQL query under “Query”

SELECT
MSP_EpmProject.ProjectUID
,MSP_EpmProject.ProjectName
FROM
MSP_EpmProject
ORDER BY MSP_EpmProject.ProjectName

Project_Dataset_Prop

Click on Ok to create the dataset
10. Right-click on @ProjectUId parameter and select “Parameter Properties”.Update as follows
Click on “General” tab and check “Allow Multiple values”

Project_Multi_Select

Click on “Available Values” tab and select option “Get values from a query”. Pick the Dataset “Param_ProjectList” and Select “ProjectUID” in value field and “ProjectName” in Label field.

ProjectParamProp_AvailableValues

11. Add and edit the Matrix
• Right–Click on the body of the report and choose Insert>Matrix.
• Right-Click on the Matrix and select Tablix Properties.
• Select the “MilestoneDataSet” into the Dataset name.

Matrix_Properties

• Select the “MilestoneDataSet” into the Dataset name and click “Ok”
• Right click on the “RowGroup” and select “Group Properties”. Select [PROJECT_UID] in “Group On:” field and update name to ProjectUID.

Group_On_Project

• Right click on the “ColumnGroup” and select “Group Properties”. Select [WeekMonthYr] in “Group On:” field and update name to WeekMonthYr.

WeekMonthYr

• Right click on “WeekMonthYr” and select “Add Group” > “Parent Group”.

WeekMonthYr_ParentGroup

• Select [MonthYear] in “Group by” and click on Ok.

MonthYear_Group

• Right click on the “MonthYear” and select “Group Properties”. Select ”Sorting” tab and click on “Add”. Select [DayByDay] in “Sort by” and Order “A to Z”. Click on Ok.

Group_Sort_DayByDay

The Matrix will look like

Matrix_Display

• Select the second row and delete row by picking option “Delete rows only”.

DeleteGroup

• Select field [ProjectName] in first column. The header is automatically updated to “Project Name”.
• In the second column, right click and pick Text Box properties

TextBox_Prop

 

Enter expression in Value

=iif(Fields!MilestonesName.Value<>"" OR NOT (Fields!MilestonesName.Value is nothing),"t",nothing)

Enter [MilestonesName] into ToolTip.
Click on “Font” tab

TextBox_Font_Prop

Click on “fx” button under Font and enter

=iif(Fields!MilestonesName.Value<>"" OR NOT (Fields!MilestonesName.Value is nothing),"Wingdings","Arial")

Click on “Fill” tab

Fill

Click on Fx Button under Fill Color and enter


=iif(Fields!ProjectInProgress.Value = 1,"LightBlue",nothing)

Click on OK.
• Decrease the width of the second column

MilestonesTimeLineView

12. Save and Run the report after selecting parameters. The diamond shape is displayed in cells where Milestone exist.

MilestonesTimeLineView_Report

Hovering over the diamond shape displays information regarding the milestone

Capture

 

Building SSRS using Report Builder for Project Server On Premises

1. Article Purpose

I have worked with SSRS for nearly five years now. It is an efficient tool to display data from Project Server Reporting database.
This article provides a step to step guide how to create reports using Report Builder from Project Server 2010 /2013 (On Premises) environment.

2. Report Builder

Report Builder is a report authoring tool that helps to design, test, print and manage reports. Different type of reports can be created using a combination of tables, matrices, lists, and charts depending on reporting needs. Additionally data can be manipulated by filtering, grouping, sorting and adding expressions and parameters. After designing the report, it can be either saved to the local computer or published to report server.

2.1 Data Source

A data source allows connecting to a repository of data. It contains connection details and credentials. Data can be read from different sources for example:

• SharePoint Lists
• XML
• Oracle
• SQL Server

2.1.1 Create Shared Data Source
The shared data source has to be created using Report Manager.
1. Open a web browser, type the URL of the reporting server in the address bar.
By default, the URL is http://servername/reports

If you are using SSRS integrated mode in SharePoint, configure a document library to have the following content types

  •   Report Data Source
  •   Report Builder Model
  •   Report Builder Report

Wherever in the article it specifies navigate to the reporting server URL from the browser type in the URL of the reporting library.
2. Click ‘New Data Source’ if using native reporting server is used else click on “New Document”>”Report Data Source” if creating from a report document library.

NewDataSource

Fill in the following details:
• Name
• Data Source Type: If source is SQL server database, select ‘Microsoft SQL Server’
Connection String: Example of connection string is
Data Source=<SERVERNAME>;Initial Catalog=<DATABASENAME>
Replace the SERVERNAME tag with the database server name instance and DATABASENAME tag with the database name in the server.
The final connection string will look like
“Data Source=SUPPORTSQL2K8R2;Initial Catalog=VM470_PWA_Reporting”
3. Click ‘Test Connection’. If the connection to the data source is successful, the message ‘Connection created successfully’ appears below the ‘Test Connection’ button.

DB_Conn_Success

4. Click ‘Apply’ to save the data source.

2.2 Data Set

2.2.1 Create Shared Data Set

A data set is a collection of related data records from a repository of data.
1. In your Web browser, type the URL for your report server in the address bar.
By default, the URL is http://servername/reports.
2. Click ‘Report Builder’  (Please note if it is the first time you are trying to access report builder, it might prompt to download report builder) if using native reporting server else click on “New Document”>”Report Builder Report” if accessing from a report document library.

3. Click ‘New Dataset’.

NewDataset

4. Click ‘Browse other data sources…’. The ‘Select Data Source’ open dialog is displayed. Navigate to the appropriate data sources folder and select a data source.

BrowseForDataConn

5. Click on ‘Create’ Button. The ‘Enter Data Source Credentials’ dialog appears.

CredentialsForDataConn

6. Enter the Data Source Credentials and click on ‘OK’. The following window appears to allow creation of the dataset.

QueryDesigner

The database view is displayed on the left panel. Four folders are available:
• Tables
Tables are physical storage of the data. In the PWA Reporting Database, some of the tables are
• MSP_EpmProject
• MSP_EpmTask
• MSP_EpmResource

• Views
A view is a representation of the data from the tables. PWA provides out of the box views to facilitate reporting. A view looks like a table but does not store the data, only definition. Common views are
• MSP_EpmProject_UserView
• MSP_EpmResource_UserView
• MSP_EpmTask_UserView
• Stored Procedures
A stored procedure can be used to perform tasks within the database, whether it is to INSERT, UPDATE, DELETE, SELECT. It can accept parameters, hence the preferred method of manipulating and returning data. A stored procedure is stored and cached by SQL Server’s optimizer and thus making it faster than an ad hoc query.

• Table-valued Functions
Functions are preferential over stored procedures because they can easily be used in JOINs and regular SELECTs. Compared to a view, it can be parameterised.
7. Tables, views, stored procedures and functions can be used to create a dataset. If you want to create a dataset to retrieve a list of tasks, expand the Tables folder from the database view panel and select the Tables ‘MSP_EpmProject’ and ‘MSP_EpmTask’.

8. Click on ‘Run Query’. Data from the two tables are displayed.
9. To filter the data returned, add filters to the dataset. In the ‘Applied filters’ section, click on the ‘Add Filter’ icon. A new row appears under the ‘Applied filters’ section.

• Select ‘TaskIsMilestone’ from the available fields for Field name column.
• Select ‘is’ in the Operator column
• Enter ‘True’ in the Value column

The filter created forces the dataset to return only tasks set as milestone.

AddFilterToDataSet

A parameter can be created using the same principle as the filter except the Parameter column is checked. In the above screenshot, ProjectUID is defined as a parameter which has to be passed through to the dataset before it is executed.
10. Click on ‘Edit as Text’ to view the SQL generated.

SELECT
MSP_EpmProject.ProjectUID AS [MSP_EpmProject ProjectUID]
,MSP_EpmProject.ProjectName
,MSP_EpmProject.ProjectAuthorName
,MSP_EpmProject.ProjectOwnerResourceUID
,MSP_EpmProject.ProjectManagerName
,MSP_EpmProject.ProjectType
,MSP_EpmProject.ProjectStartDate
,MSP_EpmProject.ProjectFinishDate
----------------------------
,MSP_EpmTask.TaskUID
,MSP_EpmTask.TaskParentUID
,MSP_EpmTask.ProjectUID AS [MSP_EpmTask ProjectUID]
,MSP_EpmTask.FixedCostAssignmentUID
,MSP_EpmTask.TaskName
,MSP_EpmTask.TaskIsExternal
,MSP_EpmTask.TaskIsRecurring
,MSP_EpmTask.TaskCostVariance
,MSP_EpmTask.TaskIsActive
----------------------------
FROM
MSP_EpmProject
INNER JOIN MSP_EpmTask
ON MSP_EpmProject.ProjectUID = MSP_EpmTask.ProjectUID
WHERE
MSP_EpmTask.TaskIsMilestone = N'True'
AND MSP_EpmProject.ProjectUID IN (@ProjectUID)

Please note the SQL shown above is not a complete script. The dashes represent other fields not specified above.
An inner join between tables MSP_EpmProject and MSP_EPMTask has been created using ProjectUID as the relationship linking the tables together.
The filters have created the two criteria under the WHERE clause.

2.3 Report

 

2.3.1 Create Report

1. In your Web browser, type the URL for your report server in the address bar.
By default, the URL is http://servername/reports.
2. Click ‘Report Builder’ (Please note if it is the first time you are trying to access report builder, it might prompt to download report builder) if using native reporting server else click on “New Document”>”Report Builder Report” if accessing from a report document library.

3. Click ‘Blank Report’.

NewReportBuilder

The default Report Builder interface has:
• The report data pane on the left which provides a single place for accessing the built-in fields, report parameters, images, and data fields. The data fields will appear after queries are defined.
• The centre region is the report designer. Controls such as text box or tablix/matrix can be inserted.
• Row Groups and Column Groups allow to setup groupings by dragging and dropping columns onto this area.

BlankReport

2.3.1.1 Use Shared Dataset

1. Right-click on ‘Datasets’ from the Report Data pane and click on ‘Add Dataset’.

NewDataSetLinkedtoSharedDs

The Dataset Properties dialog opens.

BrowseToSharedDataSet

2. Select ‘Use a shared dataset’ and click on ‘Browse’ from the ‘Dataset Properties’ dialog. The ‘Select Dataset’ dialog appears.

BrowseToSharedDataset_1

3. Navigate to where the shared dataset created earlier has been saved and select it by clicking on ‘Open’. The ‘Select Dataset’ dialog closes.
4. Rename the Dataset to ‘TasksDataset’ and click on ‘OK’.
5. The dataset is created and can be viewed by expanding the ‘Datasets’ node from the report data pane.

Notice the parameter ‘ProjectUID’ defined for the dataset is automatically created on the report level.

TasksDatasetCreated

2.3.1.2 Use Shared Data Source

http1. Right-click on ‘Data Sources’ from the Report Data pane and click on ‘Add Data Source’.

AddSharedDataSource

The ‘Data Source Properties’ dialog appears.

SelectSharedDataSource

2. Select ‘Use a shared connection or report model’ and click on Browse.. The ‘Select Data Source’ dialog opens.

BrowseToSharedDataSource
3. Navigate to the location where the shared data source is saved and click on ‘Open’ after selecting it.

4. Rename the Data Source to ‘ReportingDb’.
5. Click on ‘Test Connection’. If the connection is successful, the message ‘Connection created successfully’ is displayed.

BrowseToSharedDataSource_1

6. Click on ‘Ok’ and the data source is created.

BrowseToSharedDataSource_2

2.3.1.3 Create Embedded Dataset to SQL Server

1. Right-click on ‘Datasets’ from the Report Data pane and click on ‘Add Dataset’.

NewDataSetLinkedtoSharedDs
2. Select ‘Use a dataset embedded in my report’

3. Select ‘ReportingDb’ from the Data Source drop down field. The ‘Query Designer’ button is enabled.
4. Click on the ‘Query Designer’ button. The ‘Query Designer’ window appears.

5. Expand the Tables node and ‘MSP_EpmProject’ node. Select the fields ‘ProjectUID’ and ‘ProjectName’.

RunQuery

6. Click on ‘Run Query’. The results appears in the ‘Query results’ pane.
7. Click on ‘Ok’. The ‘Query Designer’ window closes. Please note the SQL generated in the ‘Query’ section.

GeneratedEmbeddedQuery

8. Name the dataset to ‘ProjectDataset’ and click on OK. The dataset is created.

DataSetInserted

2.3.1.4 Create Embedded Dataset to Sharepoint List

1. Right-click on ‘Datasets’ from the Report Data pane and click on ‘Add Dataset’.
2. Select ‘Use a dataset embedded in my report’.

DataSet

3. Click on ‘New..’ button next to the Data source drop down.

SharePointListConn

4. Enter ‘SharepointList’ in the Name field.

5. Select ‘Microsoft Sharepoint List’ from the ‘Select connection type’ drop down.
6. Enter the URL of the Sharepoint site you want to connect in the ‘Connection string’ field.
e.g. http://vm375/pwa/Project1/
7. Click on ‘Test Connection’ button. If the connection is successful, the message ‘Connection created successfully’ is displayed.

Conn_Success

8. Click on OK.

SP_List_Conn_Details

9. Enter IssuesDataSet in the ‘Name’ field.
10. Select ‘Text’ under ‘Query type’ and enter the following in the ‘Query’ field

<RSSharePointList xmlns:xsi=”http://www.w3.org/2001/XMLSchema-instance&#8221; xmlns:xsd=”http://www.w3.org/2001/XMLSchema”&gt;

<ListName>Issues</ListName>

<Query>

<Where>

<Eq>

<FieldRef Name=’Status’ />

<Value Type=’Choice’>(1) Active</Value>

</Eq>

</Where>

</Query>

</RSSharePointList>

This retrieves the active issues from Sharepoint List. CAML Builder can be used to build the highlighted query above.
The CAML builder can be downloaded from
http://www.u2u.be/res/tools/camlquerybuilder.aspx
11. Click on ‘Ok’.
12. The dataset is created with the list of available fields from the list.

2.3.1.5 Parameter
2.3.1.5.1 Edit Parameter

1. Expand the Parameters node and right-click on the existing parameter ‘ProjectUID’ and select ‘Parameter Properties’.

Param_Prop_0

The ‘Parameter Properties’ dialog appears.

Param_Prop_1

If the report is run, the parameter ‘ProjectUID’ appears at the top the report. It currently does not have any items to select from.

EmptyParam

If ‘View Report’ is clicked, the error message appears because the ProjectUID parameter cannot be left blank.

Param_Blank

2. Enter ‘Project Name’ in the Prompt field.
3. Click on the ‘Available Values’ from the ‘Report Parameter Properties’.
4. Choose ‘Get Values from a query’ and select ‘ProjectDataset’ from ‘Dataset’ drop down.

Get_Values_From_Param

5. Select ‘ProjectUID’ and ‘ProjectName’ from ‘Value field’ and ‘Label field’ respectively.
6. Click on ‘Default Values’ tab from the ‘Report Parameter Properties’ dialog. The default values specified are selected when the report is run. If you want all projects to be selected by default when running the report, choose the option ‘Get values from a query’, select the dataset ‘ProjectDataset’ and ‘ProjectUID’ for the ‘Value field’.

ProjectDataset

7. Click on ‘OK’ to save the changes. If the report is now run, the Project Name parameter is populated with the list of projects returned by the ‘ProjectDataset’ dataset.

2.3.1.5.2 Create Parameter

1. Right-click on Parameters node and select ‘Add Parameter’.

CreateParam

The ‘Report Parameter Properties’ dialog appears.

2. Enter ‘ReportDate’ in the ‘Name’ field.
3. Enter ‘Date of Report’ in the ‘Prompt’ field.

4. Select ‘Date/Time’ as Data type.

DateOfReport
5. Click on Ok to save the parameter details.
If the report is run, the parameter ‘Date of Report’ is displayed.

DateParam

2.3.1.6 Header

1. Right-click on the Report Designer Region, select Insert > Page Header.

PageHeader

The Page Header is created at the top of the report with the message ‘To add an item to the page header: add an item to the report and then drag it here’.

AddAnItem

2. Right-click on the Header Region and select Insert>Text Box. A Text Box is created.
3. Double click in the Text Box and enter ‘Milestones’.

TextBox
4. If the properties pane is not visible on the screen, click on the ‘View’ pane and check the ‘Properties’ check box.

ViewProperties

Select the header and the following properties are displayed.

PageHeader_Properties

 

5. Left click on the text box and drag the text box to the centre while still pressing the left click.

TextBox

6. Properties can be changed via the properties pane and properties dialog. Right click on any control and select properties to open the properties dialog.

Select the header and on the properties pane, enter 10 mm for the height property of the header. As you navigate away from the property, the page header is automatically resized.

2.3.1.7 Footer

The footer is available on the report when it is created with the Execution Time by default

ExecutionTime

1. Right-click on the Page Footer and select Footer Properties. The ‘Page Footer Properties dialog’ opens. It gives options to change the height, background colour, border, and printing criteria.
2. Right-click on Page Footer and select Insert >Text Box.
3. Right-click on the Text Box and select ‘Expression…’
4. Enter the following value in the ‘Expression’ dialog and click on ‘OK’.
= “page” & Globals!PageNumber & ” of ” & Globals!TotalPages

PageNum_TotalPages

This displays the page number at the bottom of the report in the following format ‘page 1 of 10’.

5. Right-click on the ‘[&ExecutionTime]’ text box and select ‘Text Box Properties…’ The Text Box Properties’ dialog opens.

Date_Prop

6. Select the Number tab, choose ‘Date’ from Category and ‘31/01/2000’ from Type. Click on ‘Ok’ to save the changes. This will format the execution time to ‘dd/mm/yyyy’.

2.3.1.8 Display Data

In the data region, a text box displaying ‘Click to add Title’ is present by default. Right-click on the text box and select ‘Delete’ to remove the text box.

Clicking the Insert tab on the ribbon bar displays the following:

Controls_Display

The Insert ribbon allows you to drag and drop elements onto the report designer.

2.3.1.8.1 Tablix

Tablix is a flexible report item that can be used to display data in a grid format, with layout possibilities ranging from simple tables to advanced matrices.

2.3.1.8.1.1 Create Tablix

1. In design view, click on the Insert tab from the Ribbon, select on Table and click on ‘Insert Table’. Alternatively, right-click on the report region and select Insert>Tablix.

Tablix

2. Move the cursor on the Centre Region and click. A blank table is created.
Milestones for the current month based on the report date selected need to be displayed on the table.
3. Right-click on the Tablix and select ‘Tablix Properties’

TablixProperties

4. Enter ‘MilestonesCurrentMonth’ in the Name field.
5. Select ‘TasksDataset’ from the ‘Dataset name’ drop down.
6. Double click the first cell of the Tablix and enter ‘Task Name’.
7. Click the first cell of the second row and click on the icon appearing on the top right corner. Select the field ‘TaskName’ from the available fields displayed.

Field_TaskName

8. Right-click on the tablix and select Insert Column>Right. A new column is created before the Task Name. Select the field ‘ProjectName’ and enters ‘Project Name’ as the column header.
9. Drag the field ‘Task Start Date’ to the third column. Rename the header to ‘Start Date’.
10. Drag the field ‘Task Finish Date’ to the Fourth column. Rename the header to ‘Finish Date’.
11. Drag the field ‘Task Percent Complete’ to the Fifth column. Rename the header to ‘% Completed’.
12. Run the report. All the projects are selected by default, select a date and click ‘View Report’.
13. The results are shown

TaskData_1

 

2.3.1.8.1.2 Create Filters on Tablix

To display only milestones having finish date during the current month, filter can be added to the tablix.
1. In design view, right-click on the Tablix and select ‘Tablix Properties’. Click the ‘Filter’ tab.

Filter

2. Click on ‘Add’ in the ‘Change Filters’ section.
3. Click the ‘fx’ button next to the Expression field.

Expression_Filter_Date

4. Enter the expression
=datepart(dateinterval.month,Fields!TaskFinishDate.Value)
The expression returns the month the finish date falls.
And click on ‘OK’. The expression is saved.
5. Choose ‘=’ as Operator.
6. Click the ‘fx’ button next to the ‘Value field’ and enter the following expression
=DatePart(DateInterval.Month, Parameters!ReportDate.Value)
The expression returns the month of the report date selected.
7. Click ‘Run’ to run the report and validate whether the data has been filtered by the report date.
8. Add another filter to return tasks that are 100 percent completed
• Select field ‘TaskPercentWorkCompleted’ from the Expression drop down
• Select ‘=’ in the operator
• Enter ‘=100’ in the value field
9. Click ‘Run’ to run the report and validate whether the data has been filtered by the report date and completed tasks.

2.3.1.8.1.3 Sorting

1. In design view, right-click on the Tablix and select ‘Tablix Properties’. Click on the ‘Sorting’ tab.

Sorting_01

2. Click ‘Add’ under the ‘Change Sorting options’.
3. Select ‘ProjectName’ in the ‘Sort by’ drop down. Select the order ‘A to Z’ which means sort in ascending order.
4. Click ‘Add’
5. Select ‘TaskName’ in the ‘Sort by’ drop down. Select the order ‘A to Z’ which means sort in ascending order.
6. Click ‘Add’
7. Select ‘TaskFinishDate’ in the ‘Sort by’ drop down. Select the order ‘Z to A’ which means sort in descending order.
8. Click ‘Run’ to run the report and validate whether the data has sorted based on the sorting criteria.

2.3.1.8.1.4 Create Groups on Tablix

1. In design view, insert Tablix in the report.
2. Right-click on the Tablix and select ‘Tablix Properties’.
3. Set the DataSetName Property of the tablix to the ‘TasksDataSet’.
4. In the ‘Row Groups’ region, right-click the existing group (“Details”) and select ‘Add Group’ > ‘Parent Group’

RowGroup

The ‘Tablix group’ dialog appears.

TablixGroup

5. Select ‘ProjectName’ in the ‘Group by’ drop down and click on ‘Ok’. The group is created and a column is added to the tablix displaying the ProjectName.
6. Delete two empty columns from the tablix leaving two columns.
7. If the count of milestones defined per project needs to displayed,
Enter ‘Milestone Count’ in the header of the second column and the following expression in the data cell
=Count(Fields!TaskUID.Value)

The tablix looks like

TablixWithGroup

8. Click ‘Run’ to run the report. The report does not display count of milestones per project as expected. It displays several rows with the value

Group_MilesCount

9. Go back to the design mode and Right-click on the group ‘Details1’ and select ‘Delete Group’.

DeleteGroup

The ‘Delete Group’ dialog appears.

Select the option ‘Delete group only’ and click Ok. The group ‘Details1’ is deleted.

DeleteGroupAndrelated

10. Run the report again.

The report displays number of milestones per project.

Group_MilesCount_1

2.3.1.8.2 Matrix

Matrix enables grouping by column in additional to the existing functionalities of a tablix.
1. In design view, right-click on the report region and select Insert>Matrix. A matrix is created on the report.

Matrix_Layout
2. Look at the Grouping Region.

3. A default ‘Row Group’ and ‘Column Group’ is available.
4. Right-click on the Matrix and select ‘Tablix Properties’.
5. Select ‘TasksDataset’ from the Dataset drop down and click on OK.
6. Right-click on the ‘RowGroup’ and select ‘Group Properties’.

RowGroup

7. Enter ‘ProjectNameGroup’ in the Name field.
8. Select ‘ProjectName’ from the ‘Group on’ drop down.
9. Right-click on the ‘ColumnGroup’ and select ‘Group Properties’.

ColumnGroup_Properties

10. Enter ‘MonthGroup’ in the Name Field.
11. Enter the following expression in the ‘Group on’ field:

=datepart(dateinterval.month, Fields!TaskFinishDate.Value) & “-” & datepart(dateinterval.year, Fields!TaskFinishDate.Value)

The expression evaluates to Month-Year format.

12. Click on Ok to create the column group.
13. In the column header, enter the same expression used in the ‘Group on’ Expression.

=datepart(dateinterval.month, Fields!TaskFinishDate.Value) & “-” & datepart(dateinterval.year, Fields!TaskFinishDate.Value)
14. In the Row header, select the field ‘ProjectName’
15. In the data cell, enter the following expression
=Count(Fields!TaskUID.Value)

The matrix will display count of milestones per project per calendar month.

Matrix_Row_Col_Group.png

The data can be sorted in the same way sorting was applied to the tablix.

2.3.1.8.3 Charts

Data can be displayed using different type of charts.
1. In design view, right-click on the report region and select Insert>Chart. A ‘Select Chart Type’ dialog appears.

Chart_1

2. Right-click on the report region and select Insert>Chart. A ‘Select Chart Type’ dialog appears.
3. Select the first highlighted chart. A chart is inserted in the report region.

Chart_2

4. Right-click on the Chart and select ‘Chart Properties’.

Chart_Props

5. Select ‘TasksDataset’ from the Dataset drop down.
6. Enter ‘MilestoneChart ‘in the Name field and click on ‘Ok’.
7. Double click on the chart to display the ‘Chart Data’ properties.

Chart_Series_Values

8. Click on the ‘+’ icon next the ‘Values’ section. Select ‘TaskUID’.
9. Click on the Sum(TaskUID) series and select Aggregate>Count.

Chart_3

10. Click on ‘Details’ under the Category Groups and select ‘ProjectName’. This automatically creates a group.

The chart displays count of milestones per project.
11. Double click on the ‘Axis Title’ on the Y axis and enter ‘Count’.
12. Double click on the ‘Axis Title’ on the X axis and enter ‘ProjectName’.
13. Run the report and the following chart is displayed.

Chart_Title

2.3.1.9 Formatting

The Home tab on the ribbon interface provides options to format report.
The user interface is pretty intuitive and easy to use as a Word Document. It has options to format the font, border, paragraph, etc….

2.3.1.10 Page Set Up

1. In design view, right-click outside the report region and select report properties.

ReportProp

The ‘Report Properties’ dialog appears.

Dialog

2. Select the orientation of the report to be either Landscape or Portrait. The Page Setup determines how the report will look on printing.