May 5, 2016 at 4:38 pm
I thought it would be a simple matter of going to the report's dataset, look at the properties, and then click on the button labeled "Refresh Fields". (I used this as a reference.)
I've done that twice, but the report is still showing the data that it was before I changed the stored proc used to generate the data for the report.
OK, what am I doing wrong?
Kindest Regards, Rod Connect with me on LinkedIn.
May 5, 2016 at 5:23 pm
What happens when you run the stored procedure inside SMS, not SSRS? Is there a chance that you're connecting to a different server that has a different copy of the database?
May 5, 2016 at 5:44 pm
I have had this exact same problem before. If you're developing the report in SSDT then restarting SSDT then trying again sometimes resolves the problem. Sometimes the only thing you can do is recreate the dataset (which is not a big deal).
-- Itzik Ben-Gan 2001
May 6, 2016 at 2:13 am
SSRS caches report data as a .DATA file in the solution folder.
Either run the report again and click the little "Refresh" icon - this should force SSRS to rerun the query and refresh the cache, or open the Solution folder and delete the .DATA file for that report.
BIDS helper adds a right click option to delete dataset cache files among other useful things, worth checking out.
May 6, 2016 at 8:52 am
pietlinden (5/5/2016)
What happens when you run the stored procedure inside SMS, not SSRS? Is there a chance that you're connecting to a different server that has a different copy of the database?
Running the stored proc in SSMS works fine.
Kindest Regards, Rod Connect with me on LinkedIn.
May 6, 2016 at 9:48 am
Spiff (5/6/2016)
SSRS caches report data as a .DATA file in the solution folder.Either run the report again and click the little "Refresh" icon - this should force SSRS to rerun the query and refresh the cache, or open the Solution folder and delete the .DATA file for that report.
BIDS helper adds a right click option to delete dataset cache files among other useful things, worth checking out.
OK, this is what's happened. I've deleted the .DATA file, but the report still won't sort the way I've specified in the SP. However, it did pick up on the fact that I had changed the number of parameters to the SP. Weird, huh.
Now I'm wondering if I should just forget about the SP doing the sort and instead do it in the report?
Another thing, I've got the report in Release mode instead of Debug mode. Would that make a difference?
Kindest Regards, Rod Connect with me on LinkedIn.
May 6, 2016 at 9:55 am
The project configuration mode mainly determines the deployment options for your project so shouldn't really affect this.
I'm slightly unclear about the issue now - is it the data itself that isn't refreshing or the output columns of your resultset?
May 6, 2016 at 4:12 pm
OK, this is what's happened. I've deleted the .DATA file, but the report still won't sort the way I've specified in the SP
Are the results from the stored procedure going to a tablix? If so check that there isn't any sorting there as well that could be messing with your expectations. Sorting done anywhere in the report definition will occur after the data is pulled from data sources.
Joie Andrew
"Since 1982"
May 11, 2016 at 10:41 am
Spiff (5/6/2016)
The project configuration mode mainly determines the deployment options for your project so shouldn't really affect this.I'm slightly unclear about the issue now - is it the data itself that isn't refreshing or the output columns of your resultset?
It's more the resultset. I've changed the ORDER BY in the stored procedure, but that change is never reflected in the data displayed in the report.
Kindest Regards, Rod Connect with me on LinkedIn.
May 11, 2016 at 10:43 am
Joie Andrew (5/6/2016)
OK, this is what's happened. I've deleted the .DATA file, but the report still won't sort the way I've specified in the SP
Are the results from the stored procedure going to a tablix? If so check that there isn't any sorting there as well that could be messing with your expectations. Sorting done anywhere in the report definition will occur after the data is pulled from data sources.
There is no sort order in the report. However, I've begun to think that maybe I'll just do the sorting in the report, rather than in the SP. I think it would be faster, if done in the SP, but because of how hard it is to get this done (for whatever reason) I'm considering just letting the report do the sorting.
Kindest Regards, Rod Connect with me on LinkedIn.
May 11, 2016 at 10:52 am
SSRS and Access reports are alike in that respect - they have their own sorting and grouping, so they'll override the sorting in the dataset. If you want something sorted in your report, set the sorting options there. The only thing the dataset controls is what data is in the report, not the order of the records.
May 11, 2016 at 11:17 am
OK, I found the problem. I didn't realize it, but the report already had a sort order in it. So naturally even if there's one in the stored proc, it's ignored if there's one in the report. I upgraded the report from an MS Access report. I've never worked with Access reports, so I had no idea it was there.
I feel really stupid asking you all this question, since the problem was in the report to begin with. I'm sorry, everyone.
Kindest Regards, Rod Connect with me on LinkedIn.
Viewing 12 posts - 1 through 11 (of 11 total)
You must be logged in to reply to this topic. Login to reply