SAP BusinessObjects Web Intelligence has an extensive connectivity to datasources from various source systems like SAP BW, SAP HANA, Universes and a lot more.
Most of the customers using SAP BusinessObjects for reporting, use SAP BW as the primary source along with many other Relational reporting data sources which are Universes.
Use a Relational universe (.unx) to create different sets of time period parameters and use them as default values to schedule Webi reports built on top of the BEx reports without making any changes to the underlying BEx queries or the customer exits that are already in use.
I’m considering a situation where WebI reports are built on BW OLAP universes or BEx queries through OLAP connection. The BW queries have Manual input variables on time characteristics. It could be a variable of Month, Week, Year or Fiscal period, etc.
In this blog we will be discussing about a particular scenario where a BEx query (Power query with all the information about Supply chain management) is created with Manual Input Month variables (No default values). This query has also been used to develop various other WebI reports and the users have also started to schedule these reports.
Later in some point of time, a few users wanted to schedule this report to run for the next 6 months while some users wanted it to run for last 6 months and so the prompt values needed to be manually changed every month which probably requires extra effort for the business users.
In this case, enhancing the BEx queries is a quite tedious process. If we consider the option to create a new query just for this, it is going to be a redundant effort with increase in maintainability of objects. Users need to switch to the new query, which users might not be interested in.
So, in this blog you’ll see how we can achieve this by using input parameters in the universe without making any changes in the existing BEx query
Let us see how to populate the Default values to 2 BEx variables.
1.From Month : value of current month
2.To Month : value of the 6th month from the current month
We will derive the default values as parameters from the universe. Here, the source system on which the universe is built is Oracle and it is much easier to derive parameters from universes.
Steps to derive Default Values:
1. Open Information Design Tool (IDT) -> Create a new business layer with Month object on relational connection
2. Select the Parameters and List of Values pane
Prompt variable text should be the same as the BEx variable description text or prompt text defined at OLAP universe level, so that merging of prompts will happen without any hassle.
Depending on the BEx variable representation BOBJ will append “From” / ”To” text to variable description.
3. For Current Month value
Create a parameter -> Fill in the prompt text, data type and associated list of values.
Select default values -> Formula
Enter the following formula: formatdate(CurrentDate();”YYYYMM”)
4. Similarly for the next 6 Months
Select default values -> Formula
5. Add the ORACLE universe with defined parameters to existing report.
- Additional customer exits are not required.
- No further enhancements need to be made on the existing BEx variables/queries.
- We can have as many as parameters from ORACLE universe like for the next 6 months, last 6 months, the next 10 weeks, last 10 weeks, next 45 days or last 45 days and so on.
- We can also use these parameters along with BW universes and BICS connection prompts
- We need to add another universe to existing report. Though this will not have any impact on the performance as only Month dimensions are added to result objects.
- If the order in which the prompt should be given is important, then we may need to work it out carefully to have the variable in the required order at the report level.
This idea is to utilize the capability of parameters which comes along with Universes and this is mainly to minimize enhancements or additional customer exits at the BW level.
We can have an additional universe with input parameters for all the required time period combinations and use them along with the existing reports which are built on BEx queries.
Got questions? Click here to get in touch.