Search code examples
sqlt-sqlreporting-servicessql-server-2012

SSRS shows no records in report but query returns results

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.

Solution

  • 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.