How to use table-valued function in SQL Data Source

0 Answers 31 Views
Report Designer (standalone)
Chris
Top achievements
Rank 1
Chris asked on 19 May 2022, 03:52 AM

I can't get any columns to show in the Data Explorer when setting up a SQL Data Source with a command text that calls a SQL TVF function.

My example/test function is as follows:


CREATE OR ALTER FUNCTION dbo.Test(
	@CommaSeparatedValue varchar(max)
)
RETURNS @Items TABLE(
	ItemValue numeric(18,0)
)
AS
BEGIN
	INSERT INTO @Items
	SELECT
		Items.ItemValue
	FROM dbo.StringSplit(@CommaSeparatedValue , ',') Split
	LEFT JOIN Items ON TRY_CAST(Split.[Value] AS numeric(18,0)) = Items.ItemValue
	
	RETURN
END

And my set up for the datasource object:

 

Columns show as expected when querying a table or view, but TVF functions do not seem to work. In Expressions, I cannot see any available Fields.

Neli
Telerik team
commented on 19 May 2022, 08:42 AM

Hi Chris,

You can test the approach from the Configuring Stored Procedure with Temporary Tables KB article.

No answers yet. Maybe you can help?

Tags
Report Designer (standalone)
Asked by
Chris
Top achievements
Rank 1
Share this question
or