A key date variable allows you to specify a date for time-dependent data.

 

The key date selection influnce the data retrived: for example the region name can change with time, so dependent on the date selected the region name will be given.

 

We had a time dependent master data for Geography.

We created a Key Date variable “ZKEYDT(Enter Key Date)” which was a ready for input variable created on 0calday.

Universe was created on the BEx query and the report was created using WEBi.

When the report was executed the prompt for Key Date was not coming in WEBi.

 

Solution:

If we have a Key Date variable created at the BEx query, we have to mark “YES” for the KEYDATE_ENABLE parameter at the universe level (as seen below)

 

Key Date Parameters.png

Also if the Key date variable is created on 0calday you need to remove the char 0calday from rows/column of BEx query.

Key date variable properties are mapped to the five universe parameters

 

Keydate_enable = Set to yes if the key date is enabled on the universe

Keydate_name = Technical Name of the Key date Variable

Keydate_Caption = Text of the Key date variable

Keydate_Default_value = Default value of the keydate if it exists

Keydate_mandatory = set to yes if a user must enter a value or use the default

 

We had one more requirement with respect to the key date variable. I would like to share the same too.

 

We had a BEx query on 0Employee master data which had a key date variable prompt.

It was a yearly report and user ran the BEx report 12 times to get the data for each month start data and 12 times to get the month end data.User use to get the data for each month and paste it in excel and create the report for the complete year.

 

For Example: For Month End Data

 

For Jan : Input to Key date variable was 31/01/2012

For Feb :  Input to Key date variable was 29/02/2012

For Mar : Input to Key date variable was 31/03/2012 and so on.

In WEBi user did not wanted to run the report 12 times.

 

Solution:

We create the Universe on the BEx query and used the same universe 12 times in the WEBi query designer.

We named query as Jan,Feb,mar……,Nov,Dec.

Using the merged dimension property at the WEBi level we merged all the 12 months queries to show the data. When the User ran the report , he gets the prompts for each month where he can input the start or end date of the month and run the report.

User got the data by runnig the report just once.

 

Important Document:

Web Intelligence on SAP implementation best practices

 

I hope the blog helps you all.

Senario:

The BEx query had the Percentage Function - >%GT (SUMGT) used in one of the key figure. % GT (SUMGT)function gives the Percentage Share of overall result. The overall result means the result of aggregation at the next level in the list.

 

If a BEx query is using DATA functions it cannot be enabled for the External access, and cannot be used as a data source for BO Reporting. Thus we need to remove these Data functions from the query and need to implement at BO WEBI report level. Below solution shows how the %GT function can be achieved in WEBi.

 

Solution:

 

Example Data:

Below report shows the number of employee hired by year and quarter. In BEx query we can get the percentage share of overall result using the %GT function.

 

YearQuarterNumber of Employee Hired
2010Q1600
2010Q2500
2010Q3400
2010Q4300
2011Q1300
2011Q2400
2011Q3500
2011Q4600

 

Same can be implemented in WEBi using the already available functions.

 

Percentage Function:

 

We can use the Percentage function for calculating the percentages. The function calculates the percentage of a number in relation to its surrounding context. We can use the formula Percentage ([Number of Employee Hired]) and get the below result.

 

YearQuarterNumber of Employee HiredPercentage
2010Q16000.17
2010Q25000.14
2010Q34000.11
2010Q43000.08
2011Q13000.08
2011Q24000.11
2011Q35000.14
2011Q46000.17

 

The function calculates the Number of Employee hired as a percentage of the number of employee hired. The function will work even if the report is split into sections by year. In this case the surrounding context outside the table becomes the total employee hired as seen below.

 

YearQuarterNumber of Employee HiredPercentage
2010Q16000.33
2010Q25000.28
2010Q34000.22
2010Q43000.17

 

Sum Function:

 

We can also make use of the Sum function along with the extended syntax keywords Report and Section to calculate the overall total employee hired and the yearly employee hired respectively.

 

Column "Percentage of Total" has below formula:

 

[ Number of Employee Hired ]/ ( SUM ( [ Number of Employee Hired ] In Report ))

 

Column "Percentage of Year" has below formula:

 

[ Number of Employee Hired ]/ ( SUM ( [ Number of Employee Hired ] In Section ))

 

YearQuarterNumber of Employee Hired% of Total% of Year
2010Q16000.170.33
2010Q25000.140.28
2010Q34000.110.22
2010Q43000.080.17

Hierarchy layout in WEBI XI3.1 does not come in the same way as in BEx query output.

We do have a drill function in WEBi which will drill the hirerchy from one level to another, but we cannot see all the levels of hierarchies when drilled as we see in the BEx query output.

 

FOLD/UNFOLD Function:

 

If we do not want to see the detailed rows in our report we can use the fold/unfold function.When table or break is folded the rows are collapsed and only the header and footers are displayed.

In WEBi XI3.1 we can make use of the FOLD/UNFOLD functionality to get the same hierarchy structure that we see when we run the BEx query.

 

Note: Tables must have header/footer to be folded and unfolded.

 

BEx query output:

Hierarchy in BEx.png

 

 

Below are the steps to achive the same hierarchy layout as we have in BEx.

 

1) Drag all the level (L00,L01,L02 etc) of hierarchy and the required measures in the WEBi query.

2) Apply break on the dimension object (L00 and L01 dimension objects i.e. the first and second level of hierarchy as we have 3 levels in the current example) on which the hierarchy is to be created(break is not applied in the last level i.e. L02).

3) Drag the measure in the rows, the measure value will be summed up for L00 and L01, if not then sum the measures value at each break.

4) Enable the Fold/Unfold function as shown in the below figure.

 

Fold_unfold 2.png

As you can see that the measure value for L01 (India) is the 106 which is the sum of measure value for L01 (Maharashtra(6),Goa(68) and Punjab(32)). Similarly for L01 the measure value will be sum of the measure value for L02 object. We have the L00, L01 and L02 dimension objects draged in the report and have hidden the border of the columns to make the hierarchy structure look the same as we have in BEx.

Note: The above solution cannot be implemented if we have more then one hierarchy displayed in the report.

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.

 

 

BEx Variable.png

 

3)     3) Create new customer exit variable “ZVAREMP” with below properties:

      Multiple Single value

      Mandatory

      Not Ready for Input

 

  “ZTEST_MAINQUERY”

 

BEx variable 2.png

Replacement Path Query (ZTEST_PREQUERY):

BEx Query 3.png

 

 

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.

8)     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.

 

BO Report 1.png

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.

 

BO Report 2.png

 

8)    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

 

 

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.

Actions

Filter Blog

By author:
By date:
By tag: