June 27, 2011 at 7:41 am
So after much MUCH research and digging into this report i'm developing, i've figured out that there is a problem with the report i've deployed. My stored proc is working perfectly. I put in the parameters that i would put in to get certian data and i get that data back. However, my report is not behaving the same.
For example one of the parameters is Rep, and when i put in All reps but the last one, i get the results i expect, however when i put in the last rep its not giving me back my results.
The issue is, that when i do the same thing for just the stored proc, i don't have this issue. I get the results whether that last rep is in there or not.
Also i thought it may have been a cached report issue, so i even deleted the report and re-deployed it, but that didn't fix it either.
Is this something i'm going to have to completely just delete the original .rdl file and create a new report from scratch? Or is this a bug that's come up before?
I'm using SSRS 2008 the server is also 2008, but has R2 installed. (not sure if this could be a problem)
June 27, 2011 at 7:45 am
Under the covers ssrs sends the query with sp_executesql. It can cause issues with datetimes, any parameters with commas in it and even apostrophes.
fire up profiler to see the exact command the report server is sending to sql server. That'll quite likely show you the exact problem.
June 27, 2011 at 9:19 am
I've not used profiler before. I've got it up, i'm just not sure how to run it. I can't find a good "How To" example on the web.
Am i running this on the report? or the stored proc the report is hitting?
June 27, 2011 at 9:23 am
Point the trace to a dev server.
Then start the report from the web interface (or whereever it's going to be deployed to)... of course using the test server as datasource.
I use this setup so that you can filterout the useless noise without going to deep in the tool. Just use the default trace and you'll be good to go.
June 27, 2011 at 9:28 am
Just to be clear you mean the dev reporting server?
Unfortunately i may not have access to this so i might have to get someone else in my department to run it.
June 27, 2011 at 9:30 am
Yes the dev server. But it needs to be on the server where the query will run (which very likely is not the report server which in my case only holds the reportserver's dbs).
June 27, 2011 at 10:15 am
Ok so i've run the profiler and it's definitely passing in the correct data to the stored proc.
Where do i go from here?
June 27, 2011 at 10:19 am
Copy the code that is passed in profiler and run it in SSMS. Sometimes it just looks similar.
Check if you see any difference between that code and the code that you have when running from BIDS.
June 27, 2011 at 10:22 am
I actually just did that as i got your reply, getting back the correct data with that code as well.
June 27, 2011 at 10:27 am
So what's different between the runs?
June 27, 2011 at 10:28 am
It sounds unlikely but could the data region on the report have some kind of filter within it, causing it to filter out records that are being returned from the stored procedure?
E.g. right-click the table, select properties and choose filtering.
Leonard
Madison, WI
June 27, 2011 at 10:50 am
Oh wow, so for some reason that last rep is getting a space added to the end of it. This is apparently throwing off the stored proc which somewhat makes sense. Looks like i'll have to do a trim when pulling that dropdown parameter together. let me try that and i'll let you know the results.
June 27, 2011 at 10:54 am
avitale (6/27/2011)
Oh wow, so for some reason that last rep is getting a space added to the end of it. This is apparently throwing off the stored proc which somewhat makes sense. Looks like i'll have to do a trim when pulling that dropdown parameter together. let me try that and i'll let you know the results.
As I said, that part of the inner workings of ssrs is annoying :w00t:.
June 27, 2011 at 10:57 am
Yup, VERY. I'm really the only person in my company that has to deal with it on a day to day basis too. The joys of being my companies first official Report Writer.
Rewriting the proc for that parameter drop down with an RTRIM in it seemed to have fixed my problem.
Thanks so much for all the help. And now i have some knowledge about how the profiler works! that's going to be a GREAT troubleshooting device! 😀 THANKS!
June 27, 2011 at 11:00 am
Just to be clear. You fixed the query that fills the parameter right? Not the proc itself?
Viewing 15 posts - 1 through 15 (of 16 total)
You must be logged in to reply to this topic. Login to reply