Configuring the properties of the query
Exercise 2: Create Simple Queries
Exercise Objectives
After completing this exercise, you will be able to:
• Use the BEx Query Designer to create your own simple queries
• Finding and changing existing queries
• Use the enhanced selection function for selecting restriction values
• Define and use reusable filters
• Execute queries in the BEx Analyzer and in a Web browser
Business Example
In order to evaluate the data from an InfoProvider, you require queries that are tailored to your individual requirements. You have been given the task of creating these query definitions.
Task 1:
You are interested in finding out the incoming order values and the incoming order quantities of your customers for specific materials between January and April 2005.
You want to enable the users of your report to select by specific countries.
1. Create a new query using the MultiProvider - Customer Sales (P_SALES01) InfoProvider in the Reporting Layer → Sales and Distribution InfoArea.
Position the characteristic Sold to party (T_SOLD_TO) into the rows.
Use the basic key figures Incoming Orders EUR (T_INCORDV) and Order Entry Quantity (0INCORDQTY) in the columns.
2. Save the query in your role Role## (U##_ROLE) → Queries:
Description: U00 Query First Steps 001 Technical name: U##_QD_FS001
Execute the query in the BEx Analyzer, view the results, then close the query results (do not close the BEx Analyzer).
3. In the next step, you want to change the query definition. Find the query you created (U## Query First Steps 001) using the search function.
In the query definition, include the material in the report in order to display the incoming orders for each material for the customers. You also want to filter the data with a characteristic restriction to only report data for the months 01.2005 to 04.2005.
4. Enable the users of your report to select data related to specific countries (Country (T_SOLD_TO__0COUNTRY)). Then save and execute the query. In the local view of the executed query, remove the material from the drilldown and include the country in the drilldown before sold-to party to list the sold-to parties by country. Also in the local view, restrict the country to Germany and Norway.
5. In a further step, in the global definition of the query, establish the restriction for the country to Germany and Norway as default values. Execute the report and make a note of which sold-to parties are listed in your report. Are you able to select other countries in the report?
6. Now execute your query on the Web.
Task 2:
Create a further query for the time period from April 2005 to June 2005 about the Sales Volume EUR and the Invoiced quantity of the customers and their materials and from the sales organization Germany Frankfurt. Initially, the flat screens are in the focus, but there has to be the option of selecting further materials. There should also be the option of a drilldown for month and with the possibility to select values for other months, but only within the year 2005. In the executed report try out the different options for filtering and arranging the drilldown. Additionally, store the
“Selection Situation” as a reusable Filter.
1. Define a new query, again basing on the MultiProvider - Customer Sales (P_SALES01). Include the Sold to party (T_SOLD_TO) and Material
(0MATERIAL) in the rows and the key figures Sales Volume EUR (T_INVCD_V) and Invoiced quantity (0INVCD_QTY) in the columns of your query definition.
Using Sales Organization (0SALESORG), restrict the report to Germany Frankfurt (1000).
Include Cal. Year/Month (0CALMONTH) into the query definition in order to be able to navigate with.
For the initial view, you are only interested in the flat screens Flatscreen MS 1460 P (M-06) to Flatscreen MS 1775P (M-10) and in the months April 2005 to June 2005, but in the executed report, you should also have the possibility to select values for other materials and for other months, but only within the year 2005.
2. Save the query in your role Role## (U##_ROLE) → Queries:
Description: U## Query First Steps 002 Technical name: U##_QD_FS002
Execute the query in the BEx Analyzer.
Try out the different options for arranging the drilldown and for filtering. E.g.
in order to list the sales volumes for the individual months in your report, drill across on Cal. year/month in the columns. Finally, verify which months can be selected when filtering.
3. Now execute your query on the Web.
4. Back in the query definition, drag the key figure structure into the rows under the characteristic material. Additionally, the repeated key values of the characteristics should not be hidden any more. Verify now the drilldown in the executed report.
At last, go back to the Query Designer and drag the key figure structure back into the columns area again.
5. Finally, store the “Selection Situation” as a reusable Filter. For the filter, use the Description U## Filter 001 and the Technical Name U##_FILTER_001.
What can be done with such a reusable Filter?
_________________________________________________________________________
Solution 2: Create Simple Queries
Task 1:
You are interested in finding out the incoming order values and the incoming order quantities of your customers for specific materials between January and April 2005.
You want to enable the users of your report to select by specific countries.
1. Create a new query using the MultiProvider - Customer Sales (P_SALES01) InfoProvider in the Reporting Layer → Sales and Distribution InfoArea.
Position the characteristic Sold to party (T_SOLD_TO) into the rows.
Use the basic key figures Incoming Orders EUR (T_INCORDV) and Order Entry Quantity (0INCORDQTY) in the columns.
a) To open the BEx Analyzer, choose Start → Programs → Business Explorer
→ Analyzer .
b) Microsoft Excel will be launched in a separate window. In that window, choose Add-Ins, and then from the BEx Analysis Toolbox, choose Tools → Create New Query.
c) The BEx Query Designer opens, to create a new query, choose the icon New Query from the toolbar.
d) Choose InfoArea Reporting Layer → Sales and Distribution to find the MultiProvider - Customer Sales (P_SALES01) InfoProvider.
e) Select that InfoProvider and choose Open.
f) Change to the Rows/Columns tabstrip.
g) From the Customer dimension, transfer the Sold to party (T_SOLD_TO) into the Rows using Drag&Drop.
h) From the Key Figures folder, transfer Incoming Orders EUR (T_INCORDV) and Order Entry Quantity (0INCORDQTY) into the Columns using Drag&Drop.
2. Save the query in your role Role## (U##_ROLE) → Queries:
Description: U00 Query First Steps 001 Technical name: U##_QD_FS001
Execute the query in the BEx Analyzer, view the results, then close the query results (do not close the BEx Analyzer).
a) To save the query, choose Save Query
b) Choose Roles → Role## (U##_ROLE) → Queries and enter the following description and technical name:
Description: U00 Query First Steps 001 Technical name: U##_QD_FS001
c) Choose Save.
d) Execute the query as a report in the BEx Analyzer by choosing the button Exit and use the Query.
e) View the results, then close the report results without saving them. Leave the BEx Analyzer open for the next exercise step.
3. In the next step, you want to change the query definition. Find the query you created (U## Query First Steps 001) using the search function.
In the query definition, include the material in the report in order to display the incoming orders for each material for the customers. You also want to filter the data with a characteristic restriction to only report data for the months 01.2005 to 04.2005.
a) Choose Tools → Create New Query to enter the BEx Query Designer.
In the BEx Query Designer, choose Open Query and then Find.
b) Use Search Method → Search in Names, enter FS001 in the search field and activate the Search in Technical Name flag. Press Find to start the search.
c) Select the query U##_QD_FS001 from the selection list and choose Open.
d) In the Query Designer, move the characteristic Material (0MATERIAL) from the Material dimension into the Rows, and position it under the characteristic Sold to party.
e) Choose Cal. year / month (0CALMONTH) characteristic from the Time dimension, and place it into the Characteristic Restriction area on the Filter tab page using Drag&Drop.
f) From the context menu for Cal. year / month, choose Restrict .
In the Select Values for ... screen, choose Value Ranges and then Between.
Select 01.2005 and 04.2005, and transfer the selection using the Arrow button.
Choose OK.
4. Enable the users of your report to select data related to specific countries (Country (T_SOLD_TO__0COUNTRY)). Then save and execute the query. In the local view of the executed query, remove the material from the drilldown and include the country in the drilldown before sold-to party to list the sold-to parties by country. Also in the local view, restrict the country to Germany and Norway.
a) From the Customer dimension, transfer the Country
(T_SOLD_TO__0COUNTRY) into the Free Characteristics area using Drag&Drop.
b) Save the query and then execute it in the BEx Analyzer.
c) Launch the context menu by right mouse click in the result area and choose Query Properties to open the Local Query Properties window.
d) Use the different Arrow buttons to transfer Material from the Rows into the Free Characteristics and Country from the Free characteristics into the Rows and to position Sold to Party below Country.
e) Right-click Country and choose Select Filter Value. Select More to open the Selection window, if necessary. Choose Single Values, select Germany and Norway, then transfer the selection using the Arrow button. Choose OK f) View the results.
5. In a further step, in the global definition of the query, establish the restriction for the country to Germany and Norway as default values. Execute the report and make a note of which sold-to parties are listed in your report. Are you able to select other countries in the report?
a) In order to change the global definition of the query, choose Tools and Edit Query. The BEx Query Designer opens.
b) Go to the Default Value area on the Filter tab page.
c) From the context menu for Country, choose Restrict. In the selection screen, choose Single Values. Select Germany and Norway, and transfer the selection using the Arrow button. Choose OK.
d) Choose Exit and Use the Query to save and execute the query. Only those sold-to parties for the countries in the Default Value area of the filter are listed in the initial view, but it is possible to change the filter and to analyze the other countries as well.
6. Now execute your query on the Web.
a) From the BEx Analysis Toolbar, choose Tools → BEx Web Analyzer.
Task 2:
Create a further query for the time period from April 2005 to June 2005 about the Sales Volume EUR and the Invoiced quantity of the customers and their materials and from the sales organization Germany Frankfurt. Initially, the flat screens are in the focus, but there has to be the option of selecting further materials. There should also be the option of a drilldown for month and with the possibility to select values for other months, but only within the year 2005. In the executed report try out the different options for filtering and arranging the drilldown. Additionally, store the
“Selection Situation” as a reusable Filter.
1. Define a new query, again basing on the MultiProvider - Customer Sales (P_SALES01). Include the Sold to party (T_SOLD_TO) and Material
(0MATERIAL) in the rows and the key figures Sales Volume EUR (T_INVCD_V) and Invoiced quantity (0INVCD_QTY) in the columns of your query definition.
Using Sales Organization (0SALESORG), restrict the report to Germany Frankfurt (1000).
Include Cal. Year/Month (0CALMONTH) into the query definition in order to be able to navigate with.
For the initial view, you are only interested in the flat screens Flatscreen MS 1460 P (M-06) to Flatscreen MS 1775P (M-10) and in the months April 2005 to June 2005, but in the executed report, you should also have the possibility to select values for other materials and for other months, but only within the year 2005.
a) In the BEx Analyzer, from the BEx Analysis Toolbar, choose Tools → Create New Query.
b) The BEx Query Designer opens, to create a new query, choose the icon New Query.
c) Choose the MultiProvider - Customer Sales (P_SALES01).
d) From the Customer dimension, drag Sold to party (T_SOLD_TO) to the rows area.
e) From the Material dimension, drag Material (0MATERIAL) to the rows area.
f) From the Time dimension, transfer the Cal. year / month (0CALMONTH) characteristic into the Free Characteristics section.
g) From the Key Figures folder, drag Sales Volume EUR (T_INVCD_V) and Invoiced quantity (0INVCD_QTY) into the columns area.
h) From the Sales area data dimension, place the Sales Organization (0SALESORG) characteristic into the Characteristic Restrictions area of the Filter tab page.
i) In the context menu for Sales Organization, choose Restrict.
In the selection screen, choose Single Values. Select Germany Frankfurt (1000). Adopt the selection using the Arrow button.
Choose OK.
j) In the Default Values area of the Filter tab page, enter the context menu for Material and choose Restrict .
In the selection screen, choose Single Value → Search, and in the field behind Material (Text) enter Flatscreen, and Start Search. Via the Display Key/Text button you can display the key for the materials. Select the flat screens from Flatscreen MS 1460 P (M-06) to Flatscreen MS 1775P (M-10) and adopt the selection using the Arrow button.
Choose OK.
k) In the Default Values area of the Filter tab page, enter the context menu for Cal year / month and choose Restrict .
In the selection screen, choose Value Ranges and Between. Select 04.2005 and 06.2005. Adopt the selection using the Arrow button.
Choose OK.
l) From the Time dimension, transfer the Cal. year / month (0CALMONTH) characteristic into the Characteristic Restrictions section of the Filter tab page.
In the selection screen, choose Value Ranges and Between. Select 01.2005 and 12.2005. Adopt the selection using the Arrow button.
2. Save the query in your role Role## (U##_ROLE) → Queries:
Description: U## Query First Steps 002 Technical name: U##_QD_FS002
Execute the query in the BEx Analyzer.
Try out the different options for arranging the drilldown and for filtering. E.g.
in order to list the sales volumes for the individual months in your report, drill across on Cal. year/month in the columns. Finally, verify which months can be selected when filtering.
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 First Steps 002 Technical name: U##_QD_FS002
c) Choose Save.
d) Execute the query as a report in the BEx Analyzer by choosing the button Exit and use the Query.
e) Try out the different options for filtering and arranging the drilldown. For example, in order to list the sales volumes for the individual months in your report, drill across on Cal. year/month in the columns. Finally, verify which months can be selected when filtering. Months can be selected within the year 2005 only!
3. Now execute your query on the Web.
a) From the BEx Analysis Toolbar, choose Tools → BEx Web Analyzer.
b) When prompted, log on with your assigned user ID and password.
4. Back in the query definition, drag the key figure structure into the rows under the characteristic material. Additionally, the repeated key values of the characteristics should not be hidden any more. Verify now the drilldown in the executed report.
At last, go back to the Query Designer and drag the key figure structure back into the columns area again.
a) Go back into the BEx Analyzer, from there go back into the query definition via BEx Analysis Toolbox → Edit Query.
b) Drag the key figure structure in the columns area and drop it in the rows area under the characteristic material.
c) In the Property pane of the Query Designer, select the top-most item in the drop-down list, choose the Display tab page and clear the checkbox before Hide Repeated Key Values .
Alternatively, you reach the properties of the query by using the button in the Query Designer toolbar.
d) Choose Exit and Use the Query to save and execute the query. You see the two key figures are now arranged in rows one below the other for each sold to party and each material, and the repeated key values are not suppressed any more.
e) Go back to the Query Designer. Drag the key figure structure back into the columns area again and save the query.
5. Finally, store the “Selection Situation” as a reusable Filter. For the filter, use the Description U## Filter 001 and the Technical Name U##_FILTER_001.
What can be done with such a reusable Filter?
_________________________________________________________________________
a) On the Filter tab page, either in the Characteristic Restriction area or in the Default Values area, Right click and Save as and use the description and technical name indicated above.
b) In the InfoProvider area on the left-hand side you now see a new folder named Filter and the assigned Filter objects. They can now be used for other queries on the same InfoProvider.
Lesson Summary
You should now be able to:
• Describe and use the basic functions of the BEx Query Designer
• Define your own simple queries and modify these when necessary
• Define static and dynamic filters in the query definition
• Use the enhanced selection function for selecting restriction values
• Define and use reusable filters
• Configure query properties
• Execute reports for analysis
Unit Summary
You should now be able to:
• Describe and use the basic functions of the BEx Query Designer
• Define your own simple queries and modify these when necessary
• Define static and dynamic filters in the query definition
• Use the enhanced selection function for selecting restriction values
• Define and use reusable filters
• Configure query properties
• Execute reports for analysis