Admittedly this is 4 years too late in 2016 and maybe its a case of better never than late. Given there are customers on lagging edge (is there such a thing?) of technology adoption, it may be of use to some, so here it goes.
SAP BPC allows dill-through to reports in BW, BI and to transactions or reports in ECC. This provides users a comprehensive and extended view related to BPC data. It can be rightly used for drill down, detailed analysis, reconciliation and other reporting purposes. However, for a subset of users who are much more versed with excel and Bex-Excel, inability of BPC drill-through to jump to Bex-Excel reports is a serious limitation.
While exploring this further, I built a prototype which lets user drill-through from BPC to Bex workbooks and queries. Methods for drill-through are slightly different for workbooks compared to queries. Both leverage standard drill-through functionality. Here are the main components of this solution described in brief.
BPC Drill-through definition : This stays the same. In place of a URL launching web-bex query, this solution uses a BSP launcher URL which opens Bex-Excel.
Bex-Excel Launcher : SAP has a bexlauncher BSP application which is used to launch Bex Excel. It however doesn't pass the variables to the query/workbook. This application is copied and customized so variable values from drill-through URL are passed to the target workbook. In case of drill-through to a query, since parameters can not be directly passed, a workaround is utilized (described later below). In this application
Logic similar to function rrw3_get_query_view_data is utilized to parse the URL parameters into a suitable ITAB of variable values and details. This variable ITAB is required to be passed for launching the workbook.
Logic similar to RSAH_LAUNCH_EXCEL function is used to launch Bex-Excel workbook with variable values already filled with values from drill-through selection.
For drill-through to Bex Query, the workaround steps in this application are to
Save variables values as a user-specific personal variant to the query using standard ABAP classes for variant.
Open query in excel. This shows up selection screen where user needs to select the variant which has just been saved with the drill-through parameter values for variables.
With this application, all that changes in standard drill-through is the URL for launching drill-through. Rest of the parameters and config remain the same.
Testing this solution with 7.x queries and BPC 10 worked fine. I will try to post some screenshots when I can.
Update 03/30/2016 - Added more details and screenshots below:
SAP’s Excel Bex launcher – BSP application RSR_BEX_LAUNCH (SE80 screenshot below)
URL: http://<host>:<port>/sap/bc/bsp/sap/rsr_bex_launch/bexanalyzer.bex
URL above can be used to open a bex query or workbook; for example
http://<host>:<port>/sap/bc/bsp/sap/rsr_bex_launch/bexanalyzer.bex?QUERY=ZTEST_QUERY will open query ZTEST_QUERY in excel (after login popup).
http://<host>:<port>/sap/bc/bsp/sap/rsr_bex_launch/bexanalyzer.bex?WBID=ABCEDFG)$(@ will open workbook whose id is specified in the URL (again first a popup will ask for login).
This URL can replace the query URL used in BPC drill-through configuration but for the fact that it doesn’t have any mechanism to pass variable values.
A BPC drill-through URL in comparison looks like below (with an example variable value)
What needs to be done is enhance the BSP application so we get a URL similar to the first one above that opens Bex in Excel with specified query or workbook, and, passes the variable parameters. Once we build such an application, we can use its URL instead in BPC drill-through config and get the drill-through report opened in Bex Excel.
Prototype solution did the following
- - Copy BSP application RSR_BEX_LAUNCH (to say Z_BEX_LAUNCH)
- - Enhance this application to
- Parse the URL passed so all the variable details along with query name/workbook name are stored in local variables. This logic can be similar to how variable part of URL is processed in function RRW3_QUERY_VIEW_DATA
- Now, to call the workbook with variable values, code similar to RSAH_LAUNCH_EXCEL can be used.
- There is no functionality in the function or classes to call a Bex Query with variable values. To address this, the code can save a variant to the called query with the values the drill-through has been called. Then, the query can be launched. On selection-screen, user will need to choose this variant to run the drill-through query. (One additional step of selection-screen – compared to drill-through to a workbook).
Example scenario 1 -
URL with Bex query – Calmonth variable set to 03/2014
Login popup in browser – for BW server
Application parses variables in the URL and saves values of variables as a personal variant to the query for the executing user (so multiple users can drill-through to same query without overwriting other’s variants). After login, the application tries to open Bex excel with specified query and has a login popup for Bex Excel.
Selection screen of query appears. User can select the variant and execute the query here. Notice the value for the variable (CALMONTH = 03/2014) was saved in this variant.
Example scenario 2 -
URL with Bex workbook – Calmonth variable set to 04/2014
Opening this URL
Bex login, followed by workbook results.
Caveats: (1) coding was bit involved and unfortunately I don't have access to share it. (2) Though sign-on ticket option was there, I didn't tested it. (3) It was tested only with 7x version.
- SAP Managed Tags:
4 Comments
