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: Watch the instruction video.
To set up the Finance Report you need to have account mapping structure and a dimension value with a dual value.
- Load the FinanceReport_template.xlsx with your mapping accounts (use our subroutine)
- Drag in the extension object.
- Add the dimensions for the report row
- 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.
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) 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.
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])
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
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 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.