Business Objects: Create data query in business objects WEBI

In order to ask for data retrieval from Database or any support data source by business objects you need to create a query in your report. In this query you have to identify what you want to include in your report and you will define query filters as well to narrow the returned data results. If you are familiar with SQL then you will not find any troubles to understand this section. If no then you will learn SQL select statement as well as how to create query in SAP business objects WEBI.

SQL select statement:

the simple SQL statement should contains at least the following SQL clause Select-From-Where. After select clause you should enter the data that you want to display and retrieve from database. In the from clause you have to specify from which tables. And in the where clause you need to set you filters. Lets take an example

Select Statement Syntax:

Select {List of columns that you want to display}

From {List of tables}

Where {Criteria to narrow the number of returning records}

Example:

Select employee_Name, employee id
From employee
Where employee grade =A

Create Data Query in BO WEBI Report:

Now check the data query window below. you can easily find that it almost the same concept & logic. Your available data listed in the left panel. As you can see it is grouped in folder as per their business meaning and relation. you can also differentiate between dimensions, Measures & detail objects. On the right hand side you can see the select area as well as the where or filter panel. To create a query simply drag and drop data from the left side panel to the select area. if you want to create a filter, then you need to drop your object in the where are instead,

Finally to run the query just click on the refresh data button.

Create query tutorial

Find below a step by step user guide and tutorial with examples on how to create a data query in SAP business objects Webi report. Please note that this apply to rich client as well as info view.

To create a new query in your report go to data or click edit query button. The following window will open

We can classify work area here into 4 main panels

Upper tool bar:

Show hide filter ban: This icon will hide or show the filter ban. Filter ban is the area that represent the Where clause in SQL select statement and it will contains your criteria that will be used to narrow the number of returned records or to only retrieve the data corresponding to your analysis.
Show hide scope of analysis: This will show/ Hide your scope of analysis area. for more information about scope of analysis please click on the following link.
Show / Hide data manager: This icon will show or hide data ban which contains all available data based on the selected universe. We will talk about this topic in details in a moment.
Add Query: This button will create another query. You can create as many queries as you can in your report. you can use the bar at the bottom to navigate through the created queries. Please note that you can include data queries based on different universes in the same report.
Combined query: This icon should be used to create a combined query. Combined query is an advanced query that use the union-intersect or minus. Please note that combined query can be tow or more queries. The only restriction here is that combined queries should be from the same universe.
SQL: If you click on the button you will be able to see the SQL code generated by the report engine to be sent to the database engine to retrieve the required data.

Left panel

Data Business model: this panel will display a categorized list of all objects available based on the selected universe. You can drag and drop dim measures and filters as well or simply double click on them. You can use search feature to look for a specific object by name. You can change the display for the data by selecting one of the following options from the bottom:

Display by object.
Display by hierarchy.

Properties: from the properties panel you can access the following options:

Query Name: You can type a meaningful name for your query here. the default query name is “Query#” where # is the current number of created queries in the current report.
Universe: You can even change the universe from here.
Limits: You can limit the number of records returned by your query by set one of the following options or both: Max rows retrieved or Max retrieval time. Usually we use this option during development time if the number of the returned records is huge. this will help us to test the functionality of the report and to focus on the layout. later one we can disable them when we are on UAT and production stage.
Prompt order: You can change the prompt order from here.

Right panel

Select Area: Here you can drop your objects that you want them to be part of your analysis.

Where Area: Here you should drop your query filter or create them. Pre-defined filters know as conditions are previously created in the universe and you can just drag and use them. on the other hand you can create new customized filter as per your need.

Normal filter will contains the following:

Object: The object that you want to filter one
Operator: Equal to, greater than, less than,….etc
Operand:

Constant
Value from list
Prompt
Result from another query(Any)
Object from this query

Scope of analysis Area:

In Scope of analysis area you can customize your hierarchy levels to be used in the drill down analysis. for more information about what is scope of analysis please check this link

Bottom Bar:

You can manage your queries from the bottom bar. if you right click on this bar you will able to see this menu: You can use this menu to rename query or to run a query, duplicate a query or add a new one.