How to use Table-Valued Functions in MS Report Builder

[dashmarley11]

I have a Table-Valued Function in SQL that I'm trying to pull into MS Report Builder.  It has 4 parameters (Start Date, Version ID 1, End Date, Version ID 2).

My Function is:  [dbo].[RateAttributionReportNmd] (@StartTapeDate DATE, @StartVersionId INT, @EndTapeDate DATE, @EndVersionId int)

However, those 4 parameters aren't actual fields in the data but I want to use them as parameters in the report so that the function parameters can be used to update the data/SQL query.

SELECT * FROM [dbo].[RateAttributionReportNmd] ('2024-10-31', 3, '2024-11-30', 2)

[Anonymous · Accepted solution]

Hi,@dashmarley11 .I am glad to help you.
I created the test data
this is my test:

-- Checking and deleting existing tables
IF OBJECT_ID('dbo.YourDataTable', 'U') IS NOT NULL
BEGIN
    DROP TABLE dbo.YourDataTable;
END

-- Checking and deleting existing table-valued functions
IF OBJECT_ID('dbo.RateAttributionReportNmd', 'IF') IS NOT NULL
BEGIN
    DROP FUNCTION dbo.RateAttributionReportNmd;
END

-- Creating a new data table
CREATE TABLE dbo.YourDataTable
(
    ID INT PRIMARY KEY,
    TapeDate DATE,
    VersionId INT,
    Value DECIMAL(10, 2)
);


INSERT INTO dbo.YourDataTable (ID, TapeDate, VersionId, Value) VALUES 
(1, '2024-10-01', 1, 100.00),
(2, '2024-10-15', 2, 110.00),
(3, '2024-10-31', 3, 120.00),
(4, '2024-11-01', 2, 130.00),
(5, '2024-11-15', 1, 140.00),
(6, '2024-11-30', 5, 150.00),
(7, '2024-12-01', 4, 160.00),
(8, '2024-12-15', 3, 170.00),
(9, '2024-12-31', 2, 180.00),
(10, '2025-01-01', 1, 190.00),
(11, '2025-01-15', 4, 200.00),
(12, '2025-01-31', 2, 210.00);

SELECT * FROM dbo.YourDataTable;

-- Creating Table Value Functions
CREATE FUNCTION dbo.RateAttributionReportNmd 
(
    @StartTapeDate DATE, 
    @StartVersionId INT, 
    @EndTapeDate DATE, 
    @EndVersionId INT
)
RETURNS TABLE
AS
RETURN
(
    SELECT *
    FROM dbo.YourDataTable
    WHERE TapeDate BETWEEN @StartTapeDate AND @EndTapeDate
      AND VersionId BETWEEN @StartVersionId AND @EndVersionId
);

-- Testing table value function calls
SELECT * FROM dbo.RateAttributionReportNmd ('2024-10-31', 3, '2024-11-30', 5);

Use Microsoft Report Builder/Power BI Report Builder

After the data source is successfully connected, the parameters used in the four table value functions are created

Add a Dataset(It is actually a call to a table function in SQL Server)

SELECT * FROM dbo.RateAttributionReportNmd (@StartTapeDate, @StartVersionId, @EndTapeDate, @EndVersionId)

Add a table to display the data

The final presentation results are as follows:
 

Parameter settings in paginated reports are very important.
You need to check that you have the right type of parameter, that the parameter requires a manually selected value, and that there is no default value.
What is the way to get the default value (by giving a default value directly or by querying from Data Set)

If you need the selected value of the parameter to be queried from the Data Set, then you need to set up a separate Data Set for these parameters to get the data, and isolate it from the Data Set of the main table data to avoid cross-references (circular dependencies)

Parameter mappings in the dataset also need to be scrutinized

I hope my suggestions give you good ideas, if you have any more questions, please clarify in a follow-up reply.
Best Regards,
Carson Jian,
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

[dashmarley11]

@Anonymous  Brilliant!  Thank you!