Skip to Content
0

How do I pass an external key value from a BEX Query to a Universe?

Mar 07 at 10:16 PM

141

avatar image
Former Member

I have a DROPDOWNBOX that has items from a data source (BEX Query). Let's say it contains city names. I need to pass the external key of the selected city to a data source (universe) that has the same cities and associated keys to fill some LISTBOXes. When I try to do it I get a warning "All selections for dimension City are invalid and have been discarded". How do I fix it?

Code:

// set filter on universe
DS_1.setFilterExt("_RogQIRwAEeiofrGGUX0vqg",DROPDOWN_1.getSelectedValue());
var members = DS_1.getMembers("_RozLEBwAEeiofrGGUX0vqg",400);
LISTBOX_1.removeAllItems();
members.forEach(function(m,mi) {
   LISTBOX_1.addItem(m.externalKey,m.text;
});
LISTBOX_1.sort();
LISTBOX_1.addItem("","All Cities",0);
LISTBOX_1.setSelectedValue("");
10 |10000 characters needed characters left characters exceeded
* Please Login or Register to Answer, Follow or Comment.

3 Answers

Mustafa Bensan Mar 08 at 08:58 AM
1

Hi Eric,

Can you provide screenshots of the following?:

1) The initial view of the BEx Query data source you are using to populate the Dropdown, showing the key and text values of the dimension members to be used for filtering;

2) The initial view of the Universe data source showing the key and text values of the dimension "_RogQIRwAEeiofrGGUX0vqg".

You need to make sure that the format of the key values from the BEx Query match the format of the corresponding Universe dimension values.

Also, how are you populating the Dropdown Box?

Regards,

Mustafa.

Show 10 Share
10 |10000 characters needed characters left characters exceeded
Former Member

Bex Query initial view:

Universe initial view:

Loading DROPDOWN_1:

DS2.loadDataSource();
DS2.setVariableValueExt("ZFYR_MULTI_REQ","2016;2017;2018");
DROPDOWN_1.setItems(DS2.getMemberList("0BUS_AREA",MemberPresentation.INTERNAL_KEY,
MemberDisplay.KEY_TEXT,1000));
DROPDOWN_1.sort();
0

Hi Eric,

You have re-posted screenshots of the data source initial views but haven't answered my latest question about the setting of the Members for Filtering option of Dimension BA_Key. This will have an impact on the filtering behaviour.

Regards,

Mustafa.

0
Former Member

Sorry, over looked that.

Universe

0

You should change the setting to “Only Values with Posted Data”.

0
Former Member

I made the change. The results still are not getting filtered by the selection.

untitled.png (20.4 kB)
0

Hi Eric,

As the next step, please also apply the same Members for Filtering option of "Only Values with Posted Data" to each of the Dimensions used to populate the six listboxes.

Regards,

Mustafa.

0
Former Member

Changed the filtering and it still not actually filtering the values based on the selection.

0

I didn't think to ask before but what version of Design Studio are you running?

0
Former Member

Turns out the universe was created without BA_Key as a prompt. The Devs fixed it and it's working as expected. Thanks for all your help and more importantly the timely replies!

0

Thanks for the follow-up, although I’m surprised that a BA_Key prompt was required as I thought you were using setFilter() instead of setVariableValue()?

0
Mustafa Bensan Mar 09 at 11:10 PM
1

Hi Eric,

I suggest you change your code as follows:

//set filter on universe
DS_1.setFilterExt(<Technical ID of BA_Key>,DROPDOWN_1.getSelectedValue());

Regards,

Mustafa.

Show 6 Share
10 |10000 characters needed characters left characters exceeded
Former Member

That's the code I'm using. I get the technical ID from the code completion popup. It still will not accept the BA_Key (Business Area). Here's the actual warning:

untitled.png (6.9 kB)
0

Hi Eric,

Based on the code and initial view screenshot you have provided, you are currently filtering the universe using dimension Business Area instead of BA_Key. You need to change your script to specify the Technical ID of BA_Key. Currently the script is actually referencing the Technical ID of Business Area, likely resulting in the error you are seeing.

Regards,

Mustafa.

0
Former Member

You were right about the business area. However when I changed the code....

DS1.setFilterExt("_kw5_4CJMEei8v-F1mSlcLw", DROPDOWN_1.getSelectedValue());

And this is what happens

untitled.png (8.0 kB)
0

Hi Eric,

Can you try the following code changes?:

1) In the "On Startup" script:

DROPDOWN_1.setItems(DS2.getMemberList("0BUS_AREA", MemberPresentation.INTERNAL_KEY,  MemberDisplay.KEY_TEXT, 1000));

2) "On Select" of Dropdown:

// set filter on universe
DS1.setFilter("_kw5_4CJMEei8v-F1mSlcLw", DROPDOWN_1.getSelectedValue());

Regards,

Mustafa.

0
Former Member

Changing the code to use internal key got rid of the error/warning but it doesn't appear that the universe data source is actually being filtered.


//APPLICATION.onStartUp()
DS1.loadDataSource();
DS2.loadDataSource();
DS2.setVariableValueExt("ZFYR_MULTI_REQ","2016;2017;2018");
DROPDOWN_1.setItems(DS2.getMemberList("0BUS_AREA",MemberPresentation.INTERNAL_KEY,
	MemberDisplay.KEY_TEXT,1000));
DROPDOWN_1.sort();

//DROPDOWN_1.onSelect()
var key = ["_RozLEBwAEeiofrGGUX0vqg","_Ro88EBwAEeiofrGGUX0vqg","_Ro88EhwAEeiofrGGUX0vqg",
	"_Ro88ERwAEeiofrGGUX0vqg","_Ro88ExwAEeiofrGGUX0vqg","_7JVtUCCSEeiKUtfvVqLgog"];
DS1.setFilter("_kw5_4CJMEei8v-F1mSlcLw",DROPDOWN_1.getSelectedValue());
DS1.reloadData();
count = count + 1;
TEXT_1.setText(TEXT_1.getText() + "Selection No " + Convert.floatToString(count,"0") +
	" " + DROPDOWN_1.getSelectedText() + "\n");
[LISTBOX_1,LISTBOX_2,LISTBOX_3,LISTBOX_4,LISTBOX_5,LISTBOX_6].forEach(function(b,i) {
	var members = DS1.getMembers(key[i],400);
	b.removeAllItems();
	members.forEach(function(m,mi) { 
	b.addItem(m.externalKey,m.text); });
	b.sort();
	b.addItem("","All Values",0);
	b.setSelectedValue("");
	TEXT_1.setText(TEXT_1.getText() + "Listbox " + Convert.floatToString(i+1,"0") + 
		" item count: " + Convert.floatToString(members.length,"0" + "\n"));
});
TEXT_1.setText(TEXT_1.getText() + "\n");
untitled.png (52.4 kB)
0

In the Initial View Editor for data source DS_1, have you set the Members for Filtering option of Dimension BA_Key to "Posted Values Only"?

0
avatar image
Former Member Mar 08 at 04:34 PM
0

BEX Query initial View

Universe Initial View

Business Area (City) in the BEX Query corresponds to the BA_Key (City) in the universe.

The items For DROPDOWN_1 get loaded via the following code during the application's "On Startup" event.

DS2.loadDataSource();
DS2.setVariableValueExt("ZA_BUSAR",exclusion);
DS2.setVariableValueExt("ZFYR_MULTI_REQ","2016;2017;2018");
DROPDOWN_1.setItems(DS2.getMemberList("0BUS_AREA", MemberPresentation.EXTERNAL_KEY,  MemberDisplay.KEY_TEXT, 1000));
DROPDOWN_1.sort();

Share
10 |10000 characters needed characters left characters exceeded