CCH KPI Monitoring: Generating and Delivering Reports, Forms and Documents

Dynamically Naming the Report Output File

You can have KPI Monitoring dynamically name the report output file, based on data from the triggered records. For example, an event that triggers an invoice (using a report as the invoice template) could name each generated invoice using a combination of the client’s name and order number.

To do this, select from the names of the trigger fields (e.g., a query’s columns) you wish to use in your report output files, Thus – using the preceding scenario – as long as you had selected the client name and order number in the trigger that is used by the event generating the invoices, you could specify an output file name of:

{cus_name}_{ord_no)

Note that you will need to refer to an associated trigger (and/or to KPI Monitoring’s list of date substitution variables) to get the names of any variable fields you wish to use in the report output file specification. 
Repeat this process for each report you wish KPI Monitoring to generate. 

The Report’s Schedule

A scheduled report (as opposed to a triggered report) runs on a recurring schedule, such as daily, weekly, or monthly. Click on the description beneath the Schedule heading and select the schedule that controls how often this event is generated and delivered.

THE REPORT’S SCHEDULE

Do Not Specify Any Triggers

Since a scheduled report runs on a recurring basis (and is not triggered by data conditions), be sure to have no triggers defined for a scheduled report event.

The Event’s Report(s)

A single scheduled report event may produce one report (e.g., “Weekly Sales Report”), or it may produce multiple reports (e.g., “Month-End Performance Reports”); you can have up to 999 reports associated with a single event. To add a report to an event, follow these steps:

1. Click on the blue Plus sign next to “Deliverables”.
2. Under “Alert Content”, click on the “Report” button. The following window opens:

 
AVAILABLE REPORTS FOR AN EVENT

By default, you will be shown a list of the reports that are defined for the current application; select a different application if your desired report was defined for an application other than the one you’re currently in. (You may also use the “Search” window to locate a report by its name.)
Once you have located the desired report, single-click on it then click on “Add Report”.

The Report’s Output Type (Style)

(If you do not see the “Report Properties” window on the right-side of your display, click on the report you just added to the event – beneath the “Deliverables” heading.)
On the Reports Properties window, click in the Report Type column to choose the report output format

• MS Word
• MS Excel (.xlsx)
• MS Excel legacy format (.xls)
• PDF
• HTML
• Comma Delimited
• Tab Delimited
• XML
• Image
• Ascii
• Rich Text
• Crystal

 The Report’s Properties

The Report Properties module looks like the following:
 
A REPORT’S PARAMETERS (SELECTION CRITERIA)

Report parameters let a report restrict the records that it retrieves to only those records that meet specific criteria. When a report includes parameters, they appear in the Report Properties window shown previously.
For each parameter you can supply a corresponding value that will be used whenever the report is generated. You may answer each parameter in two ways:
•    By manually filling in its value in the Answer field, i.e., enter the start/end values for a range.
•    By selecting a date substitution variable, such as “today”, “tomorrow”, or “last Monday.” 
•    Click on the green List button that appears next to the parameter you are working with and select the predefined variable you wish to use:

Report Delivery Methods

Reports can be emailed, FTP’d or linked to a webcast (dashboard) page. (See Report Delivery Methods for details.)

The Report’s Subscribers

Reports can be delivered to any combination of individual subscribers and subscriber groups. 

Triggering a Report on True/False Results

Many organizations need to generate a report if a situation proves true or false. Consider the following two examples:
Run a report if there were any sales of over $10,000 last week
Run a report if there were no sales of over $5,000 last week. 
To configure either of these conditions, you would design a trigger that uses the “count” function to count the number of records that meet the query’s criteria. In the first scenario, the report would trigger if the count of sales is greater than zero; in the second scenario, the report would trigger if the count of sales was equal to zero.

Linking a Report to a Triggered Event

To have an event generate reports that are based on the records that meet the event’s criteria, you must have some way to take the results from an event’s trigger and feed those results into the event’s report. This is accomplished through a report’s parameters. Consider the previous example – you have customers who are on hold and you want to generate an A/R Aging report for each on-hold customer. You would do the following:

1. Create a KPI Monitoring trigger (such as a query) that retrieves a list of on-hold customers.
2. Create an A/R Aging report so that is has a parameter on the customer ID.
3. Create a KPI Monitoring event that uses the trigger and report mentioned previously.

When you add the report to the event, the parameter (e.g., “please select customer ID”) will appear. In the “answer” field to this parameter, you’ll select the “customer ID” field from the event’s trigger.
When the event runs, it will:

1. Use its trigger to generate a list of on-hold customers – e.g., Client A, Client B, and Client C.
2. Take the first matching record – Client A – and pass that client’s ID into the A/R Aging report.
3. The Aging report for Client A will be generated and delivered.
4. Repeat steps #2 and #3 for clients ‘b’ and ‘c’.

Delivering a Triggered Report to the Record “Owner”

Consider the previous scenario – generating a separate report for each client who is on hold. In addition to generating a separate report per customer, you might want to deliver that report to a “related party” – such as to the customer who’s on hold, or even to the salesperson who’s responsible for that client’s account. A triggered-event enables this kind of intelligent delivery.

In the design of the “On Hold Customers” trigger, as long as you include the field(s) that contain the delivery addresses of the “related parties”, you’ll be able to tell KPI Monitoring to deliver the report to them. Using the above example, let’s say that you want to email the A/R Aging report to both the customer and their account manager.

All you would need to do is to go into the event’s trigger and make sure that it retrieved:
The field that contains the email address of the customer
and; 
The field that contains the email address of the customer’s account manager
By including those two fields in the event’s trigger, you’ll be able to select them as “advanced subscribers” for the KPI Monitoring triggered report event.

Trigger Design for Triggered Reports

It’s important to keep in mind that KPI Monitoring will (by default) generate one report per record that meets an event’s criteria. So – build your trigger so that it retrieves only one record per report that the event will generate. Consider the following example:

• Your staff gets assigned activities; sometimes those activities become overdue. You want KPI Monitoring to see if a staffer has overdue activities and (if so) generate and deliver a “Past Due Activity Report” to them.
• Your trigger should not generate a list of past due activities, because if a staffer has 10 past due activities, KPI Monitoring will generate 10 reports for that person. Instead, configure your trigger to count the number of overdue activities per staffer and retrieve only those staffers whose “past due count” is greater than zero. That way, regardless of the number of past due activities, each staffer with past due activities will appear only once in the trigger’s result set – and only one report will be generated and delivered to each of them. 

Using Triggered Reports to Generate & Deliver Forms & Documents

A very common use of triggered reports is in events that generate and deliver forms and documents such as invoices, statements, order confirmations, shipment confirmations, purchase orders, and the like.
In these scenarios, your report is actually a form (such as the ones mentioned previously), and it has a parameter that prompts for the “record ID” – such as a sales order number, customer number, or purchase order number. The event’s trigger retrieves a list of matching records (new orders, new shipments, new POs, etc).  When you link the report to the event you will pass the record ID (e.g., the sales order number from the trigger) into the report as a parameter.

Begin & End Repeat in Triggered Reports

In most cases, an event-triggered report will not make use of {BEGIN*REPEAT} and {END*REPEAT} in its alert message. In such cases, the event will generate one report for each record that meets the event’s criteria. 

However, if an event uses {BEGIN*REPEAT} and {END*REPEAT} to group multiple records within separate messages, KPI Monitoring will generate one report per delivered message. (Thus, an event that puts all matching records in a single alert message would, if it had an associated report, generate and deliver only one report, regardless of the number of records that meet the event’s criteria.

Trigger Design for Triggered Reports

Keep in mind the following 3 concepts when designing a trigger:
Use filters to control your result set – remember that KPI Monitoring will generate one report for each matching record, so it may be more effective to use the “count” aggregate function so that you do not generate the same report multiple times.

Be sure to include the columns/fields whose values need to be passed into the event’s associated reports as parameter values. Also include the columns/fields that contain the addresses of potential report recipients – such as the email addresses of the related customer, prospect, vendor, salesperson, or manager.

Report Selection

To link a triggered report to an event, follow these steps:
Open the event you wish to associate the report to.
Go to the event’s “Deliverables”, click on the blue plus sign, and add the desired report to the event.
Review the report’s “properties” and select the type of output format from the following:

o    ASCII
o    HTML
o    Rich Text
o    Microsoft Excel
o    Comma Delimited
o    Tab Delimited
o    PDF
o    Microsoft Word
o    Crystal
o    XML

(The output choice of “Crystal” is usable only by users who have the Crystal Reports “viewer” installed on their client workstation.)
Fill in the answers of any report parameters that appear. For each parameter you can choose to manually fill in the answer or click on the List button to choose either a date substitution variable or a field value from the event’s query. You may add additional reports to a single event.

The Triggered Report’s Schedule

Like any standard KPI Monitoring event, a triggered report event runs on a periodic schedule (e.g., hourly or daily) to see if the conditions specified in the event’s trigger(s) have been met. If they are met, the event is triggered and the corresponding reports are generated and delivered.
Click on the description beneath the “Schedule” heading and select the schedule that controls how often this event is submitted.

Report Delivery Methods

Reports can be emailed, FTP’d or linked to a webcast (dashboard) page. In some instances, it may be most efficient to use email (or SMS) to notify recipients about the availability of reports while using FTP or webcast to deliver those reports.

Email

At the bottom of the Email Deliverable window are two checkboxes that allow you to specify whether to attach the selected reports to the outgoing email message.

1. If you “include reports as email attachments”, KPI Monitoring will wait until the reports are successfully completed before sending the mail message. If a report fails to complete successfully, the email message will not be sent.
2. If you choose to not attach the reports, you can specify whether the email message should wait until all the associated reports are successfully generated. (Place a checkmark in “Email notifications should wait until reports are completed.”)

Embedding the Report Within the Email

If you are sending mail in HTML format, an event may be configured to send the report’s contents within the body of the email message (as opposed to sending the report as an attachment).
This function is available only if you have KPI Monitoring configured to send out mail via SMTP or any other INTERNET-based mail system. 
To configure this, follow these steps:

1. Choose the report whose content you wish to appear within the body of the email message. (You can specify only a single report.)
2. Choose a report output type format of “HTML”.
3. Go to the “Email” tab and leave the “Message Text” field blank (empty).

When the event is triggered, KPI Monitoring will generate the corresponding report in HTML and will embed this HTML within the body of the email message that is sent to the event’s subscribers. If a subscriber of such an event is using an email system that does not support HTML, the report will appear as an attachment. Note also that such an event cannot have any other attachments (i.e., no other reports and no attached files).

FTP/SFTP (Copy)

To deliver a report via this method, go to the event details window and select the option to “include reports on copy operations”. Note that the copy/ftp destination is determined within each subscriber’s profile in KPI Monitoring. Specify this location in UNC format and do not reference mapped drives.

Webcast (Dashboard)

When a report is configured for delivery, KPI Monitoring enables you to specify whether the report will appear as a URL link in a webcast (dashboard) alert message. (Please see the chapter on “Webcasting / Dashboard Alerts” for more details.)
At the bottom of the Webcast Deliverable window are two checkboxes that allow you to specify whether to link the selected reports to the generated webcast page.

• If you “include reports as URL links”, KPI Monitoring will wait until the reports are successfully completed before generating the webcast page. If a report fails to complete successfully, the webcast will not be generated.
• If you choose to not link the reports, you can specify whether the generation of the webcast page should wait until all the associated reports are successfully generated. (Place a checkmark in “Webcast notifications should wait until reports are completed.”)

Report Subscribers

Reports can be delivered to any combination of standard and advanced subscribers; both individuals and groups. Because a triggered report has associated triggers, all the trigger data fields may be used to contain recipient alert delivery addresses. 

How Reports Appear in the Monitor

Scheduled reports start out in the tab called “Report Generation” (they go from “pending” to “generated”) and then move to the tab that represents how the reports are to be delivered to their intended recipients (e.g., “Email Delivery”).
Triggered reports begin in “Application Events”, from where you can track the event’s status as “checked” and (if appropriate), “triggered.” Only if an event is triggered will its associated report(s) be generated.

Once an event with associated reports has been triggered, it will proceed to the “Report Generation” tab. From this branch, you may track the progress of the event’s associated reports as they go from a “pending” to a “generated” state.
(If a report fails to generate successfully, it will appear under “Errors” in the “Report Generation” tab.)
Once a report generates successfully, you may click on the corresponding “Delivery” tabs within the Monitor and check to see that the message (and report) was successfully delivered.
Within the Application Events  Triggered tab, you may also click on the “Details” (blue plus sign) next to an event to see whether the notifications included the corresponding reports.