If a BEx Query is using data functions, it cannot be used as a data source for BO, thus we need to remove these data functions from the query and implement it to BO WEBi report level.Below solution shows hoe the %GT function can be implemented in WEBi.

BO Solution: Calculation Context

%GT functions can easily be implemented in BO using Calculation Context.

In BO report by default the calculation context is define by the dimensions fetched on to the report.Adding or removing the dimensions from the report changes the measure amount aggregation.

However, if user wants to remove or add any dimension from the calculation context without adding or removing the dimension from the report block the this can be achieved using the calculation context.

As in below example Contribution percentage can be calculated using the formula:

=[REVENUE] / [REVENUE] In Report

The IN Report Context projects the measure up to the overall report total.By using this calculation context in the report denominator we can ensure that we will always be dividing by the grand total.

Please note we cannot achieve the above result by using =Sum[REVENUE] in denominator because Sum will be calculated in context with Resort dimension, so =Sum[REVENUE] will give $971,444.00 only when placed in adjacent column to revenue.

Hi All,

   Just sharing this tip that is useful at times. You can restrict no of rows in your webi report tables as per your requirement. I used it for displaying the input selection values of a block on the top of my report as there were no of tables involved with input control defined for other tables and its not easy for the user to remember selections from all of them. As of now there is no known way to display your input control selections on a block in BOBJ 3.1. You can only display report filters provided as a standard function.

   In our case we have two tables Tab1 & Tab2. Tab1 is showing month wise data. Tab1 is defined as input control for Tab2.Tab2 is showing dealer wise breakup for the selected month. Now user wants to see the selected month on the top of the report. Here are the steps:

1. Insert a table (Tab3) without a header on top of the report. Pass calendar month dim to it. Define input control from Tab1 to Tab3.

   First it will look like this

2. Once you select a row from Tab1 it will restrict values in Tab3. Let say you selected Dec 2011, Tab3 will look as below:

3. Logically it will solve your purpose to show Input Selection of Tab1. But still its advisable to restrict it for a single row. Otherwise in some exceptional cases

    if system assumes nothing is selected on Tab1 then all the values will be displayed on Tab3 and the table may overlap any below object.

4. To do that define a new variable Var1 with the formula: " RunningCount([L01 Calendar Year/Month]) ". It will give serial no for all the records in the table as

     below:

5. Remove Var1 from Tab3 and restrict var1 in Report Filter on Tab3 with value as constant = 1.

Now Tab3 is restricted for 1 row any time and it will display selected dim from Tab1. One can use this trick to see input selections on other tables on report so that user will know at what drill level he is analyzing the data.

Regards
Jitendra

Hi All,

  As a good practice it is recommended to make use of existing variables while creating a BEx query. Variable descriptions are fixed at the time we define them for a characteristic. A report developer assigns a descripton more relevant to report user. If a variable with same properties is required in some other report intended for a user who is part of some other business unit, for example vendor/transporter/service provider all are same but have different calling category for different business units. In these cases you do not have much choices at Bex level, you have to create a different variable if you want a different description on selection screen.

  If you are using Webi as your reporting solution then you can make use of same variable for any number of requirements without changing its base description (defined at BEx level).

For example a user who is performing analysis on Freight data will call its vendor as transporter. So webi selection screen will show Vendor by default (as maintained in BEx query variable).

Now we can fix its description in universe as below:

1) Open the underlying universe in Designer. Goto filters and double click the filter for vendor

2) A new window will open. Go in the where section and chnge descriptions maintained after @Prompt

Before Change:

After Change:

Parse and export the universe.

3) Open your webi report and refresh, you will see your selections with new descriptions.

Hope it will be useful to all BW-BOBJ developers. So make use of what system already have rather than spending some time to create a replica.

Regards:

Jitendra

Replacement Path in BEx Query and BO Workaround

You want to make BEx query accessible to Business Objects, in which a variable of type replacement path with query is used.The replacement path query has a ready for input variable thus the BEx query cannot be made available for OLAP (SAP note: #820925).

In the above example YA_FPI is replacement path query with ready for input variable 'Employee'.

BEx Output:

Shown below is the output of the main query that is based on the Employee(Person) fetched from the replacement path query.

BO Alternate Solution: Merging Dimensions

The same functionality can be achieved using the merged dimension functionality in BOBJ report.Create a report with 2 dataproviders: 1. Replacement Path Query, 2. Main Query and merge based on similar dimension (Replacement path variable from replacement path query and similar dimension from the main query should be merged).

Drag object from replacement path query(replacement path variable) and remaining objects from the main query.

This works similar way the left and right outer join works in RDBMS. See the below workflow for better understanding:

• Create 2 universes

       1. on replacement path query

       2. on main query

• Create a report based on above 2 dataproviders(Universe).

• Merge the object(replacement path variable) from replacement path query with the similar dimension object in the main query. As shown in below example - Employee Key from replacement path is merged with Employee Key from main query.

• Now we want replacement path query to drive the results, to achieve this open up each merged dimension object and use the dimension key that come from the replacement path query and fetch the other objects from the main query.

Here's what the block kook like:

BO Output:

This way the objects from main query will be restricted only for those Employee Keys(Person) fetched from the replacement path query, as the dimensions are merged.