Launching a parameterized web application from SAP Dashboards

[shai_greenberg]

Requirement and issues

• It is required to drill down from a dashboard into a detailed report, specifically a BW 7.X Web application.
• The drill down has to be done using one or more variable values from the dashboard, for instance by clicking a row on a scorecard. If all variable values are passed, the BEX variable screen should not be shown.
• Optimally new web template and queries are to be created, but what if we want to drill into an existing template? Related requirements could be filling time variables with the current time or drilling to a specific tab within the web application.
• The "#" sign does not pass correctly to the web template

Solution overview

• Get the URL for the web application and enhance it with the variable names and placeholders for the variable values.
• Use Excel formulas to replace the values.
• Customize the components to allow drilling from a scorecard (or a similar reporting component).
• Use Excel formulas and web template parameters to bridge the gap between the Dashboard and existing templates.
• Fix # values using a simple replacement

For simplicity's sake I will refer to web template parameters present in the URL as "parameters", and to the BEX variables as "variables".

Detailed Solution

Web Application Side

Get the basic URL by launching the web application from the query designer or the Web Application Designer. This really depends on your scenario, but you may want to build a web application even if the requirement is simple – in order to show variable values above the report, for instance. For simplicity’s sake I’ll assume a template is launched.

What you’ll get is a URL of the form

http://host:port/irj/servlet/prt/portal/prtroot/pcd!3aportal_content!2fcom.sap.pct!2fplatform_add_ons!2fcom.sap.ip.bi!2fiViews!2fcom.sap.ip.bi.bex?TEMPLATE=TEMPLATE_NAME&DUMMY=0

(the TEMPLATE parameter is replaced by QUERY when launching a query. There’s also a specific format for a query view you can find here)

You should now add a string containing variable values to the URL. You can see several examples for this here .

Let’s take a simple example that sets two variables named PDS06 and PDS07 - that is, these are the technical names of BEX variables in the underlying queries of the web template, which we want to fill with values from the dashboard.

These variables should be relevant for some or all of the underlying queries, so special care needs to be taken to make sure that the variable names across the different queries/query views are identical where it is required. I've used color to differentiate the string for each variable, and it is possible to add strings for more variables - although I didn't check if there was any limit on this.

&BI_COMMAND_1-BI_COMMAND_TYPE=SET_VARIABLES_STATE&BI_COMMAND_1-VARIABLE_VALUES-VARIABLE_VALUE_1-VARIABLE_TYPE=VARIABLE_INPUT_STRING&BI_COMMAND_1-VARIABLE_VALUES-VARIABLE_VALUE_1-VARIABLE_TYPE-VARIABLE_INPUT_STRING=XXXX&BI_COMMAND_1-VARIABLE_VALUES-VARIABLE_VALUE_1-VARIABLE=PDS06&BI_COMMAND_1-VARIABLE_VALUES-VARIABLE_VALUE_2-VARIABLE_TYPE=VARIABLE_INPUT_STRING&BI_COMMAND_1-VARIABLE_VALUES-VARIABLE_VALUE_2-VARIABLE_TYPE-VARIABLE_INPUT_STRING=YYYY&BI_COMMAND_1-VARIABLE_VALUES-VARIABLE_VALUE_2-VARIABLE=PDS07&VARIABLE_SCREEN=

What this does is tell the template to run the "SET_VARIABLES_STATE" command once the template is launched. Other commands can also be triggered, we'll get to that later on.

Notice “XXXX” and “YYYY”, which are placeholders for the values.

Also note that I've included the template parameter VARIABLE_SCREEN with an empty value. This can also be set in the web template level, but is overridden if present in the URL.

If the requirement is to supply all input-ready variable values of the queries from the Dashboard, it makes sense to force the web template to not show the variable screen. If the screen should be shown, the parameter should be given the value X. adding another parameter after the "=" sign causes the value of the parameter to be empty.

Dashboard Side

Copy the URL into a cell in a sheet in the dashboard, say B6. In the picture below I've titled B6 as "URL_STATIC" for documentation purposes.

The next step is to replace the placeholdes with the insertion value from the dashboard component – say the values of cells D40 and F40, assuming the row into which data is inserted is 40.

In this example, the first variable value is replaced first:

=REPLACE(B6,FIND("XXXX",B6),4,D40)

Then the second variable value is replaced in the result of the previous formula (which is in cell B7 - "URL_DYNAMIC").

=REPLACE(B7,FIND("YYYY",B7),4,F40)

The result is placed into B8, which I've titled "URL_DYNAMIC_2"

Now add a URL button component with the URL based on the final value following the replacements (B8).

In order for the URL to be launched when clicking a row in the scorecard we need to define a trigger cell in the behavior tab of the URL button properties.  This cell should be one of the cells chosen as insertion destination in the scorecard.

Notice that you may want to define the insertion in the scorecard as “Insert on Interaction only”. This would make sure the web application is not triggered when the data in the scorecard is changed. However, this does not solve the case where a row was selected and the scorecard becomes visible according to dynamic visibility (at least in Dashboards 4.0 sp02, which I've worked with).

You can’t hide the URL button if you want the trigger to work, but hiding it using placement, size and color seems reasonable enough.

Using an existing WAD template - passing the current time

Usually, specific templates and queries should be created for the drill down, however with a little effort on the dashboard side and the URL structure it may be possible to bridge the gap in order to use existing templates.

Say we have a template containing BEX queries that have a mandatory ready for input month interval variable. We would like to feed this variable with the Year-To-Date months, that is, the first month of the year and the current one. This can be easily accomplished with several Excel formulas:

Current Month (calculated in cell B23) - the result will look like "4"

=MONTH(NOW())

Current month formatted to 0calmonth format (calculated in B24) - the result will look like "04.2014"

=REPT(0,2-LEN(B23))&B23&"."&YEAR(NOW())

YTD Months - the result will look like "01.2014 - 04.2014"

="01."&YEAR(NOW())&" - "&B24

We then add this as a variable value into the URL in the same manner as with the other variables.

Notice that the "Encode URL" checkbox in the URL button (ticked on by default) makes sure the spaces in the string will be passed correctly in the URL as "%20" strings.

Using an existing WAD template - drilling into a specific tab in the template's tabstrip

I've mentioned that the passing of variables is done using the WAD command mechanism, and as it turns out this can be used with other commands as well. One scenario requiring this is the use of an existing web template with a tabstrip, where the drill down should focus into a different tab than the one defined in the template. The dashboard may even include several different drill downs that launch the template in different tabs. This is what you'd call a "nice to have" feature, but it's a good demonstration of just how far you can go with the URL interface.

When the following string is appended to the URL:

&BI_COMMAND_2-BI_COMMAND_TYPE=SET_ITEM_PARAMETERS&BI_COMMAND_2-TARGET_ITEM_REF=TABSTRIP_CONTAINER_ITEM_1&BI_COMMAND_2-INIT_PARAMETERS-INITIAL_TAB=3

The web template tabstrip item "TABSTRIP_CONTAINER_ITEM_1" will open on the third tab, regardless of what was defined in the template.

Handling "unassigned" values

I've mentioned how the URL button's encoding option correctly passes space as "%20". What it doesn't do is to pass the # value correctly. This is important because # represents the unassigned value in BW, and should be passed as "%23" to be interpreted correctly by the web template. A simple replacement of the value in the insertion row using an Excel formula is enough to handle that.

=IF(E14="#","%23",E14)

The solution was found in this thread:

Passing # value (unassigned) to a variable via url to web templates

References

BEX Web URL parameters that determine whether a template/query/other options are launched:

http://help.sap.com/saphelp_nw70/helpdata/en/33/39fa40ee14f26fe10000000a1550b0/content.htm

Parameters for Web applications:

Using Parameters to Call Web Applications - Business Intelligence - SAP Library