A macro allows the User to execute multiple queries, each with its own layout, in a set of sequential steps. The results of a step or steps can be e-mailed to one or more recipients.

When a macro is defined, it can be given a general description by clicking the "Describe" icon in the Manage Macros control near the bottom of the panel. This description will be displayed as an i-nfo icon and provide a hovering description if the macro is used in a User Menu. If the macro is shared, this can be a useful method to provide a description of the macro's purpose.

A macro is made up of query execution steps. Each step must contain a query owner and name. If both the Query Owner and Query Name are left blank, the step will be ignored. Optionally, a Layout Owner and Layout Name can be applied. If a layout is defined, it will be applied to the results of the query. Any type of query can be used in a step. It is not limited to queries that produce reports. A macro can have up to 999 steps. Steps can be added and removed by using the "Insert" and "Remove" step controls found in the bottom of both the Execution Steps and E-mail Specifications sections.

Note: When a SELECT query is used in a macro, paging between page sets is not available. Instead, the entire set of output is displayed. The limits that determine how large a report can be displayed are the Max displayed rows value in the Report Body section of the Layout panel and the Maximum output file size setting applied when QLR Manager was installed. These values default to 500 rows and 5 MB. These size limits prevent cases where a query may produce reports of tens of thousands of report rows that the browser can't handle. If there is a desire to produce reports that exceed the limit, an alternative is to Create a Menu which contains the query as a menu item. Of course, executing the query from the query or wizard panels will work as well. These options will provide the ability to page between page sets in the report panel.

The Step Builder allows the User to easily find existing queries and layouts to be used in a step. Simply locate the desired entries, choose the step number to apply the values to, and use the  Apply  button to copy the query and layout selections into the appropriate step. If a specific layout does not exist for a query, or if a layout is not desired, the "Based on query" list option can be selected from the Layout Name selection list. When chosen, the Layout Owner and Name will be left blank.

It is also acceptable to just type the Query and Layout Owner/Name values into the desired step. The Step Builder is merely offered to make the process of building a macro easier.

The Title text is the text that will be displayed for the macro step when executed. This also serves as the label that can be clicked to open the expandable/collapsible section containing the macro output. If the title text is left blank for a step, a default title is created using the Query Owner.Name and, if a layout was specified and it is being previewed, the Layout Owner.Name. The title text can also include dynamically referenced information. The following are examples of how the title is displayed for different uses.

E-mail information is entered into the E-mail Specifications section. If an e-mail address is present for a step, the output of that step will be sent to the recipients listed. In addition, content produced in prior macro steps where the recipients = "add" (all lower case, without the quotes), will be added to the current step and sent as a single e-mail. This allows for the creation of a single e-mail with multiple reports. Once an e-mail is sent, the content to be sent is reset. Multiple recipients can be specified by separating their addresses with a comma.

For example, if the recipients for steps 1 and 2 are entered as "add", and step 3 has real e-mail addresses, the results of all 3 steps will be sent to the recipient(s) in step 3 in a single e-mail. If step 4 has an e-mail address, the results of step 4 only will be sent to the recipients specified in step 4.

E-mailing only takes place when using the  Run Macro  button. When the  Preview  button is pressed, no e-mail is sent. The preview only displays the results of the macro in the Preview panel. If a particular step of a macro does not have an e-mail recipient, the results for that step are only displayed if Preview, Run Macro, or a Menu item is executed.

The e-mail "Subject" can contain dynamically referenced information, such as report column data or query variables.

A single E-mail from address can be specified for all steps in a macro by entering the "E-mail from" information into the General Specifications section of the Macro panel. A Confirmation e-mail can also be specified resulting in an e-mail sent to this address containing information about the macro that was executed. This is especially useful to track when other users are executing the macro as a Menu item.

The manner in which charts are sent in an e-mail is determined by the E-mail charts as setting in the General Specifications section. Depending on the radio button chosen, charts can be sent as attachments or as in-line images which are really HTML <img> tags that reference files that are stored on the hosting server.

In addition to e-mailing, output can be directed to a file. This is accomplished by specifying file in the Recipient(s) textarea within the E-mail Specifications section of the Macro panel. For example:

The format is the keyword of file, followed by a space, then the path and the file name. Please note that the file extension is not included. It is automatically appended to the file name based on the selected Output type. If an output type of Report or Distributed email is selected, an HTML file will be produced.

The use of the /reports or /graphs directories to store the files should be avoided. These directories are purged on an on-going basis. Both relative or absolute path references can be used.

It is possible to prevent the e-mailing of empty reports. This is accomplished by starting the recipient list with ">0" (greater than zero), such as >0 fred@yahoo.net. This indicates to QLR Manager to only send a report when at least one row of data is found. This is designed to work with queries intended to produce a single formatted report. If prior steps utilized the "add" function in the recipient field, and the step to do the e-mailing uses the ">0" option with no rows found in that step, none of the reports are e-mailed.

The File name field provides a means to specify a file name for the report attached to the e-mail. A value entered into this field will override QLR Manager's default naming convention of report_1, report_2, report_3, etc.

There are two ways to execute a macro from the Define Macro panel. The first is to "Preview" it. This is accomplished by clicking on the  Preview  button or Header tab at the top of the screen. When previewing, all actions are the same as Running the macro except that no e-mails are sent. This allows the User to see what the macro will produce prior to actually sending e-mails. Each step of the macro will be displayed in an expandable/collapsible section. Using the  Preview  button is a good way to confirm that a macro is executing as desired, prior to actually running it.

The second way to execute a macro is to click on the  Run Macro  button. When clicked, a confirmation dialogue box will first appear asking the User to confirm they want to run the macro. E-mails will be sent if recipients are defined in the macro. The content will also appear in the Preview panel.

The Preview tools icon will appear in the header adjacent to the Preview tab. This allows the User to print, create an HTML file, or e-mail the content of the Preview panel.

It is possible that the queries used in execution steps contain query variables. If this is the case, values for these variables can be provided at the macro level and the query level. In both cases, the format for providing values is the same, which is the query variable name = value. Multiple variables are separated by a comma. It is not necessary to prefix the variable names with the ** or enclosing brackets [ ] as they appear in the queries themselves, nor is it necessary to include the @length size limit that may be found at the end of the variable name. For example, if queries in the macro have the variables of {month_number} and {customer_id@7} used with MS SQL Server, [month_number] and [customer_id@7] for other supported databases, or **month_number and **customer_id@7 if using the older method, they would be entered as:

month_number=7, customer_id='FRED987'

Note: Variable or input control names may also be prefixed with the User ID that owns the control:

joe01.month_number=7, joe01.customer_id='FRED987'

If these values are entered into the Macro variable values field in the General Specifications section, the provided values will be applied to all occurrences of these variables in all steps.

When entering query variables that have multiple values, the vertical bar is used to separate each value:

states='NY'|'CO'|'NJ' or month_number=3|6|9

Notice how quotes are used for string values, but not for numeric values.

Creating entries in the Query variable values found in the Execution Steps section will only apply the given value to that step. This allows the User to override the macro level values for a given query or step. Using the above example, if month_number=7 was input as a macro variable, it could be overridden in a specific execution step by entering month_number=6 in that step.

If there are still variables that have no value assigned, either by the macro or the query level variable assignments, the User will be presented with the variable input panel showing the remaining variables that require a value. If the Confirm query variables option found in the General Specifications section is checked (which is the default value), then even if all variables have been assigned values, the User will still be presented with the variable input panel. Macro level variables can be changed at that point. However, if a query level variable assignment is present, the query level values will be used for that particular execution step.

The Custom Macro Layout allows the User to position the reports generated by each step of the macro. The report output is referenced by <reportx>, where x is the step number as defined in the Macro Execution Steps. To reference the output in step 1, <report1> is embedded in the macro custom text area.

The following is an example of using the Custom Macro Layout text area to create customized output. The intent is to display three reports side by side of statistics related to youth basketball players. By using query variables, the User to chooses the grade level (4th grade, 5th grade, etc.) and to set the number of rows that will be displayed in the reports. All 3 queries have the same two query variables, shown in bold text below:

select ord,concat(first_name,' ', last_name), games, ppg
from player_stat_summary
where grade = '[grade]'
order by 4 desc
limit [limit]

In the Custom Macro Layout text area, the report designer has defined that the output should be arranged in an HTML table, with all three reports appearing side by side:

<table border="0" cellpadding="0" cellspacing="0"><tr>
<td><report1></td><td width="10">&nbsp;</td>
<td><report2></td><td width="10">&nbsp;</td>
<td><report3></td>
</tr></table>

The only special formatting is the use of the tagging width="10" in an extra table cell between the reports to produce a 10 pixel space between the reports. Similar results can be achieved by changing the cellpadding or cellspacing values. Assuming that the User chooses a limit value of 3, the macro produces output of:

In order to provide the User the ability to see more than just the top three players in each report, a link to a QLR Manager Widget will be added to the bottom of each report. The Widget links are created using the Report Tools "Create a query widget" option. After executing each query, access the Report Tools link in the QLR header and choose the "Create a query widget" option. Simply copy and paste the created links into the Custom Macro Layout text area into the appropriate positions to be launched by the href tags. The widget links appear in red text in the example below.

In addition, values for the two query values that are present in the underlying queries have been integrated into the links by manually editing the Widget text. The [limit] variable has been manually set to show the top 100 players by including &r000limit=100. Notice how when query variables are manually added to a widget link, they are prefixed with "r000" (3 zeros) to indicate that these are values that QLR Manager should recognize as query variables. The grade that is to be used when the widget link is executed has been set to use the same grade that was selected by the User when the Macro was executed. This was accomplished by using a query variable to reference the value: &r000grade=[grade]. Query variables that are referenced in the custom text will be replaced with the values provided by the User when the Macro is executed.

When the widget link is produced, the background color of the resulting page is set to the default background color of QLR Manager. This setting is part of the link in the format of &QLRbgc=(color). This can be manually edited to display page backgrounds in a different color. In this case, the background color has been set to &QLRbgc=%23F4F7E7. "%23" is the url encoded value for a "#" character, which preceeds hex color values used in HTML. The "more" links are configured to open the QLR widget in a new window. The default arguments for the "window.open" function 'top=100,left=100,width=600,height=400,resizable=1,scrollbars=1' can be modified to size and position the window as desired.

The modified Custom Macro Layout text with the above referenced features added:

<table border="0" cellpadding="0" cellspacing="0"><tr>
<td align="center" colspan="5"><p><b>Top performers for the [grade]</b></p></td>
</tr><tr>
<td><report1></td><td width="10"> </td>
<td><report2></td><td width="10"> </td>
<td><report3></td><td width="10"> </td>
</tr><tr>
<td align="right"><div style="width:50px;background-color:#6F6F6F;border:1px solid #000000;text-align:center">
<a href="Javascript:void(0)" title="Top 10 PPG" target="qlrwidget" onclick="window.open('http://www.mysite.com/qlr/reportwin.html?Action=qlr_new&isW=yes&formname=query&qlr_linkid=(encoded link removed from widget)&qlr_hdr=Top%20[limit]%20PPG&QLRbgc=%23F4F7E7&r000limit=100&r000grade=[grade]','qlrwidget','top=100,left=100,width=600,height=400,resizable=1,scrollbars=1');return false" style="font:bold 11px Verdana,Arial,Helvetica,sans-serif;color:#FFFFFF;text-decoration:none">more</a></div></td>
<td></td>
<td align="right"><div style="width:50px;background-color:#6F6F6F;border:1px solid #000000;text-align:center">
<a href="Javascript:void(0)" title="Top 10 FG%" target="qlrwidget" onclick="window.open('http://www.mysite.com/qlr/reportwin.html?Action=qlr_new&isW=yes&formname=query&qlr_linkid=(encoded link removed from widget)&qlr_hdr=Top%20[limit]%20FG%25&QLRbgc=%23F4F7E7&r000limit=100&r000grade=[grade]','qlrwidget','top=100,left=100,width=600,height=400,resizable=1,scrollbars=1');return false" style="font:bold 11px Verdana,Arial,Helvetica,sans-serif;color:#FFFFFF;text-decoration:none">more</a></div></td>
<td></td>
<td align="right"><div style="width:50px;background-color:#6F6F6F;border:1px solid #000000;text-align:center">
<a href="Javascript:void(0)" title="Top 10 3P%" target="qlrwidget" onclick="window.open('http://www.mysite.com/qlr/reportwin.html?Action=qlr_new&isW=yes&formname=query&qlr_linkid=(encoded link removed from widget)&qlr_hdr=Top%20[limit]%203P%25&QLRbgc=%23F4F7E7&r000limit=100&r000grade=[grade]','qlrwidget','top=100,left=100,width=600,height=400,resizable=1,scrollbars=1');return false" style="font:bold 11px Verdana,Arial,Helvetica,sans-serif;color:#FFFFFF;text-decoration:none">more</a></div></td>
</tr></table>

The modified Custom Layout will display as follows (more links intentionally disabled):

Macros can be executed from outside of QLR Manager. An example of such access could be a desire to run a batch of daily reports from a cron job. This capability must be enabled by checking Allow external access in the General Specification section. Access can also be limited to a specific IP address, or IP addresses starting with a particular value, by entering a value into the IP access mask field. For example, to limit external access to IP addresses starting with 68.122, this value could be entered into the IP access mask field.

In some instances, a cron job may need to reference the directory where QLR Manager is located. In this case, a small script can be written and executed from the cron job that changes the directory and executes runmacro:

cd /var/www/html/reporting
/usr/bin/php -q ./runmacro.php rob daily_ticket_counts

The runmacro.php execution file is found in the main QLR Manager directory.

There are two variables that must be submitted, macroowner and macroname, and must be lowercase. These identify the Macro Owner and the Macro Name. If a name contains a space, the URL escape code of "%20" must be used to represent the space. If a macro contains query variables, these values can be supplied as additional Get variables. For example, if in the macro example above there was a query variable called "month_number", it could be added to the end of the URL string as: &month_number=7. Notice how the ** that is used to identify a query variable in the query is not included. Variables that are entered in the URL will override macro level variables that are defined in the macro. They do not override the variable values that are defined as Query variable values for each execution step.

When runmacro.php completes successfully, it prints a response that says "runmacro.php successfully completed". This can be controlled by adding an additional HTTP GET argument of &response=your_text. The response text will be returned, replacing underbars with blank spaces. For example, &response=Invoice_report_created would print "Invoice report created" to the screen. Messages can also be suppressed from being sent by using &response=silent.

These HTTP Get variables can also be submitted as HTTP Post variables from an HTML form. The form action would reference mydomain/mydir/runmacro.php and the values would be submitted with the form.

When the macro is run externally, the results of the steps are e-mailed to the recipients as specified in the macro. The output for execution steps without e-mail recipients defined will be ignored. Therefore, if a macro does not contain any e-mail recipients, no output will be seen. It could be that some steps in the macro are action queries, such as INSERTs or UPDATEs. These commands will be executed, but without any tracing of what actions occurred. This differs from running a macro from within QLR Manager, where this type of output would be displayed in the Preview panel.

For security reasons, the User is not asked to expose their User ID and password when executing runmacro.php. Instead, runmacro.php is executed with the same User ID, password, database/schema and server as when the macro was saved in QLR Manager. The password is saved as encoded data to prevent exposing the password here as well.

Note: If a user changes their database password, they must retrieve and re-save just one of their macros. This will update the database password associated with all their macros.