CCH KPI Monitoring : Dashboard Alerts (Webcasting)

Storing the Template

We suggest storing your webcast template(s) in a folder called “Webcaster” – you will need to create this folder – and we suggest you do so directly under the KPI Monitoring installation folder, as in the following example:

DASHBOARD  TEMPLATES LOCATION

Creating Multiple Templates

You may wish to create multiple templates for the following reasons:

• To have different dashboard layouts for different recipients. E.g., you might have one template that is used for your internal dashboards, but a different template for dashboards that are going to be seen by customers or suppliers.
• To have dashboards that reside in different locations for security reasons. Each template has its own “publishing location” and so internal dashboards might be stored inside a firewall, whereas customer dashboards might be stored outside it.
• To create multiple templates, make a copy of the original template, make any necessary changes to the new one, and then save the new template with its own unique name.

Add Publishing Location Folder(s)

The steps below show the creation of three (3) publishing locations beneath the KPI Monitoring “webcaster” folder. To create your own publishing locations, follow these steps:

1. Open Windows Explorer and navigate to the folder where you installed KPI Monitoring.
2. Navigate to the “Webcaster” folder.
3. Create a new folder (beneath the Webcaster folder) and give it a name according to the person or group who will be receiving dashboard alerts.
    
   3 PUBLISHING LOCATIONS (“FARBER”, “GENERAL”, & “MURPHY”)

Define “Default” Template & Publishing Location

Although you may define multiple templates and publishing locations, the dashboard alerts module requires that you create a “default” template and publishing location record. This becomes the template that KPI Monitoring uses for every event if no other template is specified.
Follow these steps to define the default template and publishing location:
Expand the Administrator branch and select the “Webcast Locations” branch. Click on the “Add” button to define a new location or on the blue Edit button to select an existing location:

WEBCAST TEMPLATE & PUBLISHING LOCATION DEFINITION

Description:  If you are adding your first webcast location, give this location a name of “DEFAULT” (all uppercase). You MUST have one template with a description of DEFAULT.
HTML Template:  Specify the name and location of the default template that will be used. Do not reference mapped drives; use UNC specifications instead.
Publish Location:  Enter the name of the folder beneath which you have defined the sub-folders in which webcast output will be generated.

For example, if you created publishing locations (folders)  beneath the “Webcaster” folder called “General”, “Murphy”, and “Farber”, your selection for the publish location would be:
    D:\Program Files\KPI Monitoring\Webcaster\

(Do not use mapped drives and make sure you place a backwards slash at the end of the publishing location. This tells KPI Monitoring to put the dashboard output into the appropriate sub-folders beneath this location.)
Active:  This makes the webcast publishing location available for use in KPI Monitoring events.
Dynamically create disk folders:  This option lets you specify whether you want KPI Monitoring to create folders in which dashboards are created if those folders do not already exist.

Note the following about publishing locations:
The computer running the KPI Monitoring service must have write capability to the publishing location.
You may specify an FTP/SFTP address as the publishing location using the format:
        ftp://username:password@ftp.location...directory_name

You may use a WML file as a template to support WAP access for handheld devices.

One Event – One Dashboard

If an event is going to generate a single dashboard, you should hard-code the location and name of that dashboard within the trigger itself.
Take an example of a “Customers on Hold” dashboard that displays a list of all customers on credit hold. You might decide to store this dashboard file in a folder called “General” and you wish to call that output file “customers_onhold”. Here’s how you would design the corresponding trigger/query:
Create a trigger/query that retrieves on hold customers.
Create a trigger/query column whose contents are:

'(name_of_publishing_folder)'+'\'+'(name_of_output_file).htm'

As in:

'General'+'\'+'customers_onhold.htm'

The above column name tells KPI Monitoring to create the dashboard file in the “General” publishing folder, and to give the dashboard output file a name of “customers_onhold”.
Give the above column a customized name of “dashboard_address”.
Preview the trigger/query to make sure that the “dashboard address” field shows up as:

General\customers_onhold.htm

Save your trigger/query.

WEBCAST DASHBOARD QUERY WITH HARD-CODED PUBLISHING LOCATION

One Event – Multiple Dashboards

If an event is going to generate multiple dashboards (e.g., user-specific), you will need to embed variables within the trigger/query dashboard address column. Using the example of the “Customers On Hold” dashboard, let’s say you want to create one dashboard for each salesperson who has on-hold customers and you have salesreps named Farber, Smith, and Jones. You’ve already created a publishing location folder for each of them.

Now consider the trigger; each customer is associated with a salesrep (the three mentioned above) and each salesrep’s last name corresponds to a publishing location. So – all you need to do is to tell KPI Monitoring to create a separate dashboard for salesperson who has on-hold customers. 
You would create a calculated column, and its format would again be as follows:

'(name_of_publishing_folder)'+'\'+'(name_of_output_file).htm'

But instead of hard-coding the name of the publishing folder (as previously detailed), you would use a database field to control the name of the folder in which the dashboards will be created, as in:

"AR_Salesperson"."lastname"+'\'+'customers_onhold.htm'

"AR_Salesperson"."lastname" is the field that contains the last name of the salesrep to whom a customer is assigned – and that last name also corresponds to the name of the folder in which you want each dashboard to be created. KPI Monitoring will automatically create the appropriate dashboards in the appropriate locations based on the records retrieved by a trigger.
(And so, if 3 salesreps had on-hold customers, KPI Monitoring would create three dashboard output files, one in each of the 3 corresponding publishing location folders, and each file would be called “customers_onhold.htm”.)
Triggers that create multiple dashboards must include a “Sorting” field that groups the matching records by dashboard recipient. Thus, in the above example, the query also includes a sorting field on the salesrep’s last name.

QUERY DESIGN FOR MULTIPLE DASHBOARDS (NOTE SORTING)

Dynamically Naming the Dashboard Output File

In addition to dynamically determining the publishing location of dashboard output files, KPI Monitoring also lets you dynamically name the dashboard output file itself. In the example from the preceding section, 3 salesreps named Farber, Smith, and Jones would each have a dashboard file called “overdue_activities” in their corresponding folder. But what if you wished to precede the name of each dashboard output file with the name of the salesrep?

Easy – just modify the dashboard address column as follows:

(database_field)+'\'+(database_field)+'(name_of_output_file).htm'

As in:

"AR_Salesperson"."lastname"+'\'+"AR_Salesperson"."lastname"+'_customers_onhold.htm'

Now the salesrep named Farber will have a file called “farber_customers_onhold” in his folder, and the other salesreps will have correspondingly-named dashboards as well.

Choosing a Specific Dashboard Template

(You may skip this section if you are using a single dashboard template.)
Earlier in this chapter it was mentioned that you can create multiple dashboard templates. If you have multiple templates, you can control which template the trigger’s resulting dashboard will use by modifying the dashboard address column as follows:
‘(template_location_name):’+’(publishing_folder)’+'\'+'(name_of_output_file).htm'

As in:

dash_ext:General\overdue_activities.htm

This syntax will create a dashboard called “overdue_activities” in the “General” folder using a webcast template called “dash_ext”.

A Dashboard That Uses an Aggregate Trigger

If you are creating a dashboard trigger/query that uses an aggregate function – such as the “summarize” or “count” functions, the column that contains the dashboard address must include a database field name (due to SQL syntax restrictions).
The best way to handle this is to choose a one-character field from your database (such as a “status” field) and use the “substring” command to retrieve a value from that field that will always be blank. For example:

SUBSTRING(sysdba.CLIENT.STATUS,20,1)+'General'+'\'+'Opp_Count_Value.htm'

In this example, no data is being retrieved from the “client status” field, but without a field reference, the dashboard address value (by itself) would not be accepted by the query’s SQL syntax. 

Expiration Days

The field called “Expiration Days” controls the number of days that information will remain in the Dashboard. Typically, users do not want dashboard information to remain in the display unless the corresponding business conditions remain in existence. 
An “Expiration Days” setting of minus one (-1) (the suggested setting) tells KPI Monitoring that each time the dashboard event runs, its contents will be refreshed with only those records that meet the event’s criteria.

Linking Reports/Charts/Files to a Dashboard

Dashboards may be created to include URL links to reports, charts and files. For example, you might have a dashboard that shows overdue payments and you might wish to include a report or chart that shows corresponding A/R Aging Report details.
To link a report, chart, or file to a dashboard event, follow these steps:

1. Create the report, chart or file as needed.
2. If a report or chart, define it within KPI Monitoring.
3. Open the dashboard event, go to the “Deliverables” and add the corresponding reports/charts/files. Select the output format and fill in any required parameters.
4. Go to the “Webcast” deliverable window and place a checkmark in the option boxes titled “Include reports as URL links”, “Include files as URL links” and “Webcast notifications should wait until reports are completed”. (The “report” check boxes apply to both reports and charts.)
5. The last step in linking a report, chart, or file to a dashboard is the process of specifying where – in the dashboard – you want the link to the report/chart/file (referred to as the “report reference field”) to appear. The name of the webcast field that provides the link to associated reports/charts/files is “{EVENT_URL}”.
6. (Each report, chart, or file associated with an event will appear as a separate URL link in the webcast.)

If you want the dashboard to generate just ONE report (and not one report per triggered record), you should place the KPI Monitoring variable called “{EVENT_URL}” in the webcast’s title (or “caption”), as in:

<caption class="mytable">Overdue Invoices {EVENT_URL}</caption>

If, however, you want the dashboard to generate one report per triggered record, you should place the “{EVENT_URL}” value in the body of the webcast, as in:

<th class='mytable'>Order Conf Doc</th>
<td class='mytable'>{EVENT_URL}</td>

This enables you to have reports and charts that are unique to each record in the webcast display. In the above example, the first line below is an HTML table’s column heading, and the second line is for each table row, and will display the report/chart link that is related to the corresponding triggered record:

“Repeat-Enable” the Dashboard Event

Since you want to make sure that every time a dashboard runs, KPI Monitoring will show all matching records, make sure that the event is repeat-enabled by going to the event details window and checking ON the option to “repeat notifications”.

Dashboard Subscribers

Except for user alert listings (detailed later in this chapter), dashboard events do NOT use “Standard Subscribers”. This is because the trigger/query that powers a dashboard event already contains the location and name of the dashboard output files; all you need to do is to specify the name of the trigger/query field that contains the dashboard location and name information.
(In the example from earlier in this chapter, the field where this was stored was called “dashboard_address”.) 
Thus, you would do the following to select the dashboard subscriber:

1. Click on the “Advanced Subscribers” option from within your dashboard event.
2. Click in the last field in the lower grid (“Contains webcasting name”) and a list button will appear.
3. Click on this button and choose the field called “dashboard_address”.
   
   WEBCAST ADVANCED SUBSCRIBER SELECTION