How to use Table-Valued Functions in MS Report Builder

Anonymous — Mon, 09 Dec 2024 18:07:48 GMT

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)

Re: How to use Table-Valued Functions in MS Report Builder

Anonymous — Tue, 10 Dec 2024 02:52:00 GMT

Hi,Anonymous .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.

Re: How to use Table-Valued Functions in MS Report Builder

Anonymous — Wed, 11 Dec 2024 17:26:16 GMT

Anonymous Brilliant! Thank you!

topic How to use Table-Valued Functions in MS Report Builder in Report Server

How to use Table-Valued Functions in MS Report Builder

Re: How to use Table-Valued Functions in MS Report Builder

Re: How to use Table-Valued Functions in MS Report Builder