Query Reuse question

[tupac_rd]

Hi Gurus,

I am trying to create a list report with 12 pages, one for each month (This is because I can get the month name in the tab name of Excel output). So, the query would be same for all 12 pages, except for the filter month = 1, or month =2, month =3 etc., for different queries... also the formatting for the list columns is going to be the same for all 12 pages... What is the best way of creating such a report... is there a way that I can use layout component reference here for the list, since each list will have its own query....and will the report be slow, because there will be 12 queries and is there a way to reuse queries etc.,

Thanks in advance
2pac

[blom0344]

A standard solution would be to work with sections and pagesets, but that would not yield the required Excel output I guess.  Query performance may not be an issue, since only the filter varies (the where clause of the SQL) so the acces path can be reused over and over again. So ,if the query is efficient 12 queries may not be a problem..

[tupac_rd]

Thanks for the reply blom0344. Can you please elaborate on the statement "the acces path can be reused over and over again." Do I have to do anything specific in report studio for this to happen or is this how IBM Cognos 8 query optimizer works etc.,

[blom0344]

Nope.. I meant the database query optimizer. Normally the database will take most of the query brunt..

[kattaviz]

Hi,

I think you can use Query reference here. Have a query bring all the data. Then create the required queries by filtering based on the month.

HTH

[blom0344]

Hi,

I think you can use Query reference here. Have a query bring all the data. Then create the required queries by filtering based on the month.

HTH

In the old days of client based reporting this would have worked. However webbased reporting offers no intermediate storage of all the data. If the query is inherently efficient, then creating the correct accces paths will take most of the time. Repeating it then for a number of times may not influence performance too much. Access paths are reused, perhaps some stored results are still in cache..

Later 8 versions even offer concurrent query execution, which may be even better. The proof is in the pudding..

[James_Bonnell]

I've had to do something similar recently, unfortunately while Page Sets are the fastest way to do this in terms of report development time, they don't produce the required tab name in Excel.

Using 12 queries and 12 report pages does.  Name the Page by month.

If you have 12 queries, they should execute simultaneously (if your DB server and Cognos version can handle that), filtered by month.  So they're getting separate slices of the data.  If you can, I would verify if there's an index on the source data table (one table? multiple tables?) that includes the month, or at least a date field.  If one can be created, that would be even better.

If the queries are otherwise identical, I would personally get the first page set format-wise, get the first query set as far as data is concerned, then copy the query 11 times (changing the month in the filter), then copy the page 11 times, rename it, and change which query goes to which page (or crosstab, list, whatever you are using).  It doesn't actually take all that long to do.

As to whether the report will be slow, can't really say without looking at it...

[tupac_rd]

I am more worried about if there are changes to the reports, we would need to change all the 12 queries and/or 12 pages and any conditional formattin on 12 pages.... Also, I need to create 12 different conditional variables since I am using a data item from the query, and I cannot use a conditional variable from one query for 12 different lists which have their own queries.....

[James_Bonnell]

That, or just have someone manually change the tab names in Excel, or ... I don't know Excel that well, but I wonder if that's something that could be macro'd?

As long as Page-1 is always, say, Jan, that might be something to explore...