This time, we will explain in greater detail how to dynamically shape the filter context so that relationships within the data and external factors, particularly the current date and the current user, are taken into account.

We will concentrate on two use cases:

a) dynamically restricting the selection options and

b) dynamic standard selection (default selection).

In other words, this is about two questions: Which members should be available to users for selection in Presentation Mode, and which member should be automatically selected as default when the user opens the report for the first time? Or, in figurative terms: Which entries should DeltaMaster offer in the filter bar menu and which of these entries should already be selected from the start?

In both cases, the dynamic aspect comes from the fact that the members for selection are not taken over solely from the analysis model or explicitly defined in Modification Mode; instead, DeltaMaster only determines which members should be made available or selected automatically when a report is used in Presentation Mode.

The selection options and the default selection can be described using a database query in the MDX query language (for OLAP databases) or SQL (for relational databases). We will turn our attention to the relational example in the next issue. This time, however, we are looking at MDX. As you will have gathered from these keywords, this is a matter for report editors and application administrators. The following section contains a summary for report recipients. Editors and administrators should read on – because what we are about to tell you will help you make things a whole lot easier for the recipients of your reports.

Management summary

DeltaMaster reports can be configured so that the filter options and default filters in Presentation Mode automatically adapt to changing data situations and use conditions. This makes it easier to use the application and helps to reduce the number of reports. The following relationships can be taken into account in particular.

a) Relationships between filters and measures

Example: In the time dimension, only months with values for actual revenue should be available for selection in the current report. Future months that only have target revenue values should be unavailable for selection.

b) Relationships between filters and other filters

Example: In the product dimension, only product groups or products for which the current sales team is responsible should be available for selection. The current sales team is the sales team selected in another filter property.

c) Relationships between filters and the current date (system time)

Example: In the time dimension, the current month should be selected automatically.

d) Relationships between filters and the respective user (name)

Example: When the user “chairbaumann” opens a report, the member “Baumann” should be selected in the sales team dimension; when the user “chairhohlmaier” opens the same report, the member “Hohlmaier” should be selected.

Other criteria from within and outside the DeltaMaster application can also be taken into account, including anything yielded by the query languages; however, the date and the user name are particularly common examples. The mechanism can also be used for folders, as discussed in DeltaMaster clicks 154 (08/2017); in this issue, we will focus solely on reports.

Settings: in filter context

Report editors make necessary adjustments to reports centrally on the Filter Context tab in the Report Properties (report context in the report list).

The context menu for Selection below the hierarchy levels allows you to Restrict Selection by MDX Expression. The option above this allows you to Restrict the Selection. But this is a static selection, meaning that the members offered in Presentation Mode are defined individually or on a level-by-level basis and made available identically in every situation. However, MDX is required for dynamic definition.

The second setting discussed here, Default Selection, can also be found on the Filter Context tab via the hierarchies’ context menu. A static variant without MDX is not available – and not necessary, as it would be nothing more than the report filter (view), i.e. the combination of selected filter properties with which the report was saved.

Multidimensional expressions

In both cases, the MDX expression is created and edited using the DeltaMaster MDX Editor. The requirements for the results returned by the query are also the same: DeltaMaster expects a member or a set of members from the respective dimension. A set, i.e. multiple members, is typical for a limited selection. The default selection is usually exactly one member. The default may also be a set; this corresponds to a multiple selection.

To express the desired dependencies, in the MDX expression you should reference objects or conditions outside the respective dimension. The following constructs are typically used for the four relationships described above. The MDX examples are based on Microsoft SQL Server Analysis Services.

• Measures
  To take certain measures into account, e.g. revenue or gross margin, enter the corresponding members from the measure dimension. For example: “[Measures].[Revenue]” or “[Measures].[GM]”. In the MDX Editor, all available measures are shown in the Measures branch of the structure tree at the bottom left. Double-click or drag and drop to add them to the MDX expression in the upper section of the editor.

• Selection in other dimensions
  To reference member(s) selected in another dimension, use the MDX function “CurrentMember”, e.g. “[Sales].[Sales].CurrentMember”, or the short keys (variables) in DeltaMaster, e.g. “”, where 13 is the ID of the sales dimension. The ID of a dimension can be found in the structure tree at the bottom left of the MDX Editor by holding down the Alt button and moving the mouse pointer to the dimension.

• Date
  MDX functions can be used to read the system date, convert it into a string, and use this string to select or to reference members. This is accomplished by an expression like this:

  StrToMember(“[Period].[Period].[” + Format(Now(), “MMM yyyy”) + “]”)

  Take care to ensure that the period format is correct. If it does not already match the system date formats, it must be transformed accordingly. For example: In German, March (“März”) is abbreviated as “Mrz”, not “Mär”; consequently, the month in the database must have the same format.

• User name
  The name of the current Windows user can be read in the same way. The following expression uses MDX functions to read it, strip the domain name (before the slash), then use it to select a member in a dimension that contains the system users.

  StrToMember(“[User].[User].[” + Mid(UserName(), InStr(UserName(), “”) +1) + “]”)

  In this example, the user name is used to select a dimension member directly. Alternatively, it can be used in conditions in order to select certain members, e.g. in an organization or customer dimension, depending on the user name.

When you restrict a selection while also setting a default member, this default member should be included in the selection so that the user can switch back to the default member after modifying the filters. Although these two settings are technically independent of each other, i.e. it would be possible to set a default member that cannot be selected by the user interactively, this makes it harder to use and is generally not advised.

The default selection should not reference any dimensions for which a default selection is also defined (i.e. no multi-tiered default selection).

Effect in Presentation Mode

The selection restriction and default selection only have an effect in Presentation Mode, like the filter context as a whole.

When a selection is restricted, DeltaMaster automatically adds the parent members (aggregated members at higher levels). For example, if the MDX expression only returns months, DeltaMaster also displays the corresponding quarters and years automatically.

The queries stored as default selections are run as soon as the user opens a report or a folder for the first time – and only this once. In doing so, DeltaMaster sets the filters according to the query results and retains them until the user modifies the filters or ends the session. As such, the reports behave in the same way as if a report editor had set and saved the filters like this. Users in Presentation Mode will not notice any difference, but they will appreciate the fact that reports always come with the “right” settings as default.

Example

Using the information on the structure of MDX expressions, we can suggest solutions for the tasks described above in our reference use case, “Chair”.

a) Restrict periods to months with actual revenue

The following expression restricts the time dimension so that only those periods containing revenue of greater than zero for the respective value type are available for selection.

Filter( [Period].[Period].[Month].Members,
([Measures].[Revenue], [ValueType].[ValueType].[ValueType].[Actual]) > 0 )

In principle, such filter expressions could also take analytical criteria into account, e.g. the ten biggest customers or only customers above a certain minimum revenue. However, typically, criteria like these should be put in the Axis Definition of the report rather than the filter selection.

b) Restrict products to selected sales team

The following expression restricts the product dimension so that only those products for which the current sales team is responsible are available for selection. The allocation of products and sales organizations is not mapped in this model but is implied by reference to which team generated revenue from which products.

Filter( [Product].[Product].[Product].Members,
([Measures].[Revenue], [ValueType].[ValueType].[ValueType].[Actual], [Period].[Period].[All Years],
<view13>)
> 0 )

As discussed above, “<view13>” stands for the member currently selected in the sales dimension; “[Sales].[Sales].CurrentMember” could also have been used.

This expression requires exactly one member to be selected in the referred sales dimension, not multiple members. To ensure that this is the case, Multi Selection should be additionally disabled for this dimension in the Filter Context (dimension context menu). In this example, the time reference is defined broadly to include all years; however, this could also be dynamically linked to the current report filter.

c) Set current month as default selection

The following expression, which is also shown above, makes DeltaMaster select the month corresponding to the current system clock date in the time dimension when the application is opened.

StrToMember(”Period].[Period].[” + Format(Now(), ”MMM yyyy”) + ”]”)

Where the current period for a report is derived directly from the system date as in this example (and is not used as part of a condition, e.g. in larger expressions), DeltaMaster has offered an alternative since version 6.1.6:

In Graphical Tables where the Current Magic Button is enabled, DeltaMaster automatically sets the period every time the report is displayed – as defined in Modeling on the Logic page.

The option with the suffix “Current Period” causes a dynamic behavior, so that the current period automatically reflects the system date.

Evaluating the system date is especially practical for operational analyses and reports. However, this does not take into account whether the month is closed for accounting purposes. On closer inspection, it is clear that there may be differing interpretations as to what exactly constitutes the current month. One common variant is to use the last month for which (actual) revenue is recorded. This removes the need to update the report whenever newer data becomes available; instead, the report identifies the latest period autonomously in a data-driven manner. This enables the following expression.

Tail( Filter( [Period].[Period].[Month].Members,
([Measures].[Revenue], [ValueType].[ValueType].[ValueType].[Actual]) > 0 ), 1)

The MDX function Tail returns the specified number of members or tuples at the end of the specified set. As only the last member is queried, the number of tuples is 1; the set is a filter expression that returns months with actual revenue of greater than zero.

d) Set current user’s sales team as default selection

The following expression is similar to b) with the difference that the current sales team is determined not by the setting in the filter bar, but by reference to the Windows user name of the current user.

Filter( [Product].[Product].[Product].Members,
([Measures].[Revenue], [ValueType].[ValueType].[ValueType].[Actual], [Period].[Period].[All Years],
StrToMember(“[Sales].[Sales].[” + Mid(UserName(), InStr(UserName(), “”) +1) + “]”))
> 0 )

If there is no matching member for the user name, this expression returns an error – one, however, DeltaMaster can deal with: by selecting the first member of the top permitted level in the filter context as the default selection; this is often the “All” member. If nothing else, this ensures that the default setting for the report works even if the request cannot be fulfilled.

Tips for report editors

What makes MDX expressions so appealing is that they respond very flexibly to the respective settings and help bring reports to life. With regard to report development, the following methods have proven effective.

• Prepare query in Axis Definition
  Start by editing the required MDX expression for the filter context in the Axis Definition of a Graphical Table. This makes it easier to evaluate the result sets in changing conditions.

• Save and reuse query as Named Set
  Named Sets can be used to maintain and reuse queries in a central location. For example, if you have defined a default selection based on a user name or date, you will probably want to reuse it in multiple reports, and you may wish to use a defined set of members in the Axis Definition as well as in the Filter Context. All you have to do when Editing is enter the name of the set as the MDX expression for the selection restriction or default selection. The maintenance of Named Sets forms part of Modeling: With DeltaMaster 6.1.6 and above, Named Sets can be edited via the corresponding item in the Model menu (right).

• Define report as start report
  As stated above, the default selection query is calculated only when a report is opened for the first time. This means it is not enough to switch from Modification Mode to Presentation Mode or recalculate the report when developing and testing; instead, the application must be (re)opened in order to experience the default selection in action. When testing, you may find it easier to define the report you are working on as the Start Report (Report Properties, General tab), at least temporarily, so that it is always displayed automatically when the application is opened and you can review the default selection in the filter bar.

And perhaps the most important tip of all: If you have any questions, please don’t hesitate to contact our support team and your contact persons!