This blog is an attempt to share a solution which answers a frequently asked question about Top N type of report. The requirement is if you select Top N (where is N is variable ) to drive any report , the report should also show the sum of all the “Others” which are not included in Top N criteria.
The solution is the outcome of the discussion on oracle forum and a contribution from Oracle Consultant working in BI solution area. This blog is an attempt to share the knowledge. There is no contribution from my side apart from inclusion of the solution as a blog entry.
Desired Report Output
The report should be driven by Top N (where N is user enter variable) selection and should show the sum of all the “Others” as below. I have added conditional formatting on column for better clarity for demonstration.
Steps to Achieve the Top N and Others Report
The board steps which need to follow to achieve the ‘Top N and Others’ report are as follows
· Specify the Analysis Criteria
· Format the Analysis Results
· Create a Dashboard Prompt
· Create a Dashboard
Specify the Analysis Criteria
1. Create a new Analysis, choose subject area - A - Sample Sales, add the column ‘Product Type’ which you are grouping by and then add same column second time
2. Click the context menu button next to this second column and pick Edit formula from the menu.
3. Set the Column Formula to your requirements by following this template:
CASE WHEN RANK("Base Facts"."Revenue") <= @{TopN}{9} THEN "Products"."Product Type" ELSE 'Others' END
Where
· "Base Facts"."Revenue" should be replaced with the table.column you want to rank your results by.
· TopN is the name of the Presentation Variable that you want to use to specify how many of the Top results you want to show before grouping the remaining results into the “Others” result.
· 3 is the default value to use if the Presentation Variable isn’t set.
· "Products"."Product Type" should be replaced with the table.column you are grouping by.
· 'Others' is the name you want to give to the group that all results outside the Top N should be aggregated under.
4. Check the Custom Headings checkbox.
5. Set the Column Heading to TopN Product Type.
6. Check the Treat as an attribute column checkbox. At this point the Edit Column Formula dialog should look something like this:
7. Add the column you are ranking by (e.g. Revenue).
8. Click the context menu button next to this third column and pick Edit formula from the menu.
9. Set the Column Formula to your requirements by following this template:
CASE WHEN RANK("Base Facts"."Revenue") <= @{TopN}{9} THEN 1 ELSE 2 END
10. Check the Custom Headings checkbox.
11. Set the Column Heading to Rank Group.
12. Check the Treat as an attribute column checkbox.
13. Add the column you are ranking by (e.g. Revenue).
14. Click the context menu button next to the Rank Group column and pick Sort | Sort Ascending from the menu.
15. Click the context menu button next to the Rank Group column and pick Sort | Add Descending Sort from the menu. At this point your Selected Columns should look something like this:
Format the Analysis Results
1. Click the Results tab.
2. Click the New View button and pick Pivot Table from the drop-down menu.
3. Click the Remove View button next to the original Table view.
4. Click the Edit View button next to the Pivot Table view.
5. Drag Product Type from the Rows section to the Excluded section.
6. Drag Rank Group from the Measures section to the Rows section.
7. Drag TopN Product Type from the Measures section to the Rows section.
8. Click the More Options button next to Rank Group and pick Hidden from the drop-down menu.
9. Click the Totals button in the Rows section and pick After from the drop-down menu.
At this point the Layout section should look something like this:
At this point the Layout section should look something like this:
10. Click Done and Save Analysis. I have done some conditional formatting on columns for clarity and demonstration.
Create a Dashboard Prompt
This section describes how to create a Dashboard Prompt that specifies how many of the Top results should be shown before remaining results are grouped under the Others result.
1. Create a new Dashboard Prompt.
2. Click the New button and pick Variable Prompt from the drop-down menu.
3. Set the Presentation Variable name in the Prompt For field to Top.
(Where TopN is the name of the Presentation Variable you specified in step 5 of “Specify the Analysis Criteria” above.)
(Where TopN is the name of the Presentation Variable you specified in step 5 of “Specify the Analysis Criteria” above.)
4. Set the Label to Show Top N.
5. Expand the Options section.
6. Change the Variable Data Type to Number.
7. Change Default Selection to Specific Value.
8. Specify 3 as the Specific Value. At this point the Edit Prompt dialog should look something like this:
9. Click OK.
10. Save the Prompt.
Create a Dashboard
All that remains to be done is to create a Dashboard Page that brings the Prompt and the Analysis together.
1. Create a new Dashboard page.
2. Add the Dashboard Prompt you created earlier.
3. Add the Analysis you created earlier.
4. Save the Dashboard Page.
5. Run the Dashboard Page.
At this point the Dashboard should allow user to choose the Top N variable. The output reports for Top 6 and Top 3 are shown below
