Add/Remove measures at run time to a crosstab component

[Former Member]

Hi Experts,

I have a scenario in Design Studio where I need to assign measures at run time to Crosstab component from same data source.

Our BEX query has SalesValue TY, SalesValue LY, SalesQty TY, SalesQty LY measures.

When I select Value from drop down, I would like to display SalesValue TY and SalesValue LY measures in Crosstab component and when I select Qty from dropdown, I would like to display SalesQty TY and SalesQty LY measures from the same source in same crosstab component.

I do not want to have two data sources and two crosstab components and hiding/showing them at run time.

I am able to add dimensions to crosstab component at runtime by using below two methods.

DS_1.moveDimensionToColumns(dimension, position?)

DS_1.moveDimensionToRows(dimension)

Did any one tried add/remove measures at runtime to Crosstab component? I know we need to filter measures of data source at run time but not finding any suitable methods.

Please guide me.

Thanks,

Satyam

[MustafaBensan · Accepted solution]

Hi Satyam,

To be specific, you would need to code the "On Select" event script of the dropdown list to look similar to the following:

if (me.getSelectedValue() == "V") {

// Filter Value Measures

  DS_1.setFilter("006EIC2OSTY2YAK2X13HZAWTE", ["006EIC2OSTY2YAK2X13HZB34Y", "006EIC2OSTY2YAK2X13HZB9GI"]);

}

// Filter Quantity Measures

else {

  DS_1.setFilter("006EIC2OSTY2YAK2X13HZAWTE", ["006EIC2OSTY2YAK4A4L7YHZFM", "006EIC2OSTY2YAK4A4L7YI5R6"]);

}

In the setFilter() method, the first parameter represents the ID of the Measure Dimension.  The second parameter is an array of the Measure Dimension Member IDs for the Value measures in the first case and Quantity measures in the second case.

The result is as follows:

Regards,

Mustafa.

[Former Member · Accepted solution]

Hi Satyam,

As Mustafa has pointed out setFilter() is the available option. Also please put KFs in  BEx query in the required order in which you want to see in output. Because you dont have an option to change the order in script.

Thanks & Regards,

Sivakami

[Former Member · Accepted solution]

Hi Mustafa,

Thank you for the detailed steps. It is working. I am able to filter measures using script.

Thanks,

Satyam

[former_member544090 · Accepted solution]

Hello Mustafa,

Thanks for such nice explanation.I have tried similar scenario and for me it is not working completely.

I have taken a drop down where two values are there 1. Quantity 2. YTD
According to requirement when Quantity is selected cross tab should display Quantity PY measure and when YTD is selected cross tab should display Quantity YTD measure.

When i execute the dashboard it runs fine initially Quantity PY is displayed and when i change selection to YTD it displays Quantity YTD. But when if I again switch to Quantity Cross tab doesn't change measure to Quantity PY.

This is the code I have written on Drop down.

var DD=DROPDOWN_2.getSelectedValue();

if(DD=='Quanitity')

{

DS_4.setFilter("00O2THXFHVWEFQRKJEE365OPT", ['00O2THXFHVWEFREV8ZI65WNZ5']);
TEXT_4.setText(DD);

}

else
{ DS_4.setFilter("00O2THXFHVWEFQRKJEE365OPT", ['00O2THXFHVWEFRENUXEKV7ZHD']);
TEXT_4.setText(DD);
}

Please guide me in this scenario. Thanks a lot.

Regards,
Rafat

[IngoH]

Hello Satyam,

have you considered a filter option and - depending on the option that was selected - to set filters on the measure dimension ?

regards

Ingo Hilgefort, Visual BI