SSRS shows no records in report but query returns results

Question

I have a question according to SSRS. I am working with MSSQL Server management studio 2012 and BIDS Visual studio 2008 for the report design.

I have a report with some multivalue parameters and a stored procedure in behind which returns the records.

Now I've tried to find the problem on the parameter values passed to the stored procedure and a string split function. I looked in the SQL server profiler if the strings get passed in an unexpected form but thats not the case. I ran the exact execution code in the server as a query and got results back. but if i run the report in the preview pane of the report designer i get no results back.

If you need any additional infos, just let me know. I just thought there may be someone who faced the same issue and knows the response.

Answer 1

I will take a guess and say it is 'how' you are passing the multi value parameter. Personally when dealing with SSRS I use views, table functions, or just selects as SSRS can understand natively that this:

Where thing in (@Thing)

Actually means this in SSMS:

Where thing in (@Thing.Value1, @Thing.Value2, @Thing.Value3, etc...)

I am guessing your proc is taking a string that is actually a comma seperated array. When you do a parameter that takes a string array like '1,2,3,4' and you are addressing the procedure with something like a 'Text' parameter accepting multiple values you either specify or get from a query you essentially need to 'Join' the parameters if your procedure takes a value of a string that contains the array. EG: Proc called dbo.test executes to return rows for values 1,2,4 for a parameter ids are shown like:

exec dbo.test @ids = '1,2,4'

If I wanted to run the proc in SSRS with this value and I had a multi value parameter called 'IDS' I would have to assemble the array manually in a function in SSRS like:

=JOIN(Parameters!IDS.Value, ",")

Essentially telling the proc run a parameter 'IDS' by joining together the multiple values in a chain of comma seperated values. You do this in the dataset on the left pane where it lists 'Parameters' instead of specify the Parameter as it is like [@IDS] you click the 'Fx' instead and put in the function above.

For this reason I am a big proponent of views, selects, and table functions as you can use the predicate logic to take care of this.