logo
small logo
  • Products
  • Buy
  • Support
  • Articles
  • Customer panel Support
    • en
    • pt
    • es
    • de
    • pl
    • JP
    • ZH
  • Home
  • /
  • Articles
  • /
  • How to select the top values in a matrix
  • How to use SQL queries when creating an internal report data source in FastReport .NET report designer

    September 2, 2019

    When creating a SQL database connection, you can specify a query to select filtered or

    read more
  • How to use stored procedures with multiple sets of data as a result

    May 8, 2020

    Often, when creating reports, we have to deal with databases that are far from ideal.

    read more
  • New features and comparison of Matrix and AdvancedMatrix objects

    March 23, 2022

    More recently, the AdvancedMatrix object was implemented in FastReport .NET. In this article, we

    read more
  • Creating a complex report with several matrices in FastReport .NET

    June 16, 2021

    Today we will look at a complex report with a band-oriented approach, which is usually

    read more
  • How to sort similar matrices through one-dimensional array on several pages in FastReport .NET

    September 22, 2021

    Let's say we have the task: to sort the matrix on the first page in

    read more

How to select the top values in a matrix

April 22, 2021

The article is relevant until version 2022.1.

FastReport .NET has a great tool for displaying data as an integrated table or matrix. Many of us would like to improve the functionality of matrices, for example, with such a useful option as a choice of N top values. It seems as simple as selecting the N top lines from a data source. However, besides making a selection of the top values, it is necessary to group all the rest data into a single recording, which is the main problem. This cannot be done with the built-in tools of the Matrix object.

Thus, we have to prepare the data so that they contain both the top values and the sum of all the rest values. This means is suitable for SQL databases. Everything we need is to write an SQL query.

Assume we produce a list of employees’ wages by years. In the data source editor, we may use an SQL query, if an SQL database is used.

Data processing wizard

This is how the SQL query, which will select 2 top values and the sum of all the rest values, will look like:

SELECT top 2 name, year, month, salary FROM crosstest ORDER BY salary
UNION
SELECT 'Other' AS name, year, month, SUM(salary) FROM crosstest
WHERE name NOT IN (SELECT Top 2 name FROM crosstest ORDER BY salary)
GROUP BY name, year, month

Here we combine two queries with a union operator. In the first query, we choose the top values, in the second query — the sum of all remaining values.

As a result, we obtain the following matrix:

Final result

As you can see, by using various techniques of preparing initial data, we can obtain the desired effect, even if such functionality had not been initially provided in the report generator.

about product download buy
avatar
Dmitriy Fedyashov
Technical Writer
Fast Reports Team: Dmitriy Fedyashov - Technical Writer at Fast Reports
.NET FastReport SQL Data filtering Matrix

Add comment
logo
  • 800-985-8986 (English, US)
  • +4930568373928 (German)
  • +55 19 98147-8148 (Portuguese)
  • info@fast-report.com
  • 901 N Pitt Str #325 Alexandria VA 22314
  • Buy
  • Download
  • Documentation
  • Testimonials
  • How to uninstall
  • Ticket system
  • FAQ
  • Tutorial Video
  • Forum
  • Articles
  • Our News
  • Press about us
  • Resellers
  • Extended licensing
  • Contact us

© 1998-2022 by Fast Reports Inc.

  • Privacy Policy