Crystal Reports with Top N Based on a Date Dimension

We have seen various ways to create reports like Top 10 Customers by Sales, Top 10 Cities by population, Top 5 countries by profit Margin etc. It’s usually Top N by a <Measure>. What if there’s a requirement Top N by a <Date> or Most Recent N by a <Date>? Consider the scenario where a store manager likes to see the ten most recent sales in the store. This translates to Top 10 by Sale Date. In this tech tip I’m going to demonstrate a method to achieve this.

Here’s a sample report created against the e-fashion universe. The report displays the monthly sales by State and City for the year 2006 with a group against the month dimension. In this example I will be creating a Top N by Month. The same logic can be applied to Top N by Date or any numeric dimension.

What does it take to display only the last three months of the year? Here are the Steps:

Step 1: Right click at the Page header and Insert a section:

Step 2: Insert the month dimension in the Page header field and suppress the Page header b section:

Step 3: Right click on the month in Page Header b, select Insert -> Summary:

Step 4: Select the Maximum as Summary field and display it at the group footer:

Step 5: Select Report -> Group Sort Expert

Step 6: Make the selections as shown in the screen shot below:

The last three months of data is now displayed in the report:

 

Some users would like the option to dynamically change it to last five months or last six months. This can be extended by creating a parameter and mapping the input value to the Top N formula.

About Rajesh Ponnurangam

Check Also

Let's Speak BO Webinar - Upcoming Event

Webinar: Top 10 Features in Webi v4.3 on Tuesday, November 1st

This live demo will introduce you to my top 10 features of Webi v4.3 SP …

Leave a Reply

Your email address will not be published. Required fields are marked *