Home / Tech Tips / Crystal Reports / Crystal Reports with Top N Based on a Date Dimension
InfoSol Blog Tech Tip

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.

Tech Tip Image 1

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:

Tech Tip Image 2

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

Creating a top N based on a date dimension 3

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

Tech Tip Image 4

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

Tech Tip Image 5

Step 5: Select Report -> Group Sort Expert

Creating a top N based on a date dimension 6

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

Tech Tip Image 7

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

Tech Tip Image 8

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

Rajesh Ponnurangam is a Senior Business Intelligence Consultant with Infosol. He is a SAP certified associate with extensive experience in design and development of Business Intelligence applications for apparel, retail, consumer goods and manufacturing industries. He specializes in Business Objects solutions, SAP HANA and BW data modeling. He has a proven track record in designing strategies for ETL, BI application development, implementation, rollout and support.

Check Also

InfoSol Blog Tech Tip

Data Warehouse Design – Inmon vs Kimball Architecture

We are living in the age of data revolution and more corporations are realizing the ...