Background
Database Reporting Tool (DRT) is a system of files and processes used to schedule SQLs for production-use. These SQLs can be used to create extracts and reports internally or for external parties (like clinics or individuals).
Required Files
DRT follows a "main" reporting system script, PowerShell that is called on a schedule. Main has 2 duties, call subscripts (consume return status) and provide a report to sys-admin
Subscripts
Each script will have two parts, the actual SQL file, and a config.xml which does the collection of emails, parameters and passwords - this is what a developer would create from a template containing core aspects of reporting such as:
- creating connecting to SQL
- writing to excel using template
- sending emails
Having these in a template would allow for a simple query to email, being able to drop in a file pair with minimum to no development beyond the SQL itself
Testing DRT
Developers can modify the Config.xml document, change the recipient to your own email address, and schedule a run time in task scheduler. Check if the extract was received in your email inbox.
Log folder in the sub folder for each extract can be used to debug if the behavior of the DRT was not expected.
Access DRT
- Log on to the server SPCTXPCCIS001
- Navigate to \\vch.ca\departments\PCCIS SA Share\SA\Automation\Profile EMR\Live\DRT Live
Add a Report
If the schedule shares with other reports (ex. New report A wants to run daily at 7 AM, existing report B is already setup to run daily at 7 AM), you can copy over the new report folder to the folder with the schedule name that has already been created
If the report has a new schedule, create a folder in \\vch.ca\departments\PCCIS SA Share\SA\Automation\Profile EMR\Live\DRT Live, name it "DB_EMR_DBNAME_FREQUENCY_TIME", replace DBNAME, FREQUENCY, and time, ex. DB_EMR_REPORTING_Daily_645AM
- Open task scheduler on SPCTXPCCIS001
- Click "Create New Task…" on the right panel
- Name it "PCCIS EMR DRT DBNAME_FREQUENCY_TIME", replace DBNAME, FREQUENCY, and time
- Tick "Run whether user is logged on or not" and "Run with highest privileges"
- Click Change User or Group…
- Under Enter the object name to select field, type "vch\svcemrpcc" and click check Names, the name in the field should be automatically changed to have the email address appended
- Click OK, you'll be asked for the password, which can be found in the emr password safe
- Click on the Triggers tab -> New…
- Configure the schedule and tick Enabled, click OK
- Go to Actions tab, click New…
- Select "Start a program" in Action
- Under Program/script, put "Powershell.exe"
- In Add arguments (optional), enter '-ExecutionPolicy Bypass -File " \\vch.ca\departments\PCCIS SA Share\SA\Automation\Profile EMR\Live\DRT Live\reporting.ps1" FOLDERNAME', replace FOLDERNAME with the folder you created in step iii
- Click OK
- In Settings tab, tick Allow task to be run on demand, set stop the task if it runs longer than to 4 hours, and tick if the running task does not end when requested, force it to stop
- 16) Click OK, you'll be asked for the password, which can be found in the emr password safe
Delete or Update a Report
- Find the folder corresponds to the folder name in sysconfig, make sure the path matches folder location in sysconfig too
- Copy and paste the folder under the workitem's ROLLBACK or BACKUP folder (create one if not present) with the filename "EMRxxxx_FOLDERNAME_DATE" ('xxxx' corresponds to the workitem number, and replace 'DATE' with YYYYMMDD)
- Delete the folder from server if specified in sysconfig