Sunday, January 20, 2013

[OBIEE11g] - Getting Top-N Sales Reps Using the TOP-N Function


  1. Select the three columns “Sales Rep Name”, Revenue, TOPN(Revenue, 10) to show in your Results.
  2. On the Third “Revenue” Column open the option and choose “Edit Formula”
  3. On the Edit Column Formula window options and select “Edit Formula”
  4. On the Column Formula select the “Insert Function” f(…) and choose the TopN under the Aggregate section.
  5. Select the “integer” and enter 10.
  6. Make sure the following statement is as shown below and click OK.
  7. On the second “Revenue” Column choose options and select Sort -> Sort Descending.
  8. Then Select the Results Tab to show the Top 10 Sales Rep for all the existing years in your database.
  9. To Verify that the TOPN(Revenue, 10) Column is using the proper TOP-N functionality, create two columns with just “Sales Rep Name” and Revenue and put a sort option on “Revenue” column as below:
  10. Goto the Results Tab to view the results
  11. In the above screenshot you can verify that the first 10 columns are the same that you have got while applying the Top-N Function on the Third Revenue Column.
Summary:
This is one way to show who all are the Top-10 Sales Representatives making the most business for your company. We have achieved this by using the Top-N function applied on the “Revenue Column”. Another way to achieve this by using the “Selection Steps” on “Results” Tab after our results have been shown.