Cube views provide a powerful and dynamic way to query data within OneStream. Cube views are setup by selecting dimension members on a cube view’s POV, rows, and columns to govern how data will be displayed to the user when a cube view is executed. Users can view data in a variety of formats for consumption from the standard online data grid view, to an exported Excel spreadsheet, or a PDF report. Each one of these options provides designers the ability to customize the formatting and look and feel to meet their reporting needs. Understanding cube views and the features they offer is an important step to fully leverage the full reporting capabilities of OneStream.
Within cube views, there are a variety of techniques to query the data for the desired reporting needs. One example is the GetDataCell function which allows the cube view designer to accomplish “on-the-fly” calculations where the results are not stored within the system but executed at runtime. Any type of math can be performed by referencing the appropriate member filters and operators within the GetDataCell on a row or column of a cube view. However, there are some limitations when the situation arises that the intersection of a cube view’s data set has a GetDataCell function on both the row and column. This issue can be resolved by using overrides to calculate the specific intersection on the cube view where the GetDataCell collision exists.
To illustrate this issue and its resolution, let us review a sample cube view. Below is a simple cube view with two rows referencing accounts and two columns referencing entities.
In the cube view designer, a user can add a new column that which provides the total for each row. Putting the following GetDataCell function in the column provides the total for each account for both entities.
The result of the column GetDataCell function.
In the cube view designer, another row can be added to provide the total for each column. Putting the following GetDataCell function in the row generates a total for each entity for both accounts.
The result of the row GetDataCell function.
As one can observe, the cell below where the two GetDataCell functions intersect does not display or calculate properly. This is the result of a GetDataCell collision from the row and column not calculating with the right logic/sequence.
To resolve this issue, column/row overrides are leveraged. Below is an example of how to resolve this issue.
- Click on the row or column where the collision occurs
- With the selected row, select the “Column Overrides” tab
- Place the name of the column to override in the “Column Range” line
- In the member filter line, type the member scripts of each combination of account and entity to add for this specific cell
- Save and run the cube view
With this update, the collision above is resolved and the cell is calculating the totals properly.
In summary, cube views provide the main data query mechanism within OneStream and offer a lot flexibility in how to query the data and how data is returned as the result data set in OneStream. On occasion, the basic setup of a cube view may require adjustments to query data in a more dynamic manner. Functions like GetDataCell allow designers to write very specific query data sets that differ from the standard data cells rendered in a cube view row/column intersection. Where GetDataCell functions intersect, there can be “collisions” of data and result data sets rendered that are not logical. Utilizing overrides allow the designer to calculate specific cell values within a cube view so proper results are generated.
To learn more about the reporting capabilities of OneStream visit their website below by clicking the link or scanning the QR code with your smartphone’s camera app.
https://www.onestreamsoftware.com/platform/financial-reporting-software
