Creating a Report

To create a new report from scratch, start by making sure the relevant Cubix model is open and open a new blank workbook.

Cubix includes a number of Excel based functions that can be used to retrieve data from the database into cells on the report template.

The most widely used of these is the Get Cell function which retrieves values. There are also functions that retrieve names and descriptions, set details and member attributes. These functions van be used in conjunction with Excel cell references and other formulae to create very specific yet flexible reports.

Get Cell

The Get Cell function is the most widely used function. It allows any number in your model to be accessed on your spreadsheet.
An example of the syntax for the get cell function is as follows:

=TCM_GCV8("Main","France","HISTORY","LocalCurrency","Input","Jan","PL.10020","Total","Value")

The above rule will return from the Main table the monthly Value of the Total product of variable PL.10020 in period January and Input view and Local Currency of Version History for Entity France.

This formula may appear complex at first. Remember, however, that Cubix is a multi-dimensional model and requires a reference to each dimension to get the appropriate value. You might want to think of the formula as a series of coordinates that point to the specific value you are retrieving.

To assist in writing these rules a wizard can be accessed by double clicking on any blank cell in your spreadsheet whilst Cubix is running.

Instead of writing each of the dimension members in the function, they can be selected and the resulting formula will be entered into the chosen cell on the spreadsheet.

Use Index Values in Formula displays the resulting formula as a series of numbers that relate to the position of each dimension member in the underlying Cubix model structure. Un-tick this to display the formula in a more friendly text format.

The Get Cell window can also be opened from the Cubix menu ribbon by clicking on in the Reporting group.

*Each of the dimensions in a Cubix model is initially defined as a list of items. As part of that definition, each item is given a unique name, such as an account number or company code, and a description (or long name). The descriptions do not need to be unique. Cubix also gives each item in the list a number. This index number corresponds to the position of the item in the list and it can be used to reference dimension members alongside or instead of the item’s name.
However, you need to be aware that Cubix starts counting at zero. So the first item in a dimension has an index number of 0, the second item has an index number of 1 and so on.

!Index numbers can be useful when designing reports as they can help make reports very flexible, however, they should be used with care, because if the underlying model structures changes, the index numbers for dimension members might change. What was previously item number 11 in a dimension might now turn out to be item number 16. Any references to index number 11 may return incorrect values.