Deltek Vision: Custom Reporting

Did your finance department ever approach you to create them that one specific report that’s missing? Did you ever wonder if you could write a report that would take advantage of Deltek Vision’s ® reporting capabilities and allow you to create selections, set up your own grouping, show and hide columns and create your own favourites? Or did you ever wanted to create invoices with a bit more bling than the plain standard ones?
Then the following chapters will help you doing just that. In the first article we will go through a step-by-step guide on how to create your own custom report.

General Vision Reporting Housekeeping Tasks

In order for you to be able to use these reports you will have to upload them to the Vision application. Depending on where you run Vision this can be slightly different.

Cloud Installation

If you run Vision on Deltek’s cloud environment you can upload the custom report files directly from the Report Administration screen. For you to be able to do that you have to follow a specific naming convention for your reports: <10-digit Deltek client number>_<your report name.rdl, for instance 0123456789_MyCustomReport.rdl.
To find your Deltek client number open the Module Activation screen in the Configuration menu.

To upload your custom report into the Vision application go to
1. Utilities –
2. Report Administration
3. click on the Upload button [1]
4. when the file is uploaded, select the proper location (Project, Vendor, etc…) [2]
5. click Save

On Premise Installation

While you do not have to adhere to the naming conventions used in the cloud I suggest you do anyway. This will make it much easier to manage and upload files to the on premise version as well.
To upload your custom report into the Vision application do the following:

1. copy your custom report file to the specific report folder on the Vision application server.
   This is usually C:\Program Files\Deltek\Vision\Reports\Custom\<Info Center Name> or
   C:\Program Files\Deltek\Vision\Reports\Custom\My Reports\<Info Center Name>
2. <Info Center Name> can be Project, Vendor, Employee, etc…
3. go to Utilities
4. go to Report Administration
5. Select Type Custom and enter a portion of the report name (ending with an asterisk,
   e.g. 0000000000*) in the report name. If you leave this empty the administration tool will try to
   upload all reports in the Custom folder.
6. Click Upload Reports

You can simulate a cloud environment for your on-premise installation that gives you the same user experience as described in the Cloud Installation by going into your weblink application and activate the SaaS/Hosted Instance and Allow RDL files to be uploaded in Report Administration features.

Sample

We will begin with a very simple report that just executes a statement and displays the data to a fully integrated Vision custom report where the end user can use your report the same
way they use the built-in ones.
In our sample we will produce a vendor report that will show each vendor’s invoice amounts for a fiscal or calendar year with each month as a column.

Step 0: Preparing the SQL Statement

The first step to a good report is a good SQL statement. In this step I will introduce you to some basic SQL techniques to produce the sample vendor report.
One way to break out the indiviual monthly totals and creates something similar to a pivot table is to use the case statement. In the SQL below the case statement checks for the specific month of the transaction date. So for January the month(LedgerAP.TransDate) calculates the month number of the date and compares it to the value 1. If this evaluates to true the LedgerAP.Amount is added to the column’s running total, otherwise a 0 value is generated.
In the join section we add the vendor table (VE) and the Vendor Custom Table (VendorCustomTabFields). And then all account payable financial information is added (left join LedgerAP)
[sql]
select
VE.Vendor,
VE.Name,
sum(case when month(LedgerAP.TransDate) = 1
then LedgerAP.Amount
else 0 end) as month01,
sum(case when month(LedgerAP.TransDate) = 2
then LedgerAP.Amount
else 0 end) as month02,
sum(case when month(LedgerAP.TransDate) = 3
then LedgerAP.Amount
else 0 end) as month03,
sum(case when month(LedgerAP.TransDate) = 4
then LedgerAP.Amount
else 0 end) as month04,
sum(case when month(LedgerAP.TransDate) = 5
then LedgerAP.Amount
else 0 end) as month05,
sum(case when month(LedgerAP.TransDate) = 6
then LedgerAP.Amount
else 0 end) as month06,
sum(case when month(LedgerAP.TransDate) = 7
then LedgerAP.Amount
else 0 end) as month07,
sum(case when month(LedgerAP.TransDate) = 8
then LedgerAP.Amount
else 0 end) as month08,
sum(case when month(LedgerAP.TransDate) = 9
then LedgerAP.Amount
else 0 end) as month09,
sum(case when month(LedgerAP.TransDate) = 10
then LedgerAP.Amount
else 0 end) as month10,
sum(case when month(LedgerAP.TransDate) = 11
then LedgerAP.Amount
else 0 end) as month11,
sum(case when month(LedgerAP.TransDate) = 12
then LedgerAP.Amount
else 0 end) as month12,
sum(LedgerAP.Amount) as total
from
VE
join VendorCustomTabFields on VE.Vendor = VendorCustomTabFields.Vendor
/add financial information/
left join LedgerAP on VE.Vendor = LedgerAP.Vendor
where
LedgerAP.TransType in (‘AP’, ‘CV’) and
year(LedgerAP.TransDate ) = 2005
group by
VE.Vendor,
VE.Name
order by
VE.Vendor,
VE.Name
[/sql]
We only pick transactions of type ‘AP’ (Accounts Payable) and ‘CV’ (AP Disbursements). If you want to run the statements for paid amounts you would pick type ‘PP’ (AP Payments).

Step 1: Let’s Start with A Simple Report

Once we’re happy with the outcome of our SQL statement we can start working on our custom report.
The Deltek Vision installation comes with a handful of report templates that you must use to get the full application support for your custom report. The templates are located in the Vision installation directory on your Vision Application Server (usually here: C:\Program Files\Deltek\Vision\Reports\Templates).
It is a good idea to copy all templates in that folder onto your development machine for later reference. Also copy the DefaultDataSource.rds from the ...\Reports\Standard\ folder. We will need this file when creating our Vision Sample Report.

Important Information for Visual Studio 2015 users
Vision 7.6 uses SSRS2010 as its reporting engine. By default Visual Studio creates SSRS2016 reports.
After creating your project please go the the project’s properties and set the TargetServerVersion to
SQL Server 2008 R2, 2012 or 2014. Otherwise you will receive errors when you try to upload your report to Vision.

To get started with your report follow these steps:
1. Start Visual Studio and create a new Report Server Project (under Business Intelligence – Reporting Services).
If you don’t have this option in your Visual Studio application you are missing the “Microsoft SQL Server Data Tools – Business Intelligence” add-on. You can download it for free from here
2. copy the VisionTemplateLandscape.rdl and DefaultDataSource.rds files from your developer location
to the project’s folder.
3. Right-click on Shared Data Sources, select Add..., Existing Item and pick the DefaultDataSource file.
4. Right-click on Reports, select Add..., Existing Item and pick the VisionTemplate file.
5. Rename the report to 0000000000_CustomVendorReport
5. Double-click on the DefaultDataSet and change the connection string to connect to your database.
5. Open the report file, go to the Data Sets in the Report Data View, right-click the ReportDataset and select Dataset Properties
6. Copy your SQL Statement into the Query text box (ignore and overwrite whatever is in the textbox for now).
8. Now you should see your field names listed under the ReportDataset
9. There is a Tablix called MainTable in the report template.
11. Drag and drop each of the ReportDataset fields to the end of the MainTable tablix
12. Set the width of all amount fields to 0.75 inch
13. Set the Format of all amount fields to “N2” and right align the values
14. Set the width of the Vendor field to 0.75 inch and the width of the Name field to 1.5 inch
15. Replace the header text “month01…monthXX” with proper month names (January – December)
16. Replace the [LabelLandscapeTemplate] text with “Custom Vendor Report”
17. Click into a gray area of the report, go to the properties and open the Custom Properties dialog
18. set the “ReportType” property to “Custom Vendor Report”
19. Save and preview your report.

Now that we have a first draft of our report we can upload it to Vision (as described in a previous section) and preview it in the application. Upload the file, go to Reporting – Vendor and refresh the grid.
Your “Custom Vendor Report” should now show up in the list. As you can see when you run the report it does not allow you to use the options and selection features of the default Vision reports. We will dive into this in the next step.

Important Information for Visual Studio 2015 users
You cannot use the rdl file in the project’s folder. You will have to rebuild the solution and then use the rdl file created in bin\debug for upload. Otherwise you will get an error.

Step 2: Adding Selection Features

Now that we have our initial report up and running it is time to dive into some of the advanced features of Vision Reporting and how you can use these features in your custom report.
We start with integrating the selection feature into our report. This will allow your users to pick and choose the vendors to be printed on the report. For this we need to integrate the placeholders we removed from the query text in step 1 and also look at the custom properties in more detail.
First up are the placeholders that can be integrated into your select statement.

Placeholder Text	Description	Placement in SQL Statement
/***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
/***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

In this section we are only going to use the /***EXTEND WHERE CLAUSE***/. We simply add the placeholder after the last where clause like this:
[sql]
select
VE.Vendor,
VE.Name,
sum(case when month(LedgerAP.TransDate) = 1
then LedgerAP.Amount
else 0 end) as month01,
sum(case when month(LedgerAP.TransDate) = 2
then LedgerAP.Amount
else 0 end) as month02,
sum(case when month(LedgerAP.TransDate) = 3
then LedgerAP.Amount
else 0 end) as month03,
sum(case when month(LedgerAP.TransDate) = 4
then LedgerAP.Amount
else 0 end) as month04,
sum(case when month(LedgerAP.TransDate) = 5
then LedgerAP.Amount
else 0 end) as month05,
sum(case when month(LedgerAP.TransDate) = 6
then LedgerAP.Amount
else 0 end) as month06,
sum(case when month(LedgerAP.TransDate) = 7
then LedgerAP.Amount
else 0 end) as month07,
sum(case when month(LedgerAP.TransDate) = 8
then LedgerAP.Amount
else 0 end) as month08,
sum(case when month(LedgerAP.TransDate) = 9
then LedgerAP.Amount
else 0 end) as month09,
sum(case when month(LedgerAP.TransDate) = 10
then LedgerAP.Amount
else 0 end) as month10,
sum(case when month(LedgerAP.TransDate) = 11
then LedgerAP.Amount
else 0 end) as month11,
sum(case when month(LedgerAP.TransDate) = 12
then LedgerAP.Amount
else 0 end) as month12,
sum(LedgerAP.Amount) as total
from
VE
join VendorCustomTabFields on VE.Vendor = VendorCustomTabFields.Vendor
/add financial information/
left join LedgerAP on VE.Vendor = LedgerAP.Vendor
where
LedgerAP.TransType in (‘AP’, ‘CV’) and
year(LedgerAP.TransDate ) = 2005
/EXTEND WHERE CLAUSE/
group by
VE.Vendor,
VE.Name
order by
VE.Vendor,
VE.Name
[/sql]
Next we have to look at the custom properties of this report. Vision uses these to add additional functionality to your report at run time.

Property Name	Sample Value	Description
ReportType	Custom Vendor Report	This is the name of the report used in Vision. Should match the name in the layout
ResourceFileName	VisionTemplateLandscape	Can be used to create multi-language reports
ReportVersion	7.6.0.1	Your version number of this report. Will be displayed in the Report Administration screen
LookupType	Vendor	Defines which lookup dialog and options will be shown for the Selection window
GroupingType	Vendor	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	Delek.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	Vendor	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;)

Right now we only need to turn on the correct lookup type LookupType: Vendor. This will allow Vision to display the Vendor selection dialog. The application then inserts a where condition into the place holder we set up earlier. The one thing we have to make sure is that the SQL statement does include the main table (VE) and custom field table
(VendorCustomTabFields).
Once you changed these two items you will have to rebuild your solution and then upload the report again to Vision. In my sample I have two different reports with different names but if you continue to work on your report you will have to remove the report first from the Report Administration screen, click Save and then add the report again.

Special Case with Project Reports
In order for a project report to display all three levels of the work breakdown structure you will have to make this code snippet part of your SQL Statement:

select ...
from
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 = ''
...


Step 3: Adding Custom Options

After step 2 we can now limit the number of vendors that are shown on the report. But right now we still have the year that filters the report hardcoded into our statement. We want to change the report that the end user can enter the year he’s looking for in the report options.
We also want to be able to tell the report to filter either by the calendar year or the fiscal year. If the user chooses the fiscal year, we also want the months to be fiscal months not calendar months.
In order to do that we need to add two custom properties to the report:
1. A numeric field that takes a 4 digit year
2. A dropdown that lets the user pick from two options: Fiscal and Calendar Year
First we will add these parameters to the report as @CustYear (of type int) and @CustType (of type text). We expect Vision to send us a number for the year and either C for calendar year or F for fiscal year.
Then we will modify the SQL statement as follows:
[sql]
select
VE.Vendor,
VE.Name,
sum(case when (
case when @CustType = ‘F’
then cast(LedgerAP.Period % 100 as int)
else month(LedgerAP.TransDate) end) = 1
then LedgerAP.Amount
else 0 end) as month01,
sum(case when (
case when @CustType = ‘F’
then cast(LedgerAP.Period % 100 as int)
else month(LedgerAP.TransDate) end) = 2
then LedgerAP.Amount
else 0 end) as month02,
sum(case when (
case when @CustType = ‘F’
then cast(LedgerAP.Period % 100 as int)
else month(LedgerAP.TransDate) end) = 3
then LedgerAP.Amount
else 0 end) as month03,
sum(case when (
case when @CustType = ‘F’
then cast(LedgerAP.Period % 100 as int)
else month(LedgerAP.TransDate) end) = 4
then LedgerAP.Amount
else 0 end) as month04,
sum(case when (
case when @CustType = ‘F’
then cast(LedgerAP.Period % 100 as int)
else month(LedgerAP.TransDate) end) = 5
then LedgerAP.Amount
else 0 end) as month05,
sum(case when (
case when @CustType = ‘F’
then cast(LedgerAP.Period % 100 as int)
else month(LedgerAP.TransDate) end) = 6
then LedgerAP.Amount
else 0 end) as month06,
sum(case when (
case when @CustType = ‘F’
then cast(LedgerAP.Period % 100 as int)
else month(LedgerAP.TransDate) end) = 7
then LedgerAP.Amount
else 0 end) as month07,
sum(case when (
case when @CustType = ‘F’
then cast(LedgerAP.Period % 100 as int)
else month(LedgerAP.TransDate) end) = 8
then LedgerAP.Amount
else 0 end) as month08,
sum(case when (
case when @CustType = ‘F’
then cast(LedgerAP.Period % 100 as int)
else month(LedgerAP.TransDate) end) = 9
then LedgerAP.Amount
else 0 end) as month09,
sum(case when (
case when @CustType = ‘F’
then cast(LedgerAP.Period % 100 as int)
else month(LedgerAP.TransDate) end) = 10
then LedgerAP.Amount
else 0 end) as month10,
sum(case when (
case when @CustType = ‘F’
then cast(LedgerAP.Period % 100 as int)
else month(LedgerAP.TransDate) end) = 11
then LedgerAP.Amount
else 0 end) as month11,
sum(case
when (case when @CustType = ‘F’
then cast(LedgerAP.Period % 100 as int)
else month(LedgerAP.TransDate) end) = 12
then LedgerAP.Amount
else 0 end) as month12,
sum(LedgerAP.Amount) as total
from
VE
join VendorCustomTabFields on VE.Vendor = VendorCustomTabFields.Vendor
/add financial information/
left join LedgerAP on VE.Vendor = LedgerAP.Vendor
where
LedgerAP.TransType in (‘AP’, ‘CV’) and
/check for fiscal or calendar year/
case when @CustType = ‘F’ then cast(LedgerAP.Period / 100 as int) else year(LedgerAP.TransDate) end = @CustYear
/EXTEND WHERE CLAUSE/
group by
VE.Vendor,
VE.Name
order by
VE.Vendor,
VE.Name
[/sql]
After the change go to the Parameters section of the dialog and assign the report parameters to the query parameters as shown in this screenshot:

Then go through the previously described steps to upload the changed report to the Vision application.
Once the report is available in Vision we have to assign the custom parameter defintions to the report so Vision can show them in the Custom Options tab of the report dialog and pass them on to the report engine.
Go to Configuration – General – Custom Report Options and pick your report from the dropdown.Then define the custom report options as follows:

Option Label	Parameter Name	Data Type	Default Value	Display Width	Limit To List	Decimals	Minimum Value	Maximum Value
Year	Year	Numeric		20	Checked		1999	2199
Type	Type	Lookup		50	Checked

Save your changed and then click on the Lookup Lists button. Select the Type column and define the 2 dropdown values as C and F.
Now go to the Vendor reports, select your report and open the options dialog. Set up the custom options and run the report.

Parameter Naming Conventions
In order for Vision to be able to pass on the report parameter values you have to name your parameters CustParameterName in the report but you have to define them without the Cust prefix in the report options. Also make sure that the data types between the report and Vision match.
Numeric are either Integer or Decimal, checkboxes are Text values with either Y or N and references to User Defined Info Centers are Text values.
Design Extension:
If you run the report right now it will show the calendar month names in the header. Depending on your fiscal year setup this might be wrong. In order to fix this the sample shows you an elegant solution:
I defined TWO header columns, one with the month names and one just with Month 01 to Month 12. Then I right-clicked on the row selector and picked Row Visibility. In the dialog box I selected Show or hide based on an expression and entered the following formula for the Hidden value of each header row:
Calendar Month Row: =Parameters!CustType.Value = "F"
Fiscal Month Row: =Parameters!CustType.Value = "C"
This will ensure that based on the parameter that is passed into the report either header row 1 or header row 2 will be hidden and the correct header texts are displayed.

Step 4: Adding Column Selection Features

With the next iteration of our report sample we want to make use of Vision’s built-in ability to show and hide columns so that the end user has the ability to show only specific months of the calendar or fiscal year.
There will be no changes necessary to the underlying SQL statement, these are designer changes only.
To activate this feature for the report, go to the report’s custom properties, look for UseColumnSelection and set it’s value to Y
Then we have to follow very strict naming conventions for each column and cell in the Main report tablix for Vision to be able to apply the column visibility on the fly:

Property	Location	Description	Sample Value
DataElementName	All Rows	All cells in a column have to share the same DataElementName to let Vision identify the related fields	Vendor
Expression	Detail Row	The content or Expression of the Text Box Element in the header will be used to display a heading value in the Options dialog box	Vendor
DocumentMapLabel	Header Row	This is used if no value is set in the Header Content	Vendor
Name	All Rows	Each element name in a report must be unique. The prefix is used by Vision to look up and apply changes to the specific element. The suffix for all cells in a column must be the same	xxxxx_Vendor
Name	Header Row	Must always start with the header_ prefix	header_Vendor
Name	Group Row	Must always start with the group_ prefix	group_Vendor
Name	Custom Group Row	Never use group1, group2 as your group prefix. Always use proper naming	groupMyGroup_Vendor
Name	Detail Row	Must always start with detail_ prefix	detail_Vendor

Once all elements in the table have been properly renamed (yes a very tedious work for large reports) you can also define which columns are shown by default. For this select the header cell of each column, go to the Custom Properties of this cell and add a property with a name of Default and a value of ‘Y’

After uploading your changes to Vision you will now see the Column Selection tab in the options dialog and you can show or hide any of your columns and even save this as a favorite report.

Note:
By default Vision does not show any custom selectable columns when you run the report. So if you end up with an empty report after you upload it to Vision then it’s most likely the missing Default custom property.

Step 5: Adding Grouping Features

One of the most incredible features of the Vision reporting system is that it allows you to sort and group any report by a number of different info center values without having these groupings defined in the report layout. The only thing you need to do is adding a number of placeholders into your SQL Statement and activate the feature in the report’s custom properties.

This sample goes one step further and adds a custom vendor region (as defined in the “User Defined Fields, Grids and Info Centers” blog post) as additional grouping function. That shows you how to add custom grouping in addition to the built-in grouping functionality.
The important thing there is that you follow a specific naming convention for your additional grouping. Please prefix all your new grouping fields with “groupRegion…“ instead of ”group1…”, “group2_”, etc… This would cause an error when running the report in Vision.
Here’s the query that makes up our new report:
[sql]
select
isnull(UDIC_VendorRegion.CustName, ‘(none)’) as Region,
isnull(UDIC_VendorRegion.CustDescription, ‘(none)’) as RegionDescription,
isnull(UDIC_VendorRegion.CustSortOrder, 0) as RegionSortOrder,
/GROUP COLUMNS/
sum(case when (
case when @CustType = ‘F’
then cast(LedgerAP.Period % 100 as int)
else month(LedgerAP.TransDate) end) = 1
then LedgerAP.Amount
else 0 end) as month01,
sum(case when (
case when @CustType = ‘F’
then cast(LedgerAP.Period % 100 as int)
else month(LedgerAP.TransDate) end) = 2
then LedgerAP.Amount
else 0 end) as month02,
sum(case when (
case when @CustType = ‘F’
then cast(LedgerAP.Period % 100 as int)
else month(LedgerAP.TransDate) end) = 3
then LedgerAP.Amount
else 0 end) as month03,
sum(case when (
case when @CustType = ‘F’
then cast(LedgerAP.Period % 100 as int)
else month(LedgerAP.TransDate) end) = 4
then LedgerAP.Amount
else 0 end) as month04,
sum(case when (
case when @CustType = ‘F’
then cast(LedgerAP.Period % 100 as int)
else month(LedgerAP.TransDate) end) = 5
then LedgerAP.Amount
else 0 end) as month05,
sum(case when (
case when @CustType = ‘F’
then cast(LedgerAP.Period % 100 as int)
else month(LedgerAP.TransDate) end) = 6
then LedgerAP.Amount
else 0 end) as month06,
sum(case when (
case when @CustType = ‘F’
then cast(LedgerAP.Period % 100 as int)
else month(LedgerAP.TransDate) end) = 7
then LedgerAP.Amount
else 0 end) as month07,
sum(case when (
case when @CustType = ‘F’
then cast(LedgerAP.Period % 100 as int)
else month(LedgerAP.TransDate) end) = 8
then LedgerAP.Amount
else 0 end) as month08,
sum(case when (
case when @CustType = ‘F’
then cast(LedgerAP.Period % 100 as int)
else month(LedgerAP.TransDate) end) = 9
then LedgerAP.Amount
else 0 end) as month09,
sum(case when (
case when @CustType = ‘F’
then cast(LedgerAP.Period % 100 as int)
else month(LedgerAP.TransDate) end) = 10
then LedgerAP.Amount
else 0 end) as month10,
sum(case when (
case when @CustType = ‘F’
then cast(LedgerAP.Period % 100 as int)
else month(LedgerAP.TransDate) end) = 11
then LedgerAP.Amount
else 0 end) as month11,
sum(case
when (case when @CustType = ‘F’
then cast(LedgerAP.Period % 100 as int)
else month(LedgerAP.TransDate) end) = 12
then LedgerAP.Amount
else 0 end) as month12,
sum(LedgerAP.Amount) as total
from
VE
join VendorCustomTabFields on VE.Vendor = VendorCustomTabFields.Vendor
/add financial information/
left join LedgerAP on VE.Vendor = LedgerAP.Vendor
/add our custom region definition to the query/
left join UDIC_VendorRegion on VendorCustomTabFields.CustVendorRegion = UDIC_VendorRegion.UDIC_UID
/GROUP JOIN/
where
LedgerAP.TransType in (‘AP’, ‘CV’) and
/check for fiscal or calendar year/
case when @CustType = ‘F’ then cast(LedgerAP.Period / 100 as int) else year(LedgerAP.TransDate) end = @CustYear
/EXTEND WHERE CLAUSE/
group by
/EXTEND GROUP CLAUSE/
isnull(UDIC_VendorRegion.CustName, ‘(none)’) ,
isnull(UDIC_VendorRegion.CustDescription, ‘(none)’) ,
isnull(UDIC_VendorRegion.CustSortOrder, 0)
order by
/EXTEND ORDER CLAUSE/
isnull(UDIC_VendorRegion.CustSortOrder, 0),
isnull(UDIC_VendorRegion.CustDescription, ‘(none)’)
[/sql]
Now just add a new grouping to the report, follow the naming conventions for all new fields and add summarized values for all columns.

Now when you upload the report to Vision you can pick and choose any additional column in the “Grouping/Sorting” tab to group the report by. The way this sample is designed the report will always group by region AFTER your custom chosen items but you can simply change that by changing the report design.

Download the Source Code

Go here to download the sample code for this blog post.