How to Read Data using Groovy Data Grid Definition Builders

Unlocking the Power of Groovy: Read Data with Data Grid Definition Builders

When a groovy script is called from a web form, we can iterate over the data grid and read the data that was on the form. But what if we want to read data from a different intersection, or we want to read the data from the cube even if the script is called directly and not from a web form.

The typical use case is to read data from a different cube or different intersection to pick up data that is required for driving calculations, or picking up assumptions that may be necessary for validations.

If you’ve built a SmartView grid or web forms, you should be able to build data grids in Groovy easily. You define the POVs, columns and rows to set up your data grid.

There are two builders that the API provides:

1. DataGridDefinitionBuilder
2. FlexibleDataGridDefinitionBuilder

Data Grid Definition Builder

First we had the Data Grid Definition Builder. The DataGridDefinitionBuilder is the class used to read data from the cube.

First we create the DataGridDefinitionBuilder object

Cube cube = operation.application.getCube("Plan1")
DataGridDefinitionBuilder builder = cube.dataGridDefinitionBuilder()        

With the class object, we then define the grid against which we would be saving the data. This requires, the POV, the rows and the columns.

Creating the POV requires two parameters when calling the addPov() function. The first is the list of dimension names and the second parameter is a list of member names corresponding to the dimensions in the first list. It goes without saying that the order must be the same in both lists. One thing that is important to note is that while the first is a list of dimension names, the second parameter is a list of lists. Although the POV can only have one member for each dimension, the parameter is still a list of lists, which I am guessing is to keep it similar to the addColumn and addRow functions.

Setting up the columns requires two parameters just like the addPov() function, a list of dimensions and a list of lists. The second is a list of the lists of members associated to those dimensions. The difference from the POV is that multiple members can be added to each dimension.

I have used one dimension (Account) in my example. However, if you needed to add two dimensions for columns you would just call the addColumn function once with a list containing all dimensions, followed by a list of lists containing one or more members for each dimension:

builder.addColumn(['Years', 'Period’],
[['FY23', 'FY24'],['Lvl0Descendants(YearTotal)']])        

This is how you would define the columns on a web form.

When defining the rows, you do it in exactly the same way as the columns.

builder.addPov(['Years', 'Scenario', 'Currency', 'Period', 'Version', 'Entity'], [['FY16'], ['Current'], ['Local'], ['BegBalance'], ['BU Version_1'], ['No Entity']])
builder.addColumn(['Account'], [ ['Min Salary', 'Max Salary', 'Min Bonus', 'Max Bonus'] ])
builder.addRow(['Grades'], [ ['ILvl0Descendants("Grades")'] ])        

As I mentioned earlier, you define the columns (as well as rows) how you would have done for one column or row on a web form. As you would on a web form, you can define / call the addColumn and addRow methods multiple times.

One important thing to note is that the setPov, addColumn and addRow functions all take lists of Strings. When passing the parameters you’ll need to ensure that it is not a list of Member objects.

Now all that remains to be done is to build the grid.

// Load a data grid from the specified grid definition and cube
cube.loadGrid(builder.build(), false).withCloseable { grid ->
  // grid operations go here
}        

You can get an iterator on the grid inside the loadGrid call and use that to iterate over the grid.

Flexible Data Grid Definition Builder

The Flexible Data Grid Definition Builder was introduced subsequently to simplify building the grids to read data.

First we create the FlexibleDataGridDefinitionBuilder object

Cube cube = operation.application.getCube("Plan2")
def builder = cube.flexibleDataGridDefinitionBuilder()        

With the class object, we then define the grid against which we would be saving the data. This requires, the POV, the rows and the columns.

The POV can be created with two calls to setPovDimensions and setPov each. The setPovDimensions parameters are the dimension names and the setPov parameters are the member names corresponding to the dimensions. It goes without saying that the order must be the same in both function calls. Both functions simply takes strings as varargs so this does not need to be a list of strings. One thing to note is that the call to setPovDimensions is purely optional as the builder has the capability to create the grid simply from the member names in setPov function call.

Setting up the columns and rows is very similar. There are setColumnDimensions and setRowDimensions functions that are purely optional. addColumn and addRow functions take parameters of strings that are member names or member functions

builder.setPovDimensions('Scenario', 'Currency', 'Period', 'Version', 'Years')
builder.setPov('Actual', 'USD', 'BegBalance', 'BU Version_1', 'FY22')

builder.setColumnDimensions('Employee')
builder.addColumn('ILvl0Descendants(Employee)')

builder.setRowDimensions('Entity', 'Account')
builder.addRow('ILvl0Descendants(USA)', 'Price', 'Units Sold', 'ILvl0Descendants(Alt Account)').setExclude('[Alt Account].[Units Sold]').setExcludeAll('[Alt Account].[Price]')        

If we had to build the grid without the setPovDimensions, setColumnDimensions and setRowDimensions, we can simply call the setPov, addColumn and addRow functions

builder.setPov('Actual', 'USD', 'BegBalance', 'BU Version_1', 'FY22')
builder.addColumn('ILvl0Descendants(Employee)')
builder.addRow('ILvl0Descendants(USA)', 'Price', 'Units Sold', 'ILvl0Descendants(Alt Account)').setExclude('[Alt Account].[Units Sold]').setExcludeAll('[Alt Account].[Price]')        

The setPovDimensions, setPov, setColumnDimensions, addColumn, setRowDimensions and addRow all take Object as the parameters so you can pass a String as the member name or an object of type Member.

Now we build the grid and we can iterate over the cells.

// Load a data grid from the specified grid definition and cube
cube.loadGrid(builder.build(), false).withCloseable { grid ->
 // grid operations go here
}        

You can get an iterator on the grid inside the loadGrid call and use that to iterate over the grid.

I personally use the FlexibleDataGridDefinitionBuilder a lot more simply because it is very flexible to construct a data grid with fewer calls and simpler code.