Advanced Techniques

The MODLR excel add-in allows for implementation of advanced reporting techniques that allow for a more custom and smooth report experience.

Aliases

The use of MODLR aliases allows for a more custom and easier to read report. You can use aliases by creating a column that uses the AliasGet function to grab the alias of an account code that is being used for a CubeGet, then hiding the account code and showing the alias instead.

Sending Data

The MODLR excel add-in allows for sending of data into cubes through the CubeSend function. The CubeSend function can be used by providing the value to be sent, the application to send it through, the cube to send the value into, an intersection with an element for each dimension in the cube.

In the example above we are sending the value 4284.64 through the Performance Management application into the General Ledger Cube. The General Ledger cube has the dimension Scenario, Year, Month, Department, and Measures. The example above sends the value into the instersection of Scenario: Budget, Year: 2025, Month: July, Department: Dep1, Measures: Revenue.

This formula can then be copy and pasted across the table and then refresh the worksheet to quickly upload entire sheets of data into MODLR cubes.

Practice safe sends

The MODLR excel add-in will prompt for confirmation when a CubeSend is about to be performed as a part of a refresh. This helps prevent the accidental sending of data. However to provide another layer of safety the CubeSend formula can be put within an IF function. This will allow the CubeSends to be turned on and off. Turning off the CubeSend functions may also be desirable when retrieving data frequently as this will both remove the confirmation prompt and reduce the processing of the refresh.

In the example above, the IF function checks the value of a referenced cell, when the cell is TRUE, the CubeSend will be performed during a refresh. When the referenced cell is a value other than TRUE, cells with CubeSend will be 0 and will not send values during a refresh.

It is recommended to use this technique whenever using a CubeSend function, and always setting the referenced cell to FALSE when saving the file. This will help prevent the accidental sending of data when opening a file, which may be harmful when checking old archived and outdated versions of budgets for example.

Dynamic data validations

In the basic-usage section we discussed having one excel sheet act as multiple versions of a report by using a referenced cell as an element for a dimension. This referenced cell can be a validation of a dynamic list of elements being pulled directly from the dimension in MODLR.

First use the ChildCount function to find the amount of elements in the hierarchy of choice. This will help limit the amount of processing needed to be done.

Next create an index column and use this as the index in a ChildGet function. Limit the function to only retrieve the child element if the index is under the count of children.

AliasGet can then be used if the elements are stored as codes. Using an IF function to check if the cell is empty will stop unecessary processing on elements that do not exist.

The list of elements or aliases can then be used for validation, providing a dynamic list based on the dimension from MODLR that will grow and shrink with the size of the dimension.

Show us what you've got

Got some tips and tricks you for the MODLR excel add-in you can share? You can show them off in the MODLR forums.