Difference between revisions of "Scheduled Report Export"

From SmartWiki
Jump to: navigation, search
(→‎Scheduled Export Settings)
(→‎Configuration - Essentials)
 
(36 intermediate revisions by the same user not shown)
Line 1: Line 1:
 +
__TOC__
 +
 
=Overview=
 
=Overview=
−
A report can be exported based on a schedule specified in the Report Settings. '''Scheduled Export''' feature will export a file from the report to 1.) a [[SmartFolders_Overview|SmartFolder]], or 2.) a sub-directory of a SSH File Transfer Protocol (SFTP) server.
+
* [[System Administrator]]s can schedule report export to run periodically (daily, weekly, monthly, quarterly, yearly).
 +
* Scheduled report export can be configured to export a file to 1.) a [[SmartFolders_Overview|SmartFolder]]/[[SmartFolders_Overview#Configuration_-_Advanced|Configuration Folder]], or 2.) a sub-directory of a SSH File Transfer Protocol (SFTP) server.
 +
 
 +
=Configuration - Essentials=
 +
''Scheduled Report Export'' is built using SmartSimple's [[Reports|integrated reporting subsystem]]. The settings for ''scheduled report export'' is available on the '''File Export''' tab of the Edit Reports page.
 +
 
 +
On the '''Edit Report''' page, go to '''Export''' on the left hand side menu, and then to the '''Scheduled Export''' tab.
 +
 
 +
[[File:ScheduledExport.PNG|800px]]
 +
 
 +
'''
 +
The following settings are found from the <strong>Scheduled Export''' tab:</strong>
 +
 
 +
* '''Export Type''' - specify the type of export for the scheduled export
 +
 
 +
: ''Options:''
 +
:* File Export - export file based on settings from [[Exporting_Reports|'''File Export''']] tab
 +
:* Custom Export - export file based on settings from [[Custom_Export|'''Custom Export''']] tab
 +
 
 +
* '''Export File Name''' - file name of the file can be specified including stamp date. It is recommended to append a timestamp. To stamp the date, use [YYYY],[YY],[MM],[DD],[HH],[mm]. i.e. MyExportedReport_[YYYY]-[MM]-[DD].
 +
*: Note that the file extension should not be included in this section i.e. '''PaymentDetails_[YYYY]-[MM]_[DD] '''  and NOT "PaymentDetails_[YYYY]-[MM]_[DD].csv"  The extension of the file should be specified in the a.) ''Export Format'' dropdown in the [[Exporting_Reports|'''File Export''']] tab  for standard File Export or b.) ''File Extension'' textbox in the [[Custom_Export|'''Custom Export''']] tab for custom export reports.
 +
 
 +
* '''Do not export file when no record found  '''-  no file will be exported when this toggle is enabled
 +
* '''Export with Linked Reports '''- this feature can be used to extend the number of columns past the current limit by joining multiple reports. This feature is only available for report exports using File Export format. 
 +
* '''Activate '''- this toggle should be enabled for scheduled/in demand export.
 +
* '''Run Type''' -  choose from the dropdown
 +
** '''On Demand '''- used when the report is linked to an autoloader
 +
** '''Scheduled '''- choose frequency for scheduled export of this report
 +
* '''Frequency''' - How often should the report be exported.
 +
 
 +
: ''Options:''
 +
:* Every Day
 +
:* Every Week
 +
:* Every Month
 +
:* Every Year
 +
:* Custom
 +
 
 +
* '''Hours''' - specify time for the scheduled export
 +
* '''Start Date''' - specify the start date for the scheduled export
 +
 
 +
<span style="color: #ff0000;">Settings below are available after the March 2020 upgrade.</span>
 +
 
 +
* '''Export to Folder''' - the file can be exported to 
 +
** '''Folder Type''' - select a folder type where the file will be exported to.
 +
*** '''Configuration Folder'''
 +
*** '''SmartFolder'''
 +
** '''Folder''' - - select the subfolder where you would like the file to be saved.
 +
* '''Export to SFTP'''
 +
** '''Integration Key''' - select an existing integration key from the [[Integration Key Management]] from the dropdown list or click the "+" icon to create a new integration key.
 +
** '''SFTP Folder Path''' - sftp folder location where the files will be pushed i.e. /in or /out or /Payments/inbound
 +
* '''Encryption '''- the exported file from this report can now be encrypted by adding the PGP Encryption Key
 +
** '''PGP Encryption Key''' - select the key from the dropdown. If the keys are not added, you can add it
 +
** '''Encrypt file exported to selected folder''' - enable if you want the file sent to the SmartFolder/Configuration Folder to be encrypted
 +
** '''Encrypt file exported to SFTP '''- enable if you want the file sent to the SFTP to be encrypted 
 +
* '''Process On Every Record After Export'''
 +
** '''Update Status''' - updates the status of every record in the report to the selected status from the drop down list. Note that a status change will trigger any workflows associated with the status being changed to. workflows will be triggered with this change of the status
 +
** '''Trigger Workflow''' - workflow selected will be triggered to all records after export.
 +
Note that '''Update Status''' will be processed first before '''Trigger Workflow''' is triggered.
 +
* '''Link Report Export''' - select another report that you want to link to this report to be processed after this report has been processed. 
 +
* '''Send Email Notification On Completion''' - ability to email a list of users and or roles that will be emailed the exported file as an attachment.
 +
<!--94110 - Report Export - Scheduled Email Subscriptions-->
 +
** '''Email Template''' - select an email template for the emails sent to selected users
 +
** '''Roles''' - send emails to users based on their roles
 +
** '''Select Users''' - select users to send the email to
 +
** '''Attach Exported File in Email''' - option to attach the exported file on to the email sent out. Applicable only when the file is exported to a SmartFolder or a Configuration Folder
 +
* '''Send Email Notification When Export Failed''' - select users to send notification to
 +
** '''Users''' - select users you want to send failed notification to
 +
* '''Scheduled Export Status'''
 +
** '''Last Scheduled Exported''' - time and date of the last scheduled export.
 +
** '''Last Run''' - time and date of the last run.
 +
** '''Ad-Hoc Run''' - time and date of the last Ad-Hoc run
 +
 
 +
 
 +
'''Example to configure schedule'''
 +
 
 +
* Export Every Monday of the week @ 0900
 +
** Choose a future date that falls on a Monday for the '''Start Date''', '''Weekly''' for the '''Frequency''' and select the '''Time of Export''' dropdown list.
 +
* Export Every 15th of the month
 +
** Choose '''15th''' (future date) for the '''Start Date''' and '''Monthly''' for the '''Frequency''' and select the '''Time of Export''' dropdown list.
  
−
=Configuration=
+
=History Log=
−
On the '''Edit Report''' page, go to '''Scheduled Export''' tab.<br /><br />
+
<!--85662 - Enhancement2 to the scheduled export from a report-->
 +
<!--106718 - Log file size for Payments Integration - add Scheduled report history-->
 +
<span style="color: #ff0000;">Available after March 2020 upgrade.</span> <br/>
 +
A new feature that shows history information tab. 
 +
[[Image:ReportExportHistoryLog.PNG]]
 +
* '''#''' - item number
 +
* '''File''' - filename of the exported file.
 +
* '''File Size''' - file size of the exported file in kb.
 +
* '''File Export''' - status of the exported file.
 +
** '''''Success''''' - the file was successfully exported
 +
** '''''SFTP Failed''''' - file was not exported to the sftp
 +
** '''''Save to Folder Failed''''' - file was not exported to the [[SmartFolders_Overview|SmartFolder]]/[[SmartFolders_Overview#Configuration_-_Advanced|Configuration Folder]]
 +
** '''''Saved to Folder; SFTP Failed''''' - file was exported to the [[SmartFolders_Overview|SmartFolder]]/[[SmartFolders_Overview#Configuration_-_Advanced|Configuration Folder]] but was not sent to the sftp
 +
** '''''Save to Folder Failed; SFTP Failed''''' - file was not exported to the sftp and to [[SmartFolders_Overview|SmartFolder]]/[[SmartFolders_Overview#Configuration_-_Advanced|Configuration Folder]]
 +
** '''''Empty Report; No File Created''''' - file was created w/o any records or no file was created
 +
** '''''Saved to Folder Failed; SFTP Sent''''' - file was exported to the sftp but was not sent to the [[SmartFolders_Overview|SmartFolder]]/[[SmartFolders_Overview#Configuration_-_Advanced|Configuration Folder]]
 +
* '''Start Time''' - date and time the file was processed for export
 +
* '''End Time''' - date and time the file export was completed
 +
* '''Status''' - status of the scheduled report export
 +
** '''''Started''''' - when the file is being created
 +
** '''''Completed''''' - the file was exported
 +
** '''''Failed''''' - the file failed to be exported
 +
** '''''Interrupted''''' - the process was terminated i.e. [[What_Is_Gort%3F|GORT]] was terminated abruptly (someone restarted the server)
 +
* '''Run By''' - name of the user who uploaded the file
 +
* '''Error''' - displays error i.e. sftp error:#Error: Permission denied
 +
* '''Process ID''' - unique identifier of the process
  
−
===Scheduled Export Settings===
+
=Troubleshooting=
−
:* '''Export Type''' - specify the type of export for the scheduled export
+
:* This won't work for reports that have any criteria, or reports with any other variables that require a user session or user input - for instance, the setting that allows you to select a template from a SmartFolder before exporting. Reports violating these will either not be exported, or have no data when exported, or maybe some other unpredictable result.
−
:: ''Options:''
+
:* To reset/change the schedule, '''Disabled''' the '''Frequency''' then save it. This will delete the current setting configurations from the database. Then, enter new schedule settings and save.
−
::* File Export - export file based on settings from [[File_Export|'''File Export''']] tab
 
−
::* Custom Export - export file based on settings from [[Custom_Export|'''Custom Export''']]
 
  
−
:* '''Frequency''' - How often should the report be exported.
 
  
−
::: ''Options:''
 
−
:::* Disabled
 
−
:::* Daily
 
−
:::* Weekly
 
−
:::* Monthly
 
−
:::* Quarterly
 
−
:::* Yearly
 
−
:: '''At''' - specify time for the scheduled export
 
−
::* '''Starting''' - specify the start date for the scheduled export
 
−
::* '''Export to''' - select where you want to export the file to
 
−
''Options:''
 
−
::*** '''SmartFolder'''
 
−
::**** ''SmartFolder'' - Select a SmartFolder where you would like the file to be saved.
 
−
''sftp''
 
−
::**** '''URL''' - enter the url of the sftp site i.e. ''34.234.21.159'' or mft.myurl.com:2222 or sftp.myurl.com
 
−
::**** '''Username''' - username to allow access to the sftp
 
−
::**** '''Password''' - password to allow access to the sftp
 
−
::**** '''Path''' - sftp folder location where the files will be pushed i.e. /in or /out or /Payments/inbound
 
−
'''Update Status After Export''' - status of the records will changed to the selected status after export.
 
−
::* '''Trigger Workflow After Export''' - workflow selected will be triggered to all records after export.
 
−
::* '''Export File Name''' - file name of the file can be specified including stamp date. It is recommended to append a timestamp. To stamp the date, use [YYYY],[YY],[MM],[DD],[HH],[mm]. i.e. MyExportedReport_[YYYY]-[MM]-[DD]
 
−
::* '''Last Exported''' - date of the last export.
 
  
−
=== ===
 
 
[[Category:Reports]]
 
[[Category:Reports]]

Latest revision as of 15:46, 9 August 2022

Overview

  • System Administrators can schedule report export to run periodically (daily, weekly, monthly, quarterly, yearly).
  • Scheduled report export can be configured to export a file to 1.) a SmartFolder/Configuration Folder, or 2.) a sub-directory of a SSH File Transfer Protocol (SFTP) server.

Configuration - Essentials

Scheduled Report Export is built using SmartSimple's integrated reporting subsystem. The settings for scheduled report export is available on the File Export tab of the Edit Reports page.

On the Edit Report page, go to Export on the left hand side menu, and then to the Scheduled Export tab.

ScheduledExport.PNG

The following settings are found from the Scheduled Export tab:

  • Export Type - specify the type of export for the scheduled export
Options:
  • File Export - export file based on settings from File Export tab
  • Custom Export - export file based on settings from Custom Export tab
  • Export File Name - file name of the file can be specified including stamp date. It is recommended to append a timestamp. To stamp the date, use [YYYY],[YY],[MM],[DD],[HH],[mm]. i.e. MyExportedReport_[YYYY]-[MM]-[DD].
    Note that the file extension should not be included in this section i.e. PaymentDetails_[YYYY]-[MM]_[DD]   and NOT "PaymentDetails_[YYYY]-[MM]_[DD].csv"  The extension of the file should be specified in the a.) Export Format dropdown in the File Export tab  for standard File Export or b.) File Extension textbox in the Custom Export tab for custom export reports.
  • Do not export file when no record found  -  no file will be exported when this toggle is enabled
  • Export with Linked Reports - this feature can be used to extend the number of columns past the current limit by joining multiple reports. This feature is only available for report exports using File Export format. 
  • Activate - this toggle should be enabled for scheduled/in demand export.
  • Run Type -  choose from the dropdown
    • On Demand - used when the report is linked to an autoloader
    • Scheduled - choose frequency for scheduled export of this report
  • Frequency - How often should the report be exported.
Options:
  • Every Day
  • Every Week
  • Every Month
  • Every Year
  • Custom
  • Hours - specify time for the scheduled export
  • Start Date - specify the start date for the scheduled export

Settings below are available after the March 2020 upgrade.

  • Export to Folder - the file can be exported to 
    • Folder Type - select a folder type where the file will be exported to.
      • Configuration Folder
      • SmartFolder
    • Folder - - select the subfolder where you would like the file to be saved.
  • Export to SFTP
    • Integration Key - select an existing integration key from the Integration Key Management from the dropdown list or click the "+" icon to create a new integration key.
    • SFTP Folder Path - sftp folder location where the files will be pushed i.e. /in or /out or /Payments/inbound
  • Encryption - the exported file from this report can now be encrypted by adding the PGP Encryption Key
    • PGP Encryption Key - select the key from the dropdown. If the keys are not added, you can add it
    • Encrypt file exported to selected folder - enable if you want the file sent to the SmartFolder/Configuration Folder to be encrypted
    • Encrypt file exported to SFTP - enable if you want the file sent to the SFTP to be encrypted 
  • Process On Every Record After Export
    • Update Status - updates the status of every record in the report to the selected status from the drop down list. Note that a status change will trigger any workflows associated with the status being changed to. workflows will be triggered with this change of the status
    • Trigger Workflow - workflow selected will be triggered to all records after export.

Note that Update Status will be processed first before Trigger Workflow is triggered.

  • Link Report Export - select another report that you want to link to this report to be processed after this report has been processed. 
  • Send Email Notification On Completion - ability to email a list of users and or roles that will be emailed the exported file as an attachment.
    • Email Template - select an email template for the emails sent to selected users
    • Roles - send emails to users based on their roles
    • Select Users - select users to send the email to
    • Attach Exported File in Email - option to attach the exported file on to the email sent out. Applicable only when the file is exported to a SmartFolder or a Configuration Folder
  • Send Email Notification When Export Failed - select users to send notification to
    • Users - select users you want to send failed notification to
  • Scheduled Export Status
    • Last Scheduled Exported - time and date of the last scheduled export.
    • Last Run - time and date of the last run.
    • Ad-Hoc Run - time and date of the last Ad-Hoc run


Example to configure schedule

  • Export Every Monday of the week @ 0900
    • Choose a future date that falls on a Monday for the Start Date, Weekly for the Frequency and select the Time of Export dropdown list.
  • Export Every 15th of the month
    • Choose 15th (future date) for the Start Date and Monthly for the Frequency and select the Time of Export dropdown list.

History Log

Available after March 2020 upgrade.
A new feature that shows history information tab.  ReportExportHistoryLog.PNG

  • # - item number
  • File - filename of the exported file.
  • File Size - file size of the exported file in kb.
  • File Export - status of the exported file.
    • Success - the file was successfully exported
    • SFTP Failed - file was not exported to the sftp
    • Save to Folder Failed - file was not exported to the SmartFolder/Configuration Folder
    • Saved to Folder; SFTP Failed - file was exported to the SmartFolder/Configuration Folder but was not sent to the sftp
    • Save to Folder Failed; SFTP Failed - file was not exported to the sftp and to SmartFolder/Configuration Folder
    • Empty Report; No File Created - file was created w/o any records or no file was created
    • Saved to Folder Failed; SFTP Sent - file was exported to the sftp but was not sent to the SmartFolder/Configuration Folder
  • Start Time - date and time the file was processed for export
  • End Time - date and time the file export was completed
  • Status - status of the scheduled report export
    • Started - when the file is being created
    • Completed - the file was exported
    • Failed - the file failed to be exported
    • Interrupted - the process was terminated i.e. GORT was terminated abruptly (someone restarted the server)
  • Run By - name of the user who uploaded the file
  • Error - displays error i.e. sftp error:#Error: Permission denied
  • Process ID - unique identifier of the process

Troubleshooting

  • This won't work for reports that have any criteria, or reports with any other variables that require a user session or user input - for instance, the setting that allows you to select a template from a SmartFolder before exporting. Reports violating these will either not be exported, or have no data when exported, or maybe some other unpredictable result.
  • To reset/change the schedule, Disabled the Frequency then save it. This will delete the current setting configurations from the database. Then, enter new schedule settings and save.
Retrieved from ‘https://wiki.smartsimple.com/index.php?title=Scheduled_Report_Export&oldid=40134’