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?

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?

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

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.

• Marked as answer by Challen FuModerator Thursday, November 18, 2010 9:41 AM
• Unmarked as answer by Christo Kruger Thursday, November 18, 2010 9:49 AM

Is there any work around for this issue?

The problem will appear if there is more than one input parameter function. If there is only one parameter or none, then selected fields will appear.

Thanks,

Iman

I hope this is fixed in some future release.  It would be nice to be able to use the Query Designer for queries on table-valued functions.

John