Using placeholders for dynamic filenames and messages

Using placeholders for dynamic filenames and messages

You can use placeholders in AutoSQL to make your email messages or filenames use variables like date, time, result rows, etc. A common example is to use the {date} and {time} place holder in the output filename. Instead of overwriting the same file every day, this will create a new one each time your run it. This way you can also save the history of queries.

Fields which can have placeholders

  • Name of the output file
  • Subject of the email
  • Message text of the email

By right clicking in a field you see the available placeholders:

Clicking one of the options will insert it in the text.

Different date and time format in filenames

The output path is limited to {date}, {date-1} and {time}. Actually also the format is different. Normally the dates and times are outputted in the regional settings format of the OS. However, for the output filename the date is always outputted as YYYYMMDD and time as HHMMSS.

Possible place holder details

Most placeholders are straight forward, but below you’ll find a detailed explanation.

{date} and {date-1}

The {date} placeholder will always put in the current date. However, if  you run the report with data of yesterday, you can also subtract a day with the -1 addition: {date-1} will always print the date of yesterday. Actually you can also get tomorrow with {date+1} and replace the 1 with any number. Eg to print the date of 7 days ago you can use {date-7}.

{time}

Outputs the time the query was ran.

{rows}

Outputs the number of rows in the result set of the query.

{output path}

Inserts the complete path to the output file. Especially when a placeholder was also used in the filename this can be useful to provide a link. Eg when you output the query to a network drive a user can copy the path to access it directly and you don’t need to attach it.

{action}

Outputs the name of the action.

{query}

Output the SQL query that was executed.

An example of using macros