Refresh report cache fails when report queries have required detail filters.

[cognosjon]

Evening,

I've not looked at prompt caching for a long long time but recently was asked if it could be used to help speed up the generation of a prompt page in a report that has a large selection of values to display. I'm also using 11.1.7

So I've created a job for the report, detailed that its to refresh the report cache and kicked the job off. however ive noticed that if any of the underlying queries in the report have a required parameter detailed the job to create the report prompt cache fails. It will succeed if all the detail filters are set to optional, but this is valid for this report.

So would anyone know if there is a way around this? should it be doing this? and if anyone could point me in the direction of any recent IBM detail about this it would be appreciated as i can only seem to find Cognos 8 and 10 detail.

Thanks

[MFGF]

Evening,

I've not looked at prompt caching for a long long time but recently was asked if it could be used to help speed up the generation of a prompt page in a report that has a large selection of values to display. I'm also using 11.1.7

So I've created a job for the report, detailed that its to refresh the report cache and kicked the job off. however ive noticed that if any of the underlying queries in the report have a required parameter detailed the job to create the report prompt cache fails. It will succeed if all the detail filters are set to optional, but this is valid for this report.

So would anyone know if there is a way around this? should it be doing this? and if anyone could point me in the direction of any recent IBM detail about this it would be appreciated as i can only seem to find Cognos 8 and 10 detail.

Thanks

Hi Jon,

Are we talking about regular non-parameterised detail filters, or do the detail filters have parameters (resulting in prompts)? I assume the latter?

Have you looked at the properties of the report on the 'Report' tab to see if prompt values have been specified?

When setting up the job, if you go back to edit the report step, have you set prompt values on the Prompt tab?

I'm not sure you're going to be able to achieve what you hope here. My understanding is that the report cache is used for the queries that retrieve the data directly for the report objects, but not for the prompt queries. You've known me long enough to know I'm often wrong, though. :-)

Cheers!

MF.

[cognosjon]

Often wrong??? Certainly not...

In all honesty I think my confusing mess of a post probably didn't help.

In essence in its simplest form let's say we have a report that prompts for a customer using a prompt page. The prompt is driven by its own query.

There is then a list report generated by the report that has a detail filter that is looking for the parameter passed by the prompt. This detail filter is currently set to optional.

If I run a a job to refresh the prompt cache ( I've lots of customers) the prompt cache refresh is successful. I can run the report. The prompt is populated . I select a customer and the underlying list report filters as expected.

If I then change the detail filter to be required and then try to refresh the report prompt cache the job fails.

Maybe I'm trying to use them in a way that's not intended and happy to take that one , but as I can't find any documentation on it I'm scratching my head a little.

[MFGF]

Often wrong??? Certainly not...

In all honesty I think my confusing mess of a post probably didn't help.

In essence in its simplest form let's say we have a report that prompts for a customer using a prompt page. The prompt is driven by its own query.

There is then a list report generated by the report that has a detail filter that is looking for the parameter passed by the prompt. This detail filter is currently set to optional.

If I run a a job to refresh the prompt cache ( I've lots of customers) the prompt cache refresh is successful. I can run the report. The prompt is populated . I select a customer and the underlying list report filters as expected.

If I then change the detail filter to be required and then try to refresh the report prompt cache the job fails.

Maybe I'm trying to use them in a way that's not intended and happy to take that one , but as I can't find any documentation on it I'm scratching my head a little.

Hi Jon,

If the detail filter is set to Optional, the job doesn't fire the filter, so the prompt query doesn't get touched. If the detail filter is set to Required, the filter parameter has to be fulfilled, so there needs to be a prompt parameter value set somewhere in order to be able to fulfill it. In either case, I don't believe the prompt query runs.

Cheers!

MF.

[cognosjon]

Hi,

Funnily enough I've just been looking at this again but from a backend server view (SQL).
What I see with both tests is the prompt select query firing off. I've then tested by filtering the prompt query and again I see this run.

I've arranged a websession with IBM support tomorrow so I'll come back with an answer and update.

Thanks for your help as always  :D

[MFGF]

Hi,

Funnily enough I've just been looking at this again but from a backend server view (SQL).
What I see with both tests is the prompt select query firing off. I've then tested by filtering the prompt query and again I see this run.

I've arranged a websession with IBM support tomorrow so I'll come back with an answer and update.

Thanks for your as always  :D

Often wrong??? Certainly not...

I think this proves the "often wrong" hypothesis quite nicely :-)

MF.

[cognosjon]

just a brief update,
After a webex session with IBM support it does appear as though this is actually a bug in 11.1.7 fpxx

The issue doesn't occur when using 11.2.1 and the report prompt cache runs with no error.

I'll update again once I hear about any potential fix.