Configure Automated Reports

TblUserReports

Crystal report changes
Ensure that the crystal report file that you are wanting to automate has a parameter defined named UserID as type Number.  This parameter has to exist, but does not have to filter the data through the select expert (unless required).

Database table changes
Ensure that the crystal report exists in the tables:  tblMasterReports and tblUserReports.

Who will receive the report?
  • fkidUser contains the id of the user to receive the report.  If the value is not zero, it will sent the report to the user.  If the value is zero, it will then look at the value in the column fkSecurityRole.
  • fkSecurityRole contains the id of the security role to receive the report.  If the value is not zero, it will sent the report to all users allocated to the specified security role.  If the value is zero, it will then look at the value in the column sgOtherEmails.
  • sgOtherEmails contains the email address of the person to whom the report will be CC'ed to, irrespective of the values in the fkidUser or fkSecurityRole fields.
  • mReportInstructionsOverride contains the text to be used for the body of the e-mail message.
  • NOTE:  At least one of the values for fkidUser or fkSecurityRole must be specified or the report will not be sent.
Which report will be sent?
  • fkidMasterReport contains the report number to be sent as it has been defined in the table tblMasterReports
  • sgParameters contains the values to be used for the report parameters as it has been defined within the Crystal Report. In case you want to have multiple values for your parameters you can use '|' to separate them.
          Example:  fkidContract=12345&fkidTemplateItem=340&fkidSupplier=30210&ContractAdmin=6|16|19|20|29|41|51|58|63|64&SummaryGroupSelection=4
When will the report be sent?
  • dtNextCheckTime contains the date the report will next be sent.  This field is automatically updated by ClickHome based on the frequency defined in sgAutoSendRules.
  • sgAutoSendRules contains the frequency to send the report.
    • Units of time are:
      • w – weekly
        • The day of the week and time to send the report need to be specified when using weekly.  
        • Days are from 0-6, with Sunday being 0 and Saturday being 6.  
        • The time only needs to have the hour specified, and can be 0-23.
        • Example: w-5-16 would be weekly on Friday at 4pm.
      • d – daily
        • The time of day to send the report must be set.
        • The time only needs to have the hour specified, and can be 0-23.
        • Example: d-15 would send a report Daily at 3pm.
      • m - monthly
        • The day of the month and the time to send the report need to be specified when using.  Special days can also be specified.
        • Basic days can be from 1-31.  If the number of days exceeds the number in that month, then the last day of the month will be used.
        • More advanced days can be specified.  These include first working day, last working day, last day, first working day of type, last working day of type.
        • Examples:
          • m-10-16 sends a report on the 10th of each month at 4pm.
          • m-1-16 sends a report on the 1st day of each month at 4pm.
          • m-lw-16 sends a report on the last working day of each month at 4pm.
          • m-fw-16 sends a report on the first working day of each month at 4pm.
          • m-ld-16 sends a report on the last day of each month at 4pm.
          • m-l0-16 sends a report on the last Sunday of each month at 4pm.
          • m-l4-16 sends a report on the last Thursday of each month at 4pm.
          • m-f0-16 sends a report on the first Sunday of each month at 4pm.
          • m-f3-16 sends a report on the first Wednesday of each month

Multiple entries for the same report can be set up.  So, if you want to set up a report to run weekly on Thursday and Friday at 5pm then you'd create two almost identical rows.  The only difference would be the sgAutoSendRules columns, one would have w-4-17, while the other would have w-5-17.

Testing the report

  • In the table tblUserReport, set the column dtNextCheckTime to have a date in the past (i.e. yesterday if you are sending a daily report).  Example:  2014-12-08 09:00:00.000
  • Restart the ClickHome service so that it can force the sending of the report.
  • The report should now be received in the user's mailbox.
  • Also refresh the data in the tblUserReport table and ensure that the dtNextCheckTime has been set to the next day / date it should be generated.