Recently I was asked by a client to setup notifications for a daily report output. This is a routine request that can be addressed within the Cognos portal by creating and scheduling a job for the report.
The unique challenge presented in my case however was that the client wanted results to be emailed to one set of recipients under a different condition than that for another set of recipients.
Specifically, when there were report results for a given day, report output was to be distributed to a group of managers. However, if there were no results for the day, a notification was to be mailed to the reporting staff indicating the absence of results.
The solution below involves a specialized burst setup, minor report alterations and a job created in the portal.
Solution:
Create a dummy query based on data. Provided there is no transaction activity, this ensures that there will always be something to report. This query can take many forms, in this case, the important component is an activity count set to 0.
NOTE: Unlike MS SQL or Oracle, Cognos will not perform any aggregate function on a null set. To produce the desired effect, write a query, define the field above and create a filter that chooses a data item from a model query subject. By completing these steps, the model query subject will always return results and if properly defined, the filter will always evaluate to false (ie. never affect the results).
The second part of the specialized burst setup involves writing a second query with the same column as above. Unlike before, this query will evaluate the activity count of the activity query. When there is no activity, this query will show no results and when there is activity, there will be an activity count.
Lastly, create the actual burst query – a union of the above two queries. This query evaluates the sum of the activity as one field while another field determines the recipients based on the evaluated sum. The Burst Label field can be used within the setup for the Label property.
Field Definitions:
Resulting in…
Or in the case of no results…
Multiple email addresses can be used here as well, separated by commas within the single quotes. Note that if needed, CAM ID’s can replace the email addresses.
Final Steps:
Now the traditional burst setup can be done using the union burst query defined above for recipients.
Within the report allow for empty results for your report query. From your report page, select the list properties and locate ‘No Data Contents’ in the property pane. Choose either ‘Content specified in the No Data tab’ or ‘Specified Text’.
This allows for a customized message or relevant content when no data is present in the original list query.
The last step is to setup the job within the Cognos portal. This job enables report execution either on a schedule or on demand. Be sure to enable ‘Burst the report’ and ‘Send the reports by email’. Note that at least one recipient must be specified here as well. This can be simply a dummy email address.
This solution was applied where the two conditions represented (a) results and (b) no results. However, any number of determining conditions can be created to specify recipients within the burst query to address your unique business challenge.
