Report Builder 2.0: SQL table-valued functions with multiple parameters do not show fields
In Report Builder 2.0 when creating a query for a table or a chart, if I select a SQL table-valued function (on a MS SQL 2008 database) with more than one parameter then no columns are displayed in the "Selected fields" section. If I remove all parameters or if the function only has one parameter, then columns are displayed in the "Selected fields" section. Stored procedures with multiple parameters also work fine. How do I get it to work with a function with multiple parameters, as we do want to use functions and we don't want to create a stored proc wrapper for every function?
November 10th, 2010 7:28am
I have just checked and can confirm that this issue also exists in Report Builder 3.0. Is this a bug or is there some reasoning behind this? Can anyone confirm that they are also getting this problem?
November 11th, 2010 7:22am
I have just checked and can confirm that this issue also exists in Report Builder 3.0. Is this a bug or is there some reasoning behind this? Can anyone confirm that they are also getting this problem? Hi Christo Kruger, In my local environment, even if create a table-value function with two parameters, the result is fine as well below is my function: USE [AdventureWorks] GO SET ANSI_NULLS ON GO SET QUOTED_IDENTIFIER ON GO Create function [dbo].[EmployeeNames]() returns @employeeNames table (id int, name nvarchar(20),Description nvarchar(20)) as begin INSERT @employeeNames values(1, 'Ryan','Manager'); INSERT @employeeNames values(2, 'John','Director'); INSERT @employeeNames values(3, 'Bob','New member'); return end Then in the process of creating the report, I utilize this T-sql: SELECT * from EmployeeNames() where (id = @id and name=@name) it return three datafields correctly. So here I would recommend you have a check to your Table-valued function to see whether it is correct. If it still does not work, you could post some sample T-sql code, then I will have a test. Thanks, Challen Fu
November 15th, 2010 10:04pm
Hi Challen Fu That is a work-around. Entering SELECT * FROM MyFunction(@par1, @par2) in the text query editor works, and my data set is created correctly. I figured this out a couple of days ago but didn't update this post since this way is a work-around. But you seem to not understand the actual issue. Instead of entering the above query in the text editor when designing the query for a data set, I should be able to select my function from the database view and then see the actual fields being pulled in from the function. This is definitely a bug in both Report Builder 2.0 and 3.0 as far as I can tell.
November 16th, 2010 2:31am