SAP BI Query Designer

An Nguyen – BI Consultant
Contents

1. Introduction
2. Create, save & change
3. Query Filter
4. Characteristic Properties
5. Formulas, Calculated and Restricted Key figures
6. Variables
7. Conditions And Exceptions

© 2012 An Nguyen 2
Use

BEx Query Designer is a tool to design BI reporting

© 2012 An Nguyen 3
Contents

1. Introduction
2. Create, save & change
3. Query Filter
4. Characteristic Properties
5. Formulas, Calculated and Restricted Key figures
6. Variables
7. Conditions And Exceptions

© 2012 An Nguyen 4
How to design query

 Using Query Designer

 Selecting an InfoProvider for which the query is defined
 Selecting characteristics from the InfoProvider
 Restricting selected characteristics to characteristic values, characteristic value intervals,
or hierarchy nodes
 Using variables for characteristic values, hierarchies, hierarchy nodes, formulas, and
texts or defining new variables where necessary
 Selecting key figures from the InfoProvider
 Formulating calculated key figures
 Restricting key figures by combining them with characteristics
 Arranging the characteristics and key figures in rows or columns and thereby establishing
an initial view for the query analysis

© 2012 An Nguyen 5
Open Query Designer

 1. Open the Query Designer from Start  Programs  Business Explorer  Query
Designer (7.0) version .

© 2012 An Nguyen 6
Logon to BI Server

 2. Choose BW Server

 3. Input username/password

© 2012 An Nguyen 7
Query designer Screen in BI 7.0

© 2012 An Nguyen 8
Query Designer Tool Bar

New Query Save Query Query

Execute Copy
Properties
Open Query Save All Check Query Cut Paste

InfoProvide Rows/Col Conditions Properties Message Documents

r
Filter Cells Exceptions Task Where used list Technical
Name

© 2012 An Nguyen 9
Create New Query

 1. Click on icon New Query… (Ctrl + N) Select InfoProvider

 2. From your history, or from all available

InfoProviders under InfoAreas, select the
InfoProvider that you want to base your query on.

Available objects of InfoProvider

© 2012 An Nguyen 10
Design Query

 Drag and drop the required

characteristics from the InfoProvider
tree to the Rows, Columns, or Free
Characteristics area.

 Characteristics in the Free

Characteristics area will be displayed
in the output by default.
 User can drill down the Free
Characteristics if required.

Free Characteristics
Result

© 2012 An Nguyen 11
Structure

 A structure appears in the Query Designer automatically if you move a key figure
from the InfoProvider screen area into the rows or columns of the query definition.

Structure

© 2012 An Nguyen 12
Check Query

 When you have created the query, choose Query 

Check in the menu bar to check the query definition

© 2012 An Nguyen 13
Query Properties

 Choose Query  Properties in the menu bar if you want

to change the Description, settings for Result Position,
Display Options and Number Display, or Key Date for the
query.

© 2012 An Nguyen 14
Open Query

 1. Click on Icon Open Query… (Ctrl+O)

 2. Select the query to change

 3. Change the query and save.

© 2012 An Nguyen 15
Find Query

 The Open dialog box contains different views which you can change between using the
application toolbar in the left area of the dialog box.

 (Find) You can use the Search view to

search for BI objects.(not available in Version 3.5
.
 (History) The History view displays the last
15 objects that you used (user and system
dependently) available in Version 3.5 .

 (Favorites) The Favorites view displays

your favorite objects in a tree structure.

 (Roles) The Roles view displays your role-

based objects in a tree structure.

 (InfoAreas) The InfoAreas view displays all

InfoObjects (InfoAreas, InfoCubes, queries, and
query views) in a tree structure.

© 2012 An Nguyen 16
Save Query

 Click on button Save Query (Ctrl + S)

 Enter a Technical Name and a

Description for the query.

© 2012 An Nguyen 17
Contents

1. Introduction
2. Create, save & change
3. Query Filter
4. Characteristic Properties
5. Formulas, Calculated and Restricted Key figures
6. Variables
7. Conditions And Exceptions

© 2012 An Nguyen 18
Features

 Filters are used in reporting, analysis, and planning to restrict data to a certain business
sector, product group, or time period, for example.

 By taking a subset of a dataset in this way, you can ensure that users or user groups only
have access to the data that is relevant for them; you can also ensure that only certain
data areas are visible to them within an application scenario.

 The filter selection restricts the entire query.

 You can select characteristics from the directory tree of the InfoProvider and restrict them
to individual characteristic values; you can also select individual characteristic values or a
key figure.

© 2012 An Nguyen 19
Restrict the Characteristic

 Restricting in Characteristic Restrictions will help to restrict the data set exposed through
the query to the end user

 Expand the required dimension directory

and first select one or more
characteristics that you wish to restrict.

 Restrict the Characteristic using right click

 You can restrict characteristics in the filter

to:
 Single characteristic values
 Characteristic value variables
 Value ranges

© 2012 An Nguyen 20
Restrict the Characteristic

 You can restrict to specific fixed value of

the selected characteristics.

 Excluding Values from the Selection

 You use this function to select the
values that you do not need for your
report.
 Select the desired value in the right
Selection window.
 Choose Exclude from Selection.

 Division Restricted

 Result: Restricted

© 2012 An Nguyen 21
Single Values ( Hierarchy ) Restrictions

 You can restrict to specific

hierarchy node of the selected
characteristics.

 Restricted to Singapore hierarchy  Customer Restricted to Singapore Node

© 2012 An Nguyen 22
Characteristic value variables

 When you select characteristic values in

the query definition, you can also select
variables instead of fixed values.

 Restricted to current Month

 Restricted to current Month

© 2012 An Nguyen 23
Value Ranges Restrictions

 You can Right click on the InfoObject

select the value range for restriction.

 Select your required operator from the

dropdown box.
 You have the following operators:
 Between
 Less Than or Equal to
 Greater Than or Equal to
 Less Than  Month restricted between Jul to Sep-2010
 Greater Than

 Restricted to the selected date range

© 2012 An Nguyen 24
Variable Offsets

 Select the required variable in the right

Selection window.
 Choose Set Variable Offsets.
 Enter the required offset value.

 Restricted to Current Month -2 and Current Month

© 2012 An Nguyen 25
Key Figure Filter

 If you want the query data to refer to the

numerical values of one key figure then,
select the appropriate key figure from the
InfoProvider tree and drag it to the Filter
area.

 Once you have selected key figure filter then

no key figure is allowed in the rows ,
columns and free characteristics.

 Selected key figure in the Characteristic

Restrictions

© 2012 An Nguyen 26
Default Values (Query Filter)

 Define default values for the filter:

 By specifying default values, you set the initial filter state
for the query to be executed.
 Define default values for the filter by dragging the
required filter values into the Default Values area of the
filter.
 Users can change the default values at runtime by
choosing Change Filter Values and changing the values
in the input help dialog.
 The way you restrict the default Values is same as
restricting the Characteristics restriction.

User can change the

default value in the filter.

© 2012 An Nguyen 27
Contents

1. Introduction
2. Create, save & change
3. Query Filter
4. Characteristic Properties
5. Formulas, Calculated and Restricted Key figures
6. Variables
7. Conditions And Exceptions

© 2012 An Nguyen 28
Characteristic Properties

Click on Characteristic
Tab Page: General
 Description: The name of the characteristic appears here
automatically. You can overwrite this text.
 Technical Name: The technical name of the characteristic is
displayed here.
Tab Page: Display
 Value Display: Under Display As, you can specify whether and
in which format the individual characteristic values of the
characteristic are displayed.
 No Display: The characteristic display is hidden.
 Key and Text: The characteristic values are displayed by their
technical key and text.
 Text: The characteristic values are displayed by their text.
 Key: The characteristic values are displayed by their technical
key.
 Text and Key: The characteristic values are displayed by their
text and technical key.

© 2012 An Nguyen 29
Display Tab

 Value Display
 If you set a display type that contains text, you can choose
under Text Display which text type you want to set. The
following options are available:
 Standard: The shortest available text for the characteristic
values is used.
 Short Text: The short text for the characteristic values is used.
 Long Text: The long text for the characteristic values is used.
 Medium Text: The medium text for the characteristic values is
used.

© 2012 An Nguyen 30
Display Tab - Sorting

 You can set the sorting within the characteristic according to the key or text in ascending or
descending order.

© 2012 An Nguyen 31
Display Tab - Result

 You can choose whether the results rows are Always Displayed, Always Suppressed, or
Displayed with More Than One Single Value.

Always Displayed Always Suppressed

© 2012 An Nguyen 32
Contents

1. Introduction
2. Create, save & change
3. Query Filter
4. Characteristic Properties
5. Formulas, Calculated and Restricted Key figures
6. Variables
7. Conditions And Exceptions

© 2012 An Nguyen 33
Formulas

 You can recalculate the key figures in a structure using a formula. You can include basic
key figures, restricted key figures, and calculated key figures in the formula definition.
Create New Formula

Formula

Result

© 2012 An Nguyen 34
Percentage Functions

Percentage Deviation (%)

 <Operand1> % <Operand2>
 This gives the percentage deviation between operand 1 and operand 2. This is identical to:
 Formula 100 * (<Operand1> - <Operand2>) / abs(<Operand2>)
Percentage Share (%A)
 <Operand1> %A <Operand2>
 This gives the percentage share of operand 1 and operand 2.
 It is identical to formula 100 * <Operand1> / abs(<Operand2>)

© 2012 An Nguyen 35
Percentage Functions

Percentage Share of the Overall

Result (%GT)
 %GT <Operand>

 Result

© 2012 An Nguyen 36
Create New Restricted Key Figure

 You can restrict the key figures of an InfoProvider for reuse by selecting one or more
characteristics.
 The key figures that are restricted by one or more characteristic selections can be basic
key figures, calculated key figures, or key figures that are already restricted.

Create new restricted key figure

Using drag and drop, choose a

key figure from the InfoProvider

© 2012 An Nguyen 37
Create New Restricted Key Figure

 Drag and drop the

restricted key figure
in to the column

 Result

© 2012 An Nguyen 38
Create New Calculated Key Figure

 In the Query Designer, you use formulas to recalculate the key figures in an InfoProvider so
that you can reuse them for all the queries for that info provider.
 Calculated key figures consist of formula definitions containing basic key figures, restricted
key figures or pre-calculated key figures.

Create new Calculated Key Figure

Formula

© 2012 An Nguyen 39
Create New Calculated Key Figure

 Drag and drop the restricted/Calculated key figure in to the column

 Result

© 2012 An Nguyen 40
Available operators

 Basic functions
 Percentage Functions
 Data Functions
 Mathematical functions
 Trigonometric functions
 Boolean Operators

© 2012 An Nguyen 41
Contents

1. Introduction
2. Create, save & change
3. Query Filter
4. Characteristic Properties
5. Formulas, Calculated and Restricted Key figures
6. Variables
7. Conditions And Exceptions

© 2012 An Nguyen 42
Characteristic value variables

 Characteristic value variables represent characteristic values and can be used wherever
characteristic values can be used.
Create new variable on the
required characteristics

Variable property dialog box

Enter the variable

name and technical
name

© 2012 An Nguyen 43
Characteristic value variables

Set the default value for the variable

Set the default value for the variable

Restrict the default values for the

Pur. Org to variable Restrict the default value

© 2012 An Nguyen 44
Contents

1. Introduction
2. Create, save & change
3. Query Filter
4. Characteristic Properties
5. Formulas, Calculated and Restricted Key figures
6. Variables
7. Conditions And Exceptions

© 2012 An Nguyen 45
Condition

 Conditions are used to display the set of data in which you Create new condition
are interested e.g. . Profit Centers which has sales more
than 2 Million.
 In the results area of the query, the data is filtered according
to the conditions so that only the part of the results area that
you are interested in is displayed.

 Result

© 2012 An Nguyen 46
Exception

 You can define threshold values (exceptions) for a query.

 Exceptions can be used to highlight the specific results for Sales office or Customer actual
sales is less than 70% of the planned sales.

 Data that varies from these thresholds is highlighted in color or marked with icons.

You can use these exceptions to identify

deviations from expected results at a glance.

© 2012 An Nguyen 47
Exception

 In the Exceptions screen area, choose New

Exception in the context menu.
 Define threshold values (exceptions) for a
query.

 You can choose from the following alert

levels: Good 1, Good 2, Good 3, Critical 1,
Critical 2, Critical 3, Bad 1, Bad 2, Bad 3
 You can choose from the following
operators:
 = Equal To
 <> Not Equal To
 > Greater Than
 >= Greater Than or Equal To
 < Less Than
 <= Less Than or Equal To
 [] Between
 ][ Not Between
© 2012 An Nguyen 48
Exception

 You specify which key figures the

exception is to affect. The exception
can affect all structure elements of a
structure or a selected structure
element.

© 2012 An Nguyen 49
You define queries by

 Selecting an InfoProvider for which the query is defined

 Selecting characteristics from the InfoProvider
 Restricting selected characteristics to characteristic values, characteristic value intervals, or
hierarchy nodes
 Using variables for characteristic values, hierarchies, hierarchy nodes, formulas, and texts
or defining new variables where necessary
 Selecting key figures from the InfoProvider
 Formulating calculated key figures
 Restricting key figures by combining them with characteristics
 Arranging the characteristics and key figures in rows or columns and thereby establishing
an initial view for the query analysis

© 2012 An Nguyen 50
 Thank you!

© 2012 An Nguyen 51