Setting up Document Distribution

From Vital Soft Wiki
Revision as of 19:31, 19 April 2013 by >Johno (Setting Up an Excel Template Report in DocumentDistribution)
Jump to: navigation, search

Summary

The Document Distribution module in Vital Signs allows you to create reports for automatic distribution. By default reports can be generated on a Daily, Weekly and Monthly frequency. Custom frequencies are also supported.

Report Types

There are three types of reports:

These reports require more initial setup, but are by far the most flexible option. This report type creates an Excel file which may include multiple sheets and charts with optionally different selections. Using Banding, you can create multiple documents which may contain multiple sheets. A fully functioning version of Excel must be installed on the Vital Signs server.
This option creates a simple export of a single chart or table. A single selection is supported. (Excel is not required on the server for this report type.)
This option executes a single QlikView report which may be saved as a PDF file or sent to a printer.

Report Banding

Banding is a technique that allows the same report (or chart) to be re-executed for each unique value of the banding field. For example, a report displaying delinquent loan balances could be banded by the BRANCH-NAME field so that a separate file is created for each branch. File level banding (the creation of separate files for each banding value) is supported for all report types.

  • Excel Template Reports
When using banding with Excel template reports, you could also create a separate sheet (in the same file) for each branch. "File Level" and "Sheet Level" banding can be combined so that a different file is created for each branch and within each file a different sheet is created for each loan officer.
  • Simple Excel Exports
Only file level banding is supported.
  • QlikView PDF Reports
Banding is also supported within the QlikView report editor. If banding is included within the report design, a separate page is created for each banding value.

When using banding, the current value of the banding field can be included as part of the file name or the sheet name (Excel Template Reports). The banding value can also be displayed as a text value in Excel Template reports of QlikView PDF reports.

Email Distribution

Once created, report files may be emailed as attachments. Please ensure that email attachments do not contain confidential data. A future Vital Signs release will support email notifications that do not include the actual attachment.

Initial Report Setup

Document distribution must be enabled for each document before Vital Signs is able to generate reports. Report distribution may be enabled globally or for each individual document by editing the vsreload.ini file and setting "reports=yes". The vsreload file is stored on the UNIX server in the /ASKPLUS/vsdata directory.

  • Example - Enabling report distribution globally for all documents :
[VitalSigns_Defaults]
  timeout=25
  enabled=yes
  reports=yes
  snapshots=0
  mth_snapshots=0
  • Example - Enabling report distribution for a single document :
[Membership_Analysis_by_ACCOUNT]
  stage=presentationQVW
  file=Membership_Analysis_by_ACCOUNT
  snapshots=7
  mth_snapshots=13
  reports=yes

Document Distribution

Overall, document distribution is managed by a single spreadsheet (DocumentDistribution.xls) located in the vitalsigns/CUNAME/data/config directory. The spreadsheet contains different sheets for each reporting frequency. The default frequencies are Daily, Weekly and Monthly. Each frequency sheet provides a list of all report requests for all Vital Signs applications that should be executed for that frequency. Daily reports are executed during the nightly Vital Signs reload. Weekly reports are generated on Sunday nights and Monthly reports are generated as part of the Month-End Vital Signs reload.

DocumentDistribution.xls File Layout

Excel Template Report

Template file based excel Reports

The simple export report exports the contents of a single chart or table to an excel file. This type of report is equivalent to selecting Export to Excel on a chart while running Vital Signs interactively.

  • A single selection is allowed
  • File level banding is supported
  • Excel is not required.

Setting Up an Excel Template Report in DocumentDistribution

Add an entry to the DocumentDistribution file with the following fields:

DocumentName Name of the Vital Signs Application (qvw file)
ExportType Excel
Definition Name of an Excel Template File (1)
OutputFileName Name of the Excel file to be created (2)
FileExistsAction Purge
Selection Optional: Name of the selection
Banding Optional: Name of the Banding Field
PrinterName This field is ignored for excel template reports
EmailList Optional: List of email recipients, separated by a semi-colon
(1) By default, the template definition file is located in the vitalsigns/CUNAME/data/config directory
(2) By default, the file is created in the same directory as the Vital Signs application file (usually vitalsigns/CUNAME)

Creating an Excel Template Report

An Excel template report is created by designing a template file using Excel. When the report is generated, the template file is read and a final Excel file is produced based on the definitions in the template file. The layout of the template file allows for very sophisticated Excel files to be created.

Simple Excel Export

The simple export report exports the contents of a single chart or table to an excel file. This type of report is equivalent to selecting Export to Excel on a chart while running Vital Signs interactively.

  • A single selection is allowed
  • File level banding is supported
  • Excel is not required.

Setting Up a Simple Excel Export in DocumentDistribution

Add an entry to the DocumentDistribution file with the following fields:

DocumentName Name of the Vital Signs Application (qvw file)
ExportType Export
Definition QlikView Object ID
OutputFileName Name of the Excel file to be created (*)
FileExistsAction Purge
Selection Optional: Name of the selection
Banding Optional: Name of the Banding Field
PrinterName This field is ignored for simple exports
EmailList List of email recipients, separated by a semi-colon
(*) By default, the file is created in the same directory as the Vital Signs application file (usually vitalsigns/CUNAME)

QlikView PDF Report

The simple export report exports the contents of a single chart or table to an excel file. This type of report is equivalent to selecting Export to Excel on a chart while running Vital Signs interactively.

  • A single selection is allowed
  • File level banding is supported
  • Excel is not required.

Setting Up a QlikView PDF Report in DocumentDistribution

Add an entry to the DocumentDistribution file with the following fields:

DocumentName Name of the Vital Signs Application (qvw file)
ExportType Report
Definition QlikView Report ID or Report Name
OutputFileName Name of the PDF file to be created (*)
FileExistsAction Purge
Selection Optional: Name of the selection
Banding Optional: Name of the Banding Field
PrinterName Optional: Name of printer if report is to be physically printed
EmailList Optional: List of email recipients, separated by a semi-colon
(*) By default, the file is created in the same directory as the Vital Signs application file (usually vitalsigns/CUNAME)

Creating a QlikView PDF Report

The following steps demonstrate how to create a Loan Totals PDF report out of the Membership_Analysis_by_ACCOUNT document

Log into the Windows server as the Vital Signs admin user

Find the vitalsigns/CUNAME directory and open Membership_Analysis_by_ACCOUNT

Select the Loan Charts tab

Click the Loan Totals button on the left hand blue panel

DocDistribution-PDF-Report-Example-Analysis by ACCOUNT-Loan-Totals.PNG


Click the circular orange arrow to drop down a list of possible dimensions, choose Loan Branch

DocDistribution-PDF-Report-Example-Bookmark-Creation-Loan-Totals-Cyclic-Group-Loan-Branch.PNG


Click Bookmarks->Add Bookmark...

DocDistribution-PDF-Report-Example-Add-Bookmark.PNG


Enter the name Loan Totals and click OK

DocDistribution-PDF-Report-Example-Add-Bookmark-DocDistribution-PDF-Report-Example-Loan-Totals.PNG


Click Reports->Edit Reports in the File Menu

DocDistribution-PDF-Report-Example-Edit-Reports.PNG


Click Add... Enter the name of the Report Loan Totals and Click OK

DocDistribution-PDF-Report-Example-Edit-Reports-add-new.PNG


Select Loan Totals so it is blue highlighted and Click Edit >> in the lower right

DocDistribution-PDF-Report-Example-Edit-Reports-Created.PNG


Click Page->Page Settings... On the General tab select Landscape

DocDistribution-PDF-Report-Example-Edit-Reports-Report-Editor-Page-Settings-Page-Orientation.PNG


Move the Report editor Window to the side so the Loan Totals chart behind it is visible

Left click the title bar of the Loan Totals chart and drag it onto the page inside the Report Editor window

DocDistribution-PDF-Report-Example-Dragndrop.PNG


Click Reports->Report Settings...

DocDistribution-PDF-Report-Example-Edit-Reports-Report-Editor-Reports-Settings.PNG


Select the Settings tab

Enter the Name of the Report Loan Totals

DocDistribution-PDF-Report-Example-Edit-Reports-Report-Editor-Reports-Settings-Modified-Settings.PNG


Select the Header/Footer tab

Modify the left, center and right sections

DocDistribution-PDF-Report-Example-Edit-Reports-Report-Editor-Reports-Settings-Modified-Header-Footer.PNG


Select the Selections tab

Select Bookmark and choose the Bookmark created earlier named Loan Totals and click OK

DocDistribution-PDF-Report-Example-Edit-Reports-Report-Editor-Reports-Settings-Modified-Selections.PNG


Hit OK to save the changed to the report

Click the Mbr Select tab

Click Clear

Save the Membership_Analysis_by_ACCOUNT document and exit QlikView

Open vitalsigns/YOURCU/data/config/DocumentDistribution.xls

Select the Daily tab

In DocumentName enter Membership_analysis_by_ACCOUNT

In ExportType enter Report

In Definition enter Loan Totals

In OutputFileName enter Reports\Loan Totals by Branch.pdf

In FileExistsAction enter Purge

DocDistribution-PDF-Report-Example-DocumentDistribution-Daily-tab.PNG


Login to HPUX as the bolive user

Change directory to /var/summit/spectrum/LIVE

Enter the following with the latest Date available in the Document in place of CCYYMMDD

/VSJOBS/JVSRELOAD Membership_Analysis_by_ACCOUNT CCYYMMDD PRINT=DAILY


After this finishes, you should have a Loan Totals by Branch.pdf located in vitalsigns\YOURCU\Reports\

The Loan Totals by Branch.pdf should look like this

DocDistribution-PDF-Report-Example-Analysis by ACCOUNT-Loan-Totals-PDF-Report.PNG

Document Distribution On Demand Processing

Login to HPUX as the bolive user

Change directory to /var/summit/spectrum/LIVE

Run the following with the appropriate parameters

/VSJOBS/JVSRELOAD taskname LOAD=NO PRINT=Frequency

Do I Need LOAD=YES ??

Generally speaking it is possible to process a document request using "LOAD=NO". However, if recent changes have been made to the main DocumentDistribution file, or if changes have been made to the "Select Sheet" or "Main Sheet" in an Excel Template File, the QlikView application will need to be reloaded. Changes made to a template file definition sheet (not the main or select sheets) do not require a reload. To force a reload, use "LOAD=YES", as follows:

/VSJOBS/JVSRELOAD taskname LOAD=YES PRINT=Frequency