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?
Free Windows Admin Tool Kit Click here and download it now
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.
Free Windows Admin Tool Kit Click here and download it now
November 16th, 2010 2:31am

This topic is archived. No further replies will be accepted.

Other recent topics Other recent topics