RESOLVED... (sort of): Driver/Reference Query Performance

[adam_mc]

For a relational source:

I have a driver/reference query (All Products Query) that I want to query the results of without re-querying the database.
This query gets Sales & Inventory numbers for all Products in all Stores and then Ranks them based on Sales$ and has an After Aggregation Filter to get me the Rank <= 300.
This then gets me my Top 300 ranked products across the entire chain.
If I then report these on a single All Products page it takes about 1/2 hour to run.

I then want to query those query results and display additional pages and selecting from the Top 300 products by Product Line.

So, my Query Explorer Layout looks like:

    All
Products
  Query


Product                             All
Line 1         <--------     Products
Query                               Query

However, this additional step is adding in excess of another hour to the run-time.
I can only assume that it is not performing a subquery of the All Products Query and must be re-running both queries multiple times directly against the database.
After all, there are only 300 rows in the result set of the All Products Query.

Is there some setting that I am missing that would get Cognos to work in the way I am suggesting?

Any thoughts would be greatly appreciated.
Thanks in advance,
Adam.

[Lynn]

As far as I know a query reference, such as you describe, is always going to fire the query again against the database. I don't know of a way to force a sub-query such that a result set is queried instead of the database.

Can you figure out how to have the same query do all the work? For example, instead of filtering for top 300 in the main query you could have a data item that identifies top 300 vs not. Then use a page set to handle the two groups accordingly.

There is also a property for list containers to share a result set which sends only one request to the database. Might work in your situation if grouping and properties are consistent. You could use conditional styling to show only the desired portions in each of your different sections.

[adam_mc]

Lynn...

Thanks for your reply.

We've decided to go down the path of conditional formatting on the "subset" pages as I know that will work for us.
Not the most elegant solution, but it will work and will only query the database once.

As the user wants the result Excel, I'm also going to check out CAFE as we should be able to "dump" the all data into a page on the output and then have Excel pages with filters etc. pointing to the All Data Page.
As a Cognos Report Writer, I'm horrified about offering this as a solution! But, it is what the user wants.
On the plus side, it will give me an excuse to download and experiment with CAFE.

Thanks again,
Adam.

[Lynn]

Lynn...

Thanks for your reply.

We've decided to go down the path of conditional formatting on the "subset" pages as I know that will work for us.
Not the most elegant solution, but it will work and will only query the database once.

As the user wants the result Excel, I'm also going to check out CAFE as we should be able to "dump" the all data into a page on the output and then have Excel pages with filters etc. pointing to the All Data Page.
As a Cognos Report Writer, I'm horrified about offering this as a solution! But, it is what the user wants.
On the plus side, it will give me an excuse to download and experiment with CAFE.

Thanks again,
Adam.

Glad you found a silver lining there. Cognos for Microsoft Office might be an option also. I think the difference between that and CAFE is that you can author reports in CAFE (so it is an interface for those users that don't want to work in anything else) whereas with Office you can just include content in a spreadsheet that was already authored with some other reporting tool in the Cognos suite. Since you've got an excuse to play you might want to take on a larger playground :)