Filter on dimension at run time

[Former Member]

Hello all,

I am creating a dashboard where there is new data being posted to our key figures. Each addition to the data is identified with a number.

For example:

Instance #	Value 1	Value 2
1	100	90
2	125	150
3	130	120

I am displaying the values in a Geomap. By default, the users want to see the values on the map for the most recent instance, but have the option to see prior instances. For example, when launching the dashboard after Instance #3 has been loaded to BW, they would like to see the values 130 and 120 on the map. Once Instance #4 has been loaded, they would want to see the values for that instance on the map when refreshing or launching the dashboard. 

So far, I tried creating a second BEx query with a condition such that it selects the Top 1 of Value 1 (which will increase with each instance). Then I created a restriction on my first BEx query using Replacement Path for the Instance #. This worked, in that my first query was now filtered to show only the most recent Instance. However, once I launched the dashboard, I was unable to change the filter on Instance to see a prior instance.

My question is - how can I dynamically set up the dimension filter for Instance # so that it choose the last value by default? We are currently using Design Studio 1.6, SP0.

Thank you!

Beth

[MustafaBensan · Accepted solution]

Hi Beth,

As I understand it, you'd like to display the last instance as a default but still allow the user to select from the other instances.  I think the reason why you currently can't filter on other instances is because the result set is restricted always to the last instance via the replacement path from your second query.

I was able to achieve the desired result with only a single query having a Top 1 condition.  I'm not sure why you need two BEx Queries?

In my example I have a Top 1 condition which is displayed as the default as shown below:

Using a Dimension Filter component I can still select from the other dimension members as shown below despite the active Top 1 condition:

After selecting another member value the corresponding filtered result is displayed as desired:

If you could elaborate a little more I'll try to propose further suggestions.

Regards,

Mustafa.

[Former Member · Accepted solution]

Hi Mustafa,

I tried your example, and it does work. There is a slight complication that I hadn't made clear in my simplified example. To make it a little clearer, here is some more detail:

Instance	Region	Value 1	Value 2
1	A	100	130
1	B	200	120
1	C	125	140
2	A	120	130
2	B	210	135
2	C	130	150

In the condition for my BEx query, I set it equal to the Top 1 of Value 1, which would result in the line in red and bold above. This condition would also then filter the data to select the last instance. My thought had been to then pass the selected Instance as a filter to a secondary query, and to get all three regions for Instance 2 (in this example) as a result.

Ultimately, what I want to do is filter the data on the last (or max) Instance and have the result include all regions.

Thank you,

Beth

[MustafaBensan · Accepted solution]

Hi Elizabeth,

You could try an approach like this:

1)  In your BEx Query, create a Formula Variable based on dimension Instance

2)  Create a Calculated Key Figure based on the formula variable and set Calculate Single Values as Maximum

3)  Apply the getDataAsString() method to obtain the value of the Calculated Key Figure in 2

4)  Apply setFilter("Instance", value from Step 3)

Regards,

Mustafa.

[Former Member · Accepted solution]

Hi,

I think you could just continue with the Top 1 condition on Value 1 approach. Execute the data source initially and use DS_1.getMembers(<Instance>,1) to get the only instance in the data source result set. Store it in a global variable and then deactivate the condition using DS_1.setMeasureFilterActive(<Top 1 condition ID>, false) and apply the filter on Instance dimension.

Regards,

Swapnil Koti

[MustafaBensan · Accepted solution]

Hi Swapnil,

getMembers() is independent of the data source result set.  It is based on the master data sort order, so DS_1.getMembers(<Instance>,1) will not necessarily return the latest instance, even when the Top 1 condition filter is active.  It will most likely return the first instance.

Regards,

Mustafa.

[Former Member · Accepted solution]

Hi,

Yes you are right Mustafa. I think when I checked with an example it looked like it worked because both my top 1 and the 1st member of dimension coincidentally were same.

Thank you.

Regards,

Swapnil Koti

[Former Member · Accepted solution]

Thank you, Mustafa - that worked well!