Passing selection variable values to Bex Query from Web Portal
Applies to:
This article talks about passing values to Bex query selection variables from Web Link to bypass the selection screen during run time.
For more information, visit the Business Intelligence homepage.
Summary
This document explains various types variable and passing values to them.
Options to see the selection screen with the passed values during runtime or hide the selection screen during runtime
Variable types covered:
1. Single Value
2. Multiple Single Values
3. Selection Option
i. Include
ii. Exclude
iii. Not Equal to
iv. Etc
4. Variable Type Interval
Author(s): Chiranjeevi Konduru
Company: L & T Infotech.
Created on: 25 March 2013
Author Bio
Chiranjeevi Konduru is working for L & T Infotech and has experience of 9 years in SAP BI and SAP Business Objects. He has been a part of Development and Implementation Projects.
Web URL Generation for Running a Bex Query:
Basic URL
http://<webportal>.html?redirectURL=http://<SAPNetWeaverPortal>:<Port>/sap/bw/BEx?
CMD=LDOC&SAP-LANGUAGE=EN&BSPLANGUAGE=EN&TEMPLATE_ID=<TemplateName> &VARIABLE_SCREEN=X&INFOCUBE=<InfoCubeName>&QUERY=<QueryName>
Break up of URL
http://<webportal>.html?
redirectURL=
http://<SAPNetWeaverPortal>:<Port>/sap/bw/BEx?CMD=LDOC
&SAP-LANGUAGE=EN
&BSPLANGUAGE=EN
&TEMPLATE_ID=<TemplateName>
&VARIABLE_SCREEN=X
&INFOCUBE=<InfoCubeName>
&QUERY=<QueryName>
&VAR_NAME_1=<VariableTechnicalName>&VAR_VALUE_EXT_1=<Value>
Variables can be added at the end of the URL. Detail syntax explained
Selection Screen Option
Adding text “&VARIABLE_SCREEN=X” will display the selection screen during the run Time. And the values which are passed to the variable via URL will be displayed. Click Execute to Run the query and get the results on Web Portal.
Selection Screen will be appearing like this
If the selection screen is not to be displayed during the run time of the query simple Remove the text “&VARIABLE_SCREEN=X” from the URL
Passing Variable Values to Bex Query
Single Value
If variable type is Single value:
&VAR_NAME_1=<TechnicalName>&VAR_VALUE_EXT_1=<Value>
- e.g.: &VAR_NAME_1=GPU_CDY&VAR_VALUE_EXT_1=01.01.2012
Here Variable name is GPU_CDY
URL as follows:
http://<webportal>.html?redirectURL=http://<SAPNetWeaverPortal>:<Port>/sap/bw/BEx?
CMD=LDOC&SAP-LANGUAGE=EN&BSPLANGUAGE=EN&TEMPLATE_ID=<TemplateName> &VARIABLE_SCREEN=X&INFOCUBE=<InfoCubeName>&QUERY=<QueryName>
&VAR_NAME_1=GPU_CDY&VAR_VALUE_EXT_1=01.01.2012
Selection Screen
Multiple Single Values
If variable type is Multiple Single values
&VAR_NAME_1=<TechnicalName>&VAR_VALUE_EXT_1=<Value1>&VAR_NAME_2=<TechnicalName>
&VAR_VALUE_EXT_2=<Value2>
e.g.:
&VAR_NAME_1=GPUPGSMV&VAR_VALUE_EXT_1=AJO&VAR_NAME_2=GPUPGSMV
&VAR_VALUE_EXT_2=0HR
Here the Variable Name is GPUPGSMV
URL as follows:
http://<webportal>.html?redirectURL=http://<SAPNetWeaverPortal>:<Port>/sap/bw/BEx?
CMD=LDOC&SAP-LANGUAGE=EN&BSPLANGUAGE=EN&TEMPLATE_ID=<TemplateName>
&VARIABLE_SCREEN=X&INFOCUBE=<InfoCubeName>&QUERY=<QueryName>
&VAR_NAME_1=GPUPGSMV&VAR_VALUE_EXT_1=AJO&VAR_NAME_2= GPUPGSMV
&VAR_VALUE_EXT_2=0HR
Selection Screen:
Passing Values to multiple variables
If multiple variables are present in a Bex Query
&VAR_NAME_1=<TechnicalName1>&VAR_VALUE_EXT_1=<Value1>&VAR_NAME_2=<TechnicalName1>
&VAR_VALUE_EXT_2=<Value2>&VAR_NAME_3= <TechnicalName2>&VAR_VALUE_EXT_3=<Values1>
e.g.: &VAR_NAME_1=GPUPGSMV&VAR_VALUE_EXT_1=AJO&VAR_NAME_2=GPUPGSMV
&VAR_VALUE_EXT_2=0HR&VAR_NAME_3= GIAV0PLT&VAR_VALUE_EXT_3=1720
URL as follows:
http://<webportal>.html?redirectURL=http://<SAPNetWeaverPortal>:<Port>/sap/bw/BEx?
CMD=LDOC&SAP-LANGUAGE=EN&BSPLANGUAGE=EN&TEMPLATE_ID=<TemplateName>
&VARIABLE_SCREEN=X&INFOCUBE=<InfoCubeName>&QUERY=<QueryName>
&VAR_NAME_1=GPUPGSMV&VAR_VALUE_EXT_1=AJO&VAR_NAME_2= GPUPGSMV
&VAR_VALUE_EXT_2=0HR&VAR_NAME_3= GIAV0PLT&VAR_VALUE_EXT_3=1720
Selection Screen:
Selection Option variables
If variable type is “Selection option”
Option 1: include the values
&VAR_NAME_1=<TechinicalName>&VAR_OPERATOR_1=EQ&VAR_SIGN_1=I
&VAR_VALUE_LOW_EXT_1=<Value>
e.g.:
&VAR_NAME_1=GPUVSPNH&VAR_OPERATOR_1=EQ&VAR_SIGN_1=I
&VAR_VALUE_LOW_EXT_1=D040203
Selection Screen:
URL as follows:
http://<webportal>.html?redirectURL=http://<SAPNetWeaverPortal>:<Port>/sap/bw/BEx?
CMD=LDOC&SAP-LANGUAGE=EN&BSPLANGUAGE=EN&TEMPLATE_ID=<TemplateName>
&VARIABLE_SCREEN=X&INFOCUBE= <InfoCubeName>&QUERY=<QueryName>
&VAR_NAME_1=GPUVSPNH&VAR_OPERATOR_1=EQ&VAR_SIGN_1=I
&VAR_VALUE_LOW_EXT_1=D040203
Option 2: Exclude Values
&VAR_NAME_1=<TechinicalName>&VAR_OPERATOR_1=EQ&VAR_SIGN_1=E
&VAR_VALUE_LOW_EXT_1=<Value>
e.g.:
&VAR_NAME_1=GPUVSPNH&VAR_OPERATOR_1=EQ&VAR_SIGN_1=I&VAR_VALUE_LOW_EXT_1=D040203
&VAR_NAME_2= GPUVSPNH&VAR_OPERATOR_1=EQ&VAR_SIGN_1=E&VAR_VALUE_LOW_EXT_1=D04020
Selection Screen:
URL as follows:
http://<webportal>.html?redirectURL=http://<SAPNetWeaverPortal>:<Port>/sap/bw/BEx?
CMD=LDOC&SAP-LANGUAGE=EN&BSPLANGUAGE=EN&TEMPLATE_ID=<TemplateName>
&VARIABLE_SCREEN=X&INFOCUBE= <InfoCubeName>&QUERY=<QueryName>
&VAR_NAME_1=GPUVSPNH&VAR_OPERATOR_1=EQ&VAR_SIGN_1=I
&VAR_VALUE_LOW_EXT_1=D040203&VAR_NAME_2= GPUVSPNH&VAR_OPERATOR_1=EQ
&VAR_SIGN_1=E &VAR_VALUE_LOW_EXT_1=D04020
Option 3: Not Equal to
&VAR_NAME_1=<TechinicalName>&VAR_OPERATOR_1=EQ&VAR_SIGN_1=NE
&VAR_VALUE_LOW_EXT_1=<Value>
e.g.:
&VAR_NAME_1=GPUVSPNH&VAR_OPERATOR_1=EQ&VAR_SIGN_1=I
&VAR_VALUE_LOW_EXT_1=D040203&VAR_NAME_2= GPUVSPNH&VAR_OPERATOR_1=EQ
&VAR_SIGN_1=NE&VAR_VALUE_LOW_EXT_1=D040204
Selection Screen:
URL as follows:
http://<webportal>.html?redirectURL=http://<SAPNetWeaverPortal>:<Port>/sap/bw/BEx?
CMD=LDOC&SAP-LANGUAGE=EN&BSPLANGUAGE=EN&TEMPLATE_ID=<TemplateName>
&VARIABLE_SCREEN=X&INFOCUBE= <InfoCubeName>&QUERY=<QueryName>
&VAR_NAME_1=GPUVSPNH&VAR_OPERATOR_1=EQ&VAR_SIGN_1=I &VAR_VALUE_LOW_EXT_1=D040203&VAR_NAME_2=GPUVSPNH&VAR_OPERATOR_1=EQ
&VAR_SIGN_1=NE &VAR_VALUE_LOW_EXT_1=D040204
Option 4: Others – LE for <=, GE for >=, EQ for =, GT for >, LT for <
Variable Type Interval
If variable type is Interval
&VAR_NAME_1=<TechnicalName>&VAR_VALUE_LOW_EXT_1=<Value1>
&VAR_VALUE_HIGH_EXT_1=<Value2>
e.g.: &VAR_NAME_1=GPU_FIYR&VAR_VALUE_LOW_EXT_1=2010
&VAR_VALUE_HIGH_EXT_1=2012
Here the Variable Name is GPU_FIYR
Selection Screen:
URL as follows:
http://<webportal>.html?redirectURL=http://<SAPNetWeaverPortal>:<Port>/sap/bw/BEx?
CMD=LDOC&SAP-LANGUAGE=EN&BSPLANGUAGE=EN&TEMPLATE_ID=<TemplateName>
&VARIABLE_SCREEN=X&INFOCUBE= <InfoCubeName>&QUERY=<QueryName>
&VAR_NAME_1=GPU_FIYR&VAR_VALUE_LOW_EXT_1=2010&VAR_VALUE_HIGH_EXT_1=2012
Hierarchy variable
If variable is of hierarchy type
Var_name_i = technical name of hierarchy variable
Var_value_ext_i = key value of Hierarchy
Var_node_iobjnm_i = node characteristic name.
&VAR_NAME_1=<TechnicalName>&VAR_VALUE_EXT_1=<Value1>&VAR_NODE_IOBJNM_1=<Infoobject Name>
Eg:
&VAR_NAME_1=GFR_AA_RUNIT&VAR_VALUE_EXT_1=RFBL0008&VAR_NODE_IOBJNM_1=GREUNIT
URL as follows:
http://<webportal>.html?redirectURL=http://<SAPNetWeaverPortal>:<Port>/sap/bw/BEx?
CMD=LDOC&SAP-LANGUAGE=EN&BSPLANGUAGE=EN&TEMPLATE_ID=<TemplateName>
&VARIABLE_SCREEN=X&INFOCUBE=<InfoCubeName>&QUERY=<QueryName>
&VAR_NAME_1=GFR_AA_RUNIT&VAR_VALUE_EXT_1=RFBL0008&VAR_NODE_IOBJNM_1=GREUNIT
URL with SSO
http://<PortalName>/erpsso/getsso.html?
redirectURL=http://<SAPNetWeaverPortal>:<Port>/sap/bw/BEx?CMD=LDOC
&SAP-LANGUAGE=EN&BSPLANGUAGE=EN&TEMPLATE_ID=<TemplateName>
&VARIABLE_SCREEN=X&INFOCUBE=<InfoCubeName>&QUERY=<QueryName>
&VAR_NAME_1=GFR_AA_RUNIT&VAR_VALUE_EXT_1=RFBL0008&VAR_NODE_IOBJNM_1=GREUNIT
URL without SSO
http://<SAPNetWeaverPortal>:<Port>/sap/bw/BEx?CMD=LDOC
&SAP-LANGUAGE=EN&BSPLANGUAGE=EN&TEMPLATE_ID=<TemplateName>
&VARIABLE_SCREEN=X&INFOCUBE=<InfoCubeName>&QUERY=<QueryName>
&VAR_NAME_1=GFR_AA_RUNIT&VAR_VALUE_EXT_1=RFBL0008&VAR_NODE_IOBJNM_1=GREUNIT
Related Content
Disclaimer and Liability Notice
This document may discuss sample coding or other information that does not include SAP official interfaces and therefore is not supported by SAP. Changes made based on this information are not supported and can be overwritten during an upgrade.
SAP will not be held liable for any damages caused by using or misusing the information, code or methods suggested in this document, and anyone using these methods does so at his/her own risk.
SAP offers no guarantees and assumes no responsibility or liability of any type with respect to the content of this technical article or code sample, including any liability resulting from incompatibility between the content within this document and the materials and services offered by SAP. You agree that you will not hold, or seek to hold, SAP responsible or liable with respect to the content of this document.
Hi Chiranjeevi Konduru
This is a great article, really useful. I can now fully automate a Sales Report using a single URL. I can also pull this data directly into a Query Table in Excel 2010 and have written a Blog Post entitled:
'Automatically Refresh & Scrape SAP BI Query Portal Report Data into Excel 2010 from a Bookmark by Mark Kubiszyn'
It can be found here:
http://www.kubiszyn.co.uk/bi-excel.html
Thanks again for a great article, Mark Kubiszyn
Are these instructions still valid? I cannot get them to work for my Bex Web query. I have a variable named ZVAR_0SOLD_TO_SO and I want to pass a value of 101003942 to it.
Here's the variable in Query Designer:
I tried appending &VAR_NAME_1=ZVAR_0SOLD_TO_SO&VAR_VALUE_EXT_1=101003942 to my URL's query string but the field does not fill in on my variable screen.
Is my syntax correct? Am I missing a setting on the Query Designer side to allow parameters to be passed?