The  Layout  panel is used to modify the appearance of the report data generated by a query. It provides extensive control over the output format and allows for the creation of charts and graphs. HTML tagging can be imbedded into many of the text inputs and text areas, such as the title, footers, break text, etc., to further enhance the report's appearance. The following expandable/collapsible sections correspond to the sections found in the  Layout  panel and describe their function and use.

Titles & Footers
Report title

The Report title is text that appears on the top of each page of the report. If the report title text is left blank, then no report title cell will appear on the report. The report title characteristics can be manipulated with the various formatting options that appear in the Report title section. The report title can contain either plain text or HTML tagging. If text such as <b>My <u>Report</u> Title</b> is entered, it will produce a title of My Report Title.

In addition to text and HTML, there are special replacement character references that allow the User to specify page numbering, data from within the report body, and numerous options for formatting the current date and time. More information can be found in the topic: Referencing column information.

It is also possible to split the title text to appear in 3 segments as Left, Center, and Right justified. This is accomplished by using "||" to split the text. If Fred||Summary Report||Sales Dept is entered, then Fred will appear as left justified, Summary Report will appear in the center, and Sales Dept will be right justified in the title cell. This technique works for footer text as well.

Bold, Underline and Italic text formatting can be applied to the title cell by checking the appropriate checkboxes. Bold is checked as the default for the Report title.

Column headings

General formatting, such as the font type, size, color, and background color can be applied to all of the Column headings as a single group. This area also contains a text input with a default value of "Pivoted<br />Columns" to allow for the input of a column heading when a "Pivot" column is selected in the Report Columns section of the  Layout  panel. If the User wishes to change the appearance of individual column headings, this can also be accomplished by embedding HTML tags into the specific columns as found in the Report Columns section.

Bold, Underline and Italic text formatting can be applied to all the column headings by checking the appropriate checkboxes.

Allow sorting:

If all of the data for a report can be cached and the "Allow sorting" checkbox is checked, the data in the report can be sorted by clicking the sort arrows in the column heading, or the column heading text. In most cases, depending upon Layout settings, the data will be sorted within the defined Break structure. QLR Manager sends a request back to the server to sort all of the data associated to the report, which may be more than the data that is currently being displayed.

Columns that are defined with a Break action cannot be sorted, as the sorting feature is intended to sort data within the Layout's break structure.

In Table and Pivot style Layouts, the column data that is spread across the page as a result of the Table or Pivot action cannot be sorted. Also, changing the sort order of data in a Table or Pivot may impact the number of rows displayed in a report, as the data "Grouping" may be impacted by the sort order of the information.

The sorting controls can be excluded from specific columns by entering <no sort> into the Heading text field in the Report Columns section of the  Layout  panel.

Report footer

The Report footer is text that appears on the bottom of each page of the report. If the report footer text is left blank, then no report footer cell will appear at the bottom of the report pages. The report footer characteristics can be manipulated with the various formatting options that appear in the Report footer section.

The same formatting options and characteristics available for the title can also be applied to the footer. Please see the Report title section above for more information. A common use for footer text is the entry of "Page &PN" to reference the page number.

Bold, Underline and Italic text formatting can be applied to the footer cell by checking the appropriate checkboxes.

Report Body

Font faces, colors and other attributes can be set for all the data cells in a report using the controls in the Report Body section. Changing the alignment of text in specific columns can be accomplished using the Alignment controls in the Report Columns section. The report body controls also provide many other options for controlling the appearance of the data cells in the report.

Outer border size:

The Outer border setting allows the thickness of the borders to be specified that will be displayed around the report pages. The selections range from 0 to 10 pixels. Please note that Netscape Navigator 4.x is unable to center the report pages when a border size of 0 is set. The color of the border can be set using the "Outer border color" control.

Inner border size:

The Inner border setting determines how thick the lines should be between data cells in a report. The selections range from 0 to 10 pixels. The color of these lines can be set using the "Inner border color" control.

Section border size:

The Section border size determines the line thickness that will delineate the report title and column headings from the report data. For table and pivot layouts, the column groupings will also be separated from the data with the selected section border size. The color of these lines is determined by the "Inner border color" setting.

Inner border use:

The Inner border use controls whether the vertical, horizontal or both vertical and horizontal lines are displayed. To eliminate the display of all inner border lines, the Inner border size can be set to 0.

Max displayed rows:

This value determines how many rows will be produced per "page set". A page set is the total number of rows of data to be displayed when a query is executed. For example, if a value of 500 is used in conjunction with a Rows per page value of 50, 10 pages of report output will be displayed at one time. When the number of rows produced by the query exceeds the Max displayed rows value, the User must click the appropriate navigation buttons (First, Back, More, Last) found above the report to move between page sets. A larger number will show more data, but it takes longer to display results, especially if a user is connected with a modem.

The number of rows displayed will not always be exactly equal to this setting, as subtotaling data will add additional rows to reports. QLR Manager will always complete the current page with data. If using a Max displayed rows value of 500 and the 500th row appears in the middle of the page, QLR Manager will fill up the final page with rows of data, if there is more data available.

Hint: To print a large report, or generate a PDF using a PDF writer such as Adobe Acrobat or CutePDF, this value can be set to a number of rows greater than the total rows expected to be produced by the query. If the Layout is saved as the same name as the query or wizard, it will automatically be applied when the query, wizard or menu item is executed.

Rows per page:

This determines how many rows will be produced per page. If 0 is entered, no pagination is applied and will result in one continuous report page with one set of column headings, and one title or footer if applied. There are two other limiting factors that determine how large of a continuous report page can be created. These are the Max displayed rows above and the Maximum output file size setting applied when QLR Manager was installed. These values default to 500 rows and 5 MB.

Currency symbol:

This option sets the currency symbol that will be used when the $1,234.56 or $1.234,56 formatting options are chosen in the report column Format option. The $, £ or • symbols can be used, or up to 4 text characters can be entered. If the User's keyboard cannot produce the £ or • symbols, the characters can be copied and pasted from this help text.

Currency symbol location:

The Currency symbol location determines whether the currency symbol should appear "Before" or "After" the values when a currency formatting option is selected.

Cell padding:

Cell padding refers to the amount of space between the data in a report cell and the border of the cell. The selections range from 0 to 10 pixels. This setting is applied to all cells in the report, including the title, column headings and the footer.

Date separator:

The Date separator allows the User to specify which character will be used when displaying dates. This will work with all date formatting options that contain a "/", and the "Date Time" option. Dates such as 2001/31/12 can be displayed as 2001.31.12 if "." is chosen as the separator. It also controls the appearance of the &DT date reference options that can be used in titles, footers and break text.

Pages across:

This selection provides a means to layout multiple report tables horizontally. This can be useful if each report has only a few columns of data resulting in a narrow table. The selected number of reports can be generated from left to right and make more efficient use of the browser window.

Print black & white:

With this option checked, a printed report will be produced with black text and table borders, and with the cell background colors removed. This allows for the creation of report output that is optimized for viewing and will save colored ink when printing. It does not affect the printing of charts which are images and will print as they are displayed.

Ignore wrapped line count:

There may be instances where Word wrap may be selected for a specific data column under Format or use in the Report Columns section. This will cause long lines of data to wrap within the confines of the specified column width. If the number of Rows per page have been defined to provide page breaks for printing, the additional rows created for wrapped data are not included in the row count and will likely cause printed page overruns. Selecting Adjust for wrapped data will attempt to estimate the additional rows created by the Word wrapped data to produce uniform sized pages for printing.

Note: This adjustment is only an approximation. There are many variables such as font metrics and other factors that determine how the data will wrap in a report column within the client's browser.

Alternate row background:

Alternate row coloring produces a "striped" report appearance. When checked, every other row is colored with the Report Body "Data row colors". The alternate rows are colored white.

Row hover colors:

When a report is viewed within the QLR Manager  Report  panel, the Row hover colors are applied as the mouse is moved from one row to another. This only applies to the data rows, not headers, footers, or break rows.

Negative numbers in red:

When checked, all negative numbers in the body of the report will be displayed in red.

Show HTML tags:

Reports that contain HTML tags within the data will display with the tagging rendered by the browser as the default. For example, if a field contains <u>My Information</u>, it will be displayed in the report as My Information. If "Show HTML tags" is checked, it will instead be displayed as <u>My Information</u>.

Auto arrange columns:

Auto arranging will place all Break columns, ordered from Break1 to Break5, as the left most columns in the report. The Break columns will then be followed by Group columns, and then any remaining columns will be shown as the right most columns. If the Report data cache Report data cache1.9 MB icon is visible beneath the  Log off  button, the report data will automatically be sorted based on the Break and Group columns, working from left to right.

When a query is first executed, QLR Manager will calculate if all of the data generated by the query will fit into the internal data array. If so, it will automatically check the Auto arrange checkbox and the columns in a standard report format (not table or pivot layouts) are automatically arranged so that the Break columns appear on the left, starting with Break1 through Break5. The Break columns are followed by the Group columns. All other columns appear on the right side of the report.

The order of columns within subsets: Break1-5 columns, Group columns, and all remaining columns, can be controlled with the Display order setting. For example, if there are 3 Group columns, they can be ordered such as 3,1,2 to change their order within the report. The remaining columns can be repositioned as well, such as 1,2,5,3,4. Notice how the display ordering works within the column subsets, so numbering can be reused. You cannot change the order of the Break levels, meaning that Break1 will always be to the left of Break2. However, if there is more than one column defined for the same Break level, the order of these columns can be controlled by using the Display order. In addition to reordering the columns, QLR Manager will automatically sort the data based on the order of the Breaks and Groups. If the Layout only contains Breaks (No Group Actions), then the first three columns of normal data that follow the last Break column will be sorted as well. There is no need to change the sort order in the query and rerun it.

If a Layout is saved with the Auto arrange checkbox checked, it will remain checked when the Layout is retrieved. However, if the amount of data from the current query exceeds the maximum size of the data array, then a message will appear that the auto arranging and sorting could not be applied. In these cases, the ordering of the data elements must to be controlled by the query.

It is not necessary to use Auto arrange to sort data by columns. If the data caching icon is present, then data can be sorted by columns using the Display order field.

As an example, if a query returns the following data:

FIRST
NAME
LAST
NAME
JOB
ID
EMPLOYEE
ID
SALARY MANAGER
ID
DEPARTMENT
ID
Den Raphaely PU_MAN 114 11000 100 30
Alexander Khoo PU_CLERK 115 3100 114 30
Shelli Baida PU_CLERK 116 2900 114 30
Sigal Tobias PU_CLERK 117 2800 114 30
Guy Himuro PU_CLERK 118 2600 114 30
Karen Colmenares PU_CLERK 119 2500 114 30
Jennifer Whalen AD_ASST 200 4400 101 10
Michael Hartstein MK_MAN 201 13000 100 20
Pat Fay MK_REP 202 6000 201 20
1

If Break1 is applied to the DEPARTMENT_ID column and Sum is applied to the SALARY column, Auto arrange will place the DEPARTMENT_ID on the left hand side of the report and sort the data by DEPARTMENT_ID. In addition, the first three columns following the Break are also sorted in ascending order:

DEPARTMENT
ID
FIRST
NAME
LAST
NAME
JOB
ID
EMPLOYEE
ID
SALARY MANAGER
ID
10 Jennifer Whalen AD_ASST 200 4400 101
Break 1 4400  
 
20 Michael Hartstein MK_MAN 201 13000 100
  Pat Fay MK_REP 202 6000 201
Break 1 19000  
 
30 Alexander Khoo PU_CLERK 115 3100 114
  Den Raphaely PU_MAN 114 11000 100
  Guy Himuro PU_CLERK 118 2600 114
  Karen Colmenares PU_CLERK 119 2500 114
  Shelli Baida PU_CLERK 116 2900 114
  Sigal Tobias PU_CLERK 117 2800 114
Break 1 24900  
 
Final 48300  
1

The columns following the Break could be reordered using the Display order setting. The SALARY column could be pushed to the right by either numbering all the other non-Break columns to appear before it, or by giving the SALARY column a display order greater than 1000, such as 1001. The Display order can also be specified with decimal. If it is desired to add a column to fit between existing display order settings, a value such as 3.5 is can be entered.

Ignore Table/Pivot sort:

When Table and Pivot Layouts are created, the entries that are spread across the page for these two report formats are sorted alphabetically. If the ignore option is checked, and the Format type of the chosen Table or Pivot column is "General", then the order of the data being spread across the page will be the order of the data as found in the report rows.

Fill balance of page with empty rows:

When Fill page with rows is set to "Yes", QLR Manager will fill the balance of each page with as many rows necessary to meet the number of rows set in the Rows per page option. This will create uniform sized pages throughout a report. If Alternate row coloring is checked, the filler rows will alternate between white and the specified "Data row colors". The color of the filler rows can be modified using the "Color" or "White" selections.

Report Breaks

Report Breaks allow the User to define groupings within the report to either visually separate data, or for subtotaling, counting, and other Actions. Breaks are triggered when the data value in the break column changes from one row to the next. Proper use of breaks is dependent upon how the query sorted the report data.

Up to 5 levels of breaks can be defined and they are nested to provide a "break within a break". More than one column of data can also be used to define a single break. An example might be report columns for both "State" and "City" set as "Break1".

There is an additional Spacing after break option for the first break level called "Section". A Section break causes a New Page to occur when a break in the data is encountered. More importantly, it creates a collapsing and expanding section within the report data. The default title for each section is the value of the break data. The title can be customized by entering a value in the Collapsing section title field

.

Bold, Underline and Italic text formatting can be applied to break text by checking the appropriate checkboxes. Bold is checked as the default for report breaks. Font and background colors can also be applied by launching the appropriate color selector. The break text can contain either plain text or HTML tagging.

Break text

Text before break:

The leading break text is the text that will appear prior to the start of a new break section. The space allotted to this text spans the width of the report. When referencing report data, be sure to use the &n reference (next row of data) instead of the &c reference. For leading break text it is usually the next row of data that is most relevant.

Text at the break:

The trailing break text is the text that will appear on the corresponding break line that is added into the report. The amount of column space that is allocated to the break text is dependent upon the first column of data that is encountered that has an activity that the break needs to display. For example, if the User is using the Sum action in the 4th column, the break text will span the first 3 columns of the report. The 4th column will be used to display the sum amount.

If there are no actions for a break to act upon, such as "Sum", etc., the break text will still appear in the report. If the break text is blank, the break row will not be displayed. This allows for data outlining without empty report rows being added to the report.

If it is desired to exclude the text at the break from displaying, but still include the spacing at the break, enter the tag of <omit> as the break text.

Custom Text at the break:

Custom break text can be entered, allowing for great flexibility in report creation. A classic example is to "stack" several break level items on top of each other, such as adding taxes to the bottom of an invoice.

The following is an example of how to modify a typical report, with data in separate columns, into a report that has stacked data at the break. Suppose the original report looks as follows:

Invoices for Jethro Cyprus
Between dates
01-01-2002 and 12-31-2004
Order
number
Part
number
Purchase
price
Tax†
amount
Invoice #1014, dated Oct/28/2002
Payment Type: MC
1014 srib2000 $2.95 $0.20
Total for 1014 $2.95 $0.20
 
Invoice #1020, dated Dec/18/2002
Payment Type: MC
1020 rbd01 $62.50 $4.26
Total for 1020 $62.50 $4.26
 
Final $65.45 $4.46
1

A simple example of adding plain text to the break on a separate line that spans across the entire report by changing the break text:

From:  Total for &c4
To:Total for &c4||&lt;center>End of invoice</center>

Notice the use of "||" as the indicator that custom text follows the normal break text input. An additional row in the report is created that spans the width of the report:

Invoices for Jethro Cyprus
Between dates
01-01-2002 and 12-31-2004
Order
number
Part
number
Purchase
price
Tax†
amount
Invoice #1014, dated Oct/28/2002
Payment Type: MC
1014 srib2000 $2.95 $0.20
Total for 1014 $2.95 $0.20
End of invoice
 
Invoice #1020, dated Dec/18/2002
Payment Type: MC
1020 rbd01 $62.50 $4.26
Total for 1020 $62.50 $4.26
End of invoice
 
Final $65.45 $4.46
1

The steps involved to "stack" the tax information directly below the purchase price information:

1   Hide the tax column by setting the the Column width to 0 in the Report Columns section of the Layout.
2   The "Break #1:" Text at the Break is entered as:
Total before Tax
||<td class="break" colspan="2">Plus Sales Tax</td>
<td class="bdata6">$eval(number_format(&b8~,2))</td></tr>
<tr><td class="break" colspan="2">Total with Tax</td>
<td class="bdata6">$eval(number_format(&b7~+&b8~,2))</td>

Some important notes regarding this example:

 •   Total before Tax
This is typical break text that starts the break section.
 •   ||<td class="break" colspan="2">Plus Sales Tax</td>
The "||" signifies the start of the custom text. The class of "break" is always used for the leading text, and the colspan of 2 equals the number of columns that the data needs to span in this instance. QLR automatically adds an HTML tag to start of the custom text if the <tr> tag is not provided.
 •   <td class="bdata6">
The class entry of "bdata6" refers to break data for the 7th column in the report (the first column of the report is column number 0). The reason that it does appear as the 7th column is because several columns have been hidden.
 •   $eval(number_format(&b8~,2)) </td></tr>
The text that is displayed is a dollar sign ($) followed by an embedded evaluated formula, which takes the break data for column 8 (the column that contains the sales tax data) and formats it to have 2 decimal places. The "~" that follows the &b8 indicates to use the raw unformatted data from column 8 in the evaluated formula. Finally, there is the HTML </tr> tag to close this table row.
 •   <tr><td class="break" colspan="2">Total with Tax</td>
As with the previous table cell that contained text, the class of "break" is used for the text that appears to the left of the break level formulas. The colspan of 2 represents the number of columns that the data must span in this instance.
 •   <td class="bdata6">$eval(number_format(&b7~+&b8~,2)) </td>
The text that is displayed is a dollar sign ($) followed by an embedded evaluated formula, which sums the break data for columns 7 and 8, and formats the data to have 2 decimal places. The "~" that follows &b7 and &b8 indicates to use the raw unformatted data from those columns in the evaluated formula. QLR Manager will automatically add a closing table row tag </tr> if none is provided.
Invoices for Jethro Cyprus
Between dates
01-01-2002 and 12-31-2004
Order
number
Part
number
Purchase
price
Invoice #1014, dated Oct/28/2002
Payment Type: MC
1014 srib2000 $2.95
Total before Tax $2.95
Plus Sales Tax $0.20
Total with Tax $3.15
 
Invoice #1020, dated Dec/18/2002
Payment Type: MC
1020 rbd01 $62.50
Total before Tax $62.50
Plus Sales Tax $4.26
Total with Tax $66.76
 
1

Collapsing section title:

When the Spacing after break for Break1 data has a value of "Section", QLR Manager will force a new page to begin for the next set of data and put each instance of the Break 1 data into a expandable/collapsible section that can be opened and closed by clicking on the section label. The default value for the section label is the value of the the Break1 data elements.

The section header can be customized using the the Collapsing section label field. Both text and data field references can be used. Below are some examples.

Notice that data from within the report can be accessed by using the &nx format, where x is a report column number.
Data for country code &n1
Customers in state: &n1, city: &n4

Note: In order for the second example to have the correct data within the Section, Break1 must be selected in the Report Columns Action list for both columns 1 and 4.

Referencing column data and other information in a Layout:

The  Layout  panel provides the User with the capability of including column data in a report's Break text fields, as well as the Report title and Report footer. For example, suppose the report is a list of employees that is sorted by the state in which they are employed. A break is desired to sum the "Total for state XX", with XX being the state on which the break is based. This can be accomplished by entering the following into the Break #1 "Text at the break":

Total for state &C1

This triggers QLR manager to replace &C1 with the data in the CURRENT record at the time of the break, with report column number 1's data. If column number 1 in the report is the state, and the value is "NY" at the time of the break, this would result in:

Total for state NY

If it is desired to reference the data in the NEXT record coming up in the report, &N1 would reference the data from the first column in the next record. This is useful when it is desired to reference information in the Report title.

Multiple references can be used in the text, such as "State &C1", "City &C4 employee count" is acceptable.

Either upper case or lower case C's and N's can be used as a reference.

The current break level data can also be referenced using &BXX or &bxx. It assumes that an "Action", such as Sum, Count, etc., has been performed on the referenced column to produce break level information. An example of such usage might be to count the number of items (for example in column 4) in a break section and then the "Text at the break" for "Break #1" might be:

Number of entries &b4

This approach also works if column 4 had been hidden by setting the display width to zero. By setting the display width = 0, the column "Action" can be set = Count. An alternate way of hiding a column is with the Hide Action. But using this approach would not allow for the Count action to have been applied to the column.

Note: The use of reference values in Break text can be used in conjunction with the BreakxH (hidden break action) or the Hide Action to display information in a report break, but not take up space as a column in the report. More information about Actions is available in the Report Columns section.

In addition to referencing column data, Query variables can also be referenced. Suppose a query contains a query variable called {customer_number} used with MS SQL Server only, [customer_number] for other supported databases, or the older method of **customer_number. The User supplied value for this variable can be referenced in the report title by adding the text {customer_number} for MS SQL Server or [customer_number] into the title at the desired location. The value provided for this variable will be substituted for the variable name. Note that the variable name must be enclosed in brackets or start with ** (older method) as found in the query text or wizard filter set.

When the old style ** query variable identifier is used, the @ followed by the size value is omitted when referencing the query variable name in the Layout. For example, if **year@4 is used in the query, then **year is used in the Layout. If the Layout references the query variable using brackets, then the entire query variable string value is referenced, such as [year@4]. It does not matter if the query text uses one reference style and the Layout text uses the other.

Embedding calculations in headers, footers and breaks:

When the string eval([formula]) is added to header, footer or break text, its content is evaluated as a formula. The column referencing technique described above can be used in the formula. PHP code can also be used in the formula in the same manner that the Evaluated formulas work. Some examples:

Calculations with Replacement Reference Values
FormulaDescription
eval(10/4)Returns 2.5.
EVAL(&b7/&b9)Returns column 7 divided by column 9. Notice how EVAL can be in upper (or even mixed) case.
eval(if(&b4>10) $value='Good'; else $value='Bad') This makes a conditional assignment to $value ($value must be used as the variable name) and displays either Good or Bad, depending on the value of column 4's break data.
eval(number_format(&b6 * 3.1415,2))Returns the result formatted to 2 decimal places.

If the parenthesis () do not pair up properly in the formula, a message "!_paren_error" will be displayed in the replacement text.

Page numbering in titles, footers and breaks:

Page numbering can be added (usually in the footer), by including &pn or &PN in the appropriate layout input field.

Dates and other substitutions in titles, footers and breaks:

The current server date or time can be added by using &DTx or &dtx, where the "x" represents a specific type of date formatting. These format options can be found in the Date formatting section.

Summary of Replacement Reference Values
ValueExampleDescription
&Cx or &cx&C1Replace with data value from column 1 from the current data row.
&Nx or &nx&N1Replace with data value from column 1 from the next data row.
&Bx or &bx&B1Replace with data value from column 1 with the break level data row.
&PN or &pn&PNReplace with current page number.
&DTx or &dtx&DT3Replace with a Date or Time format as found in the Date formatting section.
[query variable][year]References the value given to the "year" input variable when the query was executed.
{query variable}{year}Same as above, but curly brackets are required for use with Microsoft SQL Server.
[qlr_query_rows][qlr_query_rows]This will display the total number of rows of data in the report.
[qlr_query_text][qlr_query_text]This will display the query text that was executed to generate the report.
[qlr_query_time][qlr_query_time]This will display the number of seconds needed to generate the report (3 decimal places).
[qlr_random][qlr_random]This will display an 8 character random string.
[qlr_userdb][qlr_userdb]This displays the current database or schema.
[qlr_userid][qlr_userid]This displays the User ID that was used to generate the report.
[qlr_where_text][qlr_where_text]This displays the "Where" clause portion of the query.
[qlr_query_owner][qlr_query_owner]The owner of the Query.
[qlr_query_name][qlr_query_name]The name of the Query.
[qlr_layout_owner][qlr_layout_owner]The owner of the Layout.
[qlr_layout_name][qlr_layout_name]The name of the Layout.

Hint: If it is desired to always display the query text or other substitution options in the title area for a report that does not have an associated layout, it is possible to enter [qlr_query_text] into the Report Title area and save the layout as the Default skeleton layout applied for the User ID.

Final text:

The Final text is text printed at the end of the report. It is only printed when an Action is present to create a final row of data, such as a Count or Sum. The replacement value referencing described above can be used for final text. In addition, final calculated values can be accessed by the use of &fx or &Fx. These references can also be used prior to the final break section (such as in break level text), and will render the calculated value at the time it is called, such as cumulative summing.

Break text alignment:

The alignment of all levels of break text is controlled by this Alignment setting. Valid choices are Left, Center or Right. There are two different settings: One that controls the text that appears before a break, and one to control the text that appears at the break.

Outlining:

The Outlining checkboxes specify whether the values within a break column are blank in the following row of data, or if the same data is repeated. All new break sections and the beginning of pages will display the column value even if outlining is set to "Yes". Outlining can be controlled for each break level.

Show final text:

The "Show final checkbox" determines whether the final text/grand total information is displayed. If subtotals exist for breaks and the grand total is not desired at the bottom of the report, this value can be unchecked.

Spacing after break:

This determines how many blank lines will appear after the associated Break level is triggered. It is also possible to specify that a new page be printed.

When "Section" is selected as the spacing, a new page will be created at the break along with expandable/collapsible report sections. The default text for the Label created for each of these sections will be the combination of column title text and data value for the data elements comprising the Break. Custom section labels can be created by entering text into the Collapsing section label field. Replacement variables can also be entered. For example, if the 4th column of the data contains a postal code, entering the following would reference the upcoming postal code data:

Customers for Postal Code &n4

Notice how &n is used, with the "n" referencing the "next" data row, which will be the first row of data after the section is started. When the Show final option is checked, the Final break text will be displayed in it's own section. The section label text will be based on the Text at the break entered.

Note: The "Section" option is only available for Break 1 level data.

Wrapping break text:

When checked, the break text will be permitted to word wrap within the cell.

Use break text colors:

When checked, the break text colors as specified in the color controls are used. When not checked, the overall row coloring scheme is used.

Show break data for a single data row:

When checked, break information will be displayed even if there is only a single row of data in the current break. When unchecked, breaks with only one row of data will not produce break text, producing much "tighter" report output.

Report Charting

Please reference the separate topic about Report Charting.

Custom Layout

A Custom Layout is created by entering text into the "Custom Layout" section of the  Layout  panel. This text may contain HTML tagging. QLR Manager allows the User to reference individual data fields, break level data, an entire report, and charts for inclusion in the custom output that is generated.

The Custom Layout section can also be used to include "FusionWidgets", which provide a myriad of information visualizers to present data. More information is available in the FusionWidget topic under Report Charting.

Custom Layout controls

The Custom Layout section is intended to provide the User with "freeform" control over the formatting of output using HTML tagging. This is particularly useful to generate e-mails to multiple recipients, each with specific information related to the addressee. More information about this capability is provided in the Distribution e-mail topic below. Following is a description of the controls in this section:

Number of e-mails to display:

If e-mail fields have been identified in the Report Columns section, this option determines the number of e-mails to display at one time.

Use Custom Layout Text and HTML:

When checked, QLR Manager applies the text that has been entered to create a custom report. If not checked, the custom text is ignored. This allows for easy toggling between viewing the custom report based on the custom text and viewing a normal tabular style report.

Use font settings:

There are three font settings that can be applied to the custom text on a global basis. These are the font face, color and size. If the User wants complete control over the appearance of their custom report, they can uncheck this option and include their own HTML tagging.

Custom Layout Text and HTML:

This is the text area where the actual Custom Layout text is entered. HTML tagging can be entered to produce a freeform layout with <report> and <chart> objects nested as desired. 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. Replacement value references can also be used.

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.

Basic Custom Layout:
The following report and chart will be used to demonstrate the features of Custom Layouts. Assume that the font color has been set to blue to help identify custom text output.
 
Chart
 
Customer Invoices
email last
name
first
name
invoice
number
amount
djohn@zero.com Johnson Daryl 1002 82.31 
  Johnson Daryl 1005 286.44 
Total for Daryl Johnson 368.75 
 
fjones@yahoo.com Jones Fred 1001 284.40 
Total for Fred Jones 284.40 
 
shimpy@aol.com Shimpimple Ferb 1004 43.40 
Total for Ferb Shimpimple 43.40 
 
1

Entering simple text:

If the text "This is custom text" is entered into the Custom Layout Text and HTML text area and the checkbox is selected to Use Custom Layout Text and HTML, the following output will be produced:

This is custom text
This is custom text
 
This is custom text
 
This is custom text

This is actually useless output, but it demonstrates how the entered text is generated for each row in the report. The blank spacing between rows is controlled by the Spacing after break setting.

Referencing column data:

If the text "This is custom text for &c3 &c2" is entered into the custom text area, it will produce the output:

This is custom text for Daryl Johnson
This is custom text for Daryl Johnson
 
This is custom text for Fred Jones
 
This is custom text for Ferb Shimpimple

The data for columns 2 and 3 in the report have been added using the Column referencing technique.

Including the report or chart:

If the following text and HTML is entered into the custom text area:

This is custom text before the report
<table border="0" cellspacing="0" cellpadding="2"><tr>
<td align="center"> <report> </td>
<tr></tr>
<td align="center"> <chart> </td>
</tr></table>
This text comes after

The resulting output will look like this:

This is custom text before the report
Customer Invoices
email last
name
first
name
invoice
number
amount
djohn@zero.com Johnson Daryl 1002 82.31 
  Johnson Daryl 1005 286.44 
Total for Daryl Johnson 368.75 
 
fjones@yahoo.com Jones Fred 1001 284.40 
Total for Fred Jones 284.40 
 
shimpy@aol.com Shimpimple Ferb 1004 43.40 
Total for Ferb Shimpimple 43.40 
 
1
Chart
This text comes after
A few things to note:
 •  The tags <report> and <chart> are used to embed the report and chart into the output.
 •  A tabular report is the default output produced by QLR Manager. If a <chart> is desired, the appropriate selections must be made in the Report Charting section of the  Layout  panel.
 •  HTML tagging is used to enhance the Layout by centering the report and chart in their own table cells.
 •  Leading and trailing text was added before and after the embedded report and chart.
 •  The custom text no longer repeats for each data row. If the <report> or <chart> tags are found in the custom text area, the custom text is only used once per instance of the embedded report or chart.
Distribution e-mail

Distribution e-mail output is created when one or more fields are selected for use as an e-mail address in the Report Columns section of the  Layout  panel. This designation is applied by choosing either "Email to:", "Email cc:" or "Email bcc:" as the Format or use selection. These e-mail choices only appear when the report column is a data type of text. In order for a distribution e-mail to occur, at least one field must be designated with an "Email to:" selection. Multiple entries for Email to, Email cc and Email bcc can be chosen. Any change in the e-mail addresses associated with chosen fields will trigger a new e-mail to be produced.

The actual sending of distribution e-mails is initiated by either choosing the E-mail distribution option in the Report Tools window or by specifying an Output type of "Distributed email" in the E-mail Specifications section of the Define Macro panel. Both options require that the hosting server is running Simple Mail Transfer Protocol (SMTP).

Distribution e-mails go hand in hand with custom report layouts. It would be very rare to define a distribution e-mail and not use a Custom Layout. Additionally, distribution e-mails impact the way in which data is presented in a Custom Layout. When a new set of e-mail addresses is encountered, QLR Manager forces the page to end. For example, if the following text is entered into the Custom Layout Text and HTML text area:

&c3, here is your invoice information totaling $&b6:
<report>

The output resulting from a format of "Email to:" selected for column 1 would look like this:

Daryl, here is your invoice information totaling $368.75:
Customer Invoices
email last
name
first
name
invoice
number
amount
djohn@zero.com Johnson Daryl 1002 82.31 
  Johnson Daryl 1005 286.44 
Total for Daryl Johnson 368.75 
 
1
 
Fred, here is your invoice information totaling $284.40:
Customer Invoices
email last
name
first
name
invoice
number
amount
fjones@yahoo.com Jones Fred 1001 284.40 
Total for Fred Jones 284.40 
 
1
 
Ferb, here is your invoice information totaling $43.40:
Customer Invoices
email last
name
first
name
invoice
number
amount
shimpy@aol.com Shimpimple Ferb 1004 43.40 
Total for Ferb Shimpimple 43.40 
 
1
 
A few things to note:
 •  A new page with a page header and footer is produced for each e-mail. An e-mail can contain reports that have multiple pages.
 •  The custom text is rendered for each e-mail and &c3 is used to reference the first name value in column 3.
 •  The break level amount (the total amount in column 6) was referenced using &b6. &b is used to retrieve break level data.
 •  The report page will have a white background, providing a visual indicator that this is a distribution e-mail. Also, be aware that distribution e-mails will be sent with a white background since many e-mail clients do not support page background colors.
Other helpful hints:
 •  If the custom text includes a <chart> reference and a chart was defined in the Layout, a new chart will be generated for each e-mail.
 •  If output is desired with multiple reports or charts, advantage can be taken of the way distribution e-mails trigger the creation of individual output for each unique set of e-mail addresses. This works even if the field referenced as an e-mail address field, such as the Break1 entry, is not really an e-mail address. This also requires that the report or chart be formatted with a Custom Layout, which can be accomplished by entering as little as <center><chart><report></center> into the custom text area.
 • 

Printer page breaks are inserted between each e-mail. This allows the User to print e-mails as though they were form letters. If there are many e-mails to print, it may be helpful to increase the number of e-mails to a large number.

Report Columns

The Report Columns section of the  Layout  panel contains the most important selections for formatting a report. The options presented provide the User with extensive control over the appearance of the report and format of the data.

Heading text:

The Heading text is the text to be displayed at the top of each column. The default value is the column name selected in the query. A multiple line heading can be created by using the HTML break tag, <br />, or by using the underscore ( _ ) character. The "_" will be interpreted as the HTML break. Database column names that include "_" will therefore automatically appear on multiple lines. Both "First_Name" and "First<br />Name" will result in a column heading of:

First
Name

Each column as selected in the query is listed as a row of information in the Report Columns section.

HTML tagging can be used in the Heading text field as well, such as <u> to underline the text.

General formatting, such as the font type, size, color, and background color can be applied to all of the columns as a single group. These settings are found in the Column headings area of the Titles & Footers section.

Display order:

The order that columns of data appear in a report is based upon the order that they appear in the query that produced the report. This order can be changed by entering a value for Display order. If the User wishes to move two columns from the middle of the report to the left edge of the report, this can be accomplished by entering 1 and 2 into the two columns to be moved. To move columns to the end (right side) of a report, use a display order of greater than 1000 to move them to the right, such as 1001 and 1002. All columns that are not given a display order value will remain in their original position.

If the Report data cache Report data cache1.9 MB icon is visible beneath the  Log off  button, the display order field can also be used to indicate that the data in this column should be sorted. Including "+" will sort data in ascending order, whereas including "-" will sort the data in descending order. Multiple columns can be included in the sort. The primary sort will be the left most displayed column where a sort is defined, with the secondary sort being the next displayed column with a sort defined, etc.

Display
order
What it does
+ Sort report data based on this column in ascending order. This only works when the data caching icon is present.
1- Display this as the first column and if the data caching icon is present, it will be sorted in descending order.
Note: The sorting indicator follows the display order reference number.
2.5 Displays the column between the columns numbered 2 and 3. Decimals can be used to insert columns in between existing display ordering.

Multiple row report:

Multiple row reporting allows for the "wrapping" of column data so that a single row of data will appear over multiple lines. This is accomplished by entering key information into the Display order field. The format for the data entered into the Display order field is:

display order, +, column span

Entering a "+" in the second position indicates a start of a new line at that column. Some examples of key information entered into the Display order field:

Display
order
What it does
3 Sets the display order to 3.
3+,+ Sets the display order to 3 and if the Report data cache icon is present, it will sort this column in ascending order. Start a new row at this column.
,+ Display order is the default order. Start a new row at this column.
,+,4 Display order is the default order. Start a new row at this column. It's column span is 4, which means that it spans four data columns in the report.
,,3 Display order is the default order. A new row is not started. It's column span is 3, which means that it spans three data columns in the report.
3,,3 The Display Order is set to 3. A new row is not started. It's column span is 3, which means that it spans three data columns in the report.

Below is an example of a multiple line report. The Display order entry for the FORM ACTION column is ",+". The entry for the ORIGINAL DATA and NEW DATA columns is ",+,4":

Note: It may be necessary to adjust the Rows per page setting when multiple row reports are produced.

Alignment:

This attribute controls how data will be displayed in a report column, either Left, Right, or Centered. The Auto (default) selection will automatically display character data to the Left and numeric data to the Right.

Vertical alignment:

This attribute controls the vertical alignment of data in a report column. Data can be vertically aligned to the Top, Middle or Bottom of its table cell.

Action:

There are numerous actions that can be performed to manipulate column data in a report layout:

Action
name
What it does
None No Action performed. This is the default value.
Break 1-5 A Break is performed when the value in the column changes from the previous row's value. Breaks should only be used on columns that have been properly sorted by the query. It allows for the calculation of subtotals, etc. The data in the Break column will be displayed in an "Outline" format, meaning only the value of the first row of the break column will be displayed. Showing the value for all rows of data is controlled by the "Outline" feature in Report Breaks.
Hidden
Break 1H-5H
Hidden breaks work the same as regular breaks, except that the column is hidden from view in the report. The information can still be referenced using the &column number technique. This permits a break column to be hidden in the report, but still allows the data to be referenced in the Report Break's "Text before the break". An example might be to choose a hidden break for column 1, such as Break1H, and then reference the data in the Break #1 "Text before the break" by entering "Data for &n1". This will display the next value found in column 1.
Group

The Group action allows for the compressing of data into a single row. For example, if another column in the report is being "Summed", then the summed value for the column will be displayed as a single value for the Group. As with the Break action, it should only be used on data that has been sorted properly by the query. Columns that have an Action type of "None" or "Hide" will not be displayed in the report when grouping is present. It is possible to Group more than one column in a layout.

Note: The grouping of large amounts of data may actually exceed the PHP processing time limit set on the hosting server. It is recommended that the grouping of large amounts of data be performed with the SQL query, not the Layout.

Table The Table action allows for the creation of a "Table" report. When the Table action is used, the values found in the column where "Table" is specified are spread horizontally across the report as their own columns. The "Group" action must also be used at least once, and at least one column action, such as "Sum" or "Count", must be specified for the data in another column. The "Break" action can be used in conjunction with the "Table" action. Please refer to Table layout for more information.
Table- The Table- action works the same as that Table action, except the output is minus the Total columns that appear on the right side of a Table layout.
Pivot This selection will transpose column data into rows of data in a report.
Hide The Hide action will hide columns from being displayed on a report.
Sum Sum will add up numbers. It only works for columns that have been defined as a numeric column in the database. If applied to non-numeric column, it will be ignored.
Cum sum Cumulatively adds up numbers. At each break, the accumulation is reset back to zero. It only works for columns that have been defined as a numeric column in the database. If applied to a non-numeric column, it will be ignored.
Average Average calculates the average value of numbers. It only works for columns that have been defined as a numeric column in the database. If applied to a non-numeric column, it will be ignored.
Count The Count action counts the number of rows in the query results.
Minimum Minimum displays the minimal value for a column.
Median Median displays the middle value for a column, as if the values are sorted numerically or alphabetically, and this is the value in the middle of that list.
Mode Mode displays the most frequently occurring value in that column.
Mode(cnt) Mode displays the most frequently occurring value in that column, along with the count of the number of occurrences.
Maximum Maximum displays the maximum value for a column.
First First displays the first value found in a column.
Formula Allows for a report column that utilizes an "Evaluated Formula" to be included in a report that uses the Layout's Grouping Action. This may be necessary when report output is Grouped, because columns without a Layout Action (such as Sum, Maximum, etc.) will not appear in the report. This forces the column to display when an Evaluated Formula is present. The Evaluated Formula will be applied to both the individual rows and the Break summary level data. The Formula Action is ignored for Table and Pivot reports.
Last Last displays the last value found in a column.
List

List works only when the Group function has been applied to another column. List will create a sorted comma separated list of the unique values found in that column. For example, using the Group function on the city column would allow you to create a list of zip codes for that city, such as 13760, 13761, 13763. The List function only produces output at the row level within a report. Break level data will be blank.

Hint: You may want to use a Format of "Word wrap" to format longer result lists.
Unique This is the count of the number of unique entries. When Breaks are present, it counts the unique entries in each Break section. The Final total calculates the number of unique entries for the entire report. This means that if two different Breaks have the same value, each Break will count it as a unique entry, whereas it will be counted only once in the Final total.
% of break Calculates and displays the % of the Break1 level of data. Each Break1 will add up to 100%.
% of total Calculates and displays the % of total for each row in the column.

Format or use:

Formatting provides control over how the data in a report field appears. The available formatting options depend on the type of data selected in the query. There are 3 basic types of data for formatting. These types are character, numeric and date/time data.

Character data can be formatted as follows:

Format
option
What it does
General Displays content as it appears in the database.
File The File option is available with binary data. When applied, the content of this field is stored in the QLR Manager /reports directory as a file. The displayed value is the URL which references the file, such as "http://www.yoursite.com/qlr/reports/qlr220831Ck5V074.xls". The URL can then be used to access the file. One way of providing access in a report is to use an Evaluated formula that creates an HTML anchor href tag:

$value='<a href="'.$value.'" target="_blank">click here</a>'

A file extension that represents the data content type can be applied by using one of two methods. The first is to reference a report column in the data that contains the extension. The second method is to enter the extension into the Column Width field, such as "pdf". If no extension is desired, the Column Width can be set as blank.

Formatted Formatting will render text as though it contains HTML tagging for line breaks. Line feed characters found in the raw text input will be replace by HTML break tags (<br />). This prevents the unwanted word wrapping of text.
Textarea The Textarea option allows the User to specify an HTML textarea box to display large amounts of information in a report. The textarea will include a scroll bar and be "readonly". The size of the textarea is controlled by entering the desired width and height into the Column width field, separated by a space. An entry of 30 4 will produce a textarea of 30 characters wide by 4 lines high.
Truncate Truncate will limit the number of characters that will display in a cell. The Column width setting will determine the number of characters that will be displayed.
Unserialize This option will take data that is stored as a PHP "serialized" array, unserialize it and display it in a readable format.
Word wrap This allows for the wrapping of larger amounts of textual data within a report column. Text will wrap within the boundaries as set by the Column width. The default value is to prevent wrapping in a report cell, which is equivalent to and uses, the HTML "NOWRAP" attribute. If data is allowed to wrap in a report cell, it can greatly affect the page length for printing purposes.
Image For fields that have been defined as binary data (BLOB) fields, the Image option will appear and when selected, will display the data in the field as an image. The size of the image, its height and width, can be controlled by entering pixel values in the Column width field. For example, if the User wishes to display the images as 200 pixels high by 300 pixels wide, 200x300 should be entered into the Column width field. The "x" or "X" must be used as the separator value between the height and width. If only a one number is provided, it will be treated as the width in pixels. QLR Manager will read the file contents to try and determine the ratio of the height to the width and calculate the height to preserve the original aspect ratio. If the height cannot be determined, the single value provided will be used as both the width and the height. Since images are data intensive, only a certain number (dependent upon the number of bytes in the images) can be displayed. Once the limit is reached, the message "Image limit exceeded" will be displayed, instead of an image. The amount of memory allocated for Image cache size is a setup option defined during the QLR Manager install process.
Image URL

This option is used to build an HTML image tag out of a text string that is referencing the location of an image file. For example, a string such as:

http://mysite.com/employees/jeff.jpg

would be placed into an img tag as:

<img src="http://mysite.com/employees/jeff.jpg" width="100" height="150">
The size of the image, its height and width, can be controlled by entering pixel values in the Column width field. For example, if the User wishes to display the images as 200 pixels high by 300 pixels wide, 200x300 should be entered into the Column width field. The "x" or "X" must be used as the separator value between the height and width. If only a one number is provided, it will be treated as the width in pixels. QLR Manager will read the file contents to try and determine the ratio of the height to the width and calculate the height to maintain the original aspect ratio. If the height cannot be determined, the single value provided will be used as both the width and the height.
Email to: This selection designates the field as an e-mail address. This would normally be used for Distribution e-mails, but could also be used to produce output with multiple reports or charts by taking advantage of the way distribution e-mails trigger the creation of new output for each unique set of e-mail addresses. This works even if the field referenced as an e-mail address field, such as the Break1 entry, is not really an e-mail address. This also requires that the report be formatted with a Custom Layout.
Email cc: This selection designates the field as an e-mail address to be used as a carbon copy.
Email bcc: This selection designates the field as an e-mail address to be used as a blind carbon copy.
Barcode 25

Interleaved 2 of 5 is based on Standard 2 of 5 symbology. Allowable characters: [0-9].

The characteristics of barcodes for all the barcode types can be controlled by adding the following parameters into the Column width field, separated by commas:

[display width],
[barcode height in pixels. Default = 80, Min height = 30,Max height= 300],
[font size 0-5. 0 is no text. Default = 2],
[barcode color. 6 character hex code. Default = 000000 (black)],
[barcode background color. Default = FFFFFF (white)],
[Color of text when font !=0. Default = 000000 (black)],
[barcode orientation. Default = horizontal. v = vertical]

15, 80, 0 would set the column width to 15 characters. The barcode will be 80 pixels high with no text displayed below.

25,,,0000FF would set the column to 25 characters wide and sets the barcode color to blue.
Barcode 39

Known also as USS Code 39, 3 of 9. Code 39 can encode alphanumeric characters. The symbology is used in non-retail environment. Allowable characters:

[0-9], [A-Z], -, ., (space), $, /, +, %
Barcode 128

Code 128 is a high-density alphanumeric symbology. Used extensively worldwide. Code 128 is designed to encode 128 full ASCII characters. The symbology includes a checksum digit. Allowable characters:

[0-9], [A-Z], [a-z], :, ;, [, ], \, ^, _, `, {, }, |, ~, (space), !, ", #, $, %, &, *, +, ., -, /, (comma), <, >, =, @, ?
Barcode codabar

Known also as Ames Code, NW-7, Monarch, 2 of 7, Rationalized Codabar. Codabar was developed in 1972 by Pitney Bowes. Inc. This symbology is useful to encode digital information. It is a self-checking code, there is no check digit. Allowable characters:

[0-9], -, $, :, /, ., +, A, B, C, D

Numeric data can be formatted as follows:

Format
option
What it does
General Displays content as it appears in the database. The number of decimal places is dependent upon the raw data. Negative numbers will be shown as -123.45.
1234.56 This allows for a fixed number of decimal places. A period is used as the decimal point. Negatives are shown as -1234.56.
1,234.56 This format displays numbers with a fixed number of decimal places and a comma in the thousands position. 12345.6789 is displayed as 12,345.67 when this formatting is chosen along with the Decimals set to 2. Negative numbers are shown as (12,345.67).
$1,234.56 This represents currency formatting. Currency formatting displays numbers with a fixed number of decimal places, commas as the thousands separators and a period as the decimal point. The Currency symbol and its placement before or after the number, is set by the User in the Report Body section. Negative values are displayed as ($12,345.56).
1234,56 This allows for a fixed number of decimal places. A comma is used as the decimal point. Negatives are shown as -1234,56.
1.234,56 This option displays numbers with a fixed number of decimal places and a period in the thousands position.  12345.6789 is displayed as 12.345,67 when this formatting is chosen along with the Decimals set to 2. Negative numbers are shown as (12,345.67).
$1.234,56 This currency formatting option displays numbers with a fixed number of decimal places, a period as the thousands separators and a comma as the decimal point. The Currency symbol and its placement before or after the number, is set by the User in the Report Body section. Negative values are displayed as (£12,345.56).
% This displays the values as percentages. 1.0845 would be displayed as 108.45%.
Barcode 25

Interleaved 2 of 5 is based on Standard 2 of 5 symbology. Allowable characters: [0-9].

The characteristics of barcodes for all the barcode types can be controlled by adding the following parameters into the Column width field, separated by commas:

[display width],
[barcode height in pixels. Default = 80, Min height = 30,Max height= 300],
[font size 0-5. 0 is no text. Default = 2],
[barcode color. 6 character hex code. Default = 000000 (black)],
[barcode background color. Default = FFFFFF (white)],
[Color of text when font !=0. Default = 000000 (black)],
[barcode orientation. Default = horizontal. v = vertical]

15, 80, 0 would set the column width to 15 characters. The barcode will be 80 pixels high with no text displayed below.

25,,,0000FF would set the column to 25 characters wide and sets the barcode color to blue.
Barcode 39

Known also as USS Code 39, 3 of 9. Code 39 can encode alphanumeric characters. The symbology is used in non-retail environment. Allowable characters:

[0-9], [A-Z], -, ., (space), $, /, +, %
Barcode 128

Code 128 is a high-density alphanumeric symbology. Used extensively worldwide. Code 128 is designed to encode 128 full ASCII characters. The symbology includes a checksum digit. Allowable characters:

[0-9], [A-Z], [a-z], :, ;, [, ], \, ^, _, `, {, }, |, ~, (space), !, ", #, $, %, &, *, +, ., -, /, (comma), <, >, =, @, ?
Barcode codabar

Known also as Ames Code, NW-7, Monarch, 2 of 7, Rationalized Codabar. Codabar was developed in 1972 by Pitney Bowes. Inc. This symbology is useful to encode digital information. It is a self-checking code, there is no check digit. Allowable characters:

[0-9], -, $, :, /, ., +, A, B, C, D

Date and Time data can be formatted as follows:

In addition to formatting data in the column of a report, these options can be used to include the server's current date or time in the Report's Title, Footer or Breaks by using the replacement strings as listed with each option. See Referencing column information for more information about replacement reference values.

Note: The Date separator option found in the Report Body section determines the character displayed as the separator for the selected format.

Format
option
What it does
General Displays content as it appears in the database.
y/m/d h:m:s Date time displays data in YYYY/MM/DD HH:MM:SS format. May 6th, 2002 2:29 pm 48 seconds is displayed as 2002/05/06 14:29:48. This format can be accessed using the &DT16 replacement string.
y/m/d h:m:s pm Date time displays data in a year / month / day hour:minutes:seconds am/pm format. May 6th, 2002 2:29 pm 48 seconds is displayed as 2002/05/06 2:29:48 pm.
m/d/y h:m:s Date time displays data in a month / day / year hour:minutes:seconds format. May 6th, 2002 2:29 pm 48 seconds is displayed as 05/06/2002 14:29:48.
m/d/y h:m:s pm Date time displays data in a month / day / year hour:minutes:seconds am / pm format. May 6th, 2002 2:29 pm 48 seconds is displayed as 05/06/2002 2:29:48 pm.
d/m/y h:m:s Date time displays data in DD/MM/YYYY HH:MM:SS format. May 6th, 2002 2:29 pm 48 seconds is displayed as 06/05/2002 14:29:48. This format can be accessed using the &DT1 replacement string.
d/m/y h:m:s pm Date time displays data in a month / day / year hour:minutes:seconds am / pm format. May 6th, 2002 2:29 pm 48 seconds is displayed as 06/05/2002 2:29:48 pm.
12/31/2001 This will display May 6th, 2002 2:29 pm 48 seconds as 05/06/2002. This format can be accessed using the &DT2 replacement string.
2001/12/31 This will display May 6th, 2002 2:29 pm 48 seconds as 2002/05/06. This format can be accessed using the &DT3 replacement string.
Dec/31/2001 This will display May 6th, 2002 2:29 pm 48 seconds as May/06/2002. This format can be accessed using the &DT4 replacement string.
12/31 This will display May 6th, 2002 2:29 pm 48 seconds as 05/06. This format can be accessed using the &DT5 replacement string.
10:59:59 PM This will display May 6th, 2002 2:29 pm 48 seconds as 2:29:48 PM. This format can be accessed using the &DT6 replacement string.
10:59 PM This will display May 6th, 2002 2:29 pm 48 seconds as 2:29 PM. This format can be accessed using the &DT7 replacement string.
22:59:59 This will display May 6th, 2002 2:29 pm 48 seconds as 14:29:48. This format can be accessed using the &DT8 replacement string.
22:59 This will display May 6th, 2002 2:29 pm 48 seconds as 14:29. This format can be accessed using the &DT9 replacement string.
22 This will display May 6th, 2002 2:29 pm 48 seconds as 14. It is used to group time oriented data by the hour, using a 24 hour clock.
59 This will display May 6th, 2002 2:29 pm 48 seconds as 29. It is used to group time oriented data by the minute.
31/12/2001 This will display May 6th, 2002 2:29 pm 48 seconds as 06/05/2002. This format can be accessed using the &DT10 replacement string.
31/Dec/2001 This will display May 6th, 2002 2:29 pm 48 seconds as 06/May/2002. This format can be accessed using the &DT11 replacement string.
2001/31/12 This will display May 6th, 2002 2:29 pm 48 seconds as 2002/06/05. This format can be accessed using the &DT12 replacement string.
31/12/01 This will display May 6th, 2002 2:29 pm 48 seconds as 06/05/02. This format can be accessed using the &DT13 replacement string.
2001/12 This will display May 6th, 2002 2:29 pm 48 seconds as 2002/05.
2001/Q4 This will display May 6th, 2002 2:29 pm 48 seconds as 2002/Q2.
2001/52 This will display May 6th, 2002 2:29 pm 48 seconds as 2002/19.
12/31/01 This will display May 6th, 2002 2:29 pm 48 seconds as 05/06/02. This format can be accessed using the &DT14 replacement string.
31/12 This will display May 6th, 2002 2:29 pm 48 seconds as 06/05. This format can be accessed using the &DT15 replacement string.
Monday This will display May 6th, 2002 2:29 pm 48 seconds as Monday. This format can be accessed using the &DT17 replacement string.
Dec/01 This will display May 6th, 2002 2:29 pm 48 seconds as May/02. This format can be accessed using the &DT18 replacement string.
01/Dec This will display May 6th, 2002 2:29 pm 48 seconds as 02/May. This format can be accessed using the &DT19 replacement string.
December This will display May 6th, 2002 2:29 pm 48 seconds as May. This format can be accessed using the &DT20 replacement string.
May 5, 2012 This will display May 6th, 2002 2:29 pm 48 seconds as May 6, 2002. This format can be accessed using the &DT21 replacement string.
May 5th, 2012 This will display May 6th, 2002 2:29 pm 48 seconds as May 6th, 2002. This format can be accessed using the &DT22 replacement string.

Decimals:

The number of decimal places to be displayed for numeric data. 0 to 9 decimal places can be set. The "Auto" setting will automatically float the number to however many decimal places the number produces.

Column width:

The Column width sets the approximate number of characters to display in the column. If set to blank or zero (0), the column will not be displayed in the report. Text will only be wrapped within the cell if Word wrap is selected for the "Format or use". The values for the column widths are calculated based on the largest value found in the column, either its data or the column title. The calculation is an approximation based on the font size, font type and character count. It is not an exact number. Using a monospace font such as Courier 10pt will map the character count close to the calculated size.

Chart use:

The Chart use selections specify which columns to use as Labels or data Plots for a graph. Please reference the separate topic about Report Charting for more information.

Report links:

Report links allow for the launching of a new report, based on an existing query and layout, by clicking a link within a report. The link text is passed as a Query variable to the query associated with the link. Links can be defined by clicking on the Create/Edit Report Links icon found in this Report Columns section of the  Layout  panel.

Links are applied by assigning an existing link to a layout column. Both the Link owner and Link name must be entered. When mapping a link, the column number in the layout to which that link applies must be selected. This is accomplished by using the Col # selections found to the left of the Link owner field. If report links have been previously defined, they can be accessed by clicking the Links button.

See Linked Reports for more information about how links are defined.

Column coloring:

The color for a specific column in the report can be set by using the Color selector to select the Text color and the Background color. The colors will be applied to the Column Headings and the Data Rows, depending upon the checkboxes that are checked. The colors are only applied when a Col # is selected and either the Head and/or Data checkbox is checked. When Data is checked and a function has been applied to the column, the break data is also colored. The colors will not be applied to the rows used as Spacing after break. See below how to conditionally change a column's colors. If no condition is provided, the designated colors will be applied to all the cells in the column.

Conditional column coloring:

The colors for a data element can be set to be changed under certain conditions. This is accomplished by selecting a conditional comparison operator and proving a comparison value.

Note: The conditional coloring only applies to data rows. It is not applied to break data or Table totals.

An example when used in a Table Layout:

OperatorDescription
=Equal to one of the values entered. Multiple values can be entered separated by commas.
!=Not equal to any value entered. Multiple values can be entered separated by commas.
>Greater than. One value is entered.
>=Greater than or equal to. One value is entered.
<Less than. One value is entered.
<=Less than or equal to. One value is entered.
btBetween. Two values are entered separated by a comma. The data in the report cell must be greater than or equal to the first value and less than or equal to the second value.
!btNot between. Two values are entered separated by a comma. The data in the report cell must be less than the first value or greater than second value.
%v%Contains. The string value is found in the report cell. Multiple values can be entered separated by commas. The "%" represents a wild card search. The "v" represents the search value.
!%v%Not contains. The string value is not found in the report cell. Multiple values can be entered separated by commas.
v%Begins with. The value in the report cell begins with the value entered. Multiple values can be entered separated by commas.
!v%Not begins with. The value in the report cell does not begin with the value entered. Multiple values can be entered separated by commas.
%vEnds with. The value in the report cell ends with the value entered. Multiple values can be entered separated by commas.
!%vNot ends with. The value in the report cell does not end with the value entered. Multiple values can be entered separated by commas.
(F)

This is custom formula that uses PHP snytax. The data in the report cell is referenced by $value. Equality in PHP is evaluated based on a double equals such as $value == 'mark', or abs($value) == 12. Other columns in that data row can also be referenced, such as $val[2] - $val[5] > 15. Following is an example where the background color in column 1 has been changed based on a formula:

Evaluated formula:

QLR Manager is written in a language called PHP. The evaluated formula area allows the User to define a valid PHP command to be executed using the PHP eval() function. The variable name that represents the data to be displayed in that field is named $value.

Note: Formula evaluation takes place prior to data formatting options being applied to the data. Although this provides greater user control, it may result in some formatting issues that can be overcome by formatting the data in the formula itself. Here are some examples of how to manipulate data in the field. The evaluated formula only changes the way the data is displayed, not its underlying value.

When defining an evaluated formula, the column number in the layout to which that formula applies must be selected. This is accomplished by using the Col # selections found to the left of the Link owner field.

Desired resultEvaluated formula
To change the text from upper to lower case$value=strtolower($value)
To select a sub string of the first 15 characters in a field$value=substr($value,0,15)
To format numbers with a European style, with 2 decimal places$value=number_format($value,2,',','.')
To format a date field to a particular format$value=date('d M, Y',strtotime($value))
To conditionally change the text from one value to anotherif ($value=='CO') $value='Colorado'
To replace text line breaks with HTML line breaks$value=str_replace(chr(10),'<br />',$value)
It is also possible to reference the values in other columns with a formula. Other columns are referenced by using $val[column number]. For example, if a report contains a last name in column 3 and it is desired to highlight the data in column 5 in red where the last name is "doyle", the following evaluated formula could be entered for column 5:

if ($val[3]=='doyle') $value='<font color="red">'.$value.'</font>'

Although data in other columns can be referenced, only the data in the column where the evaluated formula is entered can be changed by referencing that data using the $value variable name. Some of the basics for writing a formula are as follows:
 •   The language is case sensitive, using lower case for commands.
 •  The "if" statement conditions are enclosed in parenthesis, such as if ($value > 100).
 •  "&&" represents logical AND conditions. "||" is used for logical OR. For example, if ($value > 100 && ($val[2]=='CO' || $val[2]=='NY')) is read as: if the value in the column where the formula is written is greater than 100, and the value in column 2 is either "CO" or "NY".
 •  The equals operator is "==" and not equal is "!=". A single equal sign assigns a value to a variable.
 •  A period is used to concatenate strings together, such as '<b>'.$value.'</b>' This would result in displaying the value as bold.
The prior row's data can also be accessed. $pvalue is the reference for the current column's prior row data and $pval[] is the array reference for all of the fields. For example, a formula can be written as:

if ($val[2] != $pval[2]) $value='This is a new customer number'

By default, the evaluated formula is executed on the row level of the initially selected data. This allows for the manipulation of the raw data. Let's call this level 0 (zero). There are additional levels of data that the Layout represents: Grouped data (level 1), Break data (level 2), and the Final data (level 3). It is possible to tell QLR Manager which level(s) of data that the evaluated formula is to act upon. This is accomplished by adding $level = [level number]; to the start of the evaluated formula.

Desired levelEvaluated formula, with level prefix
Group and Breaks$level = 12;$value=$val[2]/$val[4]
Raw data and Final. Notice how 03 is not used,
as $level will evaluate to = 3 and the zero is lost.
$level = 30;$value=$val[2]/$val[4]
Raw data, Group, Breaks and Final. All four levels specified.
The order of the numbers does not matter, other than 0 cannot be first.
$level = 1302;$value=$val[2]/$val[4]
Grouped column headings

Grouped column headings provide the ability to create a cell that can span one or more columns with its own heading text and colors. A "Start column" can be selected along with the number of "Columns to span" and the "Heading text" to be displayed above the columns. Alignment and colors can be applied to the column heading. It is also possible to extend the selected foreground and background colors through either the head of the report or through the columns of data by checking the "Head" and "Data" checkboxes.

The Heading text input will accept HTML and inline styles to further refine the Report output. For example, if a taller header tab is desired, the inline style="line-height:x" can be applied to the element. To create a header tab with a height of 25px, HTML like the following could be entered into the Heading text field:

<p style='line-height:25px'>Grouped columns</p>

Note: Any quoted HTML must be entered using single quotes.

If PDF output is desired for a report with Grouped column headings, a slightly different approach is required to achieve a taller header tab. Since mPDF is used to create PDF output and does not respond to line-height applied to an element within a table cell, HTML like the following could be entered to achieve a similar result that is compatible with both browser rendering and PDF output creation:

<font style='font-size:2px'><br /></font>Grouped columns<font style='font-size:2px'><br />&nbsp;</font>

Group Column Headers are designed to work with standard reports and the Grouped columns in a Table Layout. It is not intended to work with Pivot Layouts or multi-row report Layouts.

Below is an example of adding two column grouping tabs. Notice how the second tab has the checkboxes checked to extending the colors into the column header and data rows.


The resulting report:


Creating a table layout

A Table layout is a powerful layout action. It allows the User to create two dimensional reports by taking the data values found in the column specified with the Table or Table- action and create new report columns for each of these values, spreading them horizontally across the page. For example, if a column exists that contains a category code for each of the items in an order, a Table style layout could be created with a column to "Sum" both the inventory value and the quantity of items for each category, and tabulate the totals for each. The Table action will also produce Total columns on the right hand side of the report. A Table- layout will be minus these Total columns.

A sample report of data prior to Table formatting could look as follows:

ordnum partnum category qty amount
1001 craw1 Crawdads 3 11.85
1001 dubcr Rods 1 14.00
1001 ragd3 Reels 1 59.99
1001 squ65 Soft Baits 2 5.90
1002 smonst42 Soft Baits 3 8.85
1002 squ65 Soft Baits 6 17.70
1003 craw3 Crawdads 1 3.95
1003 dlwg25 Rods 1 20.00
1003 lbandito Crank Baits 5 25.75
1003 rcat3 Reels 1 32.00
1004 dwscr Rods 1 14.00
1

When the Action selections are changed in the Report Columns section of the Layout as depicted below, a Table layout report is produced:

The resulting report is a Table layout:

A few important things to be aware of when using the Table layout action:
 •   The Table action can be used on only 1 column. The first column found with the Table action will be used. Other instances will be treated as a "None" action.
 •  At least one Group action must be used on another data column. If a Group is not specified, an error message will be displayed on the  Layout  panel when attempting to apply the Layout.
 •  Breaks can be used in a Table layout.
 •  At least 1 data column must have an action on which the column can calculate a value, such as "Sum". Multiple calculation actions can be used with the Table action.
 •  If more than one action is specified, the data for each Table value listed horizontally across the page will be separated with a heavy vertical line in the report.
 •  Columns that do not have an action are excluded from the report.
 •  It is acceptable to use a "Format" option on the column where the "Table" action is being used. This can be extremely useful for tables created using date data. For example, using the 'December' format (which indicates to QLR Manager to extract the month name from a date field), will create a Table layout, sorting the months from January - December. It works similarly when using 'Monday' as a date format. This will sort the days of the week as Monday - Sunday, as the table headings.
 •  If headings are included when producing a CSV output file, all 3 heading lines from the Table layout will be included in the CSV file.
 •  When an XML file is produced, all 3 title levels, separated by a ".", will be used to produce a unique field name. In the example above, the amount for "Crank Baits" on order number 1003 is $25.75 and will have an XML field name of category.Crank_Baits.amount. The User can change the name by altering the Heading text for the Report Columns in the Layout.
Creating a pivot layout

A Pivoted layout is similar to a Table layout in that it spreads the "Pivot" column values horizontally across the page. But unlike the Table layout which creates a column for each of the fields that contain a form Action such as Sum, a Pivoted layout creates a row of data for each of the fields that are assigned a layout action.

In the following example, a simple set of data has been selected and formatted with a Break #1 on the Region, and Sum for the Plan and Actual columns:

Region Month Qtr Plan Actual
Southeast January 1 9.0  6.0 
  February 1 7.0  8.0 
  March 1 9.0  11.0 
  April 2 11.0  13.0 
  May 2 8.0  9.3 
  June 2 12.0  14.0 
Break 1 56.0  61.3 
 
Southwest January 1 22.4  23.1 
  February 1 29.0  26.5 
  March 1 25.4  22.3 
  April 2 24.4  20.3 
  May 2 22.0  21.0 
  June 2 26.0  19.0 
Break 1 149.2  132.2 
 
Final 205.2  193.5 
1

To illustrate the difference between the Table layout and the Pivot layout, the above report was formatted using a Table layout. Only the first 3 months are shown to limit the width of the example. An Action of Table was selected for the Month column and Group was selected for the Qtr, resulting in the following report:

Region Qtr Month
January February March Total
Plan Actual Plan Actual Plan Actual Plan Actual
Southeast 1 9.0  6.0  7.0  8.0  9.0  11.0  25.0  25.0 
Southeast 9.0  6.0  7.0  8.0  9.0  11.0  25.0  25.0 
 
Southwest 1 22.4  23.1  29.0  26.5  25.4  22.3  76.8  71.9 
Southwest 22.4  23.1  29.0  26.5  25.4  22.3  76.8  71.9 
 
Final 31.4  29.1  36.0  34.5  34.4  33.3  101.8  96.9 
1
If the Layout is adjusted to set the Action of the Month column to "Pivot", along with the following changes:
 •   The Action of the Qtr column set to "None".
 •   The Text at the break for Break #1 set to &C1 to reference the Region name.
 •   The use of break coloring is unchecked.

The following report is produced:

Region Pivoted
Columns
Month
January February March April May June Total
Southeast Plan 9.0  7.0  9.0  11.0  8.0  12.0  56.0 
  Actual 6.0  8.0  11.0  13.0  9.3  14.0  61.3 
 
Southwest Plan 22.4  29.0  25.4  24.4  22.0  26.0  149.2 
  Actual 23.1  26.5  22.3  20.3  21.0  19.0  132.2 
 
Final Plan 31.4  36.0  34.4  35.4  30.0  38.0  205.2 
  Actual 29.1  34.5  33.3  33.3  30.3  33.0  193.5 
1

Notice how both the Plan and action columns' data are rotated and displayed horizontally. Also notice how columns without an action (in this case, the Qtr column) are not part of the report. To include a column, the Group action can be used. Also, the text above the pivoted columns is displayed as "Pivoted Columns". This can be changed to the desired value ("Data" in the following example) by updating the appropriate field in the Column headings area of the Titles and Footers section:

Region Qtr Data
Month
January February March April May June Total
Southeast 1 Plan 9.0  7.0  9.0        25.0 
  1 Actual 6.0  8.0  11.0        25.0 
Southeast 2 Plan       11.0  8.0  12.0  31.0 
  2 Actual       13.0  9.3  14.0  36.3 
Southeast Plan 9.0  7.0  9.0  11.0  8.0  12.0  56.0 
  Actual 6.0  8.0  11.0  13.0  9.3  14.0  61.3 
 
Southwest 1 Plan 22.4  29.0  25.4        76.8 
  1 Actual 23.1  26.5  22.3        71.9 
Southwest 2 Plan       24.4  22.0  26.0  72.4 
  2 Actual       20.3  21.0  19.0  60.3 
Southwest Plan 22.4  29.0  25.4  24.4  22.0  26.0  149.2 
  Actual 23.1  26.5  22.3  20.3  21.0  19.0  132.2 
 
Final Plan 31.4  36.0  34.4  35.4  30.0  38.0  205.2 
  Actual 29.1  34.5  33.3  33.3  30.3  33.0  193.5 
1

To add a finishing touch, a title was added and the "Use break coloring" check box was checked, with the title colors applied to the Break text:

June YTD Plan vs Actual
Region Qtr Data
Month
January February March April May June Total
Southeast 1 Plan 9.0  7.0  9.0        25.0 
  1 Actual 6.0  8.0  11.0        25.0 
Southeast 2 Plan       11.0  8.0  12.0  31.0 
  2 Actual       13.0  9.3  14.0  36.3 
Southeast Plan 9.0  7.0  9.0  11.0  8.0  12.0  56.0 
  Actual 6.0  8.0  11.0  13.0  9.3  14.0  61.3 
 
Southwest 1 Plan 22.4  29.0  25.4        76.8 
  1 Actual 23.1  26.5  22.3        71.9 
Southwest 2 Plan       24.4  22.0  26.0  72.4 
  2 Actual       20.3  21.0  19.0  60.3 
Southwest Plan 22.4  29.0  25.4  24.4  22.0  26.0  149.2 
  Actual 23.1  26.5  22.3  20.3  21.0  19.0  132.2 
 
Final Plan 31.4  36.0  34.4  35.4  30.0  38.0  205.2 
  Actual 29.1  34.5  33.3  33.3  30.3  33.0  193.5 
1