Determining Constant Selection
Exercise 3: Create Restricted Key Figures
Exercise Objectives
After completing this exercise, you will be able to:
• Create restricted key figures at InfoProvider and query level
• Mark a defined selection as being constant
Business Example
To analyze the sales volumes of your customers, you need additional key figures that are not contained in the InfoCube.
In your report you want to generate an overview of the sales volumes in various time periods for each sold-to party. You also need detailed information on sales volumes, restricted to the division High Tech, for your individual sold-to parties.
You need three different restricted key figures to do this. In each case, you use the basic key figure Sales Volume EUR, and then proceed according to the view you want.
Task 1:
Define two restricted key figures that depict the Sales Volume EUR for the period leading up to 2006 and for the year 2006 itself, and additionally a third restricted key figure representing the Sales Volume EUR, again for the year 2006, but restricted to the division High Tech.
1. Create a new query using the InfoProvider MultiProvider - Customer Sales (P_SALES01) in the Reporting Layer → Sales and Distribution InfoArea with the characteristic Sold to party (T_SOLD_TO) in the rows.
As a global filter, in the Characteristic Restrictions area, use Sales Organization (0SALESORG) restricted to Germany Frankfurt (1000).
Define a new Restricted Key Figure at the InfoProvider level:
Description: U## Sales Volume EUR before 2006 Technical name: U##_RKF_SV_B_2006
Limit the basic key figure Sales Volume EUR (T_INVCD_V) to the period before 2006 using the characteristic Calendar year (0CALYEAR).
2. In the same query, define the second restricted key figure Sales Volume EUR 2006, this time at query level. This key figure displays the Sales Volume EUR (T_INVCD_V) for the year 2006.
Continued on next page
Please be aware that you have two different techniques to create a restricted key figure on query level: either dragging the basis key figure Sales Volume EUR (T_INVCD_V) into the Key Figure structure and then define the restriction for it, or to choose, within the Key Figure structure, New Selection.
3. Define the third restricted key figure, again at query level.
Description: Sales Volume 2006 EUR Hightech
Use the Sales Volume EUR 2006 key figure as a template, and further restrict it to the division Hightech.
4. Save the query in your role Role## (U##_ROLE) → Queries:
Description: U## Query Restr. Keyf. 001 Technical name: U##_QD_RKF001
Now execute the query in the BEx Analyzer or in the web.
Task 2:
Optional
In the precedent report, as further analysis requirement, there should also be the possibility to show the sales volumes before 2006 and in 2006 restricted to a range of specific divisions, and to compare with the sales volume for the division High Tech in the year 2006.
1. Copy the query definition from task 1 into a further query definition and save it in your role Role## (U##_ROLE) → Queries:
Description: U## Query Restr. Keyf. 002 Technical name: U##_QD_RKF002
2. Enable the users of the report to select data related to specific divisions.
3. Execute the report on the BEx Analyzer or in the web.
Restrict the data in the executed report by filtering on the characteristic Division using the filter values Pumps, Motorcycles, Lighting. What happens with the values of the restricted key figure Sales Volume EUR 2006 Hightech?
___________________________________________________________________________________
4. How can you achieve that the values for the restricted key figure Sales
Solution 3: Create Restricted Key Figures
Task 1:
Define two restricted key figures that depict the Sales Volume EUR for the period leading up to 2006 and for the year 2006 itself, and additionally a third restricted key figure representing the Sales Volume EUR, again for the year 2006, but restricted to the division High Tech.
1. Create a new query using the InfoProvider MultiProvider - Customer Sales (P_SALES01) in the Reporting Layer → Sales and Distribution InfoArea with the characteristic Sold to party (T_SOLD_TO) in the rows.
As a global filter, in the Characteristic Restrictions area, use Sales Organization (0SALESORG) restricted to Germany Frankfurt (1000).
Define a new Restricted Key Figure at the InfoProvider level:
Description: U## Sales Volume EUR before 2006 Technical name: U##_RKF_SV_B_2006
Continued on next page
Limit the basic key figure Sales Volume EUR (T_INVCD_V) to the period before 2006 using the characteristic Calendar year (0CALYEAR).
a) Open the Query Designer.
b) To create a new query, choose the icon New Query from the toolbar.
c) Choose InfoAreas → Reporting Layer → Sales and Distribution → MultiProvider - Customer Sales (P_SALES01)→ Open.
d) Change to the Rows/Columns tabstrip.
e) From the Customer dimension, place the Sold to party (T_SOLD_TO) characteristic into the Rows section.
f) From the Sales area data dimension, drag Sales Organization (0SALESORG) into the Characteristics Restrictions section on the Filter tab page.
g) In the context menu for the characteristic Sales Organization, choose Restrict. In the selection screen, choose Single Values. Select Germany Frankfurt (1000) and adopt the selection using the Arrow button.
Choose OK.
h) To define the first Restricted Key Figure at InfoProvider level, choose New Restricted Key Figure in the context menu of the Key Figures folder. Then choose Edit from the context menu of the new restricted key figure.
Description: U## Sales Volume EUR before 2006 Technical Name: U##_RKF_SV_B_2006
i) Use Drag&Drop to place Sales Volume EUR (T_INVCD_V) into the Details of the Selection area.
j) Choose the Calendar year (0CALYEAR) characteristic under the Time dimension, and place it into the Details of the Selection area.
k) In the context menu for Calendar year, choose Restrict. In the selection screen, choose Value Ranges. Choose Less than. Select 2006 and adopt the selection using the Arrow button. Choose OK.
l) Confirm with OK.
m) To add the newly defined restricted key figure to the query
Please be aware that you have two different techniques to create a restricted key figure on query level: either dragging the basis key figure Sales Volume EUR (T_INVCD_V) into the Key Figure structure and then define the restriction for it, or to choose, within the Key Figure structure, New Selection.
a) To define the second restricted key figure, this time at the query level, choose Sales Volume EUR (T_INVCD_V) from the Key Figures folder, and place it into the Columns section.
In the context menu for Sales Volume EUR (T_INVCD_V) , choose Edit. The Change Selection dialog box appears, you see that the Sales Volume EUR (T_INVCD_V) key figure is already positioned on the right side (Details of the Selection).
Or: Select the last member of the Key Figure Structure (that should be the U## Sales Volume EUR before 2006 (U##_RKF_SV_B_2006) key figure), and use the right mouse button to choose New Selection.
Right-click Selection 1 and choose Edit. The Change Selection dialog box appears. Drag the Sales Volume EUR (T_INVCD_V) key figure from the left side to the right side (Details of the Selection).
b) Still in the Change Selection dialog box, choose the Calendar year characteristic under the Time dimension, and place it into the Details of the Selection area.
c) In the context menu for Calendar year, choose Restrict. In the selection screen, choose Single Value and select 2006.
d) Adopt the selection using the Arrow button. Choose OK.
e) Description: Sales Volume EUR 2006.
f) Confirm with OK.
3. Define the third restricted key figure, again at query level.
Description: Sales Volume 2006 EUR Hightech
Continued on next page
Use the Sales Volume EUR 2006 key figure as a template, and further restrict it to the division Hightech.
a) To define this third key figure, again at the query level, select Sales Volume EUR 2006. Copy it using Copy and Paste from the context menu of the key figure.
b) Select the copy, and choose Edit in its context menu.
c) Change the description into: Sales Volume 2006 EUR Hightech
Note: Note that several lines are available for key figure names. This is to control the column width in the report.
d) Choose the characteristic Division (0DIVISION) from the Sales area data dimension and place it into the Details of the Selection area.
e) In the context menu for Division, choose Restrict.
In the Show field, choose Single Values.
Select High Tech (07) and adopt the selection using the Arrow button.
Choose OK.
4. Save the query in your role Role## (U##_ROLE) → Queries:
Description: U## Query Restr. Keyf. 001 Technical name: U##_QD_RKF001
Now execute the query in the BEx Analyzer or in the web.
a) To save the query, choose Save Query.
b) Choose Roles → Role## (U##_ROLE) → Queries and enter the following description and technical name:
Description: U## Query Restr. Keyf. 001 Technical name: U##_QD_RKF001
c) Choose Save.
d) Execute the query as a report in the BEx Analyzer by choosing the button Exit and use the Query or in the web by choosing the button Execute.
Task 2:
Optional
In the precedent report, as further analysis requirement, there should also be the possibility to show the sales volumes before 2006 and in 2006 restricted to a range of specific divisions, and to compare with the sales volume for the division High Tech in the year 2006.
1. Copy the query definition from task 1 into a further query definition and save it in your role Role## (U##_ROLE) → Queries:
Description: U## Query Restr. Keyf. 002 Technical name: U##_QD_RKF002
a) In the Query Designer, open, if it is not already opened, the query definition U00 Query Restr Keyf 001 (U##_QD_RKF001), go to the menu bar Query → Save as , using the
Description: U##0 Query Restr. Keyf. 002and Technical name: U##_QD_RKF002.
2. Enable the users of the report to select data related to specific divisions.
a) From the Sales area data dimension, drag Division (0DIVISION) into the Default Values section on the Filter tab page or into the Free Characteristics section on the Row/Columns tab page.
b) Save the query.
3. Execute the report on the BEx Analyzer or in the web.
Restrict the data in the executed report by filtering on the characteristic Division using the filter values Pumps, Motorcycles, Lighting. What happens with the values of the restricted key figure Sales Volume EUR 2006 Hightech?
Continued on next page
___________________________________________________________________________________
a) Execute the query as a report in the BEx Analyzer by choosing the button Exit and use the Query or in the web by choosing the button Execute.
b) BEx Analyzer
• In the executed report, open the Navigation pane by pressing the Filter button.
• In the navigation pane, Right-click on the characteristic Division and choose Select Filter Values.
• In the selection screen, Show Single Values, highlight the values Pumps (01), Motorcycles (02), Lightning (04), and choose OK.
Web Analyzer
• Right-click on the characteristic Division and choose Filter → Select Filter Value
• Highlight the values Pumps (01), Motorcycles (02), Lightning (04), and choose Add.
• Choose OK.
c) The whole report is now restricted to the selected values for division.
Thus there are no more values for the restricted key figure Sales Volume EUR 2006 Hightech!
4. How can you achieve that the values for the restricted key figure Sales Volume EUR 2006 Hightech are not influenced by navigation, in our case by filtering on the characteristic Division?
Test your solution using a copy of the restricted key figure Sales Volume EUR 2006 Hightech
a) Back in the query definition, in the Columns area, select Sales Volume EUR 2006 Hightech. Copy it using Copy and Paste from the context menu of the key figure.
b) Select the copy, Right click, in the upcoming Change Selection → Details of the Selection window, Right-click Division, and check Constant Selection.
Adapt the description: Sales Volume EUR 2006 Hightech Const. Sel.
Lesson Summary
You should now be able to:
• Explain the objectives and use of restricted key figures in information analyses
• Define restricted key figures at the InfoProvider level and at the query level
• Explain the use of constant selection