Vizlib Finance Report Guides - Configuration and Templates

This topic contains the following sections:

Using the Excel Template

Note: Using the template is the recommended approach but the extension will work without it too given the right configuration. See Not Using the Vizlib Excel Template.

Before starting, please watch the instruction video here.

To set up the Finance Report you need to have account mapping structure and a dimension value with a dual value.

  1. Load the FinanceReport_template.xlsx with your mapping accounts (use our subroutine)

  2. Drag in the extension object.

  3. Add the dimensions for the report row

  4. Add a measure (copy the expression in our example app to get started)

Excel Template Description

In the file FinanceReport_template.xlsx there are columns for setting up the account mapping and the formatting of the report rows. This need to be done to get the extension to work properly. Make sure you understand the different columns and how the extension and expressions use them.

Finance Template

Row Types (override option will override these ranges)

Not Using the Excel Template

In order to get the extension to work properly, the report row (the first dimension) needs to have a dual value. I.e both a text  value and a number. If you are using multiple alternative dimensions on the report row, each of these has to have the dual number setup. Our Excel template and subroutines create this automatically but if you are not using it you need to create the dual value for your dimension.

RXXSE Format

The number that needs to be in the dimension is the following:

(Rownumber * 100000) + (ExcelExportFormat * 1000) +(StyleNum * 10) + [Expandable]

Example how to code your own script for the format RXXSSE (see above)

DUAL(MyReportRow, (Rownumber * 100000) + (ExportFormat * 1000)+ (StyleNum * 10) + [Expandable])

Excel Format

Use the Excel-format option only if you are not happy with the default export result.

Export number format is a pipe(|) separated string for each measure column

# ##0;|# ##0;[Red](# ##0)|0%;[Red]-0%

Learn more about the excel number formats here.

Load Script (Sub Routine)

You can find the load script for the excel template in the project and in the example application

  • CLSP.FinanceReport.qvs

  • ClimberFinanceReport_example.qvf

The Sub Routine is called clsp_FinanceReport and to be able to use the subroutine, it has to be included before the call statement. Syntax:

Update the table with the file path and name of the excel file:

For other parameters check the Excel file.

Add Calculation Row

To add a Calculation row you need to add a calculation to the excel template. In our example measure, we have three types of calculations

Add calculation 1 for margin calculation. The input calculationinput1 refers to the row number you want to divide.

Add calculation 2 for margin calculation. The input calculationinput1 refers to the row number you want to have in the denominator.

You can add your own calculation numbers and add extra expressions in your measures.