Issue

In the now sunset Report Designer tool it was possible for runtime filters on workgroups to select an option 'Include Children'. This would then automatically report on the selected workgroup and also any child workgroup, even over many levels.

In Cognos there is no built-in functionality for this.

Resolution

(Please note this resolution requires knowledge of SQL and building reports in Cognos.)

As a workaround, it is possible to use the workgroup.OBS field to find any child workgroup for a selected workroup. To do so, the prompt filter would not be based on the workgroupID field but rather on the OBS field. This is also a unique key for a workgroup and at the same time a prefix that can be used to find all child workgroups.

Example

Query 1 would be used to build a prompt filter 'POBS':

Select name, obs from workgroup where deleted=0 order by name

Name will be the 'Display value' for the prompt and obs will be the 'Use Value'.

 

Query 2 can use this prompt filter in the following way:

 select * from workgroup
 where deleted=0 and obs like (#prompt('POBS','string')# + '%')

You can then build further queries based on this to retrieve additional information from other query subjects or SQL queries.

Additional notes

This article provides an example of how child workgroups can be included in a report, and is not covered by support agreements. The customer care team cannot provide further support on implementing this example.