Getting Started with Seal Report: Creating a Pivot Table with Custom Filters

#sealreport #tutorial #sqlserver #analytics

This is the second post in the series.

In this article, I assume that you’re already familiar with how to configure a data source in Seal Report and how to set up metadata model elements from a table loaded via the catalog.

You can find the previous post here:

Vlad Ganușceac

Mar 21

Getting Started with Seal Report: Building Your First Data Table from SQL Server

#sealreport #opensource #analytics #sqlserver

4 min read

The previous report used only out-of-the-box features. In this tutorial, we will extend it by adding a pivot table and introducing custom filters.

Adding a Pivot Table

Since the current report visualizes data from the HumanResources.Employee and Person.Person tables of the AdventureWorks2025 database, we need to use a one-to-many relationship with one of these tables to provide data suitable for a pivot table.

Let’s drag the Rate property from HumanResources.EmployeePayHistory into the Drop Data Elements section of the metadata model. Then drag the PayFrequency property from the same table into the Drop Column Elements section.

The final configuration will look like this:

Click the Execute button to see the result.

Now, in addition to the static columns, a list of payment rates grouped by frequency will appear.

Note that when you click the Rate property inside the Drop Data Elements section, a context panel appears on the right side. There you can configure several important data options:

Aggregate (default: Sum)
Calculation option (disabled by default)
Show total (disabled by default)

These settings control how numeric values are calculated and displayed in the pivot output.

Adding Custom Filters

Besides the default multi-column search available in the data table view, it is useful to add the ability to filter data using different expressions per column.

In this context, filters refer to the Restrictions and Aggregate Restrictions section located at the bottom of the Models → Model configuration panel.

These restrictions allow you to define parameterized filters that can be reused across report views and applied before the data is rendered.

Let’s drag and drop into the Restrictions and Aggregate Restrictions section the same properties that were previously added to the Row Elements of the metadata model.

For each restriction, enable the Prompt at execution option in the Prompt restriction setting so that users can specify filter values when running the report.

Your configuration should look like this:

Click the Execute button. In the new window, the newly added filters will appear. These filters are optional.

Click Execute again in that window to run the report.

Try applying different combinations of filters. The results displayed in the data table will change depending on the selected filter values.

Skill Check

So far, we have followed the approach "you filter what you see", meaning that restrictions were added only for the fields already present in the report model.

Try adding the Name field from HumanResources.Department table as Department restriction with default value set to Engineering.

Some readers might wonder why we are not using select lists yet, especially since SQL-based lists better represent real-life filtering scenarios than simple column-based restrictions.

In the next article, we will configure dynamic SQL-based select-list filters, making restrictions more flexible and closer to real-world reporting scenarios.