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

Open

Subset editor shortcut can be also added to the context menu which is useful functionality when editing views.

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

Open

• 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

Open

2. Subset Selection

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

Open

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

Open

3. Attribute Selection

Now you will practice Attribute selection.

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

Open

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.

1. Follow the steps

• Open Subset Editor for Region global filter

• Current default value is set to: World

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

Open

5. Exercise

In this exercise you will use Subset Editor to change elements displayed in Version dimension.

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

• 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

Open

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.

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

Open

• 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

Open

7. Expand above

Enabling this option reverses the order in which child elements are displayed under (or above) their parent consolidations.

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

Open

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

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

Open

» 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

Open

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

Open

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.

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

Open

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.

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

Open

1. 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 +

Open

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

Open

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.

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

Open

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

Open

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

Open

• 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

Open

• 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

Open

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.

1. 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:

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

Open

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.

Open

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

Open

• 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

Open

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

Open

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

Open

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

Open

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

Open

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

Open

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:

For this to work correctly the All Attributes option under Filterbar section needs to be turned on.

Open

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.

Open

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.

Open

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.

Open

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.

Open

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.

Open

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

Now the Product AVG element is showing the average value for the selected elements.

Open

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

Open

• 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

Open

» 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

Open

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

Open

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

Open

» 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

Open

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

Open

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.

Open

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.

Open

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.

Open

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.

Open

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.

Open

» 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

Open

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.

Open

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