Monday, March 12, 2012

Need report to NEVER cache

Let me say up front that rs:ClearSession=true didn't work :)

I have a report on RS that displays two blocks of data. The intent is to move items from one list to the other. To do so, there's a link on the detail lines which does a drill-through to another report. That report takes parameters (IDs for the record to be moved) and uses a stored procedure as it's datasource. The stored procedure takes in the parameter, does an INSERT INTO into the table that's driving which list items appear on, then returns the ID that was affected. On the report, there's just a text box stating that the item was added to the table and a "Back" text box. This Back item is a drill through back to the original report.

When we go back to the report (not really *back* since we're actually drilling through to the report anew), I want it to show the two lists with the one item moved from the one list to the other. Problem is, since the report was already run, it's showing the data from the cache. If I hit the refresh button, it updates and the item moves as it should.

I've read a number of posting on this and the only thing anyone said that should work was to use the rs:ClearSession=true option in the URL. So I generated a URL to the report, added this option. Tested that my URL worked and then changed the "Back" text box's navigation so that rather than a simple drill-through, it goes to a URL...the one with the ClearSession.

But it still doesn't work. I click the link on one of my lines, it goes to the drill through saying that it added the line to the table (I can even query the table in QA and see that it did), then I click the Back and I can see in the address box that the ClearSession arguement is there but the results still don't refresh.

I've also tried CTRL-F5 while in this state and the data doesn't refresh. The only thing that makes it re-run the stored procedure and pull in new data is the green refresh button.

Any ideas?

Thanks, Tim

Does the original report have a query parameter? If it does, the original query should be re-executed. You might need to bind it to a complext expression (not just =Parameters!XX.Value).|||

Brian,

The report has two data sets that are based on Stored Procedures. The sproc does have parameters but the only parameters being passed is a @.MODE that allows the sproc to run different blocks of code (let me know if you'd like to see the code of the sproc.

So there's nothing dynamic about what's being passed in, it's always the same. I tried adding a parameter called @.CURR_TIME to test what I thought you were saying. I set the dataset to pass "=Now()" to that parameter. Within the code of the sproc, the parameter is then ignored. In theory, everytime it's called, it would think it needs to send the sproc a new time, right? But still, when I drill through back to the parent report, it doesn't rerun. I hit the refresh and the data changes show up.

Help!!! :(

Tim

|||

Can anyone elaborate on what Brian was saying? I really need to get this report to always rerun the sproc behind it every time it refreshes through any means and Brian sounded like he knew how to do that.

Thanks,

Tim

|||

I'd still love to hear if anyone can elaborate on this. I didn't quite understand how to do what Brian was referring to.

Thanks,

Tim

|||

Hi Tim,

I had the same issue. To get around it, we created a time parameter, similar to what you described on your stored procedure, but on the report itself. Always passing the current time will make the report completely re-execute.

-Jessica

|||

Jessica,

Thanks so much for the response. I was so focused on getting the sproc to rerun, it didn't occur to me to try forcing the report to refresh in that way! I added a parameter to the report called ENSURE_REFRESH with a prompt "Ingore this prompt:", made it a datetime type and gave it a default of =DateAdd("s", 1, Now()). It worked perfectly!!!!

Everytime I drill back to the report, it's realizes it needs to refresh because the default changes and the underlying data changes are reflected!!!!

Thanks for getting me thinking in the right direction.

Tim Graffham

No comments:

Post a Comment