Stop Linked Subreports hitting database again? 1

[mrees]

CR+CE9, SQLS2k.

Dear Tipsters,

I have come across something which I didn't think should happen. I have a report that is to display call centre statistics, it contains 2 groups, Supervisor & Agent.

The idea is that the user opens up the report and is presented with a list of Supervisors (Group 1), they then drill down on the desired supervisor name, through to the list of Agents (Group2) who are looked after by that Supervisor. Next to each Agent name is various "call time" data for the previous day (in one row). This data is presented via a subreport based on a SQL SP.

The user can then drill on the Agent name where they are taken to a 2 previously suppressed sections of Group2, where there is a crosstab of further call data from within the container report (This time for the week to date) and another subreport that gives data on a different subject for that agent in a crosstab. (Again provided by a SQL SP). Neither of the SQL SP based Subreports are on demand - although they are linked to the container report on AgentID

He's where I come to my point, once the report has been run in CE - I had assumed that the SQL SP part was finished with - but it has come to light that on the first viewing of the report instance,when a user drills to an agent the page takes some time to load and the SQL SPs are being fired. (This has been proved by adding an update to an start/finish audit file within each SP). Once this has been done, if a user is to choose the same Supervisor->Agent combination again - the SPs don't fire and the report page appears straight away.

Can someone explain why this happens and a possible general solution as how to solve this (I realise its hard to picture someone elses report after 3 paragraphs of babbling!)

Thanks

mrees

 

[synapsevampire]

You did a pretty good job of babbling here, I think I get it

I think that a suppressed subreport isn't fired until the data is requested. If the report is relaunched with the appropriate settings, then I believe what you're experiencing is that the second run loads from the cache. If you waited until the cache time setting had passed and tried, it should refire the underlying SP.

Perhaps someone here has other thoughts along these lines.

There's a CE forum here which should net better results.

-k

 

[mrees]

That was spot on Synapse! To get around the problem I just grouped by agent again (So creating a third duplicate group) And put a subreport in each group. So now neither subreport needs to be conditionally suppressed as the second one is hidden in a natural drill down way.

Woohoo-my skin is saved!

mrees