Tips and Tricks for Creating Vision Custom Reports

This is a transcript of my VI–67 session at 2017’s Deltek Insight

Agenda

• Introduction
• On Premise vs. In The Cloud
• Getting with the report template
• Creating the report statement
• Using the selection feature
• Adding custom parameters
• Using the column selection feature
• Using the sorting/grouping feature

Introduction

What do we cover in this session?

• We will create a simple Vision Report that will show you an employee’s billable and nonbillable hours per project, phase and task
• Throughout the session we add all the features to the report that you expect from a built-in Vision report
• Due to time and presentation limitations we cannot go directly into Visual Studio. All code examples are screenshots
• If you want to review the code on your laptops download it from GitHub (https://github.com/mdobler/deltekinsight2017 in the VI68 folder)

On Premise vs. In the Cloud

Adding reports to an on-premise installation

• Reports must be copied to a specific folder on the Vision Application Server
• Usually C:\Program Files (x86)\Deltek\Vision\Reports\Custom\<Info Center> or C:\Program Files (x86)\Deltek\Vision\Reports\Custom\My Reports\<Info Center>
• 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)

Activating SaaS features

• Start WebLink
• Pick the correct database
• check SaaS/Hosted Instance
• check Allow RDL files to be uploaded in Report Administration
• Save and restart Vision

Getting started with the report template

Creating the Visual Studio Project

• To create Vision 7.4 (or higher) compatible reports (SSRS2010) you must use Visual Studio 2012 or higher
• You will need the “Microsoft SQL Server Data Tools – Business Intelligence for Visual Studio” add in.
• 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

Template placeholders

• Body_ReportName Text Box: Vision uses any text displayed here as “Report Name” in the report list
• MainTable Tablix:This is where the result of the SQL Statement will be rendered. The table comes with a predefined autogrouping column
• pagefooter_FooterText Text:Displays company name, etc.…
• pagefooter_Number Text Box:Displays Page X of Y information

Default parameters

Vision provides four default parameters with values when the report is executed through the application. You can use these parameters in your SQL SELECT statement

Parameter Name	Data Type	Description
ActivePeriod	Number	This parameter is used to pass on the currently selected period in Vision
ActiveCompany	Text	This parameter is used to pass on the currently selected company in Vision (if Multicompany is activated); otherwise an empty value is submitted
HeadingStartDate	Date	The start date of the current period
HeadingEndDate	Date	The end date of the current period

Custom Properties

• Vision stores vital report information in the report’s Custom Properties
• To modify, go to the report’s property box, locate the Custom Properties entry and click on …
• Modify the values accordingly in the Custom Properties Dialog

Default Custom Properties

Name	Sample Value	Description
ReportType	Deltek Insight Sample	This is the name of the report used in Vision. Should match the name in the layout
ResourceFileName	VisionTemplatePortrait	Can be used to create multi-language reports
ReportVersion	7.4.INSIGHT.01	Your version number of this report. Will be displayed in the Report Administration screen
LookupType	Employee	Defines which lookup dialog and options will be shown for the Selection window
GroupingType	Employee	Defines which items are available for Auto Grouping. Must match Lookup Type
AutoGroupingQuery	Y	Allows Vision to create  custom SQL code for grouping data
OptionForm	Deltek.Vision.ReportClient.VisionReportDialog	Specifies which Options Dialog to use. Leave it with the default value
HelpFile		Define your own help file for your report (empty by default)
UseColumnSelection	Y	Shows the Columns tab in the Options dialog and lets you show/hide columns in your report
AllowUserDefinedFields	Employees	Allows you to add any User Defined Info Center fields to the report output
AllowCalcFields	Y	Allows you to use calculated fields in the report
Modules		You can specify which modules need to be present for this report (e.g. Acct;Billing;ProjCtrl;)

SQL Placeholders

• Vision uses placeholders to inject special SQL snippets into your selection
• To integrate with overall reporting functionality you must use these placeholders accordingly

Name	Description	Placement
/***GROUP COLUMNS***/	Placeholder for the grouping fields selected in the Options dialog	Right after SELECT keyword
/***CUSTOM COLUMNS***/	Placeholder for the custom fields selected in the Columns tab	After SELECT keyword or Group Col
/***GROUP JOIN***/	Placeholder for any additional JOIN information needed for the grouping	At the end of the FROM clause
/***WHERE CLAUSE***/	Placeholder for the filter selection created in the Selection dialog. ONLY USE if there is no default where clause in your statement	Right after the FROM clause if there is no WHERE clause
/***EXTEND WHERE CLAUSE***/	Placeholder for the filter selection created in the Selection dialog. ONLY USE if there is already a default where clause in your statement	At the end of the WHERE clause
/***GROUP CLAUSE***/	Placeholder for the grouping selected in the Options dialog. ONLY USE if there is no default group by clause in your statement	Right after the WHERE clause if there is no GROUP clause
/***EXTEND GROUP CLAUSE***/	Placeholder for the grouping selected in the Options dialog. ONLY USE if there is already a default group by clause in your statement	Right after the GROUP BY keyword
/***ORDER CLAUSE***/	Placeholder for the sorting selected in the Options dialog. ONLY USE if there is no default order by clause in your statement	Right after the GROUP clause if there is no ORDER clause
/***EXTEND ORDER CLAUSE***/	Placeholder for the sorting selected in the Options dialog. ONLY USE if there is already a default order by clause in your statement	Right after the ORDER BY keyword

Creating the report statement

Sample Report – Phase 1

• The sample report will show us the following information:
• Employee (ID and Name)
• Project, Phase and Task Information
• The total hours for each employee, project, phase and task
• The billable hours of this section, based on the Non-billable Labor Code setup in the Billing Configuration
• The non-billable hours based on the setup
• The report will be an Employee report
• Phase 1 will have no custom options activated and will simply execute the statement

Phase 1 Statement

DECLARE @ActiveCompany varchar( 10 ) = ' '
DECLARE @ActivePeriod int = 200506
SELECT
    EM.Employee , EM.FirstName , EM.MiddleName , EM.LastName ,
    PR.WBS1 , PR.Name AS ProjectName ,
    LEVEL2.WBS2 , ISNULL( LEVEL2.Name , '' )AS WBS2Name ,
    LEVEL3.WBS3 , ISNULL( LEVEL3.Name , '' )AS WBS3Name , SUM( LD.RegHrs   LD.OvtHrs   LD.SpecialOvtHrs )AS TotalHours ,
    SUM( CASE WHEN LD.LaborCode LIKE CFGBillMain.NonBillLaborCode THEN 0 ELSE LD.RegHrs   LD.OvtHrs   LD.SpecialOvtHrs END )AS BillableHours ,
    SUM( CASE WHEN LD.LaborCode LIKE CFGBillMain.NonBillLaborCode THEN LD.RegHrs   LD.OvtHrs   LD.SpecialOvtHrs ELSE 0 END )AS NonBillableHours
FROM
    EM
    JOIN EmployeeCustomTabFields ON EM.Employee = EmployeeCustomTabFields.Employee
    JOIN LD ON EM.Employee = LD.Employee
    JOIN PR ON LD.WBS1 = PR.WBS1 AND PR.WBS2 = ' ' AND PR.WBS3 = ' '
    LEFT JOIN PR LEVEL2 ON LD.WBS1 = LEVEL2.WBS1 AND LD.WBS2 = LEVEL2.WBS2 AND LEVEL2.WBS3 = ' ‘
    LEFT JOIN PR LEVEL3 ON LD.WBS1 = LEVEL3.WBS1 AND LD.WBS2 = LEVEL3.WBS2 AND LD.WBS3 = LEVEL3.WBS3
    LEFT JOIN CFGBillMain ON CFGBillMain.Company = @ActiveCompany
    CROSS APPLY CFGSystem
    CROSS APPLY CFGFormat
WHERE
    ( CFGSystem.MulticompanyEnabled = 'N' OR SUBSTRING( EM.Org , CFGFormat.Org1Start , Org1Length ) = @ActiveCompany )
    AND LD.Period = @ActivePeriod
GROUP BY
    EM.Employee , EM.FirstName , EM.MiddleName , EM.LastName ,
    PR.WBS1 , PR.Name , LEVEL2.WBS2 , ISNULL( LEVEL2.Name , '' ) , LEVEL3.WBS3 , ISNULL( LEVEL3.Name , '' )
ORDER BY
    EM.Employee , PR.WBS1 , LEVEL2.WBS2 , LEVEL3.WBS3

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.
• For now, copy the complete SQL Statement (excluding the parameter declaration) into the Query field. Click OK.
• 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

PR LEVEL3
INNER JOIN PR LEVEL2 ON
    LEVEL2.WBS1 = LEVEL3.WBS1 AND LEVEL2.WBS2 = LEVEL3.WBS2 AND LEVEL2.WBS3 = ''
INNER JOIN PR ON
    PR.WBS1 = LEVEL3.WBS1 AND PR.WBS2 = '' AND PR.WBS3 = ''

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

• You can find all source code on GitHub:https://github.com/mdobler/insight2017
• Contact me on LinkedIn:https://www.linkedin.com/in/mikedobler/
• Check out my blog for related topics:https://steepvalley.net