Vlad Ganușceac

Posted on Mar 26

Getting Started with Seal Report: Static and Dynamic Select Lists

#sealreport #tutorial #sqlserver #analytics

This is the third post in the series.

In the previous post, we created a pivot table with custom filters. While those filters allowed us to apply constraints such as Equals, Contains, and Between, it would be useful to extend the example further by introducing static and dynamic select lists as filtering options.

In this post, we will enhance the existing solution by integrating these select lists and exploring how they improve the filtering experience.

Below is the link to the previous post:

Vlad Ganușceac

Mar 22

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

#sealreport #tutorial #sqlserver #analytics

3 min read

Adding a Static Enumeration

In the context of Seal Report, a static enumeration is a collection of key-value pairs defined directly in the report’s XML document definition.

For example, instead of retrieving the distinct values of PayFrequency from the HumanResources.EmployeePayHistory table, it is often more efficient to store these values directly in the data model used for rendering the report. Even executing a hardcoded SQL query each time the report runs would be less efficient than using a predefined collection.

In this case, the static enumeration behaves like a constant list that is already available at runtime and does not require additional database queries.

Select the Add Enum option available under Sources → Data Source → Enumerated Lists. A contextual configuration panel will appear on the right side.

Within the enum definition, provide a meaningful Name, and configure the following options if needed:

whether the list is dynamically loaded from a SQL Select Statement or from a Load Script
whether the list should be refreshed before report execution
whether the list values depend on the connection.

These settings allow you to control how enumeration values are loaded and updated during report execution.

For the current scope, all these options remain set to False, since we are defining a static enumeration.

The SQL Select Statement used to define the enumeration values is the following:

SELECT *
FROM (VALUES
    (1, 'Monthly'),
    (2, 'Biweekly')
) AS PayFrequencyEnum([Key], [Value])

The enum's configuration looks this way:

Note that the Refresh values option may not appear immediately in the UI. If it is not visible, reopen the enum definition or temporarily enable a dynamic loading option so the two values become available in the final collection.

The enumeration created in the UI is persisted in the report definition as a <MetaEnum> element. A simplified example looks like this:

<MetaEnum>
  <GUID>a90b0ce6-bde8-4199-8869-f2ce0c1218ce</GUID>
  <Name>PayrollFrequency</Name>
  <Sql>SELECT *
FROM (VALUES
    (CAST(1 AS INT), 'Monthly'),
    (CAST(2 AS INT), 'Biweekly')
) AS PayFrequencyEnum([Key], [Value])</Sql>
  <Values>
    <MetaEV>
      <Id>1</Id>
      <Val>Monthly</Val>
    </MetaEV>
    <MetaEV>
      <Id>2</Id>
      <Val>Biweekly</Val>
    </MetaEV>
  </Values>
</MetaEnum>

If the enumeration is configured as static, the actual values used at runtime are stored inside the <Values> section. In this case, the <Sql> element is not required for execution and mainly serves as a definition template.

You can even remove the <Sql> element and keep only the <Values> collection to preserve the same static behavior.

Adding a Dynamic Enumeration

For a dynamic enumeration, let’s select a set of properties directly from the database that are expected to change over time.

For example, the HumanResources.EmployeeDepartmentHistory table references the HumanResources.Shift table. This means that the available shift values may change in the future as new shifts are added or existing ones are modified.

In such scenarios, using a dynamic enumeration ensures that the list of available values is always loaded from the database and reflects the current state of the data.

For this particular case, the list will be dynamically loaded from the SQL Select Statement, and its values will depend on the connection.

I've chosen the following SQL Select Statement:

SELECT 
    [ShiftID] AS [KEY],
    CONCAT(
        [Name], 
        ' (',
        FORMAT(CAST([StartTime] AS datetime2), 'HH:mm'),
        ' - ',
        FORMAT(CAST([EndTime] AS datetime2), 'HH:mm'),
        ')'
    ) AS [VALUE]
FROM [AdventureWorks2025].[HumanResources].[Shift]

The configuration looks the following way:

The enumeration will be reflected in the report definition like this:

<MetaEnum>
            <GUID>c107bf33-5f3a-4f26-908d-48a8f768d0d9</GUID>
            <Name>Shifts</Name>
            <IsDynamic>true</IsDynamic>
            <ValuesPerConnection>true</ValuesPerConnection>
            <Sql>SELECT 
    [ShiftID] AS [KEY],
    CONCAT(
        [Name], 
        ' (',
        FORMAT(CAST([StartTime] AS datetime2), 'HH:mm'),
        ' - ',
        FORMAT(CAST([EndTime] AS datetime2), 'HH:mm'),
        ')'
    ) AS [VALUE]
FROM [AdventureWorks2025].[HumanResources].[Shift]</Sql>
          </MetaEnum>

Note that the report definition may also include a key–value collection inside a <Values> section. This typically appears after selecting Refresh values or pressing F9, which materializes the current enumeration entries in the report model.

Applying Enumerations on Restrictions

The process of applying a select-based filter is very similar to the one used for expression-based filters. The key difference is that, within the Advanced section, you must select the appropriate Custom enum.

Drag and drop the PayFrequency property from the HumanResources.EmployeePayHistory table into the Restrictions section. Then click on the newly created [PayFrequency Equals] expression. In the contextual panel on the right side, navigate to the Advanced section and select the PayrollFrequency enumeration.

Execute the report, and you will see the available filters as shown in the screenshot below.

It does not matter whether the enumeration is static or dynamic — both are applied in the same way when configuring a restriction or filter.

Skill Check

Apply the Shifts enumeration to the ShiftID property from the HumanResources.Shift table. The process is similar to the one discussed in the previous section.

Hint: combine restrictions using both the PayrollFrequency and Shifts enumerations.

If you’ve configured it successfully, try answering the following questions:

How many employees from the Engineering department have agreed to a Biweekly payroll frequency and are working the Day shift (07:00 – 15:00)?
How many employees with a FirstName starting with Chris are assigned to either the Evening or Night shift?

Dependent select lists allow one enumeration to react dynamically to the selection of another. In one of the following posts, I will show you how to apply dependent select lists.

Quick Summary

Static vs Dynamic Enumerations

Feature	Static	Dynamic
Loaded at runtime	❌ No	✅ Yes
Requires SQL execution	❌ No	✅ Yes
Stored in `<Values>`	✅ Yes	Optional
Reflects DB changes automatically	❌ No	✅ Yes
Depends on connection	❌ No	Optional