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. The color of the outer border can also be set by using the color selector associated with the "Outer border color".

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 found above the report to move between page sets. A larger number will show more data, but it takes longer to display results.

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.

Note: Most new browsers have the Page Setup defaults configured to omit the printing of background colors and images. To print reports in full color, leave this option unchecked and configure the borwser's Page Setup to Print Background (colors & images).

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. Both the Data row colors and Alternate row backgound color can be defined.

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 1.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:

companyname country orderid shippeddate productid unitprice quantity total price
Vins et alcools Chevalier France 10248 2017-07-16 11 14.00 12 168.00
Vins et alcools Chevalier France 10248 2017-07-16 42 9.80 10 98.00
Vins et alcools Chevalier France 10248 2017-07-16 72 34.80 5 174.00
Toms Spezialitäten Germany 10249 2017-07-10 14 18.60 9 167.40
Toms Spezialitäten Germany 10249 2017-07-10 51 42.40 40 1696.00
Hanari Carnes Brazil 10250 2017-07-12 41 7.70 10 77.00
Hanari Carnes Brazil 10250 2017-07-12 51 42.40 35 1484.00
Hanari Carnes Brazil 10250 2017-07-12 65 16.80 15 252.00
Victuailles en stock France 10251 2017-07-15 22 16.80 6 100.80
Victuailles en stock France 10251 2017-07-15 57 15.60 15 234.00
Victuailles en stock France 10251 2017-07-15 65 16.80 20 336.00
Suprêmes délices Belgium 10252 2017-07-11 20 64.80 40 2592.00
Suprêmes délices Belgium 10252 2017-07-11 33 2.00 25 50.00
Suprêmes délices Belgium 10252 2017-07-11 60 27.20 40 1088.00
Hanari Carnes Brazil 10253 2017-07-16 31 10.00 20 200.00
Hanari Carnes Brazil 10253 2017-07-16 39 14.40 42 604.80
Hanari Carnes Brazil 10253 2017-07-16 49 16.00 40 640.00
1

If Break1 is applied to the orderid column and Sum is applied to the total price column, (also formatted to 2 decimal places) Auto arrange will place the orderid on the left side of the report and sort the data by orderid. In addition, the first three columns following the Break are also sorted in ascending order:

orderid companyname country shippeddate productid unitprice quantity total price
10248 Vins et alcools Chevalier France 2017-07-16 72 34.80 5 174.00 
  Vins et alcools Chevalier France 2017-07-16 42 9.80 10 98.00 
  Vins et alcools Chevalier France 2017-07-16 11 14.00 12 168.00 
Break 1 440.00 
 
10249 Toms Spezialitäten Germany 2017-07-10 51 42.40 40 1,696.00 
  Toms Spezialitäten Germany 2017-07-10 14 18.60 9 167.40 
Break 1 1,863.40 
 
10250 Hanari Carnes Brazil 2017-07-12 65 16.80 15 252.00 
  Hanari Carnes Brazil 2017-07-12 51 42.40 35 1,484.00 
  Hanari Carnes Brazil 2017-07-12 41 7.70 10 77.00 
Break 1 1,813.00 
 
10251 Victuailles en stock France 2017-07-15 65 16.80 20 336.00 
  Victuailles en stock France 2017-07-15 22 16.80 6 100.80 
  Victuailles en stock France 2017-07-15 57 15.60 15 234.00 
Break 1 670.80 
 
10252 Suprêmes délices Belgium 2017-07-11 33 2.00 25 50.00 
  Suprêmes délices Belgium 2017-07-11 60 27.20 40 1,088.00 
  Suprêmes délices Belgium 2017-07-11 20 64.80 40 2,592.00 
Break 1 3,730.00 
 
10253 Hanari Carnes Brazil 2017-07-16 49 16.00 40 640.00 
  Hanari Carnes Brazil 2017-07-16 31 10.00 20 200.00 
  Hanari Carnes Brazil 2017-07-16 39 14.40 42 604.80 
Break 1 1,444.80 
 
Final 9,962.00 
1

The columns following the Break could be reordered using the Display order setting. The country column could be pushed to the right by either numbering all the other non-Break columns to appear before it, or by giving the country column a display order greater than 1000, such as 1001. The Display order can also be specified with a decimal value. 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.

Row numbering ties:

When row numbering is added to a query, there may be times when you wish to have ties in the numbering that is presented. This can be accomplished by adding the Report Column number that indicates tie data. If there is more than one field that indicates a tie, then multiple Report Column numbers are separated by a comma.

For example, a query to display golf scores might look as follows. Notice how 1 as qlr_rownum was added to the start of the query to create a column that will contain the row numbering.

select 1 as qlr_rownum, player,hole,topar
from scores
order by topar
qlr
rownum
player hole topar
1 Thomas 15 -9
2 Casey 16 -8
3 Johnson 15 -8
4 Harmon 16 -7
5 Day 17 -7
6 Taylor 16 -4
1

With a little bit of formatting, and defining Report Column 4 as the tie field, the row numbering now contain ties:

place player hole to par
1 Thomas 15 -9
2 Casey 16 -8
2 Johnson 15 -8
4 Harmon 16 -7
4 Day 17 -7
6 Taylor 16 -4
1
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, by adding a report title with the company name by setting the title text to Orders for &n1. &n1 indicates that the next value found in report column 1 will be used in the title.

Orders for Hanari Carnes
companyname orderid country shippeddate productid unitprice quantity total price tax
Hanari Carnes 10250 Brazil 2017-07-12 41 7.70 10 77.00  6.16 
    Brazil 2017-07-12 51 42.40 35 1,484.00  118.72 
    Brazil 2017-07-12 65 16.80 15 252.00  20.16 
Total for order 10253 1,813.00  145.04 
 
Hanari Carnes 10253 Brazil 2017-07-16 31 10.00 20 200.00  16.00 
    Brazil 2017-07-16 39 14.40 42 604.80  48.38 
    Brazil 2017-07-16 49 16.00 40 640.00  51.20 
Total for order 10252 1,444.80  115.58 
Total for Suprêmes délices 3,257.80  260.62 
1

A few changes were made to enhance this report:

The companyname field was hidden by changing the Action to Break1H. The H signifies to hide the break column.
The Break Text for Break1H is ommited by entering <omit> as the Text at the Break entry.
The Text at the Break for Break2 (which is the orderid field) is set to:

Total for order &n3||<div style="text-align:center">End of invoice</div>

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.

Orders for Hanari Carnes
orderid country shippeddate productid unitprice quantity total price tax
10250 Brazil 2017-07-12 41 7.70 10 77.00  6.16 
  Brazil 2017-07-12 51 42.40 35 1,484.00  118.72 
  Brazil 2017-07-12 65 16.80 15 252.00  20.16 
Total for order 10253 1,813.00  145.04 
End of invoice
 
10253 Brazil 2017-07-16 31 10.00 20 200.00  16.00 
  Brazil 2017-07-16 39 14.40 42 604.80  48.38 
  Brazil 2017-07-16 49 16.00 40 640.00  51.20 
Total for order 10252 1,444.80  115.58 
End of invoice
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="6">Plus Sales Tax</td>
<td class="bdata8">$eval(number_format(&b9~,2))</td></tr>
<tr><td class="break" colspan="6">Total with Tax</td>
<td class="bdata8">$eval(number_format(&b8~+&b9~,2))</td>

Some important notes regarding the following example:

Total before Tax
This is typical break text that starts the break section.
||<td class="break" colspan="6">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 6 equals the number of columns that the data needs to span in this instance. QLR automatically adds an HTML </tr> tag to start of the custom text if the tag is not provided.
<td class="bdata8">
The class entry of "bdata8" refers to break data for the 9th column in the report (the first column of the report is column number 0). The reason that it does appear as the 9th column is because several columns have been hidden.
eval(number_format(&b9~,2))</td></tr>
The text that is displayed is an embedded evaluated formula, which takes the break data for column 9 (the column that contains the sales tax data) and formats it to have 2 decimal places. The "~" that follows the &b9 indicates to use the raw unformatted data from column 9 in the evaluated formula. Finally, there is the HTML </tr> tag to close this table row.
<tr><td class="break" colspan="6">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 6 represents the number of columns that the data must span in this instance.
<td class="bdata8">$eval(number_format(&b8~+&b9~,2))</td>
The text that is displayed is an embedded evaluated formula, which sums the break data for columns 8 and 9, and formats the data to have 2 decimal places. The "~" that follows &b8 and &b9 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.
Orders for Hanari Carnes
orderid country shippeddate productid unitprice quantity total price
10250 Brazil 2017-07-12 41 7.70 10 77.00 
  Brazil 2017-07-12 51 42.40 35 1,484.00 
  Brazil 2017-07-12 65 16.80 15 252.00 
Total before Tax 1,813.00 
Plus Sales Tax 145.04 
Total with Tax 1,958.04 
 
10253 Brazil 2017-07-16 31 10.00 20 200.00 
  Brazil 2017-07-16 39 14.40 42 604.80 
  Brazil 2017-07-16 49 16.00 40 640.00 
Total before Tax 1,444.80 
Plus Sales Tax 115.58 
Total with Tax 1,560.38 
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
Formula Description
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
Value Example Description
&Cx or &cx &C1 Replace with data value from column 1 from the current data row.
&Nx or &nx &N1 Replace with data value from column 1 from the next data row.
&Bx or &bx &B1 Replace with data value from column 1 with the break level data row.
&PN or &pn &PN Replace with current page number.
&DTx or &dtx &DT3 Replace 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. The Custom Layout font color has been set to blue to help identify custom text output.

Chart
 
Invoices by Country
country companyname orderid total price tax
Belgium Suprêmes délices 10252 3,730.00  298.40 
Total for Belgium 3,730.00  298.40 
 
Brazil Hanari Carnes 10250 1,813.00  145.04 
  Hanari Carnes 10253 1,444.80  115.58 
Total for Brazil 3,257.80  260.62 
 
France Victuailles en stock 10251 670.80  53.66 
  Vins et alcools Chevalier 10248 440.00  35.20 
Total for France 1,110.80  88.86 
 
Germany Toms Spezialitäten 10249 1,863.40  149.07 
Total for Germany 1,863.40  149.07 
 
Final 9,962.00  796.96 
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 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 &c1 &c3" is entered into the custom text area (note that column 1 is a hidden break), it will produce the output:

This is custom text for Belgium 10252
 
This is custom text for Brazil 10250
This is custom text for Brazil 10253
 
This is custom text for France 10251
This is custom text for France 10248
 
This is custom text for Germany 10249

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
<div style="display:table">
<div style="display:table;margin:0 auto;padding:4px"><report></div>
<div style="display:table;margin:0 auto;padding:4px"><chart></div>
</div>
This text comes after

This is the output. Notice it is not centered. It is up to the user to align the output to their liking using the custom text input area:

This is custom text before the report
Invoices by Country
country companyname orderid total price tax
Belgium Suprêmes délices 10252 3,730.00  298.40 
Total for Belgium 3,730.00  298.40 
 
Brazil Hanari Carnes 10250 1,813.00  145.04 
  Hanari Carnes 10253 1,444.80  115.58 
Total for Brazil 3,257.80  260.62 
 
France Victuailles en stock 10251 670.80  53.66 
  Vins et alcools Chevalier 10248 440.00  35.20 
Total for France 1,110.80  88.86 
 
Germany Toms Spezialitäten 10249 1,863.40  149.07 
Total for Germany 1,863.40  149.07 
 
Final 9,962.00  796.96 
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 DIVs.
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:

Here is the invoice information for &c2, totaling $&b5:
<report>

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

Here is the invoice information for Belgium, totaling $3,730.00:
Invoices by Country
country companyname orderid total price tax
Belgium Suprêmes délices 10252 3,730.00  298.40 
Total for Belgium 3,730.00  298.40 
 
1
 
Here is the invoice information for Brazil, totaling $3,257.80:
Invoices by Country
country companyname orderid total price tax
Brazil Hanari Carnes 10250 1,813.00  145.04 
  Hanari Carnes 10253 1,444.80  115.58 
Total for Brazil 3,257.80  260.62 
 
1
 
Here is the invoice information for France, totaling $1,110.80:
Invoices by Country
country companyname orderid total price tax
France Victuailles en stock 10251 670.80  53.66 
  Vins et alcools Chevalier 10248 440.00  35.20 
Total for France 1,110.80  88.86 
 
1
 
Here is the invoice information for Germany, totaling $9,962.00:
Invoices by Country
country companyname orderid total price tax
Germany Toms Spezialitäten 10249 1,863.40  149.07 
Total for Germany 1,863.40  149.07 
 
Final 9,962.00  796.96 
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 &c2 is used to reference the country value found in column 2.
The break level amount (the total amount in column 5) was referenced using &b5. &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 1.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 productid column is ",+":

companyname country orderid shippeddate  
productid unitprice quantity total price tax
Vins et alcools Chevalier France 10248 2017-07-16  
11 14.00 12 168.00 13.4400
Vins et alcools Chevalier France 10248 2017-07-16  
42 9.80 10 98.00 7.8400
Vins et alcools Chevalier France 10248 2017-07-16  
72 34.80 5 174.00 13.9200
Toms Spezialitäten Germany 10249 2017-07-10  
14 18.60 9 167.40 13.3920
Toms Spezialitäten Germany 10249 2017-07-10  
51 42.40 40 1696.00 135.6800
Hanari Carnes Brazil 10250 2017-07-12  
41 7.70 10 77.00 6.1600
Hanari Carnes Brazil 10250 2017-07-12  
51 42.40 35 1484.00 118.7200
Hanari Carnes Brazil 10250 2017-07-12  
65 16.80 15 252.00 20.1600
Victuailles en stock France 10251 2017-07-15  
22 16.80 6 100.80 8.0640
Victuailles en stock France 10251 2017-07-15  
57 15.60 15 234.00 18.7200
Victuailles en stock France 10251 2017-07-15  
65 16.80 20 336.00 26.8800
1

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:

companyname country  
Belgium Brazil France Germany Sum
total price total price total price total price total price
Hanari Carnes   3,257.80      3,257.80 
Suprêmes délices 3,730.00        3,730.00 
Toms Spezialitäten       1,863.40  1,863.40 
Victuailles en stock     670.80    670.80 
Vins et alcools Chevalier     440.00    440.00 
 
Final 3,730.00  3,257.80  1,110.80  1,863.40  9,962.00 
1

Operator Description
=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.
bt Between. 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.
!bt Not 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.
%v Ends with. The value in the report cell ends with the value entered. Multiple values can be entered separated by commas.
!%v Not 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:

companyname orderid country  
Belgium Brazil France Germany Sum
total price total price total price total price total price
Hanari Carnes 10250   1,813.00      1,813.00 
Hanari Carnes 10253   1,444.80      1,444.80 
Suprêmes délices 10252 3,730.00        3,730.00 
Toms Spezialitäten 10249       1,863.40  1,863.40 
Victuailles en stock 10251     670.80    670.80 
Vins et alcools Chevalier 10248     440.00    440.00 
 
Final 3,730.00  3,257.80  1,110.80  1,863.40  9,962.00 
1
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 result Evaluated 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='<span style="red">'.$value.'</span>'

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 level Evaluated 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:

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

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:

Vendor information   Quantity & price  
companyname country orderid shippeddate productid unitprice quantity total price
Vins et alcools Chevalier France 10248 2017-07-16 11 14.00 12 168.00
Vins et alcools Chevalier France 10248 2017-07-16 42 9.80 10 98.00
Vins et alcools Chevalier France 10248 2017-07-16 72 34.80 5 174.00
Toms Spezialitäten Germany 10249 2017-07-10 14 18.60 9 167.40
Toms Spezialitäten Germany 10249 2017-07-10 51 42.40 40 1696.00
Hanari Carnes Brazil 10250 2017-07-12 41 7.70 10 77.00
Hanari Carnes Brazil 10250 2017-07-12 51 42.40 35 1484.00
Hanari Carnes Brazil 10250 2017-07-12 65 16.80 15 252.00
Victuailles en stock France 10251 2017-07-15 22 16.80 6 100.80
Victuailles en stock France 10251 2017-07-15 57 15.60 15 234.00
1
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:

companyname country orderid shippeddate productid unitprice quantity total price tax
Vins et alcools Chevalier France 10248 2017-07-16 11 14.00 12 168.00 13.4400
Vins et alcools Chevalier France 10248 2017-07-16 42 9.80 10 98.00 7.8400
Vins et alcools Chevalier France 10248 2017-07-16 72 34.80 5 174.00 13.9200
Toms Spezialitäten Germany 10249 2017-07-10 14 18.60 9 167.40 13.3920
Toms Spezialitäten Germany 10249 2017-07-10 51 42.40 40 1696.00 135.6800
Hanari Carnes Brazil 10250 2017-07-12 41 7.70 10 77.00 6.1600
Hanari Carnes Brazil 10250 2017-07-12 51 42.40 35 1484.00 118.7200
Hanari Carnes Brazil 10250 2017-07-12 65 16.80 15 252.00 20.1600
Victuailles en stock France 10251 2017-07-15 22 16.80 6 100.80 8.0640
Victuailles en stock France 10251 2017-07-15 57 15.60 15 234.00 18.7200
Victuailles en stock France 10251 2017-07-15 65 16.80 20 336.00 26.8800
Suprêmes délices Belgium 10252 2017-07-11 20 64.80 40 2592.00 207.3600
Suprêmes délices Belgium 10252 2017-07-11 33 2.00 25 50.00 4.0000
Suprêmes délices Belgium 10252 2017-07-11 60 27.20 40 1088.00 87.0400
Hanari Carnes Brazil 10253 2017-07-16 31 10.00 20 200.00 16.0000
Hanari Carnes Brazil 10253 2017-07-16 39 14.40 42 604.80 48.3840
Hanari Carnes Brazil 10253 2017-07-16 49 16.00 40 640.00 51.2000
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:

companyname orderid country  
Belgium Brazil France Germany Sum
total price total price total price total price total price
Hanari Carnes 10250   1813     1813
Hanari Carnes 10253   1444.8     1444.8
Suprêmes délices 10252 3730       3730
Toms Spezialitäten 10249       1863.4 1863.4
Victuailles en stock 10251     670.8   670.8
Vins et alcools Chevalier 10248     440   440
 
Final 3730 3257.8 1110.8 1863.4 9962
1

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.
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 companyname, and Sum for the total price and tax columns:

companyname country orderid shippeddate productid unitprice quantity total price tax
Hanari Carnes Brazil 10250 2017-07-12 65 16.80 15 252.00  20.16 
  Brazil 10250 2017-07-12 51 42.40 35 1,484.00  118.72 
  Brazil 10250 2017-07-12 41 7.70 10 77.00  6.16 
  Brazil 10253 2017-07-16 31 10.00 20 200.00  16.00 
  Brazil 10253 2017-07-16 49 16.00 40 640.00  51.20 
  Brazil 10253 2017-07-16 39 14.40 42 604.80  48.38 
Break 1 3,257.80  260.62 
 
Suprêmes délices Belgium 10252 2017-07-11 33 2.00 25 50.00  4.00 
  Belgium 10252 2017-07-11 20 64.80 40 2,592.00  207.36 
  Belgium 10252 2017-07-11 60 27.20 40 1,088.00  87.04 
Break 1 3,730.00  298.40 
 
Toms Spezialitäten Germany 10249 2017-07-10 51 42.40 40 1,696.00  135.68 
  Germany 10249 2017-07-10 14 18.60 9 167.40  13.39 
Break 1 1,863.40  149.07 
 
Victuailles en stock France 10251 2017-07-15 22 16.80 6 100.80  8.06 
  France 10251 2017-07-15 65 16.80 20 336.00  26.88 
  France 10251 2017-07-15 57 15.60 15 234.00  18.72 
Break 1 670.80  53.66 
 
Vins et alcools Chevalier France 10248 2017-07-16 42 9.80 10 98.00  7.84 
  France 10248 2017-07-16 11 14.00 12 168.00  13.44 
  France 10248 2017-07-16 72 34.80 5 174.00  13.92 
Break 1 440.00  35.20 
 
Final 9,962.00  796.96 
1

To illustrate the difference between the Table layout and the Pivot layout, the above report was formatted using a Table layout. An Action of Table was selected for the country column and Group was selected for the companyname and the orderid, resulting in the following report:

companyname orderid country  
Belgium Brazil France Germany Sum Sum
total price tax total price tax total price tax total price tax total price tax
Hanari Carnes 10250     1,813.00  145.04          1,813.00  145.04 
Hanari Carnes 10253     1,444.80  115.58          1,444.80  115.58 
Suprêmes délices 10252 3,730.00  298.40              3,730.00  298.40 
Toms Spezialitäten 10249             1,863.40  149.07  1,863.40  149.07 
Victuailles en stock 10251         670.80  53.66      670.80  53.66 
Vins et alcools Chevalier 10248         440.00  35.20      440.00  35.20 
 
Final 3,730.00  298.40  3,257.80  260.62  1,110.80  88.86  1,863.40  149.07  9,962.00  796.96 
1

If the Layout is adjusted to set the Action of the country column to "Pivot", along with the following changes:

The Action of the orderid and shippeddate columns are set to "Group".
The Text at the break for Break #1 set to &C2 to reference the country name.
The use of break coloring is unchecked.

The following report is produced:

companyname orderid shippeddate Amounts country
Belgium Brazil France Germany Total
Hanari Carnes 10250 2017-07-12 total price   1,813.00      1,813.00 
  10250 2017-07-12 tax   145.04      145.04 
Hanari Carnes 10253 2017-07-16 total price   1,444.80      1,444.80 
  10253 2017-07-16 tax   115.58      115.58 
Hanari Carnes total price   3,257.80      3,257.80 
  tax   260.62      260.62 
 
Suprêmes délices 10252 2017-07-11 total price 3,730.00        3,730.00 
  10252 2017-07-11 tax 298.40        298.40 
Suprêmes délices total price 3,730.00        3,730.00 
  tax 298.40        298.40 
 
Toms Spezialitäten 10249 2017-07-10 total price       1,863.40  1,863.40 
  10249 2017-07-10 tax       149.07  149.07 
Toms Spezialitäten total price       1,863.40  1,863.40 
  tax       149.07  149.07 
 
Victuailles en stock 10251 2017-07-15 total price     670.80    670.80 
  10251 2017-07-15 tax     53.66    53.66 
Victuailles en stock total price     670.80    670.80 
  tax     53.66    53.66 
1

Notice how both the total price and tax columns data are rotated and displayed horizontally. Also notice how columns without an action 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 "Amounts". This was changed to the desired value by updating the appropriate field in the Column headings area of the Titles and Footers section.

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

Invoice Information
companyname orderid shippeddate Amounts country
Belgium Brazil France Germany Total
Hanari Carnes 10250 2017-07-12 total price   1,813.00      1,813.00 
  10250 2017-07-12 tax   145.04      145.04 
Hanari Carnes 10253 2017-07-16 total price   1,444.80      1,444.80 
  10253 2017-07-16 tax   115.58      115.58 
Hanari Carnes total price   3,257.80      3,257.80 
  tax   260.62      260.62 
 
Suprêmes délices 10252 2017-07-11 total price 3,730.00        3,730.00 
  10252 2017-07-11 tax 298.40        298.40 
Suprêmes délices total price 3,730.00        3,730.00 
  tax 298.40        298.40 
 
Toms Spezialitäten 10249 2017-07-10 total price       1,863.40  1,863.40 
  10249 2017-07-10 tax       149.07  149.07 
Toms Spezialitäten total price       1,863.40  1,863.40 
  tax       149.07  149.07 
 
Victuailles en stock 10251 2017-07-15 total price     670.80    670.80 
  10251 2017-07-15 tax     53.66    53.66 
Victuailles en stock total price     670.80    670.80 
  tax     53.66    53.66 
1