Vizlib Community

Submit a ticket My Tickets
Welcome
Login

Climber Finance Report - Configuration and templates

Using the Climber 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 Climber Excel template". 


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

  1. Read in 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 (look at the example in our example app)



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 Climber 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 a 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


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 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.




V
Vizlib is the author of this solution article.

Did you find it helpful? Yes No

Send feedback
Sorry we couldn't be helpful. Help us improve this article with your feedback.