Difference between revisions of "DocumentDistribution.xls"

From Vital Soft Wiki
Jump to: navigation, search
>Jeremyb
(→‎Pdfconfig Sheet)
>Johno
(→‎Frequency Sheets: Daily, Weekly, Monthly, ...)
 
(43 intermediate revisions by 3 users not shown)
Line 1: Line 1:
−
== Overview ==
+
= Overview =
  
−
The Document Distribution spreadsheet is used to create specific tasks to generate custom reports and schedule their distribution.
+
The Document Distribution spreadsheet is the primary definition for all custom reports for all Vital Signs applications.  The file consists of a [[#Select Sheet|select sheet]], multiple [[#Frequency Sheets: Daily, Weekly, Monthly, ...|"Frequency" sheets]] and two additional [[#Configuration Sheets|configuration sheets]].
  
−
The spreadsheet contains the following sheets: Select, Daily, Weekly, Monthly, Pdfconfig and Emailconfig.
+
* Select Sheet
 +
: This sheet contains global select definitions available to all report requests. 
 +
::* Selections for [[Setting up Document Distribution#Simple Excel Export|simple exports]] and [[Setting up Document Distribution#QlikView PDF Reports|QlikView PDF reports]] must be defined here. 
 +
::* Selections for [[Setting up Document Distribution#Excel Template Reports|Excel Template Reports]] may be defined here or in the template definition file.
  
−
== Select Sheet ==
+
* Frequency Sheets (Daily, Weekly, Monthly, ...)
 +
: By default, the spreadsheet contains three sheets that define the standard reporting frequencies: Daily, Weekly and Monthly.
  
−
'''Name'''
+
: The name of the sheet must match the report frequency that is passed to vsreload using the Print=Frequency parameter:
−
    The Unique name of the selection grouping
 
  
−
'''Step'''
+
:: /VSJOBS/JVSRELOAD task Load=N Print=Daily
−
    Sequence number (1 to N), the execution order of each step
 
  
−
'''Type'''
+
: Additional custom frequency sheets can be added, for example BoardReports, Finance, Tuesday etc.
−
    The type of action for the step of the selection
 
  
−
'''TypeName'''
+
: Custom frequencies are executed via a call to JVSRELOAD
−
    Name of the Action, Bookmark, Field or Dimension
 
  
−
'''TypeValue'''
+
:: /VSJOBS/JVSRELOAD task Load=N Print=BoardReports
−
    Corresponding value for the action.
 
  
 +
: [[Setting up Document Distribution|Document Distribution Overview]]
  
−
== Daily, Weekly, Monthly Sheets ==
+
: ''Note: Whenever a new frequency sheet is added, a corresponding change must be made to the vReportFrequencies variable in the main [[Vitalsigns.xls|Vital Signs configuration file]].''
 +
 
 +
= Select Sheet =
 +
 
 +
'''This sheet contains the names and definitions of all global selections that have been defined.'''
 +
* Selections defined in the select sheet of the DocumentDistribution file are '''global''' and are accessible to all report requests. 
 +
* A selection consists of one or more steps that share the same name.
 +
* Reports generated using an [[Excel Template File]] may also define '''local''' selections.
 +
* See [[Defining a Selection|defining a selection]] for more information on how to specify a selection for a report.
 +
 
 +
= Frequency Sheets: Daily, Weekly, Monthly, ... =
 +
 
 +
The default (pre-configured) frequencies are Daily, Weekly and Monthly.  Additional custom frequencies may be created.
 +
 
 +
Each frequency sheet must include the following columns:
  
 
'''DocumentName'''
 
'''DocumentName'''
−
    The name of the QlikView (qvw) file
+
: The name of the QlikView (qvw) file
−
    ''Note: the extension .qvw is not required''
+
:: * ''Note: the extension .qvw is not required''
−
    ''Note: a quick way to temporarily disable the printing of a document is to precede the name with a #''
+
:: * ''Note: a quick way to temporarily disable the printing of a document is to precede the name with a #''
  
 
'''ExportType'''
 
'''ExportType'''
−
    Options: Excel or Report
+
: The type of report being created.  Must be one of Excel, Export, Report
 +
:: * Excel - Use for [[Excel Template Reports]]
 +
:: * Export - Use for [[Simple Exports]]
 +
:: * Report - Use for [[QlikView PDF Reports]]
  
−
'''ReportName'''
+
'''Definition'''
−
    The QlikView object ID or report name
+
: The report definition.  The definition depends on the type of report being generated:
−
    ''Note: for excel exports, it must be the object ID''
+
:: * Excel - The name of a valid Excel Template definition file.
 +
::: By default, the definition file should be located in the vitalsigns/CUNAME/data/config directory.
 +
::: ''Note: Do not include the extension xls or xlsx with the definition, just the file name.''
 +
:: * Export - The QlikView [[QlikView Object ID|object ID]] of the chart or table
 +
:: * Report - The QlikView [[QlikView Object ID|report ID]] or report name
  
 
'''OutputFilename'''
 
'''OutputFilename'''
−
    The name of the Excel or PDF file being created
+
: The name of the Excel or PDF file being created.  The name can include special tags that will be substituted at run time:
−
    Available tags for dynamic file names:
+
: * <band> - The current banding value.  Use for file level banding.
−
      <band> - The current banding value
+
: * <date> - The current date.  This is the actual create date when the file was created.
−
      <date> - The current date
+
: * $(varname) - The value of any [[QlikView Variables|qlikview variable]].
−
      $(varname) - The value of any qlikview variable
+
: A fully qualified name is valid, however, in version 1.5.3 (and higher) the path may be specified in the FileCreatePath column.  A default path may also be specified in the PDFConfig sheet.
  
 
'''FileExistsAction'''
 
'''FileExistsAction'''
−
    Purge is the only action currently supported
+
: Purge - Purge any existing file with the same name before continuing.
 +
: Keep - Do '''not''' purge the file if it exists.  Existing sheets will be overwritten if the name matches a sheetname that will be created when the report is processed.
 +
:: ''Note: Sheets are overwritten rather than deleted to preserve any existing formulas that may refer to the sheet.''
  
 
'''Selection'''
 
'''Selection'''
−
    The name of a selection to execute prior to export.  Selections are listed in the Select tab
+
: The name of a selection to execute prior to generating the report.  Selections are defined in the Select sheet.
  
 
'''Banding'''
 
'''Banding'''
−
    The field that the export should use for every distinct value, example: MBR_BRANCH_NAME
+
: The field that the export should use for every distinct value, example: MBR_BRANCH_NAME
−
 
 
−
'''Definition'''
 
−
    Reserved for future enhancements, contact Vital Soft support for information
 
  
 
'''PrinterName'''
 
'''PrinterName'''
−
    The name of a printer to physically print the report
+
: The name of a Windows printer configured on the Vital Signs server. 
−
    ''Note: this field is usually blank''
+
:: ''Valid only for QlikView PDF reports.
 +
:: ''Note: this field is usually left blank as reports are typically saved as a PDF file.''
  
 
'''EmailList'''
 
'''EmailList'''
−
    A list of email addresses (separated by a semi-colon) to receive a copy of the created file(s)
+
: A list of email addresses (separated by a semi-colon) to receive a copy of the created file(s)
 +
:: ''Note: Do not email attachments containg confidential information.''
  
 +
'''FileCreatePath''' - New in version 1.5.3
 +
: The path where the file should be created.  Use only when a fully qualified name has not been entered as the OutputFilename.
  
−
== Selection Options ==
+
'''FileLinkPath''' - New in version 1.5.3
 +
: In version 1.5.3 and higher it is possible to email a link to a file, rather than the actual file.  This practice is strongly recommended since attached files may contain confidential data.  The file may be created by Vital Signs on a server, and a link to the file sent via email. 
 +
:: The link path will typically be of the form \\machine\path
  
−
    {| class="wikitable"
+
''Note: The above description is for version 1.5 document distribution.  The layout for versions prior to 1.5 was different.''
−
    |-
 
−
    !'''Type'''
 
−
    !'''TypeName'''
 
−
    !'''TypeValue'''
 
−
    !'''Description'''
 
  
−
    |-
+
= Configuration Sheets =
−
    | Action
+
There are two configuration sheets that must be setup: PDFConfig and EmailConfig.
−
    | Clear
 
−
    | N/A
 
−
    | Clears all selections
 
  
−
    |-
+
== PDFconfig Sheet ==
−
    |
 
−
    | ClearAll
 
−
    | N/A
 
−
    | Same as Clear
 
  
−
    |-
+
'''PDFPrinterName'''
−
    |
+
: The name of the PDF printer, default : VitalSignsPDF
−
    | LockAll
 
−
    | N/A
 
−
    | Locks all currently selected fields
 
  
−
    |-
+
'''FileCreatePath''' - New in Version 1.5.3
−
    |
+
: The default file create path.  This path may be over-ridden by the FileCreatePath column in any of the frequency sheets.
−
    | UnlockAll
 
−
    | N/A
 
−
    | Unlocks all currently selected fields
 
−
 
 
−
    |-
 
−
    |
 
−
    | ClearField
 
−
    | FIELDNAME
 
−
    | Clears the specified field
 
−
   
 
−
    |-
 
−
    |
 
−
    | ClearOther
 
−
    | FIELDNAME
 
−
    | Clears all fields except the specified field
 
−
 
 
−
    |-
 
−
    |
 
−
    | Lock
 
−
    | FIELDNAME
 
−
    | Locks the specified field
 
−
 
 
−
    |-
 
−
    |
 
−
    | Unlock
 
−
    | FIELDNAME
 
−
    | Unlocks the specified field
 
 
      
 
      
−
    |-
+
'''EmailLinkPath''' - New in Version 1.5.3
−
    |
+
: The default root url to an intranet path to pre-pend in report links within emails.  May be over-ridden by the EmailLinkPath in any of the frequency columns.
−
    | SelectPossible
 
−
    | FIELDNAME
 
−
    | Selects all possible values for the specified field
 
−
   
 
−
    |-
 
−
    |
 
−
    | SelectExcluded
 
−
    | FIELDNAME
 
−
    | Selects all of the excluded values for the specified field
 
  
−
    |-
+
== Emailconfig Sheet ==
−
    |
 
−
    | Execute
 
−
    | ButtonID
 
−
    | Executes a click for the button ID
 
  
−
    |-
+
'''FromUserName'''
−
    |
+
: This will appear as the From in the Email
  
−
    |-
+
'''SmtpServer'''
−
    | Field
+
: The IP address of the SMTP server
−
    | FIELDNAME
 
−
    | value
 
−
    | Perform a selection on the specified field using the value
 
−
''Note: may contain any value that you enter in a selection box search window is valid and may contain $(varname)''
 
  
−
    |-
+
'''SmtpPort'''
−
    |
+
: The Port that the SMTP server is listening on
  
−
    |-
+
'''SendUserName'''
−
    | SetDim
+
: The Username for authentication with the SMTP server
−
    | GROUPNAME
 
−
    | value
 
−
    | Selects the specified value in the GROUP to set a dimension
 
  
−
    |-
+
'''SendPassword'''
−
    |
+
: The Password for authentication with the SMTP server
  
−
    |-
+
'''SendUsing'''
−
    | Bookmark
+
: Values 1 to 3 (Default 2)
−
    | BM_NAME
+
    1 cdoSendUsingPickup
−
    | N/A
+
    2 cdoSendUsingPort
−
    | Execute the specified bookmark by name
+
    3 cdoSendUsingExchange
  
−
    |-
+
'''SmtpAuthenticate'''
−
    |
+
: Specifies whether of not the server uses authentication
−
    | BM_ID
+
     0 or 1 (Default 1)
−
    | N/A
 
−
    | Execute the specified bookmark using the bookmark ID
 
−
     |}
 
  
−
== Pdfconfig Sheet ==
+
'''SmtpUseSSL'''
 +
: Specifies whether of not the server uses SSL
 +
    0 or 1 (Default 0)
  
−
'''PDFPrinterName'''
+
'''SmtpTimeOut'''
−
    The name of the PDF printer, default : VitalSignsPDF
+
: Timeout in seconds (Default 60)
  
−
'''FileCreatePath'''
+
'''AttachmentsOk''' - New in version 1.5.3
−
 
+
: Yes or No (Default: prior to 1.5.3 was Yes)
−
   
 
−
'''EmailLinkPath'''
 
−
    The root url to an intranet path to pre-pend in report links within emails
 
−
 
 
−
== Emailconfig Sheet ==
 

Latest revision as of 22:20, 4 December 2015

Overview

The Document Distribution spreadsheet is the primary definition for all custom reports for all Vital Signs applications. The file consists of a select sheet, multiple "Frequency" sheets and two additional configuration sheets.

  • Select Sheet
This sheet contains global select definitions available to all report requests.
  • Frequency Sheets (Daily, Weekly, Monthly, ...)
By default, the spreadsheet contains three sheets that define the standard reporting frequencies: Daily, Weekly and Monthly.
The name of the sheet must match the report frequency that is passed to vsreload using the Print=Frequency parameter:
/VSJOBS/JVSRELOAD task Load=N Print=Daily
Additional custom frequency sheets can be added, for example BoardReports, Finance, Tuesday etc.
Custom frequencies are executed via a call to JVSRELOAD
/VSJOBS/JVSRELOAD task Load=N Print=BoardReports
Document Distribution Overview
Note: Whenever a new frequency sheet is added, a corresponding change must be made to the vReportFrequencies variable in the main Vital Signs configuration file.

Select Sheet

This sheet contains the names and definitions of all global selections that have been defined.

  • Selections defined in the select sheet of the DocumentDistribution file are global and are accessible to all report requests.
  • A selection consists of one or more steps that share the same name.
  • Reports generated using an Excel Template File may also define local selections.
  • See defining a selection for more information on how to specify a selection for a report.

Frequency Sheets: Daily, Weekly, Monthly, ...

The default (pre-configured) frequencies are Daily, Weekly and Monthly. Additional custom frequencies may be created.

Each frequency sheet must include the following columns:

DocumentName

The name of the QlikView (qvw) file
* Note: the extension .qvw is not required
* Note: a quick way to temporarily disable the printing of a document is to precede the name with a #

ExportType

The type of report being created. Must be one of Excel, Export, Report
* Excel - Use for Excel Template Reports
* Export - Use for Simple Exports
* Report - Use for QlikView PDF Reports

Definition

The report definition. The definition depends on the type of report being generated:
* Excel - The name of a valid Excel Template definition file.
By default, the definition file should be located in the vitalsigns/CUNAME/data/config directory.
Note: Do not include the extension xls or xlsx with the definition, just the file name.
* Export - The QlikView object ID of the chart or table
* Report - The QlikView report ID or report name

OutputFilename

The name of the Excel or PDF file being created. The name can include special tags that will be substituted at run time:
* <band> - The current banding value. Use for file level banding.
* <date> - The current date. This is the actual create date when the file was created.
* $(varname) - The value of any qlikview variable.
A fully qualified name is valid, however, in version 1.5.3 (and higher) the path may be specified in the FileCreatePath column. A default path may also be specified in the PDFConfig sheet.

FileExistsAction

Purge - Purge any existing file with the same name before continuing.
Keep - Do not purge the file if it exists. Existing sheets will be overwritten if the name matches a sheetname that will be created when the report is processed.
Note: Sheets are overwritten rather than deleted to preserve any existing formulas that may refer to the sheet.

Selection

The name of a selection to execute prior to generating the report. Selections are defined in the Select sheet.

Banding

The field that the export should use for every distinct value, example: MBR_BRANCH_NAME

PrinterName

The name of a Windows printer configured on the Vital Signs server.
Valid only for QlikView PDF reports.
Note: this field is usually left blank as reports are typically saved as a PDF file.

EmailList

A list of email addresses (separated by a semi-colon) to receive a copy of the created file(s)
Note: Do not email attachments containg confidential information.

FileCreatePath - New in version 1.5.3

The path where the file should be created. Use only when a fully qualified name has not been entered as the OutputFilename.

FileLinkPath - New in version 1.5.3

In version 1.5.3 and higher it is possible to email a link to a file, rather than the actual file. This practice is strongly recommended since attached files may contain confidential data. The file may be created by Vital Signs on a server, and a link to the file sent via email.
The link path will typically be of the form \\machine\path

Note: The above description is for version 1.5 document distribution. The layout for versions prior to 1.5 was different.

Configuration Sheets

There are two configuration sheets that must be setup: PDFConfig and EmailConfig.

PDFconfig Sheet

PDFPrinterName

The name of the PDF printer, default : VitalSignsPDF

FileCreatePath - New in Version 1.5.3

The default file create path. This path may be over-ridden by the FileCreatePath column in any of the frequency sheets.

EmailLinkPath - New in Version 1.5.3

The default root url to an intranet path to pre-pend in report links within emails. May be over-ridden by the EmailLinkPath in any of the frequency columns.

Emailconfig Sheet

FromUserName

This will appear as the From in the Email

SmtpServer

The IP address of the SMTP server

SmtpPort

The Port that the SMTP server is listening on

SendUserName

The Username for authentication with the SMTP server

SendPassword

The Password for authentication with the SMTP server

SendUsing

Values 1 to 3 (Default 2)
   1 cdoSendUsingPickup
   2 cdoSendUsingPort
   3 cdoSendUsingExchange

SmtpAuthenticate

Specifies whether of not the server uses authentication
   0 or 1 (Default 1)

SmtpUseSSL

Specifies whether of not the server uses SSL
   0 or 1 (Default 0)

SmtpTimeOut

Timeout in seconds (Default 60)

AttachmentsOk - New in version 1.5.3

Yes or No (Default: prior to 1.5.3 was Yes)
Retrieved from "https://wiki.vital-soft.com/index.php?title=DocumentDistribution.xls&oldid=620"