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.

 

Advertisements

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