Power User - Intermediate Course
New sections added in January 2022:
- Spreading in Table Advanced Options
- adHoc columns edit in Table Advanced Options
New sections added in March 2022:
All the views used in this course have been created and configured in Basic Course in View Creation and Configuration and Dashboard Creation and Configuration sections.
Select them from App Templates folder.
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.
Before following below steps notice that rows for 478 - Mountain Bottle Cage and 479 - Road Bottle Cage are displaying only 0s and could be hidden.
To do that:
- Click view Cube Settings icon
- Open Zero Suppression drop-down list and check Rows and Columns option
- Open Zero Suppression Button drop-down list and chose again Rows and Columns
- Close the Cube Settings popup
- Save the changes
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.
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
- 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.
The view can be further edited by adding new columns or rows.
During these exercises the Edit Mode must be enabled because we will be using the context menu available by right-clicking on a cell in the table.
1.3.1. Adding column
First we will add a column:
- Right click on the Overview 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.
1.3.2. 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 e.g. "New Column"
- Click Save
- Make sure that the column header updated
- Save the changes
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.
For the purpose of this exercise we will use column added in the previous section. All changes must be done in Edit Mode, otherwise New Column won't be editable.
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(C1:H1) 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
- Save the changes
1.4.1. DBRW function
DBRW function which uses the same syntax as in Perspectives or PAfE is also supported.
Do the following configuration:
- Open view Cube Settings
- Click the plus icon visible in the Global Dimensions section
- From the Dimension drop-down list choose Period
- Click Add Dimension
- Click on the Period dimension
- Choose Default from the Subset drop-down list
- Set Display Attribute to Short Description
- Close the Cube Settings popup
In this exercise we will use this function:
=DBRW('UX_Demo', 'Retail', 'Budget', '2016', '$<<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.
This syntax: $<<(Instance_Name).(Dimensions name).(Hierarchy name)>> that allows creating dynamic titles with all the dimensions used in the application was discussed in Basic Course.
Section: Dashboard Creation and Configuration - Widget interactivity - Create a dynamic title
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.
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 we will add a new widget to Exec Dashboard.
Follow the steps below:
- Click on the plus icon
- Select Widget from the list
- Add widget title: "Spreading "
- Change Cube Source to Retail
- Click Create
- Save the changes
Configure the widget:
- Open Cube Settings for Spreading widget
- Move Product dimension to Widgets Rows
- Set Product dimension List Type to MDX and input following query into the MDX box:
{[Product].[Product].[Accessories],[Product].[Product].[Bike Racks],[Product].[Product].[Bike Racks^483],[Product].[Product].[Bike Stands],[Product].[Product].[Bike Stands^486],[Product].[Product].[Bottles and Cages],[Product].[Product].[Bottles and Cages^477],[Product].[Product].[Bottles and Cages^478],[Product].[Product].[Bottles and Cages^479]}- Move Version dimension to Widget Columns
- Set Version dimension Subset to Test
Enable spreading:
- Click Close
- Toggle the Spreading option to ON
- Close cube settings popup
- Save the changes
Once spreading is enabled two additional right-click menu options will appear:
- Spreading (opens a separate dialog)
- Holds... ( leads to a sub menu)
1.6.1. Exercise
Change dashboard filters:
- Period default value to January
- Account to Retail
- Region to USA
Open spreading menu:
- Right-click on Last Year Accessories cell in the widget
- Select Spreading option
In the popup:
- Select Repeat All Leaves Method
- Select Replace Data Action
- Input 100 in the Value to be spread textbox
- Click Apply
All leaf elements should now be equal 100.
Spreading & Holds shortcut can be entered directly in the cell without using the right-click options or spreading dialog.
1.6.2. 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)
Data Action:
Spread Direction:
1.6.3. 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.
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 Sales Planning view (App Templates - Views - Sales Planning)
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.
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 Spreading widget from Exec Dashboard.
Follow the steps below:
- Click on the settings icon and then on then on the Spreading widget header
- Open the settings for Table Config and select Column format
- Click on the + icon next to Column Format
- Select Budget from the dropdown list and click + icon
- Open the Number Format dropdown
- Expand Negative, select - Minus and click Apply
All negative values in Budget column will be formatted using - sign instead of brackets.
Discard the changes.
Option 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
Number format can be also based on the Format attribute.
This way, the specified format will be applied for the element throughout the app, unlike the overriding of the TM1 format (shown in this section) which is valid only for this specific table.
The topic of table filters has already been covered in the Columns Filters section of the Basic Course. However, in this exercise we will configure filters to be applied by default when navigating to dashboard / view.
For the purpose of this exercise we will use Exec Dashboard and Spreading widget created in the section before.
Follow the steps below:
- Click on the Setting icon and then on the Spreading widget header
- Open CODE tab
- Copy and paste the code provided below and click Update button
- Save the changes and reload the page
- Notice that the filters are still visible
{
"table": {
"tableFilters": {
"conditionsStack": [
{
"column": 3,
"operation": "conjunction",
"conditions": [
{
"name": "gt",
"args": [
"0"
]
}
]
}
]
}
}
}Code overview:
"conditionsStack" : needed to determine filter conditions
"column" : the number of the column in the table for which you want to apply the filters, starting from 0
"conditions" :
- "name" condition short name
- "args" condition arguments + value
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:
- 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:
- Click on the Settings icon
- Expand Table Config option and select Column Format
- Click on the + icon next to Column Format
- Add Actual 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 Actual column changed and the cells are no longer editable.
Additionally ready only functionality can be based on an attribute.
Create ReadOnly attribute for selected dimension and add true or false value for chosen elements.
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.
Let's create a new widget to showcase this example:
- Navigate to Welcome to Apliqo UX dashboard by selecting it from the Content Store Settings menu
- Click the “+” button visible in the dashboard toolbar and choose Widget from the list
- Enter the widget title as "Set Cell Type"
- Turn On the Control Objects option
- Change the cube source to "}ElementAttributes_Retail Measure"
- Click Create button
- Open cube settings for Set Cell Type widget
- Add Retail Measure to Widget Rows
- Open Subset editor for Retail Measure
- Click All
- Select Date and Checkbox
- Click Apply
- Add }ElementAttributes_Retail Measure to Widget Columns
- Open Subset editor for }ElementAttributes_Retail Measure
- Click All
- Select CellType
- Click Apply
- Close cube settings popup
Let's see what will happen when we change the Cell Type:
- 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
- Save the changes
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.
1.11.1. New view creation
To showcase this, we will create a new view, called "adHoc column edit". Select Retail as the source cube for this view.
View configuration:
- Open cube settings and move:
- Product to Row Dimensions
- Year and Version to Column Dimensions
- Region, Currency, Retail Measure and Period to Global Fixed Settings
Now we will configure dimensions as follows:
- Region : Set the Selected Fixed Value to World
- Currency: Set the Selected Fixed Value to Local
- Retail Measure: Set the Selected Fixed Value to Sales Amount
- Period: Set the Selected Fixed Value to Year
- Product: Set the Subset to Product Categories
For Year and Version dimensions we will use asymmetric selection of elements in stacked dimensions.
Follow the steps below:
- Select Year dimension
- Change the List Type to MDX
- In the MDX textbox input the query: {{[Year].[Year].[2015]}*{[Version].[Version].[Actual]},{[Year].[Year].[2015]}*{[Version].[Version].[Budget]},{[Year].[Year].[2015]}*{[Version].[Version].[Last Year]},{[Year].[Year].[2016]}*{[Version].[Version].[Budget]},{[Year].[Year].[2016]}*{[Version].[Version].[Actual]},{[Year].[Year].[2017]}*{[Version].[Version].[Actual]}}
- Select Version dimension
- Change the List Type to MDX
- Clear the MDX textbox
- Close the cubes settings popup and save the changes
1.11.2. Enabling adHoc columns
To enable 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 Yes
- Collapse the advanced options panel
Notice that the new set of buttons appeared in the toolbar.
1.11.3. 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
1.11.4. 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
Configure default options for new column:
- Click on the Settings icon
- Open CODE tab
- Under "adHocColumns" add "columnDefaultElements":
{
"table": {
"adHocColumns": {
"columnDimensionButton": true,
"columnDefaultElements": {
"Year": {
"dimension": "Year",
"hierarchy": "Year",
"subset": "All N Elements",
"defaultElement": "2016"
},
"Version": {
"dimension": "Version",
"hierarchy": "Version",
"subset": "All N Elements",
"defaultElement": "Actual"
}
}
}
}
}- Click refresh button
- Save the changes
1.11.5. 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 2016 Actual will appear in the table)
- Click on the pencil icon for Version dimension and change it to Budget
- Click on the pencil icon for Year dimension and change it to 2014
- Select new column and click on the Duplicate button (new column 2014 Budget 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
All the views used in this course have been created and configured in Basic Course.
Exercises 2.1 to 2.2 will use view created in View Creation and Configuration section. To navigate to it open App Templates folder, choose Views and Sales Planning.
2.1.1. Dynamic Titles
In the Basic Course we already set a dynamic title for our widget, but it can be also done in other parts of the application e.g. with view title:
- 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
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.1.2. Using aliases
Dynamic title can also work for New Column in our view. We will configure it to be dependent on the selected filter value.
For column headers, to display alias instead of principal name, you must use the following formula: $<<(Instance_Name).(Dimensions name).(Hierarchy name)-alias>>
Follow the steps below:
- Right click on the inserted column header
- Select Edit Column Header from the list
- Enter the new title: $<<UX_Demo.Period.Period-alias>>
- Click Save
- Notice that the column title changed
- Change the Period global filter to Jan
- Make sure that the column title updated to Jan
- Save the changes
2.1.3. 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 formula: $<<(Instance_Name).(Dimensions name).(Hierarchy name)::attribute name>>
In this exercise we will retrieve values for Region dimension Color attribute.
For the attribute to show correctly in the frontend we need to enable the option called All Attributes.
This is how we enable it:
- Click on the Settings icon
- Select Filterbar option
- Set All Attributes to Yes
- Collapse the settings panel and save the changes
Then follow those steps (after reloading the page):
- Click on the view title
- Change it to: Sales Planning $<<UX_Demo.Region.Region::Color>>
- Change the Region global filter to Brazil
- Make sure that the title says: Sales Planning Green
- Change the title back to Sales Planning
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.
UX placeholders can be part of MDX expressions that are used when configuring dimensions.
In this example we will configure Version dimension (Sales Planning view) to dynamically change the column content depending on the selected Global filter.
Follow the steps:
- Open view Cube Settings
- Change the sub-view to Version
- Move Version dimension from Global Fixed settings to values to Global Filters and
- Change the Subset to Default
- Set the Default Value to Actual
- Select Version column dimension
- Change the List Type to MDX
- In the MDX text box input: {[Version].[Version].[$<<UX_Demo.Version.Version>>]}
- Close the Cube Settings popup
- Save the changes
We will use this MDX syntax: {[Dimension].[Hierarchy].[$<<UX_Instance.Dimension.Hierarchy>>]}
- Change the Version Global filter to Budget
- Make sure that the data as well as column header updated
All the views used in this course have been created and configured in Basic Course.
For the purpose of this exercise we will create new App and dashboard, but we will not configure it:
- In the Navigation bar click Add App button that will open Create app popup.
- Do the following configuration in the popup:
- Public option will be chosen by default, don't change it
- Enter the App title e.g. “New App Folder”
- Create With Default Settings option should say YES
- Click Create button
Publish the New Dashboard.
Application gives us the option to edit the navigation bar.
While in edit mode, the Edit Navigation button appears in the Navigation Bar.
- Click Edit Navigation button
- Expand both of the folders visible in the popup
You can rename and reorder elements in the Navigation Bar. Adding icons is also possible, just like in Edit Wizard Steps popup described in Wizard Creation and Configuration section in Basic Course.
- Click New App Folder title and change its name to e.g. "Advanced Exercises"
- Click New Dashboard title and change its name to e.g. "Exercise 1"
- Select new icon for "Exercise 1"
- Drag & drop "Advanced Exercises" folder at the top of the list
- Drag & drop Wizard folder at the top of the list inside "App Templates" folder
Apply button is displaying number of changes made in real time.
- Click Apply
- Click See more to review the changes
- Click Save
Make sure that the changes were applied.
Navigation Edit popup is a powerful tool, but not all changes like Duplicate, Move & Delete can be made with it just yet. In this section, we discuss how we can tackle this.
3.2.1. Duplicate
- Open Advanced Exercises folder
- Click "+ Add"
- Do the following configuration in the popup:
- Public option will be chosen by default, don't change it
- Choose Duplicate current view option
- Enter the title e.g. “Exercise 2”
- Click Create button
Make sure that the elements are now available under Advanced Exercises folder.
We will not Publish this dashboard:
- Click Delete
- Check Yes, delete permanently
- Click Yes
- Make sure that the Exercise 2 dashboard is no longer visible under the Advanced Exercises folder
Views and Wizards can also be duplicated.
3.2.2. Move
Now we will move existing dashboard to a different folder:
- Navigate to Exercise 1 dashboard by selecting it from Advanced exercises folder
- Click on the 3 dots icon visible in the page toolbar
- Choose Move from the list
Do the following configuration in the popup:
- Open Select Parent drop-down list
- Choose Dashboards
- Click Move
- Check if the Exercise 1 dashboard is now visible in App Templates folder and Advanced Exercise folder is empty
Views and Wizards can also be moved.
3.2.3. Delete
Last thing for us to practice is deleting dashboard and app from the navigation bar:
- Click on the 3 dots icon visible in the Exercise 1 dashboard toolbar
- Choose Delete from the list
- Check Yes, delete permanently
- Click Yes
Make sure that the Exercise 1 dashboard is no longer visible under Dashboard folder.
In the navigation bar we can see an empty folder Advanced Exercises, we can delete it:
- Open Setting drop-down list
- Choose App management
- Click 3 dots icon for Advanced Exercises folder
- Choose Delete
- Check Yes, delete permanently
- Click Yes
Make sure that the Advanced Exercises folder is no longer visible in the navigation bar.
To make the navigation in the app easier and the transition between different reports swifter, we can configure Next and Back button. Both of them will appear in the toolbar.
4.1. Configuration
Follow the steps below:
- Navigate to Sales Planning view by selecting it from App Templates - Views folder
- Enable the edit mode
- Click on the Settings icon
- Click on the + icon next to the Filter Bar option
- Click on the + icon next to the Back & Next Buttons option
- Select Next button
- Input name for the Next button in the Label textbox e.g. Test button
- Select Button Class from the dropdown list e.g. Info (it's not mandatory)
- Chose Next button icon (also not mandatory, by default the right arrow will appear in the button)
- Click on the + icon next to Next option to set a target view for our button
- Select Target View
- Click on the plus icon
- Select target view from the dropdown list e.g. Exec Dashboard
- Save the changes
- Click on the Next button visible in the toolbar
- Notice the the app correctly navigated to the Exec Dashboard
Configuration for the Back button is done in exact same way. It will appear on the left side of the filterbar.
When creating the buttons, users are not limited to the default options available in the GUI.
Each button can be changed and customized further using html code.
Let's take a look at this example:
- Navigate back to Sales Planning
- Open again the advanced options for Next button
- Clear the Class and button Label
- Paste the code provided below into the Html textbox
- Click Update button and notice how the button changed
Discard the changes.
<i class="fas fa-arrow-right fa-lg"; style="color:green">
<span style="color:green">Test button</span></i>Code overview:
<i class="fas fa-arrow-right fa-lg"; style="color:green">
- used to add bigger arrow icon and change its color
<span style="color:green">Test button</span>
- used to change the label color
5.1.1. Create new dashboard
For the purpose of this exercise we will create new dashboard.
Follow the steps below:
- Open App Templates folder
- Choose Dashboards folder
- Inside will appear "+ Add public" option, click it
- In the popup do the following configuration:
- Change the Type by choosing Dashboard from the drop-down list
- Enter the dashboard title e.g. "Waterfall Chart"
- Change Cube Source by choosing General Ledger_C3UX from the drop-down list
- Click Create button
After clicking the create button, the application will automatically redirect to the new view.
- Publish the dashboard
- Change the New Widget title to e.g. "Grid"
- Open the Grid widget Cube Settings
To configure widget data follow the steps below by drag & dropping:
- Region dimension to the Dashboard Filters
- Department dimension to the Dashboard Filters
- Account dimension to the Dashboard Filters
- Currency dimension to the Dashboard Fixed Values
- General Ledger Measure dimension to the Dashboard Fixed Values
- Version dimension to the Widget Columns
- Account dimension to the Widget Rows
- Year Month dimension to Time - Slider Filters
Now we will configure dimensions as follows:
- Region
- Choose Default from the Subset drop-down list
- Set the attribute to Code and Description
- Department
- Choose Default from the Subset drop-down list
- Set the attribute to Code and Description
- Account
- Choose Net Income from the Subset drop-down list
- Set the Attribute to Description
- Set the Default Element to Retail
- Currency
- Set the Selected Fixed Value to Local
- General Ledger Measure
- Set the Selected Fixed Value to Amount
- Version
- Change the List Type to MDX
- In the MDX text box write: {[Version].[Version].[Bud VS LY^Budget],[Version].[Version].[Actual]}
- Set the Attribute to Description
- Account
- Choose Net Income 1 from the Subset drop-down list
- Set the Attribute to Description
- Year Month
- Set the Attribute to Caption
- Set the Value to 2016
- Set the Value MAX to 2018
After finishing the configuration close Cube Setting popup and make sure that Grid widget displays data. Save the changes.
5.1.2. Add new widget
We can do that from the dashboard level:
- Click the “+” button visible in the dashboard toolbar and choose Widget from the list
- Enter the widget title as "Waterfall"
- Change the cube source to "General Ledger_C3UX"
- Click Create button
Set up the Waterfall chart:
Click on the Cube icon visible in the Waterfall widget header and configure the dimensions.
- Drag and drop the Version dimension to the Widget Rows and:
- Choose Element Comparison from the List Type drop-down list
- Set the value to Actual
- Set the value Max to Budget
- Drag and drop the Account dimension to the Widget Columns and:
- Choose Net Income 1 from the Subset drop-down list
- Set the attribute to Description
- Change the widget type to Charts Waterfall
- Close the Cube Settings popup
- Make sure that Waterfall widget is displaying correct data in a form of a waterfall chart
- Save the changes
- Adjust the page layout using Flexible Layout (functionality discussed in Basic Course in Flexible Layout functionality section)
- Save the changes
5.1.3. 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.
5.1.4. Y-axis scaling
Turning this option on will change the minimum value for the y-axis.
- 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
5.1.5. Hyperlinks
The same as in the grid widget (Basic Course - Turn on Hyperlinks) 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
- Turn ON the option HyperLink to change filters
- Close the Cube settings popup
- Click Taxes element
- Make sure that the Account dashboard filter changed to Taxes
- Save the changes
5.1.6. 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
- Right click on the Operating Expenses cell in Budget column
- Choose Comments from the list
- In the text box write e.g. 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.
View used in this exercise has been created and configured in Basic Course in View Creation and Configuration section. To navigate to it open App Templates folder, choose Views and Sales Planning.
All charts added in the application can be customized using a large number of advanced options, the most popular will be covered in this section.
- Turn on the Edit Mode
- Open Overview popup for Accessories element
- Open widget type selection menu
- Click on the slider visible in Column type
Another example of modifying already existing chart is to display one element in another form e.g line instead of a column.
In this exercise, we will configure the Unit Price element like this:
- Click on the settings wrench icon and then on the 'Orders phasing' widget header
- Expand Chart Options and select Series
- Click on the + icon next the the Populate option
- Now all TM1 elements are displayed on the list and can be edited
- Open Type dropdown list for the Unit Price element and choose line
In the same Series tab you can also change the color, enable & configure data labels.
Close the Overview popup and save the changes.
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:
- open Cube Settings for the KPI widget
- change the widget type to 'KPI Trend'
- 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.
0 Comments
Add your comment