This is the fifth post in the series.
Retrieving data automatically from multiple tables is powerful. However, in many real-world reporting scenarios, dashboards and analytical reports rely on custom SQL expressions, user-defined functions, or stored procedures.
In this post, I’ll show how to link SQL expressions with other tables so you can benefit from both approaches at the same time - keeping Seal Report’s automatic joins and filters while still using custom SQL logic.
As a prerequisite, you should have an instance of the AdventureWorks2025 database available and be familiar with configuring a data source connection in Seal Report.
Previously, we've covered up how to apply dependent filters:
The Goal
In this post, we will rewrite the report created in the previous article using a different approach:
- retrieving data from the Person.Person table together with data from the HumanResources schema using a single SQL expression;
- applying a custom join between this SQL expression and the Person.Person table;
- keeping the rest of the dynamic and custom filters available, just like in the previous posts in this series.
The SQL statement used in this example will not be implemented as a parameterized user-defined function or stored procedure, as that scenario will be covered in the next article:
SELECT
employee.BusinessEntityID
, employee.JobTitle
, employee.BirthDate
, employee.MaritalStatus
, employee.Gender
, person.FirstName
, person.MiddleName
, person.LastName
, pay_history.PayFrequency
, pay_history.Rate
FROM HumanResources.Employee employee
LEFT JOIN HumanResources.EmployeePayHistory pay_history
on employee.BusinessEntityID = pay_history.BusinessEntityID
INNER JOIN Person.Person person
on employee.BusinessEntityID = person.BusinessEntityID
Joining Data Tables Within the Data Source
To add the SQL expression as a virtual table, open Sources → Data Source → Tables and choose Add Table.
In the configuration panel on the right side, set the Name property to Employees_Virtual, then paste the SQL query into SQL Select Statement.
After that, press F9 (or Refresh columns) so that Seal Report loads the column definitions from the query.
At this point, the SQL expression becomes available as a regular table inside the data source and can be used later when defining joins.
Note that this does not create a new table in the database — the table exists only at the data source level in Seal Report.
The following data tables should be loaded from the Catalog:
- Person.Person
- Person.BusinessEntity
- Person.BusinessEntityAddress
- Person.Address
- Person.StateProvince
- Person.CountryRegion
After loading them, the Tables section should look like this:
Now it is time to apply a custom join statement. Open Sources → Data Source → Joins and use the Add Join option from the context menu.
Since the required join is bi-directional, it does not matter whether the virtual table is defined as the left or the right side of the join. In the Join clause, apply the following condition:
Employee_Virtual.BusinessEntityID = Person.Person.BusinessEntityID
The joins used in this example are shown in the screenshot below:
Creating Pivot Table and Using Custom Filters
This time, the elements used for data visualization will be retrieved from a single source — the Employee_Virtual custom table.
The Row Elements should contain the following properties:
- FirstName
- MiddleName
- LastName
- JobTitle
- BirthDate
- MaritalStatus
- Gender
For the pivoted part, the Column Elements should contain PayFrequency, and the Data Elements should contain the Rate property.
Let’s apply the what-I-see-is-what-I-can-filter principle to the static part of the table definition. Drag and drop the same properties used within the Row Elements into the Restriction section. Do not forget to enable prompt restrictions for the newly added expressions in that section.
If everything has been configured correctly, the model definition should look like this:
If you had any difficulties completing this exercise, you may take a look at the following post:
Skill Check
Only one step remains to reach the final goal — applying a filter on a table other than the custom one and verifying that it influences the report output.
Since we defined a custom join between the Employee_Virtual table and the base table (Person.Person), we now need to apply a restriction within the Person schema to complete the scenario.
Let’s drag and drop the CountryRegionCode field from Person.CountryRegion into the Restrictions section.
For example, applying the FR region code will return just one row in the table:
The example above would be even more useful if dependent filters were applied to CountryRegion and StateProvince. Try implementing them yourself. If you encounter any difficulties, the previous post explains how to configure them.
So far, so good. In the next post, I will show how to pass parameters into user-defined functions and stored procedures when using them as data sources in Seal Report.
Top comments (0)