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

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.

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

» 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

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>>

  • 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

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

• 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.

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.

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.

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.

» 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.

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

• 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. Overview of Basic Options

Application offers various chart types and depending on which widget is selected, settings panel will return the available options. But some of them are common to most of chart types and first we will focus on those.

• Tooltip shared

• Chart title

• Chart Null Values

• Calculated Rows and columns.

• Series section, which gives you many customization options, after clicking on the + icon next to Populate our data will automatically be added to the advanced options panel and now you can change:

  • Type: we have area, line, bar and column option

  • Color, which can be selected from the color picker

  • We can enable data labels for selected series

  • And change their format

Plot Options and Data Labels

What’s more graphs in the app naturally come with drill through capacity.

You can adjust those settings in the Charts Driablle section:

• Cross drill ➟ Available when we right click on the column and select Drill down by, we can turn it off using this option

• Filter by option➟ which is available after turning on the hyperlink option for the selected dimension

• Series drill➟ Which allows us to drill on any column or the column series it

In one of the latest versions a new option has been added to the advanced options GUI: Chart Show Percentage o facilitate adding percentage formatting in charts. Enabling the option will result in % format and recalculated values to be displayed in data labels, tooltips as well as y- axis. No further configuration is required.

• open the advanced options panel

• select Chart widget by clicking on its header

• select Chart Options

• set Chart Show Percentage option to Yes

• save the changes

The formatting in the chart will change, you will notice % visible in the data labels, tooltips as well as y- axis.

2. 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

» 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

» Hyperlinks

The same as in the grid widget elements of the chart can be set as hyperlinks that will change dashboard filters.

• Enable the Edit Mode

• Open Cube Settings for the Waterfall widget

• Click on the Account dimension

• Open the advanced options panel and select Dimension Settings

• Set option HyperLink to change filters to Yes

• Close the Cube settings popup

• Click Taxes element

• Make sure that the Account dashboard filter changed to Taxes

• Save 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

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

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 )

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>>]
)

• 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>>]
)

• 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.