Power User - Advanced
- Ada
- Calista Opsomer (Unlicensed)
Welcome to our training materials! We're excited to have you here and hope that this training will provide you with valuable knowledge and skills. Below, you can find the complete table of contents, this will give you an overview of what topics will be covered and in what order. Be sure to check back regularly for any updates, as we make changes or additions to the training materials.
- 1 Working with Subset Editor
- 2 Working with Settings Service
- 3 Working with Attributes
- 4 Table Advanced Options
- 4.1 1. Zero Suppression
- 4.2 2. Subset Selection
- 4.3 3. Context menu options
- 4.3.1 » User defined consolidations: Rollup
- 4.3.2 » Subset Editor Shortcuts
- 4.3.3 » Inserting new column and row
- 4.3.3.1 Adding column
- 4.3.3.2 Column title change
- 4.3.4 » Formulas
- 4.4 4. Cross Drill
- 4.5 5. Spreading
- 4.6 6. Number Format
- 4.7 7. Read only
- 4.8 8. adHoc columns edit
- 4.8.1 » Enabling adHoc columns
- 4.8.2 » Overview of the available options
- 4.8.3 » Configure Default Elements
- 4.8.4 » Exercise
- 4.9 9. Undo the changes
- 5 Charts configuration and edition
- 5.1 1. Waterfall Chart
- 5.1.1 » Drill Down
- 5.1.2 » Y-axis scaling
- 5.1.3 » Add commentary
- 5.1 1. Waterfall Chart
- 6 KPI widget types and configuration
- 7 Advanced TI integration
- 7.1 1. Basic TI setup
- 7.2 2. Advanced TI settings
- 7.2.1 » TI Process configuration using new Advanced Options GUI
- 7.2.1.1 Hide specific parameters
- 7.2.1.2 Add labels
- 7.2.1.3 Disable parameters
- 7.2.1.4 Add default value
- 7.2.1.5 Configure subnm selection
- 7.2.1.6 Change the parameter type to checkbox
- 7.2.1 » TI Process configuration using new Advanced Options GUI
- 7.3 3. Track process status in notification drawer
- 7.4 4. Create TI Process button from Action menu
- 7.5 5. Run on click
- 8 Advanced MDX
- 9 Full MDX view
- 10 User default settings
- 11 Final Exercise
Working with Subset Editor
1. Open Subset editor from different levels of the app
Navigate to Exec Dashboard.
The subset editor is accessible from:
Filter bar
Widget header for every available row or column dimension in each widget
Cube Settings popup
Subset editor shortcut can be also added to the context menu which is useful functionality when editing views.
- Follow the steps
Navigate to Reports / Views - Simple planning template view
Enable the edit mode
Click on the cube setting icon
Select Product dimension
Open the advanced option GUI for the Dimensions Settings
Turn on Enable Subset Editor option
Close the cube settings popup
Save the changes
Right click on any element from Product dimension
Notice the the Subset Editor option is available in the context menu
Navigate back to Exec Dashboard
2. Subset Selection
- Open Subset editor for Overview widget Region dimension directly form its header.
Change subset selection by opening the Subset drop-down list
Click through the available options to see how the displayed elements change
Your choice is immediately visible and you can decide if those are the elements you want to display. The list includes Static and Private subsets.
- Create your own subset
Select Europe and Asia element
Use Keep button to confirm the selection
Click Save icon
In the popup input: my test subset
Click Save button
New Subset will be now available in the Subset drop-down list
Click Apply to see the changes in the Overview widget
Discard the changes
If you want to make additional adjustments it's possible to copy & paste and change the order of the elements.
3. Attribute Selection
Now you will practice Attribute selection.
- Follow the steps
Open Subset Editor for Overview widget Region dimension directly form its header
Open Attribute drop-down list to see all available attributes for selected dimension
Choose Color and Currency
Like in previous case you immediately can see how given attribute will look like if it was applied.
Open Attribute list again
Unselect Code and Description
Click Apply to see the changes in the Overview widget
Click Save & Discard button and Discard changes in the confirmation popup to revert to original configuration.
4. Choose Default Value
Subset editor also allows you to change default values for global filters and fixed values.
- Follow the steps
Open Subset Editor for Region global filter
Current default value is set to: World
- You can easily change that
Go through the elements visible in the dimension and notice that cursor icon appears
If you hover over it, the Select default message will appear
Select Europe as Default
Click Apply
Save the changes and reload the page
Turn on the Edit Mode
Region filter should be displaying Europe element.
You can decide to remove default element by clicking "x" visible next to the selected element.
5. Exercise
In this exercise you will use Subset Editor to change elements displayed in Version dimension.
- Follow the steps
Open Subset Editor for Overview widget directly from its header
Choose Version from the list
Select Actual, Budget and Act_Bud_Var from the list by pressing and holding Ctrl to select multiple items
Click Keep button
Click Apply button
Overview widget is now displaying updated column elements selection.
Open cube settings for Overview widget
Select Version dimension
After applying changes, List Type is now set to MDX and the selected elements are listed in the MDX statement in the textbox
Change List Type back to Subset and choose Default from Subset drop-down list
Close the popup
6. Rollup
Rollup functionality, allows users to quickly and easily aggregate data from selected members into a single, summarized record. The rollup feature can simplify data analysis and provide more comprehensive view of information. Aggregated data can be displayed in tables and charts.
In Subset Editor you will find dedicated Rollup button or you can utilize the Rollup option inside the context menu.
- Follow the steps
Open the subset editor for Region dimension in Overview widget
Click Show All Elements (All E) button
Select all leaf level elements
Click Rollup button
Click Apply button
You will notice that new rollup was saved in the subset editor toolbar.
Click Apply button
New consolidated element will be visible in the table
Open subset editor again
Click on the x button to reset the Rollup
Select different leaf level elements
Right click on any element to open up the context menu
Select Rollup from the context menu
Click on the x button to reset the Rollup
Rollup will be no longer visible in the subset editor toolbar
7. Expand above
Enabling this option reverses the order in which child elements are displayed under (or above) their parent consolidations.
- Follow the steps
Click Show All Elements (All E) button
Click Expand Above button
Click Apply button
Save the changes
Collapse and expand Region dimension consolidations and you will notice that the order in which children elements are displayed changed.
Working with Settings Service
Values stored in settings service can be easily retrieved and utilized in reports. For instance, you can use them to set dynamic titles and descriptions, pass them into MDX and more.
The values in the Settings Service are stored in a 3-part key, delimited by periods. This key includes information such as the instance name, dimension name, and hierarchy name, making it easy to access and work with these values.
Syntax example: UX_Demo.Product.Product
1. UX Placeholders
» Dynamic Titles
In this exercise, you'll be using the UX placeholder to set a dynamic title that changes based on the global filter value.
To do this, you need to reference that value in the report's title using the UX placeholder. This way, every time the user changes the filter, the report title will update accordingly
- Follow the steps
Navigate to Sales Planning view (Exercises / Views)
Enable the Edit Mode
Click on the page title
Change it to: Sales Planning $<<UX_Demo.Region.Region>>
Change the Region global filter to World
Make sure that the title updated
With the use of the UX placeholder, you can also retrieve the alias of elements. This is done by using the syntax: $<<Instance_Name.Dimensions_Name.Hierarchy_Name-alias>>
» Attribute values
It is also possible to retrieve specific attribute value from Settings Service and use in e.g view title. You must use the following syntax: $<<Instance_Name.Dimensions_Name.Hierarchy_Name::attribute>>
In this exercise you will retrieve values for Region dimension Country Code attribute.
For the attribute to show correctly in the frontend option called All Attributes needs to be enabled.
This is how we enable it:
Click on the Settings icon
Select Filterbar option
Set All Attributes to Yes
Collapse the settings panel
Save the changes
Reload the page
Enable the edit mode
- Then follow the steps
Click on the view title
Change it to: Sales Planning $<<UX_Demo.Region.Region::CountryCode>>
Change the Region global filter to Spain
Make sure that the title says: Sales Planning ES
Those variables can be used in every text field in the view definition: info tool-tips, page titles etc. as well as in MDX query.
2. Dynamic MDX
UX placeholders can be part of MDX expressions that are used when configuring dimensions.
In this example you will configure Version dimension (Exercises / Views / Sales Planning view) to dynamically change the column content depending on the selected global filter.
- Follow the steps
Click on the cube icon to open up the cube settings popup
Change the sub-view to Version
Select Version column dimension
Open the advanced options panel and select Dimension Settings
Change the List Type to MDX
In the MDX text box input: {[Version].[Version].[$<<UX_Demo.Version.Version>>]}
Close the Cube Settings popup
Change the Version global filter to Budget
Make sure that the data as well as column header updated
Save the changes
3. Dynamic Formatting
Another way to utilize the UX placeholders is to make the formatting of our table more dynamic. Instead of using the column name for formatting, we can use the UX placeholder, which will apply the formatting based on the value of the global filter.
- Follow the steps
Click on the cube icon to open up the cube settings popup
Click + icon visible the Global Filters section
In the popup:
From the dropdown list select Period dimension
Click Add Dimension button
Change the Attribute (for newly added Period dimension) to Short Description
Close the cube settings popup
- Follow the steps
Open the advanced option panel
Expand Table Config section by clicking on the + icon
Select Column Format
Click + Column Format
Add Feb column by selecting it from the dropdown list and clicking +
Click on the pencil icon in className textbox and select any class
Click Apply button
Open the CODE tab
Replace the Feb column name with $<<UX_Demo.Period.Period::Short Description>>
Collapse advanced options panel
Save the changes
Change the Period global filter and notice that formatting is based on currently selected value (only Year column will have different formatting applied)
4. UX placeholders used in hierarchy context
This functionality allows users to dynamically define a dimension hierarchy using a placeholder. After clicking Select button the Hierarchy dropdown list will be changed to text box to enable placeholder input.
- Create new view
Select Add public... option inside Exercises - Views folder
Do the following configuration inside the popup:
Add view title: Dynamic hierarchy
Change the Cube Source by selecting Retail from the dropdown list
Click Create button
Publish the view
- Set up the view
Click on the cube icon to open cube settings popup
Select Default view
Drag and drop:
Period to Row Dimensions
Product to Column Dimensions
- Set up Product dimension to dynamically change based on the hierarchy selected
Click Add Dimension (+ icon) button in Global Filters section
Choose Product from the list
Click Add Dimension button
Click Add Dimension (+ icon) button again
Set Control Objects option to Yes
Choose }Hierarchies_Product from the list
Click Add Dimension button
Select }Hierarchies_Product dimension
Open Subset Editor
Click All E button
Select Product:Color and Product:Size from the list of available elements
Click Apply button
Select Product global filter dimension
Open the advanced options panel Dimension Settings
Click Select button visible above Hierarchy dropdown list
In the editable text box input UX Placeholder: $<<UX_Demo.}Hierarchies_Product.}Hierarchies_Product::Hierarchy>>
Change the List Type to MDX
Select Product dimension added in columns
Add the same placeholder in the Hierarchy text box
Change the List Type to MDX
Close the cube settings popup
Change }Hierarchies_Product filter to Product:Size and back to Product:Color
Notice how Product dimension hierarchy dynamically updated in global filter as well as in columns.
Save the changes
This configuration will work for global filters as well as for dimensions selected in rows or columns.
5. Nested UX placeholders
Use the view configured in the previous exercise to practice adding nested UX placeholders.
In this example the selection will be even more dynamic, both filters are configurated the same way as in previous case, but the MDX changes were made for the Product dimension added in columns.
- Update the configuration and follow the steps
Click on the cube icon to open cube settings
Select Product dimension added in columns
Open advanced options panel and select Dimension Settings
Click Select button visible above Hierarchy dropdown list
In the editable text box input UX Placeholder: $<<UX_Demo.}Hierarchies_Product.}Hierarchies_Product::Hierarchy>>
Change the MDX query to:
{[Product].[$<<UX_Demo.}Hierarchies_Product.}Hierarchies_Product>>].[$<<UX_Demo.Product.$<<UX_Demo.}Hierarchies_Product.}Hierarchies_Product>>>>]}Close the cube settings popup
Select element from Product global filter and notice how the column element dynamically updated
Change }Hierarchies_Product filter to Product:Size and select different element from Product global filter
Save the changes
Above example shows that by nesting two UX placeholder statements within each other users can not only define dynamic element selection based on filters, but also dimension hierarchy.
MDX details:
{[Product].
product dimension
[$<<UX_Demo.}Hierarchies_Product.}Hierarchies_Product>>].
dynamic hierarchy based on the global filter selection
[$<<UX_Demo.Product.$<<UX_Demo.}Hierarchies_Product.}Hierarchies_Product>>>>]}
nested UX placeholder enabling to selection from Product dimension even when its hierarchy is changing dynamically
Working with Attributes
1. Cell Types
Apliqo supports few predefined cell types that can help you create the reports you need, we will take a look at Date picker & Checkbox. Both of them are defined by CellType attribute.
Navigate to UX Samples - Cell Types - Date Picket options
Let's see what will happen when we change the Cell Type:
Open Set Cell Type popup
Click on the Checkbox CellType and notice that the box is checked
Clear the Checkbox CellType by clicking backspace button
Cell type changed and the checkbox is no longer visible
Input checkbox value into the cell to change the type again
Click on the date CellType and notice that the calendar opens
Clear the "date" value
The calendar is no longer available
Input date value into the cell to change the type again
2. Table format based on attribute
Navigate to UX Samples - Attributes -Format - driven by Attribute
In Simple Condition widget you can edit the value of the Region UX_Class attribute that is is used to drive the format in the Style grid.
Let's try it out:
change the value for USA cell to bg-orange-600
you will notice that the formatting in style widget changed
Now let's take a look at the advanced options to see how this was achieved:
Open advanced options panel
Click on the Style widget header
Click on the CODE tab
In the table section you will notice that instead of class the attribute was used to drive the formatting
Replace UX_Class with:
$<<UX_Demo.}ElementAttributes_Region.}ElementAttributes_Region>>
Collapse advanced options panel
change the }ElementAttributes_Region global filter to Class2
You will notice that the cell color for the USA and Brazil elements was updated.
3. Read only based on attribute
Read-only access can be applied to the whole table, specific rows, columns or be based on attribute value. In this exercise you will use UX Placeholder to see how the read access can change based on the selected filter.
For the purpose of this exercise navigate to UX Samples - Attributes - ReadOnly and Popup display based on dimension attribute.
Take a look at the Read only option based on Region dimension attribute widget, right now the leaf level elements are editable, which is indicated by the grey formatting.
Change the Region global filter to Brazil
Formatting of the cells will be changed to read only
Follow the steps below to practice the configuration:
Open the subset editor for Region global filter
From the attributes list select ReadOnly (this atribute is used to dynamically drive read access of the table. It's configured with true/false values)
Click on the pencil icon to edit the attributes
Set the value of ReadOnly attribute for USA element to true
Apply the changes
Change the Region global filter to USA and you will notice that cells are no longer editable
In this part you will check the advanced options that allowed for readOnly access to change dynamically:
Open the advanced options panel and select CODE tab
Click on the Read only option based on Region dimension attribute widget header
Under the table section you will see "readOnly" option
In this example true/false value was replaced with the placeholder referencing in this case Region global filter dimension and ReadOnly attribute:
"$<<UX_Demo.Region.Region::ReadOnly>>"
For this to work correctly the All Attributes option under Filterbar section needs to be turned on.
4. Show table popup based on attribute
By default, popup icon will appear for all elements in the table. However, if you want to limit the display of the icon to only certain elements based on their attribute values, you can do so by assigning a value of 1 to those elements. This exercise will show you how to set up and use this feature effectively.
For the purpose of this exercise navigate to UX Samples - Attributes - ReadOnly and Popup display based on dimension attribute.
Take a look at the Popup display based on Region dimension attribute widget, popup icon is limited to just few rows.
Follow the steps below to practice the configuration:
Open the subset editor for Region global filter
From the attributes list select ShowTablePopUp (you can use whatever name you prefer for your attribute)
Click on the pencil icon to edit the attributes
Change the value for America, Europe & Asia to 1
Apply the changes
And all done, now the popup icon will be displayed for the selected elements (all with the value 1) from the dimension.
Second part of the configuration was done using table advanced options:
Open the advanced options panel
Click on the Popup display based on Region dimension attribute widget header
Select Table Config section
Popup Attribute Enabled option needs to be set to Yes
Change it to No and notice that now the popup icon will be displayed for all Region elements in the table
Change it back to Yes
Also correct Display Popup Attribute must be selected from the list to ensure correct configuration.
Table Advanced Options
1. Zero Suppression
In case you do not want your table to display rows/ columns containing only null data, you can achieve this by changing the 'Zero Supression' settings.
Navigate to Views - Sales Planning
Before following below steps notice that rows displaying only 0s were hidden. Now you will check what happens if you change this option:
Click view Cube Settings icon
Open Zero Suppression drop-down list and un-check Rows and Columns option
Open advanced options panel and select SubView Settings
Open Zero Suppression Button drop-down list and chose Rows and Columns
Close the Cube Settings popup
If you look at the table now you will rows contain 0 values. Select rows from the new Zero Suppression button and they will disappear again.
When the Zero Suppression button is enabled, there is no need to go back to the cube settings to change zero suppression selection, it can be done directly from the view level.
2. Subset Selection
To provide a more dynamic analysis of the data, another very useful feature is available in the cube settings - Subset Selection. Enabling it allows you to change the subsets of the dimension from the list available at the view level. Additionally, we decide which subsets are included in the selection.
Do the following configuration:
Open view Cube Settings
Click on the Product dimension
Open the advanced options panel and select Dimension Settings
Enable Subset Selection
Choose Accessories, Bikes and Clothing in Multi Subset Selection list
Close the pop-up and check the subset selection availability and save the changes.
3. Context menu options
» User defined consolidations: Rollup
Two new options, Rollup and Custom Rollup, have been added to the context menu for grid widgets and views.
Rollup: creates a new element that summarizes the selected members by displaying their sum.
Users also have the ability to create a Custom Rollup which requires an additional dialog in order to select the mathematical function to apply to the rollup.
You can specify both Rollup Name and Rollup Function:
AGGREGATE/SUM: display the total for the selected members
AVG: display the average value of the selected members
COUNT: display the number of the selected members
MIN: display the minimum value of the selected members
MAX: display the maximum value of the selected members
STDDEV: displays sample standard deviation of the selected members Follow the steps below to practice it:
Select few elements from Product dimension (e.g. leaf level elements from Mountain Bikes) • Right click on one of the elements to open up a context menu • Select Rollup option
New consolidated element Product Sum will appear in the table.
You can edit the Rollup at any time:
right click on the Product Sum element
select Edit Rollup option from the context menu
in the popup do the following configuration
◦ Change the Rollup Name to: Product AVG
◦ Change the function to AVG
◦ Apply the changes
Now the Product AVG element is showing the average value for the selected elements.
What is more you can also Save the changes made or Reset them to get back to the original view configuration:
Right click on the Product AVG element
Select Save option
Save the view & reload the page
Product AVG will still be available in the view configuration
Enable the edit
Right click on the Product AVG element
Select Reset option
Product AVG will no longer be available in the view configuration
Save the changes
» Subset Editor Shortcuts
These shortcuts allow you to change selected elements or attributes on the fly, making it easy to manipulate data quickly and efficiently. There's also an option to reset or save changes.
Follow the steps below:
Right click on the Helmets element
Open Selection list
Select Keep + children
You will notice that the table updated the selection accordingly
Right click on the Helmets element
Open Attributes list
Select Spanish Description
The Product dimension will now display updated attribute
Select Reset from the context menu to return to the original configuration
» Inserting new column and row
In this exercise, we'll focus on enhancing reports by inserting new columns or rows. Adding custom calculations to your reports can provide valuable insights and context to your data, making it more informative and visually appealing.
Adding column
First we will add a column:
Right click on the Dec column
Choose Insert column right form the list
Context menu will automatically close and the new column will appear in the table.
Adding new rows works the same way. After right clicking on the row element, context menu appears and we will have the option to add row above or below selected element.
Column title change
All the columns added to the table will appear with the default name: Inserted + number (depending on the amount of already added columns).
It can be changed using context menu:
Right click on the inserted column header
Select Edit Column Header from the list
Enter the new title in the popup: New Column
Click Save
Make sure that the column header updated
Save the changes
» Formulas
Adding new columns in views or grid widgets is often done to perform further calculations. The application allows us to use arithmetic operations or standard Excel formulas.
We will use our new column to calculate the sum of the values for the first half of the year, so months of Jan through Jun.
Follow the steps below:
Click Toggle Excel Headers button for easier formulas input
Click on the firs row cell in New Column
Input the formula e.g. =SUM(D1:J1) and click Enter
Formula entered on the first row cell is automatically applied to all elements in rows. Now only the first row cell is editable.
You can change it:
right clicking on the first cell in New Column
select Remove Auto Column Fill from the context menu
select Auto Column Fill from the context menu to populate rest of the cells again •
Save the changes
DBRW function
DBRW function which uses the same syntax as in Perspectives or PAfE is also supported.
In this exercise we will use this function:
=DBRW("UX_Demo", "Retail", "Actual", "2012", "$<<UX_Demo.Period.Period>>", "Local", "USA",
A1, "Sales Amount")
Period dimension was replaced with: '$<<UX_Demo.Period.Period>>' to allow the value to dynamically change together with the Global filter.
Product dimension was replaced with A1, which refers to the row dimension Follow the steps below:
Double click on the first row cell in New Column
Input the DBRW function and click Enter
Change the Period global filter to May
Make sure that the data updated
Save the changes
As you can see, the data from the New Column displays the same data as the May column.
COLHEAD - referencing column headers in custom formulas
To fully benefit from the formulas new custom function =COLHEAD(1,0) was added to the app that allows to reference column header name.
First parameter refers to column number (starting from 0 for column A)
Second parameter specifies the column header index and is needed only if nested column headers are used, in which case the user must specify the header row. The default value if omitted is 0.
In this exercise you will use a DBRW function, which will include =COLHEAD formula.
First you will update New Column name and connect it with Period global filter with the use of settings service placeholder:
Right click on the New Column header
Select Edit Column Header option from the context menu
In the textbox input $<<UX_Demo.Period.Period>>
Click Save button
The column name will update to match the current Period global filter selection.
In this part of the exercise you will add DBRW formula to the table.
double click on the first cell in the Year column (so it will become editable)
input the following formula in the cell =DBRW("UX_Demo","Retail","Actual","2012", COLHEAD(15), "Local","USA",A1, "Sales Amount")
Now, thanks to the fact that you used a settings service placeholder in the column name and a new COLHEAD formula in the DBRW function, you can see how the data will dynamically update when we change the Period global filter.
Change the Period global filer by selecting Jan from the dropdown list. The column name header as well as the column data will update.
4. Cross Drill
For better analytical possibilities, the application allows us to drill on each row, by any other dimension, which is part of the cube the view is based on.
For the purpose of this exercise navigate to Exec Dashboard and follow those steps:
Right click on the USA element in the Overview widget
Make sure that the context menu appeared and the Drill down by... option is available
Chose Account from the list of available dimensions
Drill appeared in the table and is marked with blue color. You can collapse and expend the consolidations.
To close the drill, right click on one of its elements and choose Close from the list.
5. Spreading
Spreading is used to distribute a value across a range of cells or all leaf elements in a consolidation. For the purpose of this exercise navigate to Reports / Views - Input on C-level Cells.
Once spreading is enabled two additional right-click menu options will appear:
Spreading (opens a separate dialog)
Holds... ( leads to a sub menu) Follow the steps below:
Change the Region global filter to USA
Right-click on February - Bottles and Cages • Select Spreading option
In the popup:
Select Proportional Method
Select Replace Data Action
Input 1000 in the Value to be spread textbox
Click Apply
All leaf elements should now be equal 333.
Spreading & Holds shortcut can be entered directly in the cell without using the right-click options or spreading dialog.
» Overview of available options
Method:
The proportional spread method distributes a specified value among cells proportional to existing cell values. (P)
The equal spread method distributes a specified value equally across cells in a view (S)
The repeat method repeats a specified value across cells in a view. (R)
The Clear method clears values from cells in a view. (C)
The percent change method multiplies current cell values by a specified percentage. (P%)
The straight line data spreading method populates cube cells by linear interpolation between two specified endpoints. (SL)
The growth % method accepts an initial value and a growth percentage. (GR)
» Spreading advanced options
When spreading is enabled for a view or widget then by default all spreading types are enabled. But with the use of advanced options you can control which types are allowed.
Follow the steps below:
Click on the Spreading widget header
Click on the Settings icon
Click on the + icon visible next to Table Config option
Select Spreading Items
All available spreading types will be visible in the right panel of the advanced options.
Set Proportional, Equal and Repeat to No
Collapse the Settings panel
Right-click on any cell in Spreading Widget
Select Spreading from the menu
Open Method dropdown list
Disabled spreading types will not be visible
Close the popup and Discard the changes.
» Input on consolidations
After enabling Input on Consolidations in the advanced options by default non editable consolidated cells can be updated. It will work with spreading and holds shortcuts.
For the purpose of this exercise we will use again Simple Planning template Follow this steps to check out this functionality:
Click on the Jan cell for Accessories element and notice that the cell can't be edited
Open advanced options panel and expand Table Config option
Select Input on Consolidations and enable it
Double click on the same cell again to check that now editable textbox will appear.
Save the changes.
6. Number Format
In this section, we will learn how to override TM1 number format with the use of advanced options. For the purpose of this exercise we will use the Report / Views - Simple Planning Template.
Follow the steps below:
Click on the settings icon
Open the settings for Table Config and select Column format
Click on the + icon next to Column Format
Select January from the dropdown list and click + icon
Open the Number Format dropdown
Expand Zero, select - Minus and click Apply
All zero values in January column will be formatted using - sign instead of 0.
Discard the changes.
Options available in Number Format dropdown:
Separator change: , or .
Number of decimal places: from 0 to 4
Option to apply: % Percentage, K Thousands, M Millions
Negative values display : - or ( )
Null values display: 0 or - • Add currency
7. Read only
Read only option can be applied for the whole table if we don’t want enduser to update the data. It can be easily done by changing one advanced option. This functionality works independent of the TM1 security.
Follow the steps below:
Change Region global filter to USA
Click on the Settings icon
Select Table Config option
Change Read Only option to Yes
Notice that the table changed formatting from gray to white and now leaf level elements can't be edited.
Discard the changes.
Read only functionality can be also applied to specific rows or columns.
We can select row / column and add it in the Column/Row format advanced option panel. Each added element additionally to formatting options have readOnly option.
Follow the steps below:
Change Region global filter to USA
Click on the Settings icon
Expand Table Config option and select Column Format
Click on the + icon next to Column Format
Add February column to the advanced options panel by clicking on the + icon visible next to the dropdown list
Set readOnly option to Yes
Again notice that the formatting for the February column changed and the cells are no longer editable.
8. adHoc columns edit
After enabling advanced adHoc columns option, users will have access to a new set of toolbar (or widget header) buttons to facilitate column editing without the need to manually change MDX query (especially asymmetrical selection for stacked dimensions).
» Enabling adHoc columns
For the purpose of this exercise navigate to Reports / Views - AdHoc Edition - Asymmetric selection on columns with adHoc edit options.
You will notice that on this page additional set of toolbar buttons is already visible. In the next steps you will disable and enable back adHoc column edit, follow the steps:
Click on the Settings icon
Click on the + icon next to the Table Config option
Select Adhoc Columns
Set Compound Column Select to No
Set Compound Column Select to Yes
Collapse the advanced options panel
» Overview of the available options
Add Column (+ icon): A new column will appear in the table with the default dimensions configured.
Duplicate columns: After selecting a column from the grid the button becomes active. After clicking it, duplicated column will appear in the table.
Pencil icon visible in the column headers allows users further customization of the stacked dimension without the need of changing MDX query.
Remove Column (trash can icon): Remove selected column from the table.
Save Columns: Save the changes
» Configure Default Elements
In this example we are using Year and Version so the configuration needs will be done for those two element.
First we have dimension and it's hierarchy which is used in our table.
Next is subset - It will define what elements will be displayed in the dropdown list after clicking on the pencil icon
Important part is also setting up column default elements - when adding new column default elements will be applied when click on the + icon
Default options for new column were already added in this view but you for the purpose of this exercise you will change them:
Click on the Settings icon
Open CODE tab
Under "adHocColumns" you will see "columnDefaultElements":
Change default element to 2014 for Year dimension
Change default element to Budget for Version Dimension
Click refresh button
Save the changes
» Exercise
The changes will disappear after the full reload of the page, unless they were made in Edit Mode and the Save Columns option has been selected.
Click + icon (new column 2014 Budget will appear in the table)
Click on the pencil icon for Version dimension and change it to Actual
Click on the pencil icon for Year dimension and change it to 2016
Select new column and click on the Duplicate button (new column 2014 Actual will appear in the table)
Select newly duplicated column and click trash can icon (column will disappear from the table)
Click Save Columns button
Save the changed
9. Undo the changes
Undo functionality allows users to view data entry changes made to values in a screen and to undo them if needed. A new button has been added to the toolbar on each page to allow user who made the data adjustments to undo changes. It will not be active until a change is detected.
For the purpose of this exercise navigate to Simple planning template and follow the steps below:
Change the Region global filter to USA
Change the value for 483 - Bike Rack Standard element in January column to 5000
The Undo button will be now active, click it
New popup will appear where you can review the changes.
To undo the changes follow the steps below:
Check the box for the change you want to undo
UNDO button will become active, click it
The value in the table will be reverted to the original one.
When many changes are made at once (e.g. via a copy/paste action), these are stored in the change log in a summarized form:
Change the value for 483 - Bike Rack Standard element in January column to 5000 then drag and drop the value for the rest of the columns
The Undo button will be now active, click it
Click Show details to review the changes for the batch
Click Cancel to revert to previous view
Check the box for the batch change
UNDO button will become active, click it
The values in the table will be reverted to the original one.
Charts configuration and edition
1. Waterfall Chart
In this section we will check out some of the advance adoptions specific for Waterfall chart. For the purpose of this exercise navigate to Latest Releases - UX Version 2020.08 - Waterfall Chart improvements
» Drill Down
If the chart contains consolidated elements, you can drill down to display its children elements. • Click the Net Sales element
Notice that the elements displayed in the waterfall chart were updated and now the children elements of Net Sales are being displayed
Click the Gross Sales element and make sure that the chart once again changed its contents
Click on the House icon to navigate back to the original state
Name of the elements that can be drilled are marked in bold. Use breadcrumbs to navigate back to previous step on the drill path.
» Y-axis scaling
Turning this option on will change the minimum value for the y-axis.
Change the Department global filter to Executive General and Administrations
Click on the settings wrench icon an then on the Waterfall widget header
Expand Chart Options and select Waterfall Chart
Set Y Axis Min Auto to Yes
Collapse the advanced options panel
Make sure that now the differences between the values are more distinct & discard the changes
» Add commentary
The Waterfall chart includes an enhancement that allows comments added to the table to be displayed directly in the chart. By default this option is not enabled, but you can easily change it using advanced options:
Click on the settings wrench icon and then on the Waterfall widget header
Expand Chart Options and select Waterfall Chart
Set Show Cell Commentary to Yes
Collapse the advanced options panel
In our example this option is already turned on.
Right click on the Operating Expenses cell in Actual column
Choose Comments from the list
In the text box write: This is test commentary
Click Post
Close the popup
Make sure that the cell is marked with red triangle that indicates that the comments was added
Click Refresh button
Make sure that Waterfall chart is displaying the added comment for Operating Expenses element
As the waterfall chart dynamically calculates the variance between 2 series, "Comment Row: 1" means that the chart will display comments for 1st series.
KPI widget types and configuration
There are three types of KPI Widgets that can be added to a page to provide high level information that is key to a report:
KPI
KPI Simple
KPI Trend
In this exercise we will add and correctly configure KPI trend widget.
Follow the steps:
Navigate to Exec Dashboard
Click on the plus icon and select Widget from the list
In the popup:
Set the title to 'KPI'
Set the cube source to 'General Ledger'
Click Create button
Next, we are going to set up what information to retrieve for the KPI widget:
change the widget type to 'KPI Trend'
open Cube Settings for the KPI widget
move Region dimension to rows
Change List Type to Subset
move Version dimension to rows
Change List Type to Element Comparison
Set Value to Actual and Value MAX to Budget
move Period dimension to columns
Change the subset to Default
Set the Attribute to Short Description
Close the cube settings popup
Adjust the widget size using the flexible layout
Now the widget correctly displays a comparison of Actual and Budget for the countries we have selected. The period that has been defined is reflected in the graph at the bottom.
Save the changes.
Advanced TI integration
As an example for the advanced TI integration module, we will use the "}bedrock.cube.data.export" TI process.
Create a new Dashboard, called TI integration and publish it. Change the widget type from Grid to TI Process.
1. Basic TI setup
Click on the settings icon and then select Process Config option:
Set Embed in Page to Yes
Select UX_Samples from the Instance drop-down list
Select }bedrock.cube.data.export from the Process Name drop-down list
This configuration will already show all the parameters of the process in the webform.
2. Advanced TI settings
To make the results even friendlier to the users we can add additional code for the parameters.
Inside the "parameters" tag we can add additional config options, in order to set default values and hide specific parameters. Additionally, we can assign check boxes and subsets.
Here is the configuration we can use to make the process easier to us.
Below you can also find, in the attached file, the complete TI setup.
"parameters": {
"pLogoutput": {
"hidden": true,
"value": 0
},
"pCube": {
"label": "Cube Name",
"type": "subnm",
"instance": "UX_Samples",
"dimension": "}Cubes",
"tm1Mdx": "{ [}Cubes].[}Cubes].Members }",
"tm1Attribute": ""
},
"pView": {
"hidden": true
},
"pFilter": {
"hidden": true
},
"pFilterParallel": {
"hidden": true
},
"pParallelThreads": {
"hidden": true,
"value": 0
},
"pDimDelim": {
"label": "Dimension Delimiter",
"disabled": true,
"value": "&"
},
"pEleStartDelim": {
"label": "Element Start Delimiter",
"disabled": true,
"value": ":"
},
"pEleDelim": {
"label": "Element Delimiter",
"disabled": true,
"value": "+"
},
"pSuppressZero": {
"label": "Suppress Zero",
"type": "checkbox",
"trueValue": 1,
"falseValue": 0
},
"pSuppressConsol": {
"label": "Suppress consolidation",
"type": "checkbox",
"trueValue": 1,
"falseValue": 0
},
"pSuppressRules": {
"label": "Suppress rules",
"type": "checkbox",
"trueValue": 1,
"falseValue": 0
},
"pZeroSource": {
"label": "Clear Source Data",
"type": "checkbox",
"trueValue": 1,
"falseValue": 0
},
"pCubeLogging": {
"label": "Cube Logging",
"type": "checkbox",
"trueValue": 1,
"falseValue": 0
},
"pTemp": {
"label": "Use Temp Views",
"type": "checkbox",
"trueValue": 1,
"falseValue": 0
},
"pFilePath": {
"label": "File Path",
"disabled": true,
"value": "C:\\"
},
"pFileName": {
"label": "File Name",
"type": "input"
},
"pDelim": {
"label": "File delimiter",
"disabled": true,
"value": ","
},
"pQuote": {
"label": "File quote",
"disabled": true,
"value": "\""
},
"pTitleRecord": {
"label": "Title Records",
"disabled": true,
"value": "1"
},
"pSandbox": {
"hidden": true
}
},» TI Process configuration using new Advanced Options GUI
Parameters configuration is available in the dedicated tab: Process Parameters.
Hide specific parameters
Add labels
Disable parameters
Add default value
Configure subnm selection
Change the parameter type to checkbox
Final result should look like this:
3. Track process status in notification drawer
Since UX Version 2023.10 Notification drawer has been disabled by default (bell icon not visible in the app), to enable it please add the below option to the default.constants.js file and set it to true.
'NOTIFICATION_DRAWER': true
File is located in ApliqoServer\webapps\Apliqo_Demo\apq-c3-custom\js folder.
With the help of the processes drawer in the notifications panel, users can monitor running processes and their status changes.
To benefit from this functionality, additional advanced option Enable Drawer was added for three TI Process Status Notifications: Running, Success, Failed.
Navigate to Latest Releases - UX Version 2022.06 - Commentaries and Notification Follow the steps below to see how to enable it:
Enable the edit mode
Click on the settings icon and then on the Add Product widget header
Click on the + icon visible next to the Process config option
Click on the + icon visible next to the Status Notifications option
Select Success
Notice that the Drawer Enabled option is set to Yes -> this is the option enabling users to keep track of process status changes
Now we will see how the status changes will be shown in the notifications drawer:
In the Add Product widget input "My New Product" in the Product textbox
Click Run button
If the process was successful, new notification will appear next to the bell icon
Expand the Process section
Click on the 3 dots icon
Select Process Info option to review the paraments
4. Create TI Process button from Action menu
The Add menu (located in the toolbar) now contains a new option: TI button
Choosing this option will open up a create dialog for the TI process button. User can specify: title, instance, process and location of the process button (by default it will be located in toolbar, but by enabling Widget PopUp it can be placed in a selected widgets' header instead) Follow the steps below to try it out:
Click on the + icon visible in the page toolbar • Select TI Button from the list
In the popup:
Input title e.g. My New Process
Pick one Process form the dropdown list
Click Create
New Process button will appear in the toolbar. Further configuration can be done with the advanced options GUI as explained in the previous exercises. Save the changes.
5. Run on click
New advanced option Run On-Click has been added to the Process Configuration to enable users to run the process with predefined parameter values without the need to open the popup dialog. After setting this option to true, the process will start with the button click and run in the background. For this process configuration the Drawer Enabled option will be by default turned on so that the user can track the process status in the Notifications window.
Follow the steps below to see how to enable this option:
Click on the Wait toolbar button
Open the advanced options menu
Click on the popup header
Select Process Config
Set Run On-Click to Yes
Expand Process Config section by clicking on the + icon
Select Process Parameters
For pWaitSec parameter set the value to 5
collapse advanced options panel
save the changes
disable the edit mode
click Wait toolbar button
Process started automatically and you can track it status in the notification drawer.
Advanced MDX
Advanced MDX techniques can be used everywhere in the Apliqo application.
In the following example, we are going to check a few of the most common MDX queries that can be used to improve the functionality and the user experience.
1. Dynamic filtering
One way to use dynamic filters with the help of MDX is to query a member base on the value of an attribute of another member.
For the purpose of this exercise create a new view, based on General Ledger cube.
Open cube settings popup
Select Default view
In this exercise, we will work on making the Currency selection dynamic based on the selected Region. To accomplish this, we will make use of the existing Currency attribute of the Region dimension.
Move Version dimension to columns section
Move Region column dimension to filters section
Open Subset editor for Region global filter dimension
Change the subset to All Countries
Change Attribute to Code and Description
Click Apply
select Currency dimension
open advanced options panel
select Dimension Settings
in the MDX textbox input following code:
{[Currency].[Currency].[$<<UX_Demo.Region.Region::Currency>>]}close the cube settings popup
Observe that whenever you change the country, the currency will update automatically based on the attribute.
» Alternative version of the MDX
{StrToMember("[Currency].[Currency].["+[}ElementAttributes_Region].([Region].[Region].[$<<UX_Demo.Region.Region>>], [}ElementAttributes_Region].[Currency])+"]")}Define MDX for Currency dimension to display the currency set in attribute of selected Region element:[Currency].[Currency].["+[}ElementAttributes_Region]
Set Region element to change dynamically: [Region].[Region].[$<<UX_Demo.Region.Region>>]
Define the Region attribute : [}ElementAttributes_Region].[Currency])+"]
2. Filtering displayed elements
The next example will allow us to see only filtered Region elements on the columns based on their value in the selected cube.
For the purpose of this exercise navigate to UX Samples - Filters - Filtering displayed elements in columns by value using MDX
change the Version & Account global filter and observe how the displayed elements change based on their value
Now we will take a look at the configuration:
Open the cube settings popup
Select Region column dimension
Open the advanced options panel
Select Dimension Settings
In the MDX text box you will see following MDX query:
{
Filter(
{ TM1SubsetAll ( [Region] ) }
, [General Ledger_C3UX].(
[Version].[$<<UX_Demo.Version.Version>>],
[T Year-Month].[2012],
[Currency].[Local],
[Region].CurrentMember,
[Department].[Corporate],
[Account].[$<<UX_Demo.Account.Account>>],
[General Ledger Measure].[Amount]
)
> 1000000 )
}» Additional information about used MDX
Filter all the elements from the Region dimension:
Filter( { TM1SubsetAll ( [Region] ) }
Filter all elements based on their value in General Ledger_C3UX cube:
[General Ledger_C3UX].([Version].[$<<UX_Demo.Version.Version>>],[T Year-Month].[2016],[Currency].[Local],[Region].CurrentMember, [Department].[Corporate],[Account].[$<<UX_Demo.Account.Account>>],[General Ledger Measure].[Amount])
Filter values bigger that 1,000,000:
> 1000000 )
UX Placeholders for Version ($<<UX_Demo.Version.Version>>) and Account dimensions ($<<UX_Demo.Account.Account>>) were added to enable the dynamic change of selected elements.
In the next part we will update the configuration:
Replace [T Year-Month].[2012] with [T Year-Month].[$<<UX_Demo.T Year-Month.T Year-Month>>] to enable dynamic selection
Close cube settings popup
Change YEAR MONTH global filter to 2014
Notice that different elements will be now displayed in the table
Full MDX view
With Full MDX functionality, users can create reports faster, more easily include custom columns and calculations, and still benefit from the placeholder functionality.
Full MDX statements can be added using cube settings popup. After turning on Full MDX option, new textbox will be provided to input query. Dimensions can be left undefined, configured to set the dimensions Attributes or used to incorporate dynamic filter selection with the help of UX placeholders.
For the purpose of this exercise add new widget based on General Ledger cube, you can use any dashboard available.
Then follow the steps:
Open the cube settings of newly created widget
Move Period and Version dimensions to global filters
Open the advanced options panel by clicking on the wrench icon
Select Widget Settings
Turn on the Full MDX option
Collapse the advanced options panel
Click Edit MDX button
In the textbox input the query provided below:
SELECT
{TM1SubsetToSet( [Region].[Region], 'All Countries')} ON COLUMNS,
{TM1FILTERBYLEVEL({[Account].[Account].Members},
1 )} ON ROWS
FROM [General Ledger]
WHERE (
[Period].[Period].[$<<UX_Demo.Period.Period>>],
[General Ledger Measure].[General Ledger Measure].[Amount],
[Department].[Department].[1],
[Currency].[Currency].[Local],
[Year].[Year].[2012],
[Version].[Version].[$<<UX_Demo.Version.Version>>]
)
Period and Version dimension where defined dynamically with the use of settings service placeholder to facilitate dynamic data update based on selected value.
Close the cube settings popup
table will be generated based on the added MDX query
Change Period and Version global filter to test the data update.
At this point selected elements from both dimensions are displayed without attributes, this can be easily changed.
Open the cube settings again
move Region dimension to Widget Column
move Account dimension to Widget Rows
Open the advanced options panel
select Account dimension » open Dimensions Settings and change the Attribute by selecting Code and Description from the dropdown list
select Region dimension » change Attribute by selecting Code and Description from the dropdown list
Close the cube settings popup and you will notice that selected attributes are visible in the table.
» Add custom column to Full MDX view
As Full MDX functionality allows to easily include custom columns and calculations, in this exercise you will practice that.
Open the cube settings popup
change the MDX query in the Full MDX textbox to the one provided below:
WITH
MEMBER [Region].[Region].[All Countries SUM] AS
Sum(TM1SubsetToSet( [Region].[Region], "All Countries" ) )
SELECT {[Region].[Region].[ All Countries SUM],{TM1SubsetToSet( [Region].[Region], "All Countries")}} ON COLUMNS,
NON EMPTY {TM1FILTERBYLEVEL(
{TM1ToggleExpandMode({[Account].[Account].Members}, EXPAND_BELOW )},
1 )} ON ROWS
FROM [General Ledger]
WHERE (
[Period].[Period].[$<<UX_Demo.Period.Period>>],
[General Ledger Measure].[General Ledger Measure].[Amount],
[Department].[Department].[1],
[Currency].[Currency].[Local],
[Year].[Year].[2012],
[Version].[Version].[$<<UX_Demo.Version.Version>>]
)Using WITH MEMBER query you added an additional column, which is not part of the cube, that will show the sum of previously selected elements from the Region dimension (sum of all elements from the “All Countries” subset).
Close the cube settings
table will now display additional All Countries SUM column
Save the changes
User default settings
1. Set up Home Page
» Use "Set Home Page per user" widget to change the Home Page
Each user of the Application can have a dedicated Home Page selected.
This is how we can set it using the Set Home Page per user widget:
Navigate to Welcome to Apliqo UX dashboard by selecting it from the Content Store Settings menu
Change the Demo1 user home page to “Exec Dashboard”
Click on the Logo and make sure that it navigates to Exec Dashboard
» Use User Default Settings to change the Home Page
There is an alternative way to set up a Home page.
In the right corner of the page there is a name of the user visible (Demo1), click it
Choose User Default Settings from the list
In the popup open Set Default Homepage drop-down list
Choose Welcome to Apliqo UX
Close the popup
Click on the Logo and make sure that it navigates to Welcome to Apliqo UX
Final Exercise
Your task: Recreate a wizard based on provided screenshots. Pay attention to details like colors, typography, formatting and layout. View demo below to check the details.