Best practices for filtering widgets from different cubes in InterSystems DeepSee

Question

Question

Evgeny Shvarov · Apr 9, 2017

#Dashboards #MDX #InterSystems IRIS BI (DeepSee)

Hi!

Sometimes I need to filter the widget on a dashboard from a different cube. And I face the following problem:

Widget A refers to a query from Cube A and I want to filter Widget B from Widget B.

Widget's B pivot refers to Cube B, and which has different dimensions for the same data.

E.g. cube A has the dimension Author and the Cube B has the dimension Member for the same data. So there is no way to filter such a widget B from the widget A.

Actually, once we filter a given widget B with another widget A, we add the Filter Expression to the MDX query which looks like member's expression from Cube A, like:

[Outlet].[H1].[Region].&[Asia]

Is there any way to alter the filter expression for Widget B, just changing the value of the last part (Asia in this case) of the filter expression?

Discussion (1)0

Log in or sign up to continue

Alessandro Marin · Apr 9, 2017

One way to do this is by using a pivot variable. Create the same pivot variable "Region" in both pivots on which your widgets are based. These pivot variables should return the members, in your example Asia, Europe, N. America, S. America. You can define the manually or in a termlist, or use a kpi to retrieve them.

For the example in the screenshot below I created a HoleFood2 cube with a Outlet2.H1.Region2 level. This level is "incompatible" with an Outlet.H1.Region level in HoleFoods. In my manual Region pivot variable I simply defined two regions, which can be selected manually.

Once you have these two pivot variables create a calculated dimension on each pivot using the pivot variable. In your example in HoleFoods the expression should be Outlet.[$variable.Region]. Place the calculated dimension on Filters.

This is how I did it in HoleFoods:

and this is how I did it in HoleFoods2:

Finally, add an ApplyVariable control on one of your widgets with "*" as target. Selecting a region will filter both widgets.

2 0