Driving Queries

[RandyM]

I have a report that contains 4 lists that all have the same content but are each filtered differently by just one field (say code=A, B, C & D).  The query structure for each list is exactly the same and is made up of about 10 queries with numerous joins (not simple).  Without copying the somewhat long list of queries 4 times, is there a way to drive the one query structure to run for each List? 

[New_Guy]

Hi,
You can do list sectioning by the data item and use in condition for the filter like code in ('a','b'....). Another option is a page set if you want each list for each selection. Let me know if you need further help.
Good luck
New guy


Sent from my iPhone using Tapatalk

[Lynn]

I have a report that contains 4 lists that all have the same content but are each filtered differently by just one field (say code=A, B, C & D).  The query structure for each list is exactly the same and is made up of about 10 queries with numerous joins (not simple).  Without copying the somewhat long list of queries 4 times, is there a way to drive the one query structure to run for each List?

Certainly the grouping/sectioning approach that RandyM mentions is a good option to consider. There are two other options I know of although performance is something to be wary of.

One option is to use a query reference. Drag a query into the query explorer and then drag your final big query alongside it to the right. Then go into the new query and add a filter for code=A. Repeat that for the others, changing the filter expression in each. Each list will be fed from the appropriate base query. You can see this concept illustrated in figure 4 here: https://www.ibm.com/developerworks/data/library/cognos/reporting/performance_and_tuning/page582.html

Another option is to use advanced conditional styles. Create your list for code A pointing to your big query. Navigate to the list body column style level of the list and set an advanced conditional style there. Call it something like "Show A" and then set the expression as [YourCode] = 'A'. The style for this condition would be the default but the style for all other values would be set to box type none (on the advanced tab). Carry on in the same manner for B, C, and D.

The strain of processing with both of these approaches can fall locally on the Cognos server so be aware of performance implications. If your lists are barfing up hundreds of thousands of rows then the second option would be a bad idea, although a report that barfs up a massive data dump is already less than desirable  ;)