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.

Answer 2

This answer might look outdated but in case someone needs it I will just reproduce my experience since I had the same problem just yesterday and I the solution from a webpage.

The problem: My report has 2 date parameters so to get data between that range, but i first noticed it did not work for 1 day, then I noticed that using a range did not bring the data in the last day

Workaround: I worked with the query, making some arrangement but when i made the query to return a whole month and nothing happened in the report i knew all the problem was in SSRS, of course I checked the parameters I was sending to the report.

Solution: My date parameters were OK (no problem with the time part) and I read somewhere to check for the filters in the "Data" Panel, that is the other tab besides the parameters. I had put those filters and totally forgot about then.

Answer 3

You haven't provided a repro or any details, so it's really a guessing game for us. Answering the question "What can I do to debug unexpected (no) data being rendered in preview?" can be done with at least:

• Delete the .data files (Visual Studio heavily caches data)
• Reboot Visual Studio (yes, unsatisfactory, but with SSRS it's helped my out on occasions)
• Check the ExecutionLog2 table in the ReportServer database to debug (RowsAffected and such)
• Run SQL profiler to check the query and results being returned

Answer 4

My problem was that I did not add the parameter for the report to the dataset. You have to right click on the dataset in the Report Data tab, and refresh the fields of the stored procedure. You can also add a parameter manually in the Fields menu item in the dataset properties window.

Answer 5

I ran into this and when I added the parameter value to the report, it automatically put in =Parameters!parametername(0), which caused no records to be returned because parameter(0) correlated to a value that wasn't in the data.

If you see an <exp> in the Value field, change it to the [@parametername] (I ran into this using the filter property of the dataset.). It allows you to create a semi-dynamic filter and I chose fieldname and then I chose IN as the comparative. Later I created an expression that got that messed up (0) value.