When viewing a report or other type of output, a Report tools, Output tools, or Preview tools icon will appear in the header adjacent to the Report or Preview tab. Clicking on this icon allows the User to Find information in the current page set, or create various output formats, such as downloads of formatted output, data files, XML output, and e-mails. The options available are dependent on the type of output generated. Individual reports provide the greatest flexibility in terms of the download and e-mail options. Macro output and the output from UPDATEs, INSERTs, or batch queries is not conducive to formatting some types of downloads and most options will not be available.

Find text in output

The Find function, available in most modern browsers, provides the ability to search the report for a string. It only searches the pages that are currently displayed. For other types of output, such as Macro output organized into expandable/collapsible sections, a checkbox is available for "Visible text only". This will limit the Find to only those sections that are expanded.

Printing output

If the User wishes to print output in full color, the Print black & white checkbox in the Report Body section of the Layout panel must be deselected. In addition, the browser's print settings may have to be changed. The default installation for most browsers prevents printing page and table cell backgrounds in color to conserve ink. These settings are typicallly found in File > Page Setup or Print > Background graphics.

Download file formats

For some types of output, the only download option is HTML. Individual reports can be downloaded as HTML, or Microsoft Word and Excel files. The User can choose to create the file based on the currently Displayed page set, or if there are more rows than can be displayed in the current page set, a file of all pages (Whole report).

If the DOC or XLS radio button is selected, the HTML will be modified to allow the file to be loaded into the chosen application and maintain the report formatting, including colors, font sizes, etc. The only noticeable difference between this format and the normal HTML output is that some types of numeric formatting may not line up precisely.

Download as DATA file

QLR Manager allows the User to create several different types of data files for report output. Microsoft Excel (XLS), comma separated values (CSV) and semi-colon separated values (TXT) files can be created. These files can then be imported into other applications such as Microsoft Excel. The file is created on a "What You See, Is What You Get" basis, giving the User maximum control over the format of their output. The layout capabilities found in the Layout panel can be used to format, break and group the data, prior to creating the data file. All HTML formatting is stripped out of the data when the file is created. A report that looks like this:

partnum name boh price
dubcr Eagle Spinning Rod 0 14.00
dwscr Shakespear Casting Rod 18 14.00
dlwg25 Super G Caster 43 29.95
dml47 Light weight glass rod 0 28.95
1

Will produce a CSV file that contains:

"partnum","name","boh","price"
"dubcr","Eagle Spinning Rod",0,14.00
"dwscr","Shakespear Casting Rod",18,14.00
"dlwg25","Super G Caster",43,29.95
"dml47","Light weight glass rod",0,28.95

An XLS file can be created with the same data to be loaded directly into Microsoft Excel. Text formatting, such as colors and fonts, will not be preserved.

Report pages:

This option specifies the scope of the amount of data that will be output. If the "Displayed" option is selected, then the output content will only contain what is in the currently viewed page set. If "Whole report" is selected, then all the data associated with the report will be output. There is a third option available when downloading a XML or Data file. The selection of "Whole report  (Raw data - fast)" creates a data file, bypassing any changes made in the Layout panel. The benefit of this option is that it is much faster when working with large amounts of data.

Include column headers:

When selected, this option will add one additional row of data to the top of the data file, which are the column headings as they appear in the report.

File type:

This option allows the User to specify the type of file to be created. Microsoft Excel (XLS), comma separated values (CSV) and semi-colon separated values (TXT) files can be produced.

There are two types of XLS files available. The first is in a Binary Interchange File Format (BIFF). This option can be used with most versions of Microsoft Excel. The second option is to create an XLS file containing Tab delimited data. This may be necessary when using an older version of Microsoft Excel. Please note that Excel is limited to importing 65536 rows of data.

TXT files can be produced using a delimiter between columns or as fixed width data where the column positions are preserved by using leading and trailing spaces. These files are best viewed using a mono-space font.

File name:

This option allows for a file name to be assigned to the output file. The file extension will automatically be added depending on the type of file being created.

Download as PDF file

Transforming the HTML output from QLR Manager into a PDF file is computation intensive. It is possible that large report output may timeout on the server before the output is created. For this reason, PDF downloads should be limited to smaller reports. The number of cells in the tabular output is the critical factor. Ten pages or less can usually be converted without any difficulty, but more pages with a large number of report columns may timeout. Smaller chunks of PDF output can be created and merged together with Adobe Acrobat if desired.

Note: An alternate method of producing large PDFs from QLR Manager output can be achieved by displaying the whole report and using a PDF writer such as Adobe Acrobat or CutePDF (available free from www.cutepdf.com). This method is recommended for large reports and is usually faster than converting HTML to PDF on the server.

Orientation:

This selection sets the way in which the rectangular page is oriented for normal viewing.

Page margins:

These are the page margins that will be applied to the PDF output. The default values are 10mm, which is about 4/10th inch.

Background:

The background css can be used to create some impressive effects in the PDF. Below are a few examples:

background-color:#F6F6F6
 
background:radial-gradient(center,ellipse cover,#1B3B4E 0%,#A6D8F7 100%)
 
background:#EAC58A url('../misc/wood.png')
Color background Gradient background Image background

Note: If any of the above examples containing commas are used as a value in the Optional settings of a macro step, the commas must be entered as a double comma.

Headers and Footers:

Headers and footers can be split into left||center||right segments. Omitting the vertical pipes will center the value. The default Header text of &DT21 left justifies a QLR Date format || centers the text "QLR Manager" || right justifies a QLR Time format. The default Footer text of 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.

Watermark:

The watermark text 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.

Paper size:

This option provides a large selection of paper sizes for the PDF output.

Prohibit options:

By default, a PDF file is created with full access to the file. If it is desired to prohibit the User from being able to Copy, Print or Edit the contents of the PDF, check the appropriate checkbox. The same capability exists when e-mailing PDFs as macro steps by specifying the Optional settings of copy=no, edit=no, print=no

Download as XML file

This option allows the User to create an XML file of the report data, which contains an internal DTD. The User is allowed to define their own root tag, and a tag for the record level data. The tags for each field will be the column titles that are entered in the Layout panel. QLR manager will try to interpret column titles to create tags, replacing spaces and HTML breaks with "_" characters.

Note: It is up to the User to provide column titles using the Layoutb> panel. These values will be used as element tags in the XML output.

As with the CSV file creation, it is a WYSIWYG approach to creating the data, with the exception that break information and blank lines will not be included in the output. All HTML tagging is stripped out of the output, and the characters, <, >, &, ' and " are replaced with their XML equivalents. Be careful with "Breaks" in the report output where data outlining is checked. This will cause blank data entries to be created for the outlined fields. Remember, What You See, Is What You Get.

A report that looks like this:

partnum name boh price
dubcr Eagle Spinning Rod 0 14.00
dwscr Shakespear Casting Rod 18 14.00
dlwg25 Super G Caster 43 29.95
dml47 Light weight glass rod 0 28.95
1

Will produce an XML file that contains:

<?xml version='1.0' encoding='ISO-8859-1'?>
<!DOCTYPE data [
  <!ELEMENT data (record+)>
  <!ELEMENT record (partnum,name,boh,price)>
  <!ELEMENT partnum (#PCDATA)>
  <!ELEMENT name (#PCDATA)>
  <!ELEMENT boh (#PCDATA)>
  <!ELEMENT price (#PCDATA)>
]>
<data>
<record>
  <partnum>dubcr</partnum>
  <name>Eagle Spinning Rod</name>
  <boh>0</boh>
  <price>14.00</price>
</record>
<record>
  <partnum>dwscr</partnum>
  <name>Shakespear Casting Rod</name>
  <boh>18</boh>
  <price>14.00</price>
</record>
<record>
  <partnum>dlwg25</partnum>
  <name>Super G Caster</name>
  <boh>43</boh>
  <price>29.95</price>
</record>
<record>
  <partnum>dml47</partnum>
  <name>Light weight glass rod</name>
  <boh>0</boh>
  <price>28.95</price>
</record>
</data>
E-mailing output

This option allows output to be e-mailed to one or more recipients. Multiple recipients can be specified by separating each address with a comma. The output is sent as HTML formatted data, just as it appears on the screen. A message can be added to the top of the note using the "Message before report" text box. HTML tagging can be included in the message.

Report pages:

This option specifies the scope of the amount of data that will be output. If the "Displayed" option is selected, then the output content will only contain what is in the currently viewed page set. If "Whole report" is selected, then all the data associated with the report will be output.

Sending charts:

If charts or graphs are included in the output, they can be sent as attachments or In-line images. In-line images are base64 encoded versions of the chart images and are the most convenient way for the recipient to view charts. Some e-mail clients may not be able to display images in-line within the e-mail, but should automatically include them as attachment(s).

E-mail distribution

This distribution option is only available if the report data contains at least one e-mail address field that has been designated as a Format or use of "Email to:" in the Report Columns section of the Layout panel. Selections for "Email cc:" and "Email bcc:" are also available when multiple e-mail address fields are present in the report data.

Distribution e-mails are typically used with Custom Layouts that allow tabular report data and charts to be embedded within "free form" text and HTML. This is an efficient means of executing a large e-mail distribution with the same output sent to all recipients, or output specifically tailored to each recipient, using e-mail addresses stored in the database.

When distributing e-mail, QLR Manager will attempt to use a direct connection to the SMTP mail server as defined in your environment's php.ini file. This is usually a server of localhost and port 25. In some situations, the direct socket connect method may not work correctly. The socket connection method can be bypassed by adding the phrase "useSocket = No" to the bottom of your qlr.ini file. QLR Manager will then use a standard method for distributing e-mail. The only drawback is the speed at which the e-mail will be sent. This should not be an issue if you are sending less than several hundred e-mails at a time.

Note: The e-mail content is based on the data associated to Break1. In most cases, the data column that contains the "e-mail to:" address would be Break1. But this might not always be the case. For example, you may have two intended recipients, such as husband and wife, who share the same e-mail account, and you want each of them to receive an e-mail. In this case, Break1 would be based on the recipient's name instead of the e-mail address. Remember, if you are managing the first and last names as separate data elements, the combination of both can be defined as Break1. Not having any columns defined as Break1 in your Layout can lead to unpredictable results.

Sending charts:

If charts or graphs are included in the output, they can be sent as attachments or In-line images. In-line images are base64 encoded versions of the chart images and are the most convenient way for the recipient to view charts. Some e-mail clients may not be able to display images in-line within the e-mail, but should automatically include them as attachment(s).

Creating a QLR Widget

A QLR Widget is an HTML link that can be used to allow users to gain direct access to a Query, Macro, User Menu or Form. QLR Manager produces the HTML that can be copied and pasted into an existing web page. The HTML is formatted as an HTML anchor tag with href, title and target attributes:

<a href="WidgetURL" title="QLR Linked Report" target="qlrwidget">click here</a>

When the link is executed, it takes the User directly into QLR Manager, either displaying the results of the Query, Macro, User Menu, or Form. The QLR connection is established using the logon ID, logon password, database reference, and database server reference that is active at the time the widget is created. The connection information is passed to QLR Manager as the encrypted part of the widget link. Although the connection ID and password are encrypted in the link, it is still prudent to use an ID with limited QLR Manager authorities and does not have update authority to the database when creating the QLR Widget, unless it is a Form Widget which will require DELETE, INSERT or UPDATE authority depending on the type of form.

If a widget is created for a query or macro that has query variables, the values of those variables are displayed when the widget is being created. The author of the widget can choose to change the values that will be used when the widget is executed, or leave the values as blank. When left blank, QLR Manager will prompt the User of the widget for input values when the widget is executed. It is also possible to set the default value for a query variable and still allow the User to change that default value. This is accomplished by checking the Show checkbox found next to the Query Variable Value text input field. If a value is provided and Show is not checked, the Query Variable will not be presented to the User to change the value. This allows for the creation of very flexible widgets.

Widgets for queries are created using either Report Tools or Output Tools. The only place that widgets can be created for Macros, Menus and Forms is after logging on to the Macros · Menus · Forms suite from the Connect panel. Widget creation is available from Preview Tools when previewing the object. In order to create a widget of any type, the object being created must be saved in order for QLR Manager to know the logged on Owner and the query, macro, menu, or form Name to associate with the widget.

Depending on which type of widget is created, the resulting output will have access to either the Report or Output Tools. However, for security reasons, a widget cannot be created from within a widget.

Window open target:

A widget can be defined to open in either the existing browser window when the link is executed or open in a new browser window. When defined to open in a new window, the target name for the new window is set as target="qlrwidget". A new window contains a frameset comprised of a header frame, a message frame, and the main content frame. The header frame includes either the Report Tools or Output Tools option, depending on the type of output.

If it is desired to have various widget links open in separate browser windows, the widget target attribute can be manually edited to change the name of the target="value" to something different for each link. Javascript can also be used to create a pop-up window with specific features and is described below.

HTML tag type:

Widgets can be created as either an HTML <a href> tag or an <iframe> tag. When created as an href tag, a text link is displayed that must be clicked in order to execute the widget. An iframe is an in-line frame and that may be more suitable for some applications, such as presenting report output in-line within a web page. The content of the iframe is loaded at the same time the web page is loaded.

Output header text:

When a new window is opened, the "Output header text" which appears at the top of the window in the blue header bar can be specified when the widget is created. The default text that will appear in this area is "QLR Linked Report". This can be changed or omitted entirely be clearing the value in this input field.

Text for widget link:

The link text that will appear within the HTML href tag is defined in the "Text for widget link" input field. An HTML image tag can also be entered to allow the link to be associated with an image.

Optional password:

If a value is provided in the optional password field, the User is prompted to supply the password when they execute the Widget. QLR Manager prompts the User by presenting the Query Variables panel. If there are other query variables for which the User must provide input, the password field will appear at the top of the Query Variable panel.

Widget body css:

CSS entries can be entered into this field to be applied to the body of the widget. The default values for the body CSS are "margin:0 10px 10px 10px; background:#EEEEEE". It may be useful to change the margins or background color to better suit the intended use of the widget.

Editing a widget link to add query variable values:

When developing customized applications, it may be desirable to create a widget link and add specific values for query variables to be used when that link is executed. This can be accomplished by adding the query variable name prefixed with r000 (zeros), such as r000custnum. Any periods or spaces in query variable names must be specifically encoded to pass the variable name back to the server. This is accomplished by replacing any periods with "_pe_" and any spaces with " _sp_". For example, a query variable name such as "sales region", becomes "sales_sp_region". If the query variable used belongs to a different User ID owner and is referenced in the query by prefixing the variable name with the owner name like [frank.region], the period must be replaced with "_pe_". The resulting query variable reference should be r000frank_pe_region. These replacements are necessary to accommodate the way PHP handles spaces and periods in HTML variable names. For security reasons, variables that are already defined in the encoded link cannot be overridden by adding a variable as described below. Some examples are as follows:

Note: There should be no line breaks when real links are created.

http://www.mysite.com/qlr/reportwin.html?r000sales%20region=northwest&Action=qlr_new&isW=yes& formname=query &qlr_linkid=V1RKb2JGa3ljMmRpUnpscVdWZDRiMkl6VGpCWVVYZ1SlIzUm9aRTZVZwRFFuaGlTRXAwV1ZjMW

http://www.mysite.com/qlr/reportwin.html?Action=qlr_new&isW=yes& formname=query &qlr_linkid=V1RKb2JGa3ljMmRpUnpscVdWZDRiMkl6VWVVYZ1SlIzUm9aRWhTZVZwRFFuaGlTRXAwV1ZjMW &r000start_date=2007-10-09

Another way of passing a variable to pages that can process php requests (such as when using Drupal, and the "Input format" is set to allow PHP code), is to embed php code to processes either an HTML POST or GET argument. The following is an example of placing a widget within a page called showme.php, where the start date is being passed as a GET variable. The requesting URL is showme.php?r000start_date=2007-10-09. The widget code within showme.php would be as follows to process the incoming GET variable. Notice that the php code even checks to make sure that the URL string contains the variable "r000start_date".

http://www.mysite.com/qlr/reportwin.html?Action=qlr_new&isW=yes& formname=query &qlr_linkid=V1RKb2JGa3ljMmRpUnpscVdWZDRiMkl6VWVwRFFuaGlTRXAwV1ZjMW <?php if (isset($_REQUEST['r000start_date'])) echo '&r000start_date='.$_REQUEST['r000start_date']; ?>

Saving the widget HTML:

In some development environments, it may be desirable to save the HTML that is generated for triggering the widget. This can be accomplished by granting a User ID the authority to Save Widget HTML on the User ID Admin panel.

After the Create button is clicked to create a widget, the widget HTML is presented for copying. For User IDs with authority, an additional textarea will be presented to allow the User to save the widget HTML with a description:

The default value of the description is the object name, the layout name (if used in the creation of the widget), followed by the values given for any query variables. If the Show checkbox had been checked for the variable value, then (s) would follow that value. In the above example, 2013 would be displayed as 2013(s). The save checkbox must be checked and the description cannot be blank in order to save the entry.

The entry is saved in the qlr_object table. It is given an object_type of "widget html". The owner is the widget creator's User ID. The name is the same name given to the corresponding "widget link" that is also saved. The description information is added to the description field. The HTML is placed into the object_data field.

Hint: If a query is executed to see this information, there is an option in the Report Body section of the Layout panel which allows you to Show HTML tagging. Otherwise, the content will be displayed as a widget. This means that a report can be generated in QLR Manager that is a collection of widgets.

For User IDs with this authority, widgets can also be generated from the Output Tools pop-up window after executing a Query or Macro from the User Menu panel. If the menu item was defined with a title, the title is used as the default description text. Otherwise, the default text will be the object's name, and in the case of a query, the associated layout if applicable.

Javascript pop-up window:

Javascript can be used to create a pop-up window to load the QLR Widget. This will require some manual editing of the widget link to add an onclick event to call the Javascript window open method:

onclick="window.open(URL,windowName[,windowFeatures]);return false"

Since Javascript must be enabled in the User's browser for the onclick event to be recognized, the addition of the onclick event without altering the standard href and target attributes will allow the link to be accessed without Javascript. With Javascript enabled, the use of the return false at the end of the onclick event will cancel the normal href action and load the link into the window opened by Javascript with the specified features. The following is an example of the modification to the QLR Widget link to use this Javascript method. The manual addition is in (blue). The entire <a href=...>click here</a> tag must be kept on one line in the HTML page:

<a href="WidgetURL" title="QLR Linked Report" target="qlrwidget"
onclick="window.open(this.href,'qlrwidget','top=100,left=100,width=300,height=200,
resizable=1,scrollbars=1');return false">click here</a>

Note: The "WidgetURL" is the long encoded string that follows the <a href= attribute in the widget link and must be substituted above.

To specify a window feature, add the desired value to the windowFeatures arguments as feature=1(true) or feature=0(false), as illustrated by the use of resizable=1,scrollbars=1 in the above example. Below are some descriptions of the more common Javascript window.open features.

Feature Description
top The top placement of the window in pixels.
left The left placement of the window in pixels.
width The width of the window in pixels.
height The height of the window in pixels.
status The status bar at the bottom of the window.
toolbar The browser toolbar with buttons such as Back and Forward.
location The location entry field where the URL is displayed or entered.
menubar The menu bar of the window with options for File, Edit, View, etc.
directories The browser directory buttons, such as What's New and What's Cool.
resizable Allows the User to resize the window (recommended).
scrollbars Enables scrollbars if the output is larger than the window (recommended).
Saving data to a Table

QLR Manager supports the saving of report data to a table. This works with both Standard and Table Layouts. This option is not available for Pivoted Layouts. This feature uses a WYSIWYG approach to save the data. For example, if the report data is in a Table Layout that produces 20 columns of data, the saved data will be in a format that looks like the Table Layout and will have 20 columns. The column names in the new table will be based upon the report column names provided in the Layout. The saved data only includes rows of report data. It does not include Break data or Final summary information.

The data can be saved "cross database", meaning a query can be executed in one database engine such as SQL Server, and the data can be saved in a table in a different database engine such as Oracle. QLR Manager will attempt to map the column data type from one database engine to the next, meaning that character data will be saved as character data, numeric data will be saved as numeric data, etc.

When saving data, the User must provide a Table Name. If the destination table already exists, the User is given the option to choose to add the data to the existing table, or to replace the existing table.

The connection credentials to the destination database engine must also be provided. These include specifying the database engine from the list of QLR supported databases in the install environment, the User ID and Password. The Server reference must also be provided.

In addition, the destination database is also referenced. This behaves differently, depending upon the destination database engine:

For MySQL and SQL Server, this is simply the database.
For Oracle, this is ignored. The table will be saved in User ID Schema.
For PostgreSQL, this is the database. To reference a specfic Schema within the database, the Table Name should be prefixed with the schema, such as purchasing.sales_info.
Saving data as a Report Cube

When all of the report data can be cached in memory, as indicated by the presence of the Filter icon in the upper right corner of the Report panel header, the capability exists to save the data as a Report Cube. A Report Cube stores all of the data associated with the report, including the layout, and makes the data available for quick access in the future. After Report Cube(s) have been saved, they can be retrieved or deleted using a control that will appear at the bottom of the Query panel.

Saving data as a Report Cube can be useful when running queries with a long execution time, or when trying to save a snapshot of the data in a dynamic environment.

MySQL database users may see the following error when trying to save large cubes:

Database error # 1118. The size of BLOB/TEXT data inserted in one transaction is greater than 10% of redo log size. Increase the redo log size using innodb_log_file_size.

If this occurs, try setting innodb_log_file_size = 1024MB in the MySQL ini file (my.ini). The MySQL server must be restarted for this change to be initiated.