You can add custom reports to any report section available to you (Project, Employee, Client, Contact, Vendor, Billing, etc…)

There are no naming conventions for the report file itself but we suggest to follow the Cloud Naming Convention: <Your 10 digit client number>_<Your Report name>.rdl (e.g.: 0000012345_MyCustomReport.rdl)

Open Vision and go to Utilities – Report Administration, select Custom in Type and upload all or specific report files by using a filter in the Report Name field (e.g. 0000012345*) and then click Load Report Files

Adding reports to a SaaS installation

Reports can be uploaded directly from your local machine to the cloud

If you want to upload a new version of an existing report, delete this report first and save

Use the Upload button to select a report file on your local machine

You must follow the Cloud Naming Convention: <Your 10 digit client number>_<Your Report name>.rdl (e.g.: 0000012345_MyCustomReport.rdl). All other files will be ignored.

Once the file is uploaded and appears in the custom report table, select the appropriate Location from the dropdown menu

You can turn on this feature for On Premise installations as well. (see follow up)

Create a new project and pick the “Report Server Project” from the Business Intelligence – Reporting Services] template folder.

Select an appropriate name and folder for the solution.

We suggest to use some kind of source control system.

Adding a report to the project

Right click on the Reports folder in the Solution Explorer, select Add > Existing Item … and pick one or more Vision Report Templates from Vision Installation Directory>\Reports\Template. It is a good idea to copy the complete folder to your development machine.

Add an existing item by right-clicking on the Shared Data Sources and pick the DefaultDataSource.rds from the <Vision Installation Directory>\Reports folder

Both files have now been copied to your solution folder. Make sure that the files are not Read-Only

Change the connection in DefaultDataSource.rds to your Vision database

Rename the VisionTemplatePortrait.rdl file to 0000000000_InsightSampleReport.rdl

Phase 1 statement explained

The base info center tables (EM, EmployeeCustomTabFields) must be the first items in the FROM clause of the statement.

Each project level is added independently to the output (as PR, LEVEL1 and LEVEL2)

The Non-billable Labor Code is stored in the CFGBillMain table (per company)

We only want to see the employees of the current company. The active company code is provided later by Vision, we use the CFGSystem to check if the database has Multi Company enabled and then use the CFGFormat table to see which portion of the Org code is used for the company code.

CROSS APPLY is used instead of JOIN for both the CFGSystem and CFGFormat because these tables only contain one data row

Setting up our first report

In Visual Studio open the “Report Data View”, expand the Datasets element and double click on the ReportDataset item.

The system now assigns the report parameters to the query parameters and updates the available fields list for the ReportDataset

Do not delete or overwrite the first column in the MainTable Tablix and add the dataset fields to the Tablix

Save it and upload it to Vision

Phase 1 Demo

Open Report in VS

Add SQL Statement

Set up parameters

Upload report to Vision and run

Using the selection feature

Phase 2 extensions

Change Name in Design and Custom Properties

Change Report Version

Set LookupType in Custom Properties to Employee

Add the /***EXTEND WHERE CLAUSE***/ Placeholder to the SQL Statement

Upload the report

Phase 2 Demo

Make changes as described in previous screen

Upload report to Vision

Selection column is now available. Select employee(s)

Run Report

Adding custom parameters

Phase 3 extensions

We want to add the ability to show Regular Projects Only. We will add a custom check box to the Custom Report Options called “Regular Projects Only”

The option has to be defined as “RegularProjectsOnly” of type Check Box

In the report parameters it must be added as @CustRegularProjectsOnly of type Text (Vision will send ‘Y’ or ‘N’ as values)

Edit the SQL and add this before the /***EXTEND WHERE CLAUSE***/ Placeholder: AND (@CustRegularProjectsOnly = 'N' or PR.ChargeType = 'R')

Add the /***EXTEND WHERE CLAUSE***/ Placeholder to the SQL Statement

Upload the report

Phase 3 Demo

Make changes to SQL

Add additional parameter in report design

Upload report to Vision

Add Custom Report Options

In Report Options, set new parameter

Run report

Using the column selection feature

Phase 4 extensions

We want to add the ability show, hide and format report columns through the columns tab in the Options dialog

This needs changes in the designer only, no change to the SQL Statement necessary

Change the Custom Property UseColumnSelection to “Y”

You must follow a strict naming convention for each element in the Tablix table for this to work

Each column can be set to show by default. We need to apply the “Default” custom property to each header element and set it to “Y”

Column and field naming conventions

Property

Description

Sample Value

DataElementName

All cells in a column have to share the same DataElementName to let Vision identify the related fields

EmployeeName

[Content of Header]

will be used to display a heading value in the Options dialog box

Employee Name

DocumentMapLabel (in Header Row)

This is used if no value is set in the Header Content

Employee Name

Name

Each element name in a report must be unique. The prefix is used to track the specific element. The suffix for all cells in a column must be the same

xxxxx_EmployeeName

Name (in Header Row)

Must always start with the header_ prefix

header_EmployeeName

Name (in Group Row)

Must always start with the group_ prefix

group_EmployeeName

Name (in Custom Group)

Never use group1, group2 as your group prefix. Always use proper naming

groupMyGroup_EmployeeName

Name (in Detail Row)

Must always start with detail_ prefix

detail_EmployeeName

Phase 4 Demo

Add Data Element Names

Add Names

Add Titles

Set Custom Property

Upload to Vision

Select columns in report options

Using the sorting/grouping feature

Phase 5 extensions

Add the /***GROUP COLUMNS***/, /***GROUP JOIN***/, /***GROUP CLAUSE***/ (or /***EXTEND GROUP CLAUSE***/) and /***ORDER CLAUSE***/, (or /***EXTEND ORDER CLAUSE***/) into the correct positions of the SQL Statement. Make sure your statement still works if new or additional groupings are added automatically!

Set the GroupingType to “Employee” and the AutoGroupQuery to “Y” in the report’s custom properties

If any totals should be shown for any column, the total calculation has to be placed in the predefined group header

Phase 5 Demo

Add placeholders into SQL

Set up grouping type and autogroup flag

Upload report to Vision and select groupings in Report options

Run report

Other custom properties and features

Additional properties

AllowUserDefinedFields: set this property to the Info Center for which you want to show the fields (Employees, Projects, …). Make sure that the xxx_CustomTabFields is available in your SQL Statement and that the /***CUSTOM COLUMNS***/ placeholder is inserted after the SELECT keyword or the /***GROUP COLUMNS***/ keyword

AllowCalcFields: simply set it to “Y” to be able to add calculated fields to your custom report

Handling HTML text fields: some of the Vision text fields contain HTML formatted text (e.g. Timesheet comments). To display these correctly an additional custom property must be set for the text box called TextFormatType. The value must be set to HTMLFormat

Project reports and WBS strucures

Any report that is referring to projects and their WBS structure needs to implement a specific join clause in the FROM clause of the project. You can then add any additional tables to the SQL Statement. When referring to the project level, join to the PR table, when referring to the lowest available level refer to the LEVEL3 table

Show table header on all pages

If the report should not repeat the header row on each new page check the following settings:
– Click on the arrow in the Column Groups properties and select “Advanced Mode”
– Pick the topmost Row Group and make sure the properties are set as listed below

Compiling reports in VS 2015 and newer

Visual Studio 2015 and newer support SQL Reporting 2016

If you create or modify a report in these versions they are automatically updated to the latest available version. You cannot copy the .rdl file directly from the project folder into Vision

Right click the project and select properties

In the properties dialog, pick “SQL Server 2008 R2, 2012 or 2014”

Build your report project and use the .rdl files in bin\debug or bin\release

Troubleshooting

Turning Off Backend Processing of the MainTable Tablix: if you are trouble shooting and want to make sure that Vision is not interfering with your report, set the custom property SkipProcessing on the MainTable element to “Y”

Additional Resources

Links and Downloads

All demos use the VisionDemo76 database which can be downloaded from the Deltek Support Site