Skip to main content
Eptura Knowledge Center

Automated Reports

Serraview Only Configuration

I'm sure you've seen all the fabulous reports we have on offer, or have even asked us for a custom report. These reports are what you swear by, and you use quite frequently to show off to your manager or to maintain your portfolio. So much so, that it can add another task to your to-do list.

So what if we can automate this for you? Yes, we can do that!

Automating reports assist you to run these regular reports without you lifting a finger. 

Reports can automatically be emailed to specified people at your desired time, just like a present you bought for yourself, arriving exactly when the post tracker says so. How convenient!

Examples of common automated reports include:

  • monthly archive candidate report
  • monthly utilization report
  • bi-weekly asset report 

So let's get automating!

We first need the following details:

  • What report ID would like automated.
  • How often this will be generated.
  • By hourly, day, or month.
  • Specific time (every 15 minutes), day of the week, or date of the month.
  • We recommend running this outside of business hours.
  • Email addresses of the recipients.

I'm ready to automate the report, how do I set this up?

The report details need to be set up in a JSON schema. A step-by-step instructions are outlined below in Automate a Report.

Wait what is JSON?

Don't worry, if you don't have a working understanding of JSON and this isn't something you want to implement yourself, then just contact the friendly Serraview Support team with the required details mentioned above, and we'll take care of it.

Automate a Report

Pre-requisite knowledge

  • Understanding of basic JSON

If you need help with setting up the automated report's JSON code, then contact your Customer Success Manager or the  Serraview Support team to request a report(s) to be automated. In the request you will need to specify the following:

  • the report ID.
  • how often this will be generated:
    • by hourly, day, or month.
    • specific time (every 15 minutes), day of the week, or date of the month.
    • we recommend running this outside of business hours e.g. 4:00am to 8:00am or 6:00pm to 11:00pm.
  • email addresses of the recipients.

You can automate the report.

  1. Navigate to Configuration > General.
  2. Select Reporting.
  3. In the Automated Report Schema field, enter the JSON code.

Remember to validate the JSON code before you paste it into the field, use the JSON Validator https://jsonlint.com/

Refer to the Automated Report Schema Examples - JSON Code below for the examples and code description.

5. Click the Updated button.

Automated Report Schema Examples - JSON code

The automated report is created using the JSON (JavaScript Object Notation) format.

Monthly Example

This report is created on the 5th day of the month at the time of 22:30.

The second report is created on the 5th day of the month at the time of 23:30 and it is generated from the latest snapshot. The report is renamed to the timestamp of yyyyMMdd HH.mm.ss.

The email notification is sent to username1 and username2 and if the SFTP fails then username1 will receive the failure email.

{
    "reports": [
    {
        "frequency": "monthly",
        "days": "5",
        "time": "22:30",
        "reportUrl": "2&floorSelector=&shiftToSnapshot=-2147483648&OutputType=Excel&PageSize=A4&PageOrientation=Portrait",    
        "subject": "report 2 monthly"
    }
    {
        "frequency": "monthly",
        "days": "5",
        "time": "23:30",
        "reportUrl": "2&floorSelector=&OutputType=Excel&PageSize=A4&PageOrientation=Portrait",    
        "parameters": [{
            "useSnapshot": "[[latest]]"
        }],
        "renameFile": "[[timestamp:yyyyMMdd HH.mm.ss]]NewFileName.ext[[timestamp:yyyyMMdd HH.mm.ss]]"
    }
    ],
    "to_email": "username1@company.com, username2@company.com",
    "sftp_config": {
        "hostname": "sftp.xxxx.xxxxx",
        "port": "22",
        "username": "testusername",
        "password": "bkcd/EMD1+oPFPltJhB9Hdhz2a/QN9KAB6tpY6K7cwAw8XDlQHSw8dUIC0S",
        "fingerprint": "ssh-rsa 1024 39:05:08:3d:f8:fb:41:f2:17:a4:68:3a:2f:ab:a0:2b"
    },
    "sftp_remote_path": "to_client_name",
    "local_path": "\\test",
    "emails_on_failure": "username1@company.com"
}

The JSON parameters are:

Parameter Description
reports Define an array of report objects.
frequency

Defines the frequency.

The options are monthly, weekly, daily, hourly

If the frequency parameter is not defined, it defaults to weekly, and then days parameter is mandatory.

When it is set to monthly, use the digit to represent the day of the month. A negative will count from the end of the month e.g. -1 means the last day of the month.

days

Defines the days.

If frequency parameter is not provided then the days parameter is required to specify which days the snapshot must be created.

The days parameter's values are:

  • day's name Monday to Sunday
  • day of the month 1 to 31. Also, the days parameter can accept multiple days e.g. "days": "15,-1" this will create a report on the 15 of the month and -1 is the last day of the month. If the frequency parameter is monthly, then days parameter is the day of the month 1 to 31.
time

Defines the time the automation will occur. Use the 24 hours format, for example, 23:30.

Do not use a time from 1:30 to 03:30 as this is reserved for nightly tasks that start at 1:30, instead use 00:45 or 03:45.

reportUrl

The ReportID and the following parameters.

From the Reporting module complete the following:

  1. Navigate to the Analytics > Reporting.
  2. In the Search field, enter the name of the report or the report's number.
  3. Set the parameters.
  4. Click the Copy URL to Clipboard button.
  5. Paste the URL into a text editor

For example:

ReportID=2&floorSelector=&OutputType=Excel&PageSize=A4&PageOrientation=Portrait

3. Copy all characters after 'ReportId= into the JSON code.

2&floorSelector=&OutputType=Excel&PageSize=A4&PageOrientation=Portrait
subject

When the to_email is defined this is mandatory.

This is the email subject for the email that is sent to the receiver.

Each report will be sent in separate emails.

email_body

This is the email body and it can be either defined or not defined.

email_body defined

If it is defined then you include a customized HTML content.

Content will be in HTML format and you must use HTML tags.  Also, note that the following format must be used: 

  • For a carriage return and new line, use the HTML tag <br/> 
  • For a link, use  and this place holder will be replaced by the <a href="http://actual_report_download_Url">link> in the email content and the text between will be displayed as text the content in the <a> tag.

email_body not defined

If no email_body field is defined then by default, the content is:

Please click the <a href="$link">link</a> to download the report.

parameters

The following parameters can be applied to the reportUrl

Parameter Description
useSnapshot

Either you can specify a snapshot name or use the latest snapshot.

To specify the snapshot name use:

"useSnapshot": "SnapshotName" ]"

To specify to use the latest snapshot data use:

"useSnapshot": "[[latest]]"

If the specified snapshot name or the latest snapshot does not exist, then current data is used by default.

renameFile

The downloaded report will be renamed to NewFileName.ext.

Important note the file extension must be .csv.

If the 'OutputType=CSV' is specified in the reportUrl, then the renameFile must be NewFileName.csv.

You can use the following values: 

  • - this will be replaced to the value of the current date time in the format of yyyy-MM-dd, it can be place at any position in the new file name.
  • - this will be replaced by the timestamp formatted as yyyy-MM-dd HH.mm.ss by default, it can be placed at any position in the new file name. 

Timestamp supports the following: 

  • [[timestamp:yyyyMMdd HHmmss]] will be formatted the new file name as 20170927 171345 
  • separators such as - (dash), . (period/full stop), / (back-slash) or / (forward-slash). For example will be formatted as 2017/09/27 171345.

If \ (forward-slash) is used then an extra \ is required to be placed in front as the escape character (this is part of the code syntax).

For example

to_email

This is the receiver of the email.

If there is multiple receivers then use a comma separate list.

"to_email": "username1@company.com, username2@company.com",

If both to_email are defined in the report and common field, it will send the report to the specified recipients plus the common recipients.

When this field is given, the Automated Report will be created and uploaded to the Document Library and it will include the download link in the email body. 

If an email address is invalid e.g. fake@serraview.com, the report will still be created and uploaded to the Document Library.

sftp_config

This is the SFTP details and it will override the SFTP details configuration in the Configuration > General > SFTP.

Parameter Description
hostname SFTP server host name.
port Port number on SFTP server to connect to.
username Account name for server authentication.
password

Encrypted password.

fingerprint

Each SFTP server has a unique identifying host key code to prevent it from forging another server's key.

Provided by the client.

UseSSHKey  T.B.A.
GPGEncryptFile

True / False

When true is set, it will use the following options to encrypt the report:

  • GPGPublicKey
  • GPGRecipient
  • GPGASCIIArmored
GPGPublicKey

String

Provided by the client.

GPGRecipient

String

Provided by the client and in an email format indicates who generated the public / private keys.

GPGASCIIArmored

True / False

Specify the output file in ASCII or binary format.

delimiter

The delimiter can either be: Tab, Pipe, or Tilde.

When you use the delimiter this will force the renaming of the report file's extension to .TXT. This will override the report's extension and the extension specified in the renameFile.

trimLastEmptyLine

True / False

This indicates if the last empty line will be trimmed or not. If set to True, then it will not append '\r\n' to the last line of data row. 

sftp_remote_path

Remote path to push reports to.

Serraview will use the SFTP details in the Configuration > General > SFTP, unless a specified SFTP server is configured as above.

local_path

This is a local path (local to Serraview's SFTP server) to which reports are pushed.

The local_path will contain the report files and then a Client's SFTP can pull them and this requires that the Client has requested SFTP access to Serraview's server. 

Serraview will append local_path to the instance's SFTP \out folder, creating any sub-folders as needed. Use "//" for dumping the file to the root \out folder and if the local_path contains any sub-folders then separate each with a double backslash e.g. "local_path": "my\\local\\path"

import

This is used for Automated Import Reports.

For this report to be imported then set this to "true" and any other values are ignored.

importComment

This is only used when the import parameter is used.

Any text in the importComment will be added to the Recent Imports' Comment field, refer to View Recent Imports.

disable

Use this to disable the automated report.

Set this to "true" and any other values are ignored.

emails_on_failure

Whenever there is email / SFTP Remote / SFTP local failure, this will send an email alert to the specified comma separated emails.

This can be configured to a specific report definition, so the configured emails will only receive when that individual report fails to run. Or it could be configured to the common field, then it will send to these emails for each failure report (in one email).