This is the sixth post in the series.
It is quite common that restrictions applied at the data model property level (regardless of their origin—expression filters, static or dynamic enumerations, etc.) cannot implement the complex business logic that a stored procedure or a user-defined function can handle.
Another motivation is related to custom joins defined in the generated SQL statement. In such cases, it is not always optimal to rely on JOIN statements generated automatically (under the hood) instead of using EXISTS statements that a database administrator might prefer for performance or clarity reasons.
The goal of this post is to demonstrate how to map data model restrictions to the parameters of a user-defined function (UDF) or stored procedure.
Defining a "Virtual" Table
This exercise mostly mimics what was described in the previous post:
The custom (non-base) table in this post will use the following user-defined function (UDF):
CREATE OR ALTER FUNCTION dbo.udf_get_persons
(
@state_province_ids NVARCHAR(MAX) = NULL,
@department_ids NVARCHAR(MAX) = NULL
)
RETURNS TABLE
AS
RETURN
(
SELECT
person.FirstName,
person.MiddleName,
person.LastName,
employee.BirthDate,
employee.Gender,
employee.MaritalStatus,
employee.JobTitle
FROM Person.Person person
INNER JOIN HumanResources.Employee employee
ON person.BusinessEntityID = employee.BusinessEntityID
WHERE
(
NULLIF(NULLIF(@state_province_ids,''),'NULL') IS NULL
OR EXISTS
(
SELECT 1
FROM Person.BusinessEntityAddress entity_address
JOIN Person.Address address
ON address.AddressID = entity_address.AddressID
WHERE entity_address.BusinessEntityID = person.BusinessEntityID
AND address.StateProvinceID IN
(
SELECT TRY_CAST(value AS INT)
FROM STRING_SPLIT(
REPLACE(REPLACE(@state_province_ids,'(',''),')',''),
','
)
)
)
)
AND
(
NULLIF(NULLIF(@department_ids,''),'NULL') IS NULL
OR EXISTS
(
SELECT 1
FROM HumanResources.EmployeeDepartmentHistory edh
WHERE edh.BusinessEntityID = employee.BusinessEntityID
AND edh.EndDate IS NULL
AND edh.DepartmentID IN
(
SELECT TRY_CAST(value AS INT)
FROM STRING_SPLIT(
REPLACE(REPLACE(@department_ids,'(',''),')',''),
','
)
)
)
)
)
The user-defined function is already adapted to Seal Report conventions: it accepts multiple selected values as parameters, or NULL if nothing is selected.
The SQL above demonstrates an example that Seal Report would not generate automatically. The motivation is straightforward: if no properties from the joined table need to be selected, why use a JOIN statement instead of an EXISTS condition?
The main difference is that the UDF shown above should be invoked like this:
SELECT *
FROM dbo.udf_get_persons(
'{CommonRestriction_ProvinceIds}',
'{CommonRestriction_DepartmentIds}'
)
If you receive errors when using the Refresh columns (F9) and Check SQL options, don’t worry — here is the workaround:
Temporarily use default values as parameters in order to generate the required table columns:
SELECT * FROM dbo.udf_get_persons( NULL, NULL )
Applying the UDF to the Data Model
The next step is to define Common restrictions and values within the data model. However, these will not appear unless at least one property from the virtual table is added to the Elements section.
So, let’s drag and drop all the properties available in our virtual table into the Row Elements section.
Once the virtual table exists and its properties are mapped to the data model, it becomes possible to map the UDF input parameters to the model restrictions.
Next, navigate to Models → Model → Model Definition → Common restrictions and values to configure the restrictions.
The two UDF restrictions should already be available for configuration.
These common restrictions should be linked to dynamic select lists by design.
I previously covered how to configure dynamic select lists here:
Skill Check
If you didn’t identify the required configuration for the common restrictions, here is my version:
The final result should look like this:
When using an MSSQL stored procedure, what limitation prevents you from implementing the same approach as with a UDF?
To better understand the behavior, try applying different filters. You may encounter issues when using multi-select values of type Text. In such cases, consider using a single value per parameter instead.
This example demonstrates several edge-case scenarios that are very useful in real-life reporting situations
In the next post, I will demonstrate how to create shared filters across multiple UI elements.
Top comments (0)