Data Analysis: Expand, Collapse, Keep, Exclude
Exercise 3: Sorting, Filtering, and Ranking
Exercise Objectives
After completing this exercise, you will be able to:
• Use the Sorting, Filtering, and Ranking options in your report.
Business Example
You would like to analyze the results of your report by sorting, filtering, and ranking.
Task 1:
Sort the results of your previously created report based on the 2011 total sales.
1. Go to the Excel client for your Sales model in your BPC430_## environment.
Note: If you do not have a connection for the Sales model, use the instructions in Exercise #2 to create it.
Note: If you are ALREADY in Excel, go to Step #2.
2. Create a new blank workbook.
3. Choose the Sales - BPC430_## Connection 4. Set your context for the Sales model.
5. Create a new report with product in the rows, and time in the columns, using the EPM Pane.
6. From the EPM Pane, select Member and Children for the Bird and Dog parents.
7. From the EPM Pane, select Member and Children for Time.
8. Open the EPM - Report Editor to sort the results of your report.
9. You realize the dog products are mixed in with the bird products. You would like to sort the values, but keep the products within their groups.
10. Stay in the report for the next Task.
Continued on next page
Task 2:
Filter the results of your report based on the products that have greater than 200,000 in sales in 2011.
1. Open the EPM - Report Editor to filter the results of your report.
2. Remove the filter
Task 3:
Rank the results of your report to determine the top 3 selling products in 2011.
1. Open the EPM - Report Editor to rank the results of your report.
2. Save your Report.
Solution 3: Sorting, Filtering, and Ranking.
Task 1:
Sort the results of your previously created report based on the 2011 total sales.
1. Go to the Excel client for your Sales model in your BPC430_## environment.
Note: If you do not have a connection for the Sales model, use the instructions in Exercise #2 to create it.
Note: If you are ALREADY in Excel, go to Step #2.
a) From your web client's Start Page, choose EPM Office Add-in Excel.
Excel opens and the EPM - Connection Manager appears.
b) Choose the Sales - BPC430_## connection.
c) Click OK.
d) If you are prompted, log on again with your user ID and password as shown below:
User ID Password
BPC-## training
2. Create a new blank workbook.
a) Create a blank workbook by choosing the Office button and then New.
b) Choose OK or Create.
3. Choose the Sales - BPC430_## Connection
a) On the EPM Pane, under Active Connection, choose the Sales - BPC430_##
connection.
Continued on next page
4. Set your context for the Sales model.
a) Set your EPM Context to the following:
Dimension Member ID - Description CATEGORY Actual - Actual from GL
ENTITY CONMID - Mid-Size Store Consolidated
PRODUCT FISH - All Fish Products
P_ACCT EXTSALES - External Sales
RPTCURRENCY LC - Local Currency
TIME 2011.TOTAL - 2011
MEASURES PERIODIC - Periodic
5. Create a new report with product in the rows, and time in the columns, using the EPM Pane.
a) In the EPM Pane, right-click Product and select Move to Row Axis.
b) Right-click Time and select Move to Column Axis.
The Report is executed.
6. From the EPM Pane, select Member and Children for the Bird and Dog parents.
a) In the Row Axis , choose Product.
The EPM - Member Selector opens.
b) Click the member display icon, and select ID.
When you run the report with this setting, only the IDs will appear in the report.
c) Select BIRD and DOG by selecting the checkbox to the left of each of them.
d) Under Selection Relationship, select Member and Children.
e) Choose the blue right arrow to move the selection to Selected Members.
Choose the blue left arrow to remove the prior selection which is already highlighted.
f) Choose OK.
7. From the EPM Pane, select Member and Children for Time.
a) In the Column Axis, choose Time.
The EPM - Member Selector opens.
b) Click the member display icon, and select ID.
c) Select 2011 by selecting the checkbox to the left of it.
d) Under Selection Relationship, select Member and Children.
e) Choose the right arrow to move the selection to Selected Members.
Choose the blue left arrow to remove the prior selection which is already highlighted.
f) Choose OK.
The report is executed with the new selections.
8. Open the EPM - Report Editor to sort the results of your report.
a) From the EPM Ribbon, choose Edit Report.
b) Choose the Sorting tab.
c) Select Sort data Using the Following Criteria.
d) Under Sorting Criteria, select the radio button for Last Column e) Leave the checkbox for Show Only Members Displayed in the Report
selected as well as the radio button Ascending selections.
Figure 85: Sorting Criteria
f) Choose OK.
Your report is now in ascending order according to the product sales for 2011.
Continued on next page
9. You realize the dog products are mixed in with the bird products. You would like to sort the values, but keep the products within their groups.
a) From the EPM Ribbon, choose Edit Report.
The EPM - Report Editor window opens on the Layout tab by default.
b) Choose the Sorting tab.
c) In the bottom left-hand corner, select Sort Inside Level.
d) Choose OK.
Your report is now in ascending order, according to the product sales for 2011, but has kept the BIRD products and DOG products together.
Figure 86: Sort Inside Level 10. Stay in the report for the next Task.
a) Stay in the report for the next Task.
Task 2:
Filter the results of your report based on the products that have greater than 200,000 in sales in 2011.
1. Open the EPM - Report Editor to filter the results of your report.
a) From the EPM Ribbon, choose Edit Report.
b) Choose the Filtering tab.
c) Select Filter data Using the Following Criteria.
d) Under Filtering Criteria, choose the ellipsis (...) button at the end of the Based on: field.
The Column Selector window opens.
e) Highlight 2011.TOTAL (it may be displayed as 2011) and choose OK.
f) Under the Based on: field, select the middle radio button, and from the dropdown, select > (Greater than) and enter 200000.
g) Above Filter Expression, choose Add Criteria.
h) Choose OK.
Figure 87: Filtered Results (your values may vary)
Your report is returned with only the products that exceeded 200,000 in sales in 2011.
Continued on next page
2. Remove the filter
a) From the EPM Ribbon, choose Edit Report.
b) Choose the Filtering tab.
c) Deselect Filter Data Using the Following Criteria.
d) Choose OK.
Your report is no longer filtered.
Task 3:
Rank the results of your report to determine the top 3 selling products in 2011.
1. Open the EPM - Report Editor to rank the results of your report.
a) From the EPM Ribbon, choose Edit Report.
b) On the Layout tab, under the Row Axis Dimensions, choose the PRODUCT hyperlink.
The EPM - Member Selector opens.
c) Choose Bird and Dog by selecting the checkbox to the left of the dimension members. For the Selection Relationship, choose Base Level and move your selections to the right with the arrow.
d) Remove the selections for Bird → Member and Children and Dog → Member and Children, by highlighting them and choosing the blue arrow pointing to the left.
e) Choose OK.
f) Choose the Ranking tab.
g) Select Rank data Using the Following Criteria.
h) Under the Ranking Criteria choose the ellipsis (...) button at the end of the Based on: field
The Column Selector window opens.
i) Highlight 2011.TOTAL (it may be displayed as 2011) and choose OK.
j) Next to Display, select the radio button to Display the Top and enter 3 k) Choose OK.
Figure 88: Ranking Results
Your report is returned with only 3 products with the highest sales.
Continued on next page
2. Save your Report.
a) From the EPM Ribbon, choose Save → Save to Server Root Folder b) Choose Company(Public), then choose Reports.
c) For the File Name, enter GR## Top 3 Products.
d) Choose Save.