How to automate reporting tasks with DTM Query Reporter?

It is not a problem to publish some part of the database if you need to do that only once. Just copy result set to your favorite editor like Microsoft Word or Open Office Writer and save to file. Want to create HTML document? Just export if from your editor by once click.

DTM Query Reporter: output format options

But what should you do if the document, file or spreadsheet must be created every hour or at midnight? In this case you need a tool to automate the reporting process.

There are basic requirements for the tool:

  • Easy to initial setup of the reporting process: database connection, query and output file options.
  • Rich output format options: HTML, Excel spreadsheet and text editors compatible text are most useful formats. Plane text, XML document or PDF are also useful for some cases.
  • Complete command line support. The schedule system will run once configured reporting project from the first requirement.

DTM Query Reporter has been designed for described scenario. It offers GUI mode for project preparation and command line mode for scheduled execution. Of course the GUI mode support execution as well.

The user allowed to specify a few output formats at once. I.e. HTML and DOC files can be generated during one project execution.

DTM Query Reporter: query level variables

The important product feature is variables. The user can specify static SQL statement as well as a statement with variables (?1, ?2, etc) instead of parts of the expression. At the execution step DTM Query Reporter read actual values for variable from the disk file provided by user. This feature makes reporting process more flexible: actual SQL statement can be modified without project file changing. Let us to remind that SQL statement can be entered directly to the software or read from the .SQL file at execution step. Moreover, the user can specify file with SQL scenario as command line parameter.

Reporting Process Automation

There are only two mandatory steps to automate database query reporting process with DTM Query Reporter:

  • Create project file with connection, query and output definition.
  • Create scheduled task with commend line run of the software like: 
    at 12:00 "c:\tools\QueryReporter\qr.exe -c d:\SQL\ShippersReport.qrp"
DTM Query Reporter: file name macros usage

The important question is how to save a few output files during continuous report execution. The reporting tool allows users to include $DATE$ and $TIME$ macros into output file name. With these macros DTM Query Reporter will create new report file for each project execution.