How to use the addDimensionToColumns(), addDimensionToRows(), getDimensionsOnColumns(), and getDimensionsOnRows with SAP Analytics Designer
In this blog we will be explaining how to use:
- addDimensionToColumns() adds to dimension to the Columns axis, at the specifies position, If no position is specified, then the dimension is added as the last dimension of the Columns axis.
- addDimensionToRows() adds to dimension to the Rows axis, at the specifies position, If no position is specified, then the dimension is added as the last dimension of the Row axis.
- getDimensionsOnColumns() returns the dimensions on the Column axis
- getDimensionsOnRows() returns the dimensions on the Row axis
Before we get into the technical part of adding and removing dimensions from rows and columns, I will start with an brief overall explanation about the EPM style report so you understand some of the functionalities and connect the dots quicker.
The EPM Report Developer Application is a self service reporting application designed to enable end users to customize their financial reports and bookmark them privately and publicly. By doing so, organizational users can access the bookmarks and view the report in the required structure and details without any further action.
The InsightCubes Cloud Consolidation – EPM Report Developer Dashboard includes a global filtering pane at the top for Entity, Account, Flow, Audit Trail, Scope, Intercompany, Currency, and Version; allowing the user to detail their selection through a popup filter option for each dimension based on dimension Hierarchy, Properties, the dimension location on the report with the option of removing it from the report and a member selection pane.
The filter pane also includes a Date selector filter, the ability to export the report to Excel, PDF, and CSV; Input on and off optionality, bookmarking selections, report settings, and sort and rank.
On the left side of the EPM Report Developer dashboard you find four checkbox groups. The first checkbox includes a list of all the accounts; users can select a specific set of accounts from the list or specific accounts based on their attributes using the filter button. The second is a checkbox including the dimensions already in the rows of the report with the ability to remove the dimension. The third is a checkbox including the dimensions already in the columns of the report with the ability to remove the dimension. The fourth is the Free Checkbox explained below.
The SAP Analytics Application below allows the users to configure the structure of their report by selecting the specific dimensions that will be used on the columns and the rows, and further refining the report by indicating the set of members based on either Id’s or property values. However in this blog we will only be discussing how to add and remove dimensions from rows and columns, and how to get the dimensions that exist in the rows and columns of the report.
The Free Checkbox includes all the dimension in the planning model, and each dimension can be added to either rows or columns in the report at a click of a button. When you remove a dimension form Columns or Rows Checkbox groups, it is automatically populated again in the Free Checkbox to be added again either to columns or rows.
Add To Column button utilizes the addDimensionToColumns() as shown in the snippet below
Add To Row button utilizes the addDimensionToRows() as shown in the snippet below
On the dashboard we will list the set of dimensions, we create two Global Script Variables “CurrentDimensionColumn” & “CurrentDimensionRow” set them as array of Type.string. We get the dimensions from the table, add them to all the CBG_AllDimensions (hidden Checkbox Group) which in turn runs the setDimesnionCB Script Object.
We create a local variable member called dimCol which will store all of the dimensions in the column on the widget called Table. Then we compile this result and push to the global Script Variable “CurrentDimensionColumn” as shown below
Then we create a local variable member called dimRows which will store all of the dimensions in the Rows on the widget called Table. Then we compile this result and push to the global Script Variable “CurrentDimensionRows” as shown below
Adding all the dimensions to the Checkbox group called CBG_free as shown below
Compile the list of dimensions found in the global script variable called “CurrentDimensionRows” and add them to the Checkbox Group called CBG_Rows and remove them from the Checkbox Group called CBG_Free as shown below
Compile the list of dimensions found in the global script variable called “CurrentDimensionColumn” and add them to the Checkbox Group called CBG_Columns and remove them from the Checkbox Group called CBG_Free as shown below