Overview

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.

Macro Execution Steps

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.

Starting with QLR Manager version 9, queries and layouts associated with different database engines can be used in a single Macro. This is accomplished by selecting the associated database engine in the Query Source field. If the Query Source is left blank, QLR Manager will assume that the query is associated to the current database engine. Please see the Step Builder Assistant to see how to find queries from other database engines that are available for use. To make a queries available they must be retrieved and re-saved in QLR Version 9 or later. This is necessary for QLR Manager to save the appropriate connection credentials along with the object. Layouts must be saved as Shared in order to be available for use.

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.

Step Builder Assistant

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.

As of QLR Manager version 9, queries and layouts from multiple database engines can be referenced in a single Macro. For example, if QLR Manager is being used in an environment that is comprised of both Oracle and MySQL queries, a Macro can be created in the MySQL environment that has reports from both the MySQL and the Oracle databases.

Note: In order for queries to be used from other database engines, they must be retrieved and re-saved with QLR Version 9 or later. This is necessary for QLR Manager to save the appropriate connection credentials associated to the query at the time it is saved, with the password being encrypted. along with the object.

The connection information associated to the query will be used when the database engine of the query step differs from the database engine of the Macro itself. When the database engine of the query step is the same as the Macro, the connection credentials of the Macro are used to execute the query step.

If queries are saved as Shared in a database engine other than the Macro's database engine, the top of the Step Builder will display a select list control which will allow the User to see the available queries from different database engines. If no such queries exist, then this control will not be displayed. Users using QLR Manager in a single database environment will never see this control.

Step Title text

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.

Default title applied in preview mode:
Step #1 -  Query: frank.overstock  Layout: frank.overstock
Title displayed in a User Menu:
Query: frank.overstock
Title text applied to the step:
Excess Inventory
Output destinations

The default output for a Macro Step is to display the report content in the browser window.

E-mail addresses and distribution lists are selected from the available E-mail To and Bcc lists. When at least one To or Bcc recipient is selected, the output of that step will be e-mailed to the selected recipients. However, there are some Output options which may override the sending of e-mails. New e-mail addresses and distribution lists can be created, edited and deleted by clicking on the Manage E-mail & distribution lists icon found adjacent to each of these select lists. This will launch a pop-up window to manage the lists. The available e-mail addresses and distribution lists are specific to each ID used to log onto QLR Manager.

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 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 selected, 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.

Output options:

In addition to e-mailing content, the Output options selections allow other output destinations to be specified.

Add report output to next step provides the capability to combine reports from several Macro Steps to be used in a later Macro Step. For example, 3 PDF files could be created in steps 1-3 and all e-mailed as file attachments in Macro Step #4. When this option is selected, no e-mailing takes place for that step, not even when To or Bcc recipients are selected.
Save report output to a file provides the means to create a file and save it on the server. When this option is selected, a path + file name should be provided in the File name field, such as ../myoutput/alarmdata. A file extension, such as .pdf, will automatically be applied depending upon the Output type that has been specified. When this option is selected, no e-mailing takes place for that step, even if To or Bcc recipients are selected.
E-mail only when report has data will prevent e-mail from being sent when the underlying query that is generating the report returns no rows of data.

File name:

The File name field provides a means to specify a file name for the report attached to the e-mail or one that is saved to the server. A value entered into this field will override QLR Manager's default naming convention.

File names can be dynamically created using QLR Manager's replacement variables. It can include query variables, data column references and PHP functions. For example, a file name of file ./reports/[my query variable]-&c1-eval(date('Y-m-d-H-i-s',time())) would create a file in the reports directory based on a query variable value, the value of the data found in column 1 of the report, and a time-stamp built using the PHP date() function.

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

Output types:

Various types of output can be specified for e-mails by setting the Output type. Each macro step can have a different output type specified. The Output type is found in the E-mail Specifications section.

 •  Report:   This is the standard report embedded into the body of an e-mail. This output type along with Distributed email are the only options that will send charts. If charts are present, they are handled in accordance with the "E-mail charts as" selection in the General Specifications section.
 •  CSV file:   A Comma Separated Value file is created and sent as an attachment.
 •  CSV +headings:   A Comma Separated Value file, containing the column headings from the report, is sent as a file attachment.
 •  Excel file:   A Microsoft Excel file is sent as an attachment.
 •  Excel +headings:   A Microsoft Excel file, containing the column heading from the report, is sent as an attachment.
 •  HTML file:   An HTML file of the report sent as an attachment.
 •  HTML for Excel:   An HTML file of the report, modified to be loaded into Microsoft Excel, is sent as an attachment. When loaded into Excel, the color and border attributes are maintained.
 •  Formatted Excel:   An XLS file of the report, modified to be loaded directly into Microsoft Excel, is sent as an attachment. When loaded into Excel, the color and border attributes are preserved. Note that this file format only works in MS Excel 2002 or newer.
 •  PDF file:   A PDF file can be created using this option. The default values used for creating a PDF file are margins of 10 millimeters, orientation of portrait, and a paper size of "Letter". See the Optional settings for information about setting various output options.
 •  MS Word Doc:   A DOC file of the report, modified to be loaded into Microsoft Word, is sent as an attachment. When loaded into Word, the color and border attributes are preserved.
 •  Distributed email:   This will send e-mails to recipients based on the email addresses specified in the layout's "Format or Use" field in the Report Columns section. In order for e-mails to be sent, at least one field has to be identified as an "Email to" address. See distribution e-mail for information about creating distribution e-mails.
 •  Save to Table:  

This allows for report data from a SELECT query to be saved to a table. When this output option is chosen, the Optional settings field is used to specify the necessary parameters for saving the query data to a table. Each of these parameters is separated by a comma. They are:

table name, database engine, userid, password, destination database, host system [, add or file]

The table name needs to be a valid SQL table name. The database engine can be values of Mysql, Oracle, PostgreSQL or SQL Server. QLR Manager will look at the first character of this parameter to determine the database engine. So simply entering M, O, P or S (case insensitive) can be used. The Userid and Password is a User ID and Password that is recognized by the database engine. The Destination Database is used to specify the specific database where the table is to be created. The Host System specifies the server where the database engine resides. An optional seventh parameter can also be used. When the data is saved to a Table, the default behavior is to replace the Table if it already exists (QLR Manager does this by first executing an SQL DROP TABLE command). If the word add (or simply the letter a) is added as seventh parameter, then the data will be added to the Table if it already exists. An example of specifying these parameters is:

invoice_summary, mysql, fred, J1mmyj0hn, purchasing, localhost, add

The above example will connect to a MySQL database called purchasing on the localhost server using the User ID of fred and the password of J1mmyj0hn. If the Table called invoice_summary already exsits, and it has the same field characteristics as the data being saved, the data will be added to the Table. If it not found, a new Table called invoice_summary will be created.

invoice_summary, mysql, *, *, purchasing, localhost, file

The above example will connect to the database using the current connection User ID and password, signified by the '*'. The output type of file has been specified. This will create a file of the SQL "create table" and "insert" commands associated to the data. The file name is determined by what is entered into the "file name" field. QLR Manager will add a ".sql" file extension. If no file name is present, the table name is used as the file name. The resulting file will be e-mailed to the recipients that are defined for this step.

When the Macro is executed in Preview mode and this option is used, you will see the resulting report data, followed by a summary table that lists how QLR Manager interpreted each of the provided parameters.

More information about the Save Table option can be found in the Report Tools help section called Saving data to a Table.

 •  Save chart file:  

This option allows the User to save a chart image using the any desired directory path and file name. QLR Manager creates ".png" images, and this file extension should not be changed.

Saving an image is accomplished by providing a file name in the E-mail specifications section of the  Define Macro  panel and selecting the "Save chart file" Output type. If no File name is provided, the chart image will be saved in the QLR Manager /graphs directory, with a file name of chartx.png, where "x" is a sequential number. Some examples of file names:

../mycharts/sales.png:    This would save a chart called sales.png one level above the QLR Manager directory structure in the directory: /mycharts.
/qlrcharts/inventory_levels.png:   

This would save a chart called inventory_level.png in a /qlrcharts directory inside the QLR Manager directory structure.

QLR Manager has the ability to create more than one chart associated with a query. This can be triggered by selecting the "Compare" option as the  Layout  panel's Report Charting Chart engine, or by embedding a FusionWidget into the  Layout  panel's Custom Layout textarea. When creating multiple charts in one Macro step, multiple chart names separated by commas should be entered into the File name field. The order that chart names are applied is the JpGraph chart is referenced first, a FusionWidget is referenced second, and a FusionChart is referenced third. If only two charts are created, only two file names are needed, paying attention to the order listed above.

When executing a  Preview  of the Macro and the Show macro statistics checkbox is checked, the macro statistics section will show the "directory/file names" that will be used when creating the files. If the Macro is actually executed from the  Menu  or QLR Widget, the chart files will be created.

Optional settings:

Optional settings are primarily used to control the output created in a Macro step. They are name = value pairs separated by a comma. If a comma is required within the value of an option, two commas must be entered as the text.

A common option is to specify a "note" when emailing output. This allows the User to specify the text for the body of the email to be included in those Macro steps that are emailed. If not specified, the text of "Please see attached: [file name]" is used as the email note text. In the following example, a comma is specified by entering two commas:

note = Dear Fred,,<br /><br />Please see the attached file of the monthly sales.

There are many options that can be set for PDF files. Below is an example of all the available options and sample values:
note=Montly sales report attached, left=20, right=10, top=15, bottom=15, header=&DT21||QLR Manager||&DT7, footer=||||{PAGENO} / {nb}, watermark=confidential, orient=potrait, format=A4, copy=no, edit=no, print=no, background=background:radial-gradient(center,,ellipse cover,,#1B3B4E 0%,,#A6D8F7 100%)
 •   The left, right, top, and bottom values are the page margins applied in millimeters.
 •   Headers and footers can be split into left||center||right segments. Omitting the vertical pipes will center the value.
 •   The header=&DT21 left justifies a QLR Date format || centers the text "QLR Manager" || right justifies a QLR Time format.
 •   The footer=||||{PAGENO} / {nb} will produce a format of page number / total pages in the right segment of the footer. QLR Manager uses mPDF as its PDF creation engine and this footer page numbering example is calling an mPDF function. More examples and mPDF documentation are available at the mPDF web site.
 •   The watermark value places grey, semi-transparent characters diagonally across each page. If a url value is entered such as "www.mpdf1.com", the rectangular area holding the watermark becomes a link.
 •   The two values for orient are portrait or landscape. Portrait orientation is the default.
 •   The format is the paper size with the default being "Letter". Following are the available paper sizes:
A0, A1, A2, A3, A4, A5, A6, A7, A8, A9, A10, B0, B1, B2, B3, B4, B5, B6, B7, B8, B9, B10, C0, C1, C2, C3, C4, C5, C6, C7, C8, C9, C10, 4A0, 2A0, RA0, RA1, RA2, RA3, RA4, SRA0, SRA1, SRA2, SRA3, SRA4, Demy, Executive, Folio, Letter, Legal, Ledger, Royal, Tabloid, A, B.
 •   The copy, edit and print values of "no" set the PDF security to prohibit these actions.
 •   The background css can be used to create some impressive effects in the PDF. Below are a few examples:
background-color:#EEEEEE
    
background:radial-gradient(center,ellipse cover,#1B3B4E 0%,#A6D8F7 100%)
    
background:#EAC58A url('../misc/wood.png')
Color background Gradient background Image background
 
Previewing and running

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.

Macro and Query variables

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.

Custom Macro Layout

The Custom Macro Layout allows the User to position the reports generated by each step of the macro. The TinyMCE WYSIWYG HTML Editor has been incorporated into the pop-up window launched by the TinyMCE editor icon. In addition to aiding with the creation of HTML, this Editor also provides a means of using templates and an image browser. A few QLR examples are provided with accompanying explanations. The  Source  button near the bottom of the pop-up window can be used to exit the Editor and enter Text and HTML as text without the aid of the WYSIWYG Editor.

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:

Top 3 Points Per Game
  Player Gms Points
1 Jordan Ball 3 25.3
2 Sammy Lockhart 3 19.3
3 Matthew Sneed 3 18.3
 
Top 3 Field Goal %
  Player Gms Percent
1 Morton Randall 3 57.1%
2 Matthew Sneed 3 56.7%
3 Lex Hastings 3 53.6%
 
Top 3 Three Point %
  Player Gms Percent
1 Morton Randall 3 46.7%
2 Jordan Ball 3 45.5%
3 Sean Peterson 3 43.8%

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):

Top performers for the 4th Grade

Top 3 Points Per Game
  Player Gms Points
1 Jordan Ball 3 25.3
2 Sammy Lockhart 3 19.3
3 Matthew Sneed 3 18.3
 
Top 3 Field Goal %
  Player Gms Percent
1 Morton Randall 3 57.1%
2 Matthew Sneed 3 56.7%
3 Lex Hastings 3 53.6%
 
Top 3 Three Point %
  Player Gms Percent
1 Morton Randall 3 46.7%
2 Jordan Ball 3 45.5%
3 Sean Peterson 3 43.8%

The Custom Macro Layout textarea can be used to create a PDF file that contains the content from multiple Macro steps. The following example would be used to combine the reports generated in 3 Macro steps into a single PDF document. Notice the use of the special MPDF tags (QLR Manager uses a program called MPDF to create PDF files) that instruct MPDF to force a page break:

<p>This is leading text to appear at the beginning of the document</p>
<br />
<report1><!--mpdf <pagebreak /> mpdf-->
<report2><!--mpdf <pagebreak /> mpdf-->
<report3>

The Output type for steps 1 and 2 should be set as the default value of "Inline report". The Output options for these steps should be left blank. "PDF file" should be selected as the Output type for step 3. If it is desired to save the PDF as a file, the Output option of "Save report to a file" can be selected.

If the output for the last step is sent in an e-mail, the e-mail body will be blank and there will be an attached PDF file with the content from all reports defined in the Custom Macro Layout. To include a note in the body of the e-mail, text can be entered into the Optional settings textarea, such as:

note = the text to be included in the email

Note: When using the Custom Macro Layout textarea to create a single PDF file, PDF creation does not work properly if the embedded report output is contained in an HTML table cell. Avoid custom text that looks like:

<td><report1></td>

External access

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.

External access is accomplished by entering the following URL pattern, that contains HTTP Get variables:
For URL execution:
Pattern:  mydomain/mydir/runmacro.php?macroowner=(the macro owner)&macroname=(the macro name)
Example:  www.baitshop.com/qlr/runmacro.php?macroowner=frank&macroname=reorder
For cron jobs that do not support URL execution, the values can be provided as command line arguments separated by spaces. As many query variable name/value pairs can be added as needed. If arguments contain spaces, the url encoding value of %20 should be used for those spaces.
Pattern:  runmacro.php [macroowner] [macroname] [query variable name] [query variable value]
Example:  runmacro.php frank reorder month 12 year 2005

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.

Execution log files can also be created for external macro execution. These can be helpful in debugging problems. These are created by adding a name/value pair of qlrlog=yes or qlrlog yes, depending upon the above method you are using to executed the macro. When this option is present, a log file is created in the /reports directory. The file will be named as [unix timestamp].log, such as 1439905607.log. It will contain the start time of the macro, it's name, and the time to execute each step.

Begin Macro vendor.excel test at 2015-08-18 01:46:47
Begin step 1. Query: vendor.excel test. Elapsed time:0.181 seconds.
Begin step 2. Query: vendor.report2. Elapsed time:2.393 seconds.
End Macro vendor.excel test. Elapsed time:4.205 seconds.

This can be very useful in identifying slow running queries, or macros that do not actually finish all execution steps. The elapsed time that is listed is the amount of time passed since the last time check. Thus, step 1's processing time would be 2.393 seconds - 0.181 seconds. Step 2's processing time would be 4.205 seconds - 2.393 seconds.