Vizlib Finance Report Guides - Configuration and Templates

TABLE OF CONTENTS

• Using the Excel template
• Not using the Excel template
• Load script (Sub routine) 
• Add Calculation row 

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 section: "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)

Back to Contents

Not Using the Excel Template

In order to get the extension to work properly the report row (the first dimension) need 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 (se 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. 

Back to Contents

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.

Back to Contents

Add Calculation Row

To add a Calculation row you need to add a calculation to the excel template. In our example measure we have 3 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.

Back to Contents