Report tip: Build a top 10 values report displaying "Others" row among the values

What do we want to do?

The purpose of this article is to give a step by step tutorial to build a report which would display the top 9 values from the datasource and summarise other values in a 10th row. So if we consider that we have the total sales for many customers.

Our aim here is to build a report which:

• Show the top 9 customers in terms of total sales figures,
• As 10th value, calculate the total of all sales for customers which are not in the top 9, that will be to amount for 'Other',
• Order all 10 values by total customers sales, descending,
• Highlight the 'Other' with a beautiful color.

So it will a similar report than this one:

Tools and version used for this example

This example was done using iReport 4.5 on a windows machine. It uses the sample datasource which is shipped with JasperReports Server 4.5.1 (JRS 4.5.1).

How to, Step by Step

The key here is to use a XMLA datasource which allows us to query the data with MDX.

1. Using iReport Designer if you want to preview your report you will have to create and XML/A connection to your server.

   Click on

2. Then you will need to get your data. Open the Query designer by clicking on

3. Select Query language to MDX

4. 4- And type your query. In our example we use the MDX query:

   with set [TopSelection] 
         as 'TopCount(Filter([Customers].[USA].[CA].Children, 
                             ([Measures].[Store Sales] > 0.0)),
                             Parameter("TopCount", NUMERIC, 9.0,
                                       "Number of Customers to show"),
                             [Measures].[Store Sales])'
     member [Customers].[USA].[CA].[Total] as 'Sum([TopSelection])'
     member [Customers].[USA].[CA].[Other Customers]
         as '([Customers].[USA].[CA] - [Customers].[USA].[CA].[Total])'
     select NON EMPTY {[Measures].[Store Sales]} ON COLUMNS, 
      Order({[TopSelection], [Customers].[USA].[CA].[Other Customers]},
            [Measures].[Store Sales], DESC) 
         ON ROWS from [Sales] 
   

5. Press Read Fields if the fields are not automatically retrieved (Fields highlighted in the fields section)

   

6. Then the fields will be listed in the Report Inspector

   

7. The last thing will be to Drop your fields in the report.

Conclusion

If you have setup the XML/A connection in iReports Designer, then you will be able to preview the report, otherwise deploy it to your server using the Foodmart XML/A Connection which is located by default under /Organization/analysis/connections.

A Zip file, containing the JRXML of the report used to generate the screenshot below, is attached Top9.zip. It uses the Foodmart XML/A Connection which is included in the samples that are shipped with JasperReports Server v4.5.1. Also an export of Top9_&_other.pdf is attached.