In the BEx to BOBJ migration projects you will come across many limitations and functionality that cannot be passed to BOBJ. This blog will give you step by step solution on one of the limitation in WEBi XI 3.1.
Business Scenario
You want to migrate the BEx query to BOBJ WEBi in which a variable of type replacement path with query is used. The pre query/replacement path query has a ready for input variable thus the BEx query cannot be made available for OLAP.
Example Scenario:
AS-IS Query: ZTEST_MAINQUERY
Replacement Path Variable on 0EMPLOYEE
Replacement Path Query/Pre Query: ZTEST_PREQUERY
Key Date variable: ZVARA - Reporting Key date (Single value; Mandatory; Ready for Input)
When the AS-IS query (ZTEST_MAINQUERY) is ran the variable “ZVARA” also comes as pop up.
The Replacement Path Query/Pre Query runs and the output of the report becomes the input to the AS-IS Query.
Issue:
When the query is made available for OLAP (Tick the property: Allow external access for the query) we get the below error while running the Replacement path query which has the ready for input variable.
'A database error occurred. The database error text is... (WIS 10901)' error when trying to run the Web I report
Cannot run a report that contains replacement path with prompt
SAP Note: 820925
Step By Step Solution 1)
Below solution can be used if the Pre Query is built on Master data or DSO as it would be easy to read the Master data and DSO tables.
1) 1) The Replacement path query/Pre Query “ZTEST_PREQUERY” will be removed.
2) 2) Dummy Key figure will be restricted by the variable ZVARA. This variable will be similar to the one present in the Replacement Path query.
3) 3) Create new customer exit variable “ZVAREMP” with below properties:
Multiple Single value
Mandatory
Not Ready for Input
“ZTEST_MAINQUERY”
Replacement Path Query (ZTEST_PREQUERY):
4) PSEUDO CODE:
a) a)Read the user entered “From” and “To” values of “ZVARA” variable
b) b)Store the values in a variable “ZEMPDTFR” and “ZEMPDTTO” respectively
c) c)Read the 0employee master data table with the restrictions present in the PRE Query (Personnel Area and Last date Employed)
d) d)Sort the internal table
e) e)Append l_s_range to e_t_range
ABAP CODE is given at the end of the blog.
5) 5) Make the BEx query available for external access and create the universe on the BEx query.
6) 6)Create the BOBJ Report on the universe
7) 7)Enter the prompt values and run the report. The ABAP code will be executed and restrict the ZTEST_MAINQUERY with the same employee as the ZTEST_PREQUERY would have restricted.
😎 7)BOBJ Report and the BEx query result will match.
Step by Step Solution 2)
It would be a difficult task to read the Cube, Multi provider or Info set data by writing the ABAP code for customer Exit variable.
Pre Query: ZTEST_PREQUERY is on cube.
In this solution we will use the Pre query along with the main query.
1) 1) Replacement path variable will be removed from the main query (ZTEST_MAINQUERY) and no changes will be done to the Pre Query (ZTEST_PREQUERY).
2) 2) Create the Universe on both the queries (ZTEST_MAINQUERY and ZTEST_PREQUERY) and make the copy of the Detail Object (Example: Employee Key on which the replacement path variable was created in the Main Query) and save it as the dimension.
3) 3) Use both the queries for the BO Report development.
4) 4) Take the Employee Key dimension from both the Universe and the required fields in the report (at least one measure has to be dragged in the report pan) as shown below. Run the report, the prompt will come from both the BEx queries.
5)
6) 5) Create a merge dimension by using the Employee Key dimension created in the universe.
7) 6) Drag the Employee key from the Pre Query and all the required fields from the Main Query. This works similar to right/left outer join in RDBMS i.e. the data will come for all the employees from the replacement path query.
😎 7) Ran the report and the prompts will come for both the queries and the data will be displayed for the Employees from Pre query only.
I would publish few more BLOGs on the BOBJ WEBi tool limitations and the solution/workaround for the same.
Look out for the my blogs :smile:
Source Code to read the 0employee master data.
CASE I_VNAM.
WHEN 'ZVAREMP'.
IF i_step = 2.
READ TABLE i_t_var_range INTO l_s_var_range
WITH KEY vnam = 'ZVARA'.
ZEMPDTFR = l_s_var_range-low.
ZEMPDTTO = l_s_var_range-high.
*fill Internal Table
IF sy-subrc = 0.
SELECT DISTINCT EMPLOYEE
INTO CORRESPONDING FIELDS OF TABLE t_empl
FROM /BI0/QEMPLOYEE
WHERE /BIC/ZHRDATZB BETWEEN ZEMPDTFR and ZEMPDTTO
and PERS_AREA IN ('AB', 'CD', 'EF', 'GH', 'XY', 'UV')
ENDIF.
* End of fill Internal tableSORT t_empl.
LOOP AT t_empl.
l_s_range-low = t_empl-EMPLOYEE.append l_s_range TO e_t_range.
ENDLOOP.
ENDIF.
ENDCASE.
- SAP Managed Tags:
