Skip to Content
0

SAP Design Studio passing values to DB

Feb 01, 2017 at 03:49 PM

326

avatar image

Hi,

We are building a Design Studio dashboard using a UNX as the backend. The version is 1.6SP4.

We have a requirement whereby the values of a drop-down filter are to be passed to the Database as a where condition in the query.

This way instead of loading the datasource and filtering the values within Design Studio using Setfilter function, we intend to restrict the amount of data that is fetched into Design Studio application in the first place by passing the value of the dropdown as a where condition to the DB (using prompts in query panel)

We tried using the setVariable and setVariableext function and then loading the datasource, but the passing of values to database did not work.

Has anyone accomplished the same in the past?

10 |10000 characters needed characters left characters exceeded
* Please Login or Register to Answer, Follow or Comment.

8 Answers

Tammy Powlas
Feb 01, 2017 at 04:44 PM
0
Share
10 |10000 characters needed characters left characters exceeded
Alok Soni Feb 06, 2017 at 05:38 PM
0

Hi Tammy,

Thank you for the response. I did try that however the situation is a little different. This article basically sets filter on retrieved data but does not pass values to DB itself by loading/re loading Datasource which is what I am after.

Share
10 |10000 characters needed characters left characters exceeded
Mustafa Bensan Feb 07, 2017 at 09:55 AM
0

Hi Alok,

If you execute the setFilter() method immediately after the loadDataSource() method don't you find that it returns the subset anyway, in the same way that you would expect from passing variable values? Perhaps it works differently for a UNX data source but at least for BW I was under the impression that as long as the data load and filter script commands were executed sequentially, they would be applied to the data retrieval together.

Regards,

Mustafa.

Share
10 |10000 characters needed characters left characters exceeded
Alok Soni Feb 07, 2017 at 08:56 PM
0

Yes, but two things there, on the intial load DS would fetch a huge amount of data...in some cases it might be more than 20 thousand rows

second, in some cases we want the aggregations to happen at the DB level - for example percentage calculations

When the dropdown values are changed the top/bottom molecules, percentage calculations etc should change. This cant be achieved if we cant fire the query at the DB post every prompt (dropdown) change

Show 1 Share
10 |10000 characters needed characters left characters exceeded

Regarding your points:

1) When you say "initial load" does that mean you are not loading the data source via script?

2) You make a valid point regarding aggregations at the DB level. I think this is an issue specific to relational databases consumed via a Universe data source. This is not an issue for OLAP data sources such as BW or HANA because the correct aggregations are still applied when using setFilter().

0
Alok Soni Feb 08, 2017 at 08:04 PM
0

Hello,

We tried using the setVariableValue() function to pass the value of the dropdown to the prompt in the query. This datasource is loaded in sript which applies on selection of dropdowns.However, on the first refresh, Datasource fetches all the data even though we load the datasource in the script and pass values from the dropdown to it using setVariablValue() function.
Exact scenario in our case below :
A set of dropdowns will be populated with values from a data source (Say DS_1) loaded at Application startup.

When users select values in these dropdowns these values should be passed to another datasource (DS_2 which is not yet loaded) as prompt answers and the datasource should then run for those prompt values only.

We tried using setVariableValue function to pass the values of dropdowns to the prompts and then loading the datasource (DS_2) in the On-click function of the dropdown button.

However, on the first refresh all the data in the datasource(DS_2) is retrieved irrespective of the prompt values passed to the this datasource

Any help is appreciated, Thank you.
Share
10 |10000 characters needed characters left characters exceeded
Mustafa Bensan Feb 09, 2017 at 12:33 AM
0

Hi Alok,

When using setVariable() in conjunction with loadDataSource(), you need to make sure that the setVariable() is executed immediately after loadDataSource() to ensure the corresponding subset of data is returned instead of the entire data set. I suggest you try the following code in the "On Select" event of each Dropdown:

var mySelection = me.getSelectedValue();

if (DS_2.isInitialized()) {	

	DS_2.setVariableValue("variable name", mySelection);
	
} else {	

	DS_2.loadDataSource();
	DS_2.setVariableValue("variable name", mySelection);

}

Regards,

Mustafa.

Share
10 |10000 characters needed characters left characters exceeded
Alok Soni Feb 09, 2017 at 05:18 PM
0

Hello Mustafa,

Thank you for the reply.

We have a data source containing 40000 rows of data of which we want to show only 100 on the dashboard.

If we load the data source and then set the Variable value (pass values to underlying prompts) will we still not fetch all the data (40000 rows) for this dataset and then apply filter at the Design Studio level ? As against applying SetVariableValue to the prompts and then loading the data source thus retrieving only the required 100 rows of data from the Database into the Dashboard in the first place.

Besides, fetching such huge sums of data might exceed the allowed row fetch size for Design Studio and also impact the performance adversely.

Show 1 Share
10 |10000 characters needed characters left characters exceeded

Alok,

It is my understanding that if you execute the code in exactly the consecutive sequence I have provided in my script example above, the value passed in setVariableValue() should be applied to the initial load of the datasource via script, thereby returning only the required 100 rows instead of 40000.

I suggest you try the code first and then we can proceed accordingly based on the results.

Regards,

Mustafa.

0
Alok Soni Feb 10, 2017 at 03:13 PM
0

Hello Mustafa, We have incorporated the above code in the on click of the dropdown. However, we are receiving the below error when the application is run through Internet Explorer. The error occurs only when the data source is initialized and setVariablevalue() function is applied in the on click of dropdown. Please let us know if there is an insight into the error.

<div id="header"><h1>Server Error</h1></div>

<div id="content">

<div class="content-container"><fieldset>

<h2>500 - Internal server error.</h2>

<h3>There is a problem with the resource you are looking for and it cannot be displayed.</h3>

</fieldset>

</div> </div>

Share
10 |10000 characters needed characters left characters exceeded