Hi all,
I am creating an active report built off of a DMR. I have multiple charts using a data that is rolled up at different levels. Query A is lowest level. B is a rolled up version of A. C is a rolled up version of B...etc. up to E
My question is should I
Option 1 - Use Query A as a source for B,C,D and E?"
Option 2 - Or should I be using A for the source of B, B for the source of C, etc.
Option 3 - Or do I write individual queries for each level?
Option 4 - Other
I thought Option 2 would be better... but not if it runs all of the queries every time.
Any information you can provide about how it works when you use a query in a query would be great.
Cognos will only reuse a query that's shared between multiple data containers (charts, crosstab, etc) if they have the same grouping, sorting, and elements. If they don't, it will fire off separate SQL statements regardless of whether or not they share the same query.
With the understanding of how Cognos handles performance, you should go with the method that requires least maintenance and performs best. That may be option 1 or 3 for you, depending on performance rules above, and the complexity of your queries.
Jay