How to Multiply a Column Values in a Crosstab with Variable Value

[Former Member]

Hi,

I have created a sample Viz in Design Studio based on a Universe(UNX) and in turn pointing to oracle database. I have a Crosstab and Chart(Line Chart) components on my Canvas. I have like 1 dimension and 2 measure objects in Crosstab and have Line chart with 2 lines pointing to the measures in the crosstab. My requirement is to have Input Field component Named Percentage Increase so when a user enters 10 or 15  one of the measure in the crosstab should change based on the number entered by the User in Percentage Increase(Input Field)

For Example:

Two measure are Actual and Plan. So when a user enter 10 or 20 in the Input Filed. The Plan Measure in crosstab should increase by using the below formula.

Plan = Plan+(Plan*(A/100))   (Where "A" is the value entered by user in Input Field eg: 10 or 20)

Till Now what i did is to place a Input Field(Percentage Increase) and a place a Button(Enter) beside it. So when a user enters a Button the above Formula calculation needs to be done on Crosstab and it will results in changes on chart as well because it is tied up to the values in the crosstab.

I just put this lines on Button(Enter) component  in order to Capture the values entered by user in Input Field and convert that Variable in to Interger.

Variable is Global variable defined a String.

Variable1 is Global variable defined a Numeric.

Variable=INPUTFIELD_1.getValue();

Variable1=Convert.stringToInt(Variable);

Experts please guide me now after Capturing the user response now how to proceed further in implementing the above formula and get results change in crosstab and chart. I Know in Webi where we can create a extra variable and put this logic and append that variable to the table but not sure about in Design studio.

Thanks in Advance.

[MustafaBensan · Accepted solution]

Hi Kumar,

There is no good workaround to perform the calculation on the client side in Design Studio.  However, you should be able to achieve the desired result by performing the calculation in the Universe itself, with the following steps:

1)  Create a variable in your universe to represent PercentageIncrease

2)  Create a calculated measure in the universe that performs the calculation RevisedPlan = Plan + (Plan*(PercentageIncrease/100))

3)   In your Design Studio script, after capturing the user input value, pass the Variable1 value back to the universe with the script method APPLICATION.setVariableValue("PercentageIncrease", Variable1).  This should then refresh the universe data source with the new calculated plan values and automatically update the crosstab.

Regards,

Mustafa.

[IngoH · Accepted solution]

or just wait another 4 weeks =:)

regards

Ingo Hilgefort, Visual BI

[MustafaBensan · Accepted solution]

Well, it's always worth evaluating both standard and add-on options to determine which best fits the business requirement

Regards,

Mustafa.

[Former Member · Accepted solution]

Hi Mustafa,

Thanks for the Workaround. I was able to do the Calculation within IDT itself for PercentageIncrease as mentioned by you.

1)  Create a variable in your universe to represent PercentageIncrease :

I have createad a parameter in IDT as PercentageIncrease. I Assume "variable" is same as "parameter" .Please correct me if i am wrong.

But i am facing issue in the Design Studio on how to pass the User selected  InputField value from Design Studio to the  Parameter which i have created in Universe.

Variable is Global variable defined a String.

Variable1 is Global variable defined a Numeric.

1. Variable=INPUTFIELD_1.getValue();

2. Variable1=Convert.stringToInt(Variable);

3. APPLICATION.setVariableValue("PercentageIncrease",Variable1);

'

I am getting this error at line number '3'.

Cannot convert from "int" to "VariableValue"

Any help on this would be appreciated.

Thanks.

[Karol-K · Accepted solution]

Hi Kumar,

setVariableValue requires string, so you can skipt step 2 (the to-int conversion).

1. Variable=INPUTFIELD_1.getValue();

3. APPLICATION.setVariableValue("PercentageIncrease",Variable);

Karol

[MustafaBensan · Accepted solution]

Hi Kumar,

I don't have access to a BusinessObjects backend at the moment to test directly but let's see if we can work through this a step at a time.  Can you try the following?:

APPLICATION.setVariableValueExt("PercentageIncrease",Variable1);

Let me know how that goes.

Regards,

Mustafa.

Edit:  I was too slow again    Karol's suggestion should do the trick.

[Former Member · Accepted solution]

Hi Karol,

I have tried by skipping the second step but still i am having the issue

Variable=INPUTFIELD_1.getValue();

APPLICATION.setVariableValue("PercentageIncrease",Variable);

Mustafa,

I Have tried 'setVariableValueExt' method as well  i don't see any effect of it.

APPLICATION.setVariableValueExt("PercentageIncrease",Variable1);

My Question is how to refresh the datasource once i click on the "Enter" Button which i created beside the "Input Field" where i am writing the above logic. Because when i click "Enter" i dont see any refresh happening on the Web Browser.

And another thing is i have created a "parameter" in IDT is that the same as "Variable" as u mentioned in one of the previous replies Mustafa?

Please let me know on how to debug or test whether my parameter "PercentageIncrease" is accepting  my variable value  and its getting applied on my Query(Data source).

Thanks for all your replies and help on this.

[Karol-K · Accepted solution]

actually, after this script the data source will be reloaded (not directly, but when you render the data via crosstab).

please check the statistics with &PROFILING=X, you can also attach the text downloaded file before and after you pass the variable value.

Karol

[Former Member · Accepted solution]

Hi Karol,

I have collected the stats using &PROFILING=X. Please see the attached logs Before and After passing the variable.

Thanks,

Kumar

After_Variable_Design_Studio_statistics.txt.zip

Before_Variable_Design_Studio_statistics.txt.zip

[Karol-K · Accepted solution]

the variable was set, line

0 ms: Process data provider command SET_VARIABLES_STATE (13101)

but w/o effect. there is nothing passed to backend.

what was the value before and after? was it changed?

can you try to add the line:

DS_1.reloadData();

after variable change? Does this change it?

Karol

[Former Member · Accepted solution]

Hi Karol.

After entering the Data in to Input Field the Data in CrossTab is not changing. I Suspect the value is not getting assigned to 'PercentageIncrease' When i created a parameter "PercentageIncrease" in IDT i  have mentioned the DataType as Numeric and in below statement 'Variable' is DataType of 'String' so i assume something is missing over here. And more over when i hit "Control + Space" i don't see "PercentageIncrease" in the list so i have Hardcode it.

APPLICATION.setVariableValue("PercentageIncrease",Variable);

Before when i  was working with passing the values to the prompts from Design Studio i have kept those dimensions in Filter area in Query panel and when i hit "Control + Space" i would able to see those prompt text in the list.

I Assume some thing is missing over here. When i put the below line in Button Component as mentioned by you i am getting this warning message.

Method "reloadData" has no effect on DSL data sources

DS_1.reloadData();

Thanks.

[MustafaBensan · Accepted solution]

Hi Kumar,

Unfortunately the reloadData() method is not supported for Universe data sources.  This is a known current limitation of Design Studio.  Nevertheless, the setVariableValue() method should work.  You mention that the variable name PercentageIncrease does not appear in the content assistance when you press "Control + Space".  Does this mean you see an error in the script as shown below with a red underline?:

Can you perhaps provide a screenshot of your script window to verify?

Regards,

Mustafa.

[MustafaBensan · Accepted solution]

Hi Kumar,

Can you also provide the following to help further analyse this issue?:

1)  Screenshots of how you have defined the PercentageIncrease parameter in the IDT?

2)  Screenshots of how you have linked the PercentageIncrease parameter to the RevisedPlan calculated measure in the universe?

3)  A Design Studio screenshot of the universe query panel for your data source.

Thanks,

Mustafa.

Message was edited by: Mustafa Bensan Updated list of questions.

[Former Member · Accepted solution]

Hi Mustafa,

Thanks for  your followup can i send those screenshots to your personal inbox?

If so can u please let me know the process for it.

Thanks,

Kumar

[Former Member · Accepted solution]

Hi Mustafa,

Please find these attached screen shot. I have created "PercentageIncrease" which is the parameter created in IDT and that doesn't show any error.

I have created "PercentageIncrease123" as well and this one doesn't exist in IDT but still doesn't show any error. I think if we  mention any text within

" "  it will treat as a Static Component. Please correct me if am wrong.

APPLICATION.setVariableValue("PercentageIncrease",Variable);

APPLICATION.setVariableValue("PercentageIncrease123",Variable);

Thanks,

Kumar

[MustafaBensan · Accepted solution]

Hi Kumar,

Actually, specifying the variable name in quotation marks (" ") is correct.  Design Studio will interpret this as the technical name of the variable.  If you don't specify quotation marks, the Design Studio will interpret this as a script variable that contains the name of the variable (parameter) you want to pass.

From your screenshot, it looks like both "PercentageIncrease" and "PercentageIncrease123" have been validated in the script.  When you say you have 'created "PercentageIncrease123" as well' what do you mean by this?  Where did you create it, in the data source query panel?

Regards,

Mustafa.

[MustafaBensan · Accepted solution]

Hi Kumar,

I don't think it's possible to attach files/screenshots to SCN direct messages.  If you are concerned about confidentiality you can just blur the field names in the screenshot except for the parameter/prompt names.  Is that okay?

Also, sharing the screenshot would make it easier for others in the community to help as well.

Regards,

Mustafa.

[Former Member · Accepted solution]

Hi Mustafa,

I mean that when i mention "PercentageIncrease123"  in the script it still got validated inspite i did not created that as a parameter in IDT not created as variable in Design Studio.

I was just doing some testing u can ignore it if it caused any confusion.

Please let me know whether i can send the screenshots u requested earlier today to your personal inbox? If so please let me know the process.

Thanks in advance.

[Former Member · Accepted solution]

Hi Mustafa,

Please find the attached screenshots as requested by you i have blurred some private information.

Thanks,

Kumar

[MustafaBensan · Accepted solution]

Hi Kumar,

Thanks for the screenshots.  The setup looks reasonable to me.  Can you confirm if you get a prompt dialog when you click the Refresh button on the Design Studio Query Panel (i.e. in your last screenshot) ?

Regards,

Mustafa.

[Former Member · Accepted solution]

Hi Mustafa,

Please find the attached screenshot which was requested by you. I Assume the when ever i am entering any Value in the input field Data_Source is not getting refreshed. When i first execute(Run it Locally) the web browser opens with the Prompt for the first time and when i enter my value in the prompt my Datasource and crosstab is getting refreshed for the first time with no issues.

When i enter the values for second and third time in the Input Field and hit the "Enter" Button Component i think value is not getting passed to the "PercentageIncrease" parameter or Datasource is not getting refreshed. If you can help in that area that would be great and more over i don't want the Prompt window to be displayed when i refresh my design studio document for first time.

User need to enter the values in the Input Field itself.

Thanks,

Kumar

[Karol-K · Accepted solution]

Hi Kumar,

have you tried to change this prompt by use of the method

APPLICATION.openPromptDialog(800, 600);

is the behavior the same?

Karol

[Former Member · Accepted solution]

Hi Karol,

When i put these method in the Button Component it is able to get the prompts from the DataSource and prompting me to enter the new value.

As you know there is no Slider option in Design Studio as we have in Xcelsius i thought of creating a input field where user can enter the values and when they hit "Enter" Button The values in the crosstab needs to be changed( Datasource needs to get refreshed with prompt value).

I think User will be more Comfortable if i can provide the option as mentioned above by me. A "Input Field" and "Enter" Button.

Thanks for all your help and prompt response on this. Appreciate it.

Thanks,

Kumar

[MustafaBensan · Accepted solution]

Hi Kumar,

Thanks for the screenshot.  This shows that at least the PercentageIncrease prompt is being recognised by Design Studio since it appears in the Query Panel.  What happens when you enter a value in the Query Panel?  Do you see the updated results in the Data Preview?  What happens if you subsequently click the Refresh button on the Data Preview of the Query Panel?  Do you get the Prompt Dialogue again allowing you to input a new value?

I'd just like to confirm that the prompt is working correctly in the data source first.  So my questions above all relate to the Data Preview in the Query Panel and not the local execution of the application.

Regards,

Mustafa.

[MustafaBensan · Accepted solution]

Hi Kumar,

Regarding your requirement for a Slider, I recommend you download the SCN Design Studio SDK Development Community components from the online repository.  It includes a Slider Component that you can use for the PercentageIncrease value, like you are used to doing in Xcelsius.

Of course we first need to determine the cause of the prompt refresh issue.

Regards,

Mustafa.

[Karol-K · Accepted solution]

HI Kumar,

the same as in Mustafa comment below, the reason for this question is to check if we face a general issue with prompt change or some issue with the methods used in the workflow with input field (and later perhaps with slider).

the question is, is the prompt behaving correctly when you change it via the official prompt dialog?

Karol

[Former Member · Accepted solution]

Hi Karol,

Yes the prompt is working fine when i put this following method under the "Button" Component.

APPLICATION.openPromptDialog(800, 600);

Somehow the when i use the "Input Field" and use the "Button" Component and write this following logic inside it i don't see the prompt is not taking the values what i am entering in input field i assume that its issue with data-source not getting refreshed.

Variable=INPUTFIELD_1.getValue();

APPLICATION.setVariableValue("PercentageIncrease",Variable);

Thanks,

Kumar

[Karol-K · Accepted solution]

this looks more like an support incident.

1. can you assure that the "PercentageIncrease" is the technical name of the variable?

2. if you write the code and make CTRL-SPACE, is this offered to you as value help in the method?

3. can you try the same with the "ext" version of this method,

setVariableValueExt()

Please check if there is a statement in some documentation on support of the setVariableValue() method on Universe data source.

One more roundtrip and then we will see if this needs to go as support incident.

Karol

[Former Member · Accepted solution]

Hi Mustafa,

I have refreshed the query in the Query Panel in the Design Studio and did it multiples times and each time when I refresh I get the prompt and when I click OK the Data Preview will be having the update values according to the value I entered in the PROMPT.

Thanks,

Kumar

[Former Member · Accepted solution]

Hi Karol,

1) Yes I can assure "PercentageIncrease" is the technical name of the variable. Please do see my screenshot which I have attached in previous replies on this thread.

2)When I  was writing the Code and make CTRL_SPACE it didn't offered the variable value "PercentageIncrease" in the help.

3)I have tried using the "setVariableExt()" method but it was the same result.

I have checked the documentation and there was no luck.

Thanks,

Kumar

[Karol-K · Accepted solution]

ok, I think you better open customer incident on Design Studio product. The methods should work exactly same as the Prompt Dialog.

We probably do not fix this in this thread. You can link to this thread.

Karol

[Former Member · Accepted solution]

Hi Karol,

I have downloaded the Slider component from Design Studio SDK. Can u please let me know how can I bind it to "PercentageIncrase" Variable. I am a naïve to this SDSK and when I see it for first time it bit confusing as when I see multiple version of SDK.

Thanks,

Kumar

[Former Member · Accepted solution]

Hi Mustafa,

One more question whether that value we have entered for "PercentageIncrease"(Prompt) Can we capture those value and publish it Via web service and that another Java Web application can able to that value.

What I mean create a Hyperlink on the Design Studio Dashboard and when the user clicks on this it should generate a  new Web URL and open in a new Tab. That Web URL should have the parameter values what we have entered for "PercentageIncrease" and it should display on the browser as well.

Thanks,

Kumar

[MustafaBensan · Accepted solution]

Hi Kumar,

For the case of opening a hyperlink in a new tab, you can simply use the APPLICATION.openNewWindow(URLstring) method in your script, where you would need to use string concatenation to build the URLstring value which includes your PercentageIncrease parameter.  The URLstring would need to be created with the appropriate syntax required by the target application.

Regards,

Mustafa.

[MustafaBensan · Accepted solution]

Hi Kumar,

You can set the slider properties as shown below for a percentage range of 0 to 100:

Then in the On Change script you can set the variable value with the following code:

APPLICATION.setVariableValue("PercentageIncrease",SLIDER_1.getValue());

Regards,

Mustafa.

[Former Member · Accepted solution]

Hi Mustafa,.

I would have mentioned before but i have tried this option but i was not successful with it. I was getting the following errors.

APPLICATION.setVariableValue("psPercentageIncrease",SLIDER_1.getValue());

When i use the above Method on On Change Script of the Slider i was getting these error "Cannot convert from "float" to "VariableValue" as these method was returning float value.

Later i tried below method but that was also not successful.

APPLICATION.setVariableValue("psPercentageIncrease",SLIDER_1.getValueAsInt());

For the above method i was getting "Cannot convert from "int" to "VariableValue".

Need your inputs on these "PercentageIncrease" is parameter defined in IDT and its numeric datatype.

Thanks,

Kumar

[Karol-K · Accepted solution]

try to use this line:

var slidervalue= SLIDER_1.getValueAsInt();

APPLICATION.setVariableValue("psPercentageIncrease", "" + slidervalue);

the content

"" + slidervalue

will make impplicit conversion to string.

[Former Member · Accepted solution]

Thanks Karol for u quick response. The above method worked but however when i Slide the Slider i think values are not getting passed to Prompt "PercentageIncrease".

When i move the slider the page is getting refreshed and i get the "PercentageIncrase" Prompt window to enter the value as attached in the screenshot. I have attached the Screenshot of parameter in IDT as well.

All i need is when i move the Slider the appropriate value in the Slider should get passed to "PercentageIncrase" Prompt and my values in Datasource(Crosstab and Chart) needs to get refreshed accordingly without get that Prompt window.

Thanks,

Kumar

[Former Member · Accepted solution]

Hi Karol,

Can u please reply on this just waned to finish this one.

Thanks,

Kumar

[Karol-K · Accepted solution]

Hi Kumar,

please send me a private message - we can look at this together in a call, it is difficult to say what is wrong in your scenario. When we find a solution, we will document this in this therad.

Karol

[Former Member · Accepted solution]

Hi Mustafa,

We found the solution instead of  "PercentageIncrease" if we put "psPercentageIncrease"  as shown below it works. Now whenever i slide the slider the variable "slidervalue" is capturing that value and passing it to "PercentageIncrease" parameter we created in IDT.

var slidervalue= SLIDER_1.getValueAsInt();

APPLICATION.setVariableValueExt("psPercentageIncrease", "" + slidervalue);

I didn't understood when 'ps' is appended before 'PercentageIncrease'( "psPercentageIncrease" )variable it works and it doesn't work when I just declare like below.

APPLICATION.setVariableValueExt("PercentageIncrease", "" + slidervalue);

Anyhow now my application is working now but need some clarification on this.

Thanks,

Kumar

[MustafaBensan · Accepted solution]

Hi Kumar,

Thanks for sharing the solution.  That's very unusual.  I didn't realise that the IDT parameter had to be prefixed by "ps" in order to be recognised by Design Studio.  Are you sure there wasn't another definition in the IDT where "psPercentageIncrease" was specified or in the Query Builder panel of Design Studio for universe data sources?

By the way, how did you come across this solution?  Was it the response to an SAP support ticket?

Regards,

Mustafa.

[Former Member · Accepted solution]

Hi Mustafa,

No It was not solution from SAP ticket. When i hit (Ctrl + Space) for Content assistance it got popped out once as  "psPercentageIncrease"  in the list and it was intermittent some times it was popping sometime it was not. So that way i came to know and it solved my issue.

Yes i am sure that in IDT there wasn't any "ps" prefixed anywhere.

Thanks,

Kumar

[IngoH]

Hello Kumar,

just to show you a small sneak preview of what will become available by SAPPHIRE.

regards

Ingo Hilgefort

[Former Member]

Thanks Ingo. By the way can u elaborate what is that SAPPHIRE?

Is it an extension that we can add in to the Design Studio tool?

Thanks,

Kumar

[IngoH]

Hello Kumar,

SAPPHIRE is SAP's main conference.

Latest Event Updates for SAPPHIRE NOW + ASUG Annual Conference</title> <meta http-equiv=&qu...

and yes what I am showing is an extension for SAP Design Studio.

http://visualbi.com/sap-design-studio/dsx-extensions/

regards

Ingo Hilgefort, Visual BI

[Former Member]

Thanks Tammy. Atleast is there any workaround to implement my logic?

[TammyPowlas]

I am not aware of any work around

[IngoH]

Hello Kumar,

just be a little patient and wait for SAPPHIRE and then take a look at our Design Studio Extensions

regards

Ingo Hilgefort, Visual BI

[TammyPowlas]

Hello Kumar,

My take is that Design Studio currently does not support this type of calculation - it is planned for a future release - please see the Roadmap at https://websmp108.sap-ag.de/~sapidb/011000358700000390622012D.pdf (SMP logon required)

[Former Member]

This message was moderated.