Defining Exception Cells
Task 3: Formula Collision and Cell Editor
Generate a report that represents the sales volumes and incoming orders for the 2 half-years of 2006 as well as for the whole year 2006 with percentage changes.
1. Create a new query based on the InfoProvider MultiProvider - Customer Sales (P_SALES01) in the Reporting Layer → Sales and Distribution InfoArea.
In the rows, create a structure with the following description: Half-year Comparison
This structure contains the first and second half-years of 2006 (Description:
Half-year 1/2006and Half-year 2/2006) The structure should also include the whole year 2006 (Description: Year 2006) as a sum of the two half-years.
You define the fourth part as a formula for calculating the percentage variance of the two half-years contained in the structure (Description: % Variance of Half-years 1 and 2/2006).
2. In the columns, include the basic key figures Sales Volume EUR
(T_INVCD_V) and Incoming Orders (T_INCORDV). Define a new formula that calculates the percentage share of the sales volume in relation to incoming orders.
Describe the formula with: Sales Volume % Share of Incoming Orders
3. Save the query under Roles → Role## (U##_Role) → Queries:
Description: U## Query Structures 003 Technical Name: U##_QD_STR003
Now, execute the query.
4. At the points where the calculated key figure Sales Volume % Share of Incoming Orders in the column crosses both Year 2006 and % Variance of Half-years 1 and 2/2006 in the rows, there are formula collisions.
Make the necessary setting to have the desired calculations in the proper way.
Continued on next page
5. You need information on the percentage share of the sales volume for both the half-years in relation to the whole year. You define a new formula Sales Volume % Share of Sales Volume - Year 2006that uses the value from a particular cell for this calculation. Use the Cell Editor for selecting the sales volume for year 2006 as a reference cell.
Use the Cell Editor to suppress the cell results at the point were the formula Sales Volume % Share of Sales Volume - Year 2006 in the columns crosses with % Variance of Half-years 1 and 2/2006 in the rows.
Solution 6: Define Structures and work with Cells
Task 1: Characteristic Structure (Without Key Figures) and Key Figure Structure (With Key Figures)
Generate a report that represents the analysis of sales volumes for the whole year 2006 and December 2006 referring to all materials as well as to pumps.
1. Create a new query based on the InfoProvider MutliProvider - Customer Sales (P_SALES01) in the Reporting Layer → Sales and Distribution InfoArea.
In the rows, create a Characteristic structure with the following name:
Description: U## Comparison Pumps
Continued on next page
This structure contains as the first structure element All Materials and as second structure element only the Pumps (only materials whose key begins with P-10*).
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) Right-click in the heading of the Rows area. In the context menu, choose New Structure and fill in the description U## Comparison Pumpsin the Properties area.
e) To define the first element of the structure, go to the context menu of the U## Comparison Pumps structure, and chose New Selection.
Enter All Materials as the description in the Properties area. In the context menu of the All Materials structure element, choose Edit.
Under the Material dimension, choose the Material characteristic and place it in the selection window using Drag & Drop. Choose OK.
f) For the definition of the second structure element, choose New Selection again in the context menu for structure U## Comparison Pumps. Enter Pumps as the description in the Properties area. In the context menu of the Pumps structure element, choose Edit. Under the Material dimension, choose the Material characteristic, and place it in the selection screen using Drag & Drop.
g) In the context menu for Material, choose Restrict → Show: Single Values. Select the Search link. In the Material (Key) field, enter P-10*. Choose the Start Search button. Highlight all the found materials and transfer them to the right side by using the Arrow button.
Choose OK two times.
2. In the columns, within a Key Figure Structure, include the basic key figure Sales Volume EUR two times.
Restrict one of them to all months in 2006 and name it Sales Volume EUR 2006.
Restrict the other to December 2006 and name it Sales Volume EUR December 2006.
Define a new formula that calculates the percentage share of the sales volume for December 2006 in relation to the sales volume for the whole year 2006 and name it % Share Sales Volume EUR Dec 2006 to 2006
Hint: Remember that you have two ways to define a key figure structure:
1. Drag the desired key figures into the columns where you can Edit them, for example,to create a restricted key figure. The Key Figures structure will appear automatically.
2. Or:
Select the heading of the Columns area and use the right mouse button to choose New Structure. Select the structure header and use the right mouse button to choose New Selection from the context menu. The New Selection dialog box appears where you can select the desired key figure and restrict it if wanted.
a) Use Drag & Drop to place the basic key figure Sales Volume EUR (T_INVCD_V) into the Columns area. In the context menu of the selected key figure choose Edit.
Under the Time dimension, choose the Cal. year / quar characteristic and place it in the Details of the Selection screen using Drag & Drop.
In the context menu for Cal. year / quar , choose Restrict → Show:
Value Range → Between: January 2006 and December 2006. Use the arrow button to transfer the restriction to the right side. Choose OK . In the description area, enter Sales Volume EUR 2006, split in two rows. Choose OK.
b) In the context menu of the key figure Sales Volume EUR 2006, choose Copy and Paste. Enter the context menu of the new key figure and choose Edit. Change the description into Sales Volume EUR December 2006, split into two lines.
In the context menu for Cal. year / quar , choose Restrict → Show:
Single Values and select December 2006. Use the arrow button to transfer the restriction to the right side and use the other arrow button to transfer the January 2006 to December 2006 restriction to the left side. Choose OK two times.
Make sure that this key figure is positioned last in the structure.
c) For the calculation of the percentage share of the sales volume for December 2006 in relation to the sales volume of the whole year 2006, choose New Formula in the context menu for the key figure structure.
Then choose Edit from the context menu of the formula.
Continued on next page
Description: % Share Sales Volume EUR Dec 2006 to 2006
d) Use Drag & Drop to place Sales Volume EUR December 2006 into the Detail View area. Under Percentage Functions, choose Percentage Share (%A) and use Drag & Drop to place it into the Formula area.
Use Drag & Drop to place Sales Volume EUR 2006 into the Formula field. Choose OK.
Make sure that this key figure is positioned last in the structure.
3. Finally, save the query under Roles → Role## (U##_Role) → Queries:
Description: U## Query Structures 001 Technical Name: U##_QD_STR001
Now, execute the query.
a) Save the query under Roles → Role## (U##_Role) → Queries:
Choose Save Query :
Description: U## Query Structures 001 Technical Name: U##_QD_STR001
b) Execute the query via Exit and use the query or via Execute.
4. To create a reusable structure, save the characteristic structure U##
Comparison Pumps as a global structure with the technical name of U##_STR_CP001.
a) Go back into the Query Designer.
b) In the context menu of the U## Comparison Pumps structure, choose Save as.... When prompted, enter the technical name U##_STR_CP001and choose OK.
c) Note that you now can find the structure as a global structure in the InfoProvider area on the left-hand side of the Query Designer under Structures → Without Key Figures.
Task 2: Two Characteristic Structures (Without Key
Figures)
In the rows, reuse the global structure U## Comparison Pumps and enhance the structure - after changing it into a local structure - by adding a formula that calculates the percentage share of the Pumps in relation to All Materials.
Use Pumps % Share of All Materials as the description.
a) Define a new query in the Query Designer:
Choose New Query→ InfoAreas → Reporting Layer → Sales and Distribution → MutliProvider - Customer Sales (P_SALES01)→ Open.
b) From the Structures → Without Key Figures folder, transfer the U##
Comparison Pumps into the Rows using Drag & Drop.
c) In the context menu of the structure choose Remove Reference.
d) In the context menu of the structure choose New Formula. In the context menu of the new formula, choose Edit.
Enter the Description Pumps % Share of All Materials.
Use Drag & Drop to place the structure element Pumps into the Detail View area. Under Percentage Functions, choose Percentage Share (%A) and use Drag & Drop to place it into the Formula area. Use Drag
& Drop to place All Materials into the Formula field. Choose OK.
Continued on next page
2. For the columns, create a second characteristic structure named U##
Comparison Countries. The first structure element has to represent the characteristic country restricted to Germany, the second structure element all Other Countries.
a) Call up the context menu in the heading of the Columns area.
Choose New Structure and fill in the description U## Comparison Countriesin the Properties area.
b) To define the first element of the structure, go to the context menu of the U## Comparison Countries structure, and chose New Selection.
Enter Germany as the description in the Properties area. In the context menu of the Germany structure element, choose Edit. Under the Customer dimension, choose the Country (T_SOLD_TO__0COUNTRY) characteristic and place it in the selection screen using Drag & Drop.
In the context menu for Country, choose Restrict → Show: Single Values and select Germany (DE) . Use the Arrow button to transfer the restriction to the right side . Choose OK two times.
c) For the definition of the second structure element, choose the context menu of the first structure element “Germany”, then Copy and Paste.
Enter the context menu of the new structure element and choose Edit.
Change the description into Other Countries.
In the context menu for Country, choose Restrict, select the value for Germany in the right-hand window and then choose Exclude from Selection. Choose OK two times.
d) Make sure that the Other Countries structure element is last in the structure.
3. In the report, the key figure Sales Volume EUR has to be shown.
a) Use Drag & Drop to place the basic key figure Sales Volume EUR (T_INVCD_V) into the Characteristic Restrictions area on the Filter tab.
4. Save the query under Roles → Role## (U##_Role) → Queries:
Description: U## Query Structures 002 Technical Name: U##_QD_STR002
Now, execute the query.
a) Save the query under Roles → Role## (U##_Role) → Queries:
Choose Save Query :
Description: U## Query Structures 002 Technical Name: U##_QD_STR002
b) Execute the query via Exit and use the query or via Execute.
Task 3: Formula Collision and Cell Editor
Generate a report that represents the sales volumes and incoming orders for the 2 half-years of 2006 as well as for the whole year 2006 with percentage changes.
1. Create a new query based on the InfoProvider MultiProvider - Customer Sales (P_SALES01) in the Reporting Layer → Sales and Distribution InfoArea.
In the rows, create a structure with the following description: Half-year Comparison
This structure contains the first and second half-years of 2006 (Description:
Half-year 1/2006and Half-year 2/2006) The structure should also include the whole year 2006 (Description: Year 2006) as a sum of the two half-years.
You define the fourth part as a formula for calculating the percentage variance of the two half-years contained in the structure (Description: % Variance of Half-years 1 and 2/2006).
a) Define a new query in the Query Designer:
Choose New Query→ InfoAreas → Reporting Layer → Sales and Distribution → MutliProvider - Customer Sales (P_SALES01)→ Open.
b) Call up the context menu in the heading of the Rows area. Choose New Structure and fill in the description Half-year Comparison in the Properties area.
c) To define the first half-year as part of the structure, go to the context menu of the Half-year Comparison structure, and chose New Selection.
Enter Half-Year 1/2006 as the description in the Properties area.
In the context menu of the Half-year 1/2006 structure element, choose Edit. Under the Time dimension, choose the Cal. year / quar characteristic and place it in the selection screen using Drag & Drop.
In the context menu for Cal. year / quar, choose Restrict → Show:
Single Values and select 1.2006 and 2.2006. Use the arrow button to transfer the restriction to the right side. Choose OK twice.
Continued on next page
d) For the definition of the second half-year as part of the structure, go to the context menu of the Half-year Comparison structure, and chose New Selection. Enter Half-year 2/2006 as the description in the Properties area. In the context menu of the Half-year 2/2006 structure element, choose Edit. Under the Time dimension, choose the Cal. year / quar characteristic and place it in the selection screen using Drag
& Drop.
In the context menu for Cal. year / quar, choose Restrict → Show:
Single Values and select 3.2006 and 4.2006. Use the arrow button to transfer the restriction to the right side. Choose OK twice.
e) For the definition of the two new formulas in the structure, choose New Formula in the context menu for the structure Half-year Comparison.
Description: Year 2006.
Use Drag & Drop to place Half-year 1/2006 into the Detail View field.
Choose the operator + (Plus) and place Half-year 2/2006 into the Detail View field.
Confirm your entries with OK.
f) In the context menu for the structure Half-year Comparison, choose New Formula. Choose Edit from the context menu of the new formula.
Description: % Variance of Half-years 1 and 2/2006 Use Drag & Drop to place half-year 2/2006 into the Detail View field.
Choose the Percentage Variance (%) function. Use Drag & Drop to place Half-year 1/2006 into the Detail View field. Choose OK.
2. In the columns, include the basic key figures Sales Volume EUR
(T_INVCD_V) and Incoming Orders (T_INCORDV). Define a new formula that calculates the percentage share of the sales volume in relation to incoming orders.
Describe the formula with: Sales Volume % Share of Incoming Orders
a) Use Drag & Drop to place the basic key figures Sales Volume EUR (T_INVCD_V) and Incoming Orders (T_INCORDV) into the columns.
b) For the calculation of the percentage share of the sales volume in relation to incoming orders, choose New Formula in the context menu for the key figure structure. Then choose Edit from the context menu of the new formula.
Description: Sales Volume % Share of Incoming Orders
Use Drag & Drop to place Sales Volume EUR (T_INVCD_V) into the Detail View field. Under Percentage Functions, choose Percentage Share (%A) and use Drag & Drop to place it into the Detail View field.
Use Drag & Drop to place Incoming Orders EUR (T_INCORDV) into the Detail View field. Choose OK.
c) Make sure that the new formula is positioned last in the key figure structure.
3. Save the query under Roles → Role## (U##_Role) → Queries:
Description: U## Query Structures 003 Technical Name: U##_QD_STR003
Now, execute the query.
a) Save the query under Roles → Role## (U##_Role) → Queries:
Choose Save Query :
Description: U## Query Structures 003 Technical Name: U##_QD_STR003
b) Execute the query via Exit and use the query or via Execute.
4. At the points where the calculated key figure Sales Volume % Share of Incoming Orders in the column crosses both Year 2006 and % Variance of Half-years 1 and 2/2006 in the rows, there are formula collisions.
Continued on next page
Make the necessary setting to have the desired calculations in the proper way.
a) Go back to the query definition .
b) In the Properties area for the formula Sales Volume % Share of Incoming Orders, choose Extended → Eliminate Formula Collision
→ Use Results of this Formula.
c) In the Properties area for the formula % Variance of Half-years 1 and 2/2006, choose Extended → Eliminate Formula Collision → Use Results of this Formula.
d) Execute the query again and check the resulting report.
5. You need information on the percentage share of the sales volume for both the half-years in relation to the whole year. You define a new formula Sales Volume % Share of Sales Volume - Year 2006that uses the value from a particular cell for this calculation. Use the Cell Editor for selecting the sales volume for year 2006 as a reference cell.
Use the Cell Editor to suppress the cell results at the point were the formula Sales Volume % Share of Sales Volume - Year 2006 in the columns crosses with % Variance of Half-years 1 and 2/2006 in the rows.
a) Go back to the query definition.
b) Use the Cells icon to call up the Cell Editor.
c) At the place where the Year 2006 and Sales Volume EUR calculations cross, choose New Cell Reference in the context menu. Go back to the Rows/Columns tab page of the Query Designer.
d) For the definition of the percentage share of the sales volume in relation to the sales volume of the year 2006, choose New Formula in the context menu for the key figure structure.
Description: Sales Volume % Share of Sales Volume -Year 2006.
Use Drag & Drop to place Sales Volume EUR into the Detail View field.
Choose Percentage Share (%A). Place Sales Volume EUR/Year 2006 from the Cells section into the Detail View field. Choose OK.
e) Call up the Cell Editor again, and at the intersection of % Variance of Halfyears 1 and 2/2006 and Sales Volume % Share of Sales Volume
-Lesson Summary
You should now be able to:
• Define structures
• Work with queries containing two structures
• Save structures as reusable objects and reuse them
• Handle formula collision
• Use the cell editor
Unit 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
• Explain the objectives and use of calculated key figures in information analyses
• Define calculated key figures at the InfoProvider level and at the query level
• Define a calculated key figure and defining missing objects directly
• Using Boolean Operators within calculated key figures
• Identify the General, Display, Extended, and Calculations options within the properties of key figures
• Carry out Currency Translation in the Business Explorer
• Use the Exception Aggregation to deliver many additional options for analysis
• Define structures
• Work with queries containing two structures
• Save structures as reusable objects and reuse them
• Handle formula collision
• Use the cell editor