Filter on dimension at run time
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.
|Instance #||Value 1||Value 2|
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.
Mustafa Bensan replied
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)