Reports Workspace

From Lianjapedia
Jump to: navigation, search

Overview

The Reports Workspace provides a full blown Report Builder that handles master/detail reports and is jam-packed full of features and functionality to help you be more productive.

A Report Section can now be a fully fledged reporting portal for your users.

Reports can be categorized and provided with a textual description providing end users with the ability to select and print reports with ease.

Reports Workspace


Additionally, the Lianja system object offers a range of methods (expanded in Lianja v9.4) to print, view, export and email the reports created in the Report Builder programmatically:

Method Description
emailReport Email the specified report.
exportReport Export the specified report to a file of a specified type.
loadReport Load the specified report into a specified section.
previewReport Open the specified report in print preview.
printReport Print the specified report.
saveReportAsPdf Save the specified report to a PDF, optionally specifying a filter.
showReportViewer Show the specified report in the report viewer.

Also see this blog article: Working with reports in Lianja 9.4

See Also

Lianja Methods, Report Options, Report Section Attributes, Working with reports in Lianja 9.4

Report Explorer

The Report Explorer panel displays the available reports grouped by category. Click on a report description/name to open it in the design panel.

Reports Workspace


Buttons

Reports Workspace Buttons


Button Description
New Create a new report.
Save Save the current report.
Copy Copy the current report to a new report.
Backup Backup the current report to a new report. From v7.1.
Restore Restore a backup of the current report. From v7.1.
Preview Display the current report in the print preview viewer with the option to print.
Close Close the current report.
Rename Rename the current report.
Delete Delete the current report.
Undo Undo changes made to the current report.
Refresh Save and refresh the current report.
Export Export the current report to one of the following formats (from v9.0):
  • PDF
  • EMAIL
  • HTML
  • CSV
  • XML
  • EXCEL
  • JSON
  • ADO

See below for more details.

Deploy Deploys the current report on the local machine. Note: if any sub reports are specified, they should be deployed individually.
Apply Apply changes to the current report.

Export

Format Notes
PDF Enter the PDF filename when prompted. The file will be opened in the default PDF viewer.
EMAIL Enter the email address and optional email subject and body text when prompted. This should be in the format:
email-address/subject/body
The email subject and body text have the following defaults if not specified:
Subject: <report-name> report for user <user>
Body: Find attached the report that you requested
The report is emailed as a PDF attachment.
HTML Enter the HTML filename when prompted. The file will be opened in the default browser.
CSV Enter the CSV filename when prompted. The file will be opened in the .csv file type associated application.
XML Enter the XML filename when prompted. The file will be opened in the .xml file type associated application.
EXCEL Enter the XLSX filename when prompted. The file (EXCEL format XML) will be opened in the file type .xml associated application.
JSON Enter the JSON filename when prompted. The file will be opened in the .json file type associated application.
ADO Enter the XML filename when prompted. The file (ADO format XML) will be opened in the file type .xml associated application.

Run/Design

Reports Workspace


Click the Run button to switch to runtime view and the Design button to return to the development view.

Report Builder


Context Menu

From v9.0, right-clicking on a report name opens the context menu, allowing the following operations:

  • Delete Report
  • Rename Report
  • Copy Report
Reports Workspace


Report

Setting Description
Name Name of the report. This is set when the report is created and the setting is readonly. To change the name of the report, use the Rename button.
Category An optional category for the report. The categories are used to group reports in the Report Explorer. If no category is specified, the report is listed under General.
Description An optional description for the report. If specified, the Description is used in the Report Explorer, otherwise the Name is used.
Roles The roles controlling who can run the report. Roles are created in the Users workspace. Roles are specified in a comma-separated list. The -rolename syntax can be used to exclude users who have that role; * allows all roles and is the default. If a user's role excludes them from running the report, it does not appear in the Report Explorer.
Creator Name of the user who created the report. This is set when the report is created and the setting is readonly.
Date Modified Last modification date and time for the report. This is updated automatically and the setting is readonly.

Data Source

The Data Source settings allow the ODBC or native Lianja data source to be configured.

Setting Required Description
Connection No The ODBC DSN for the data source. The pulldown lists available 32-bit ODBC DSNs.
Database Yes, unless Custom SQL is specified and references the database name, e.g. select * from southwind!shippers. The database for the data source. The pulldown lists available Lianja databases.
Table Yes, unless Custom SQL is specified The table for the data source. The pulldown lists tables from the specified Database.
Fields Yes, unless Custom SQL is specified The fields to be included from the Table. Click the [...] button to display the 'Choose fields' dialog. The default is * indicating all fields from the Table.
Filter No The filter condition to be applied to the records from the Table. Click the [...] button to display the 'Custom Query Builder'.
Needs Filter No Whether a filter is required before the report is displayed. Clicking the 'Search' button displays the Query Builder allowing the end user to build the required query.
Memos No Whether to include variable length text memo fields. If true, the text of the memo field contents is displayed. If false (default), 'MEMO' is displayed when the memo field has contents and 'Memo' when it is empty.
Images No Whether to include image object fields. If true, the image is displayed. If false (default), the field name is displayed.
Custom SQL No, unless Table is not specified The SQL select query for the data source.

Once the Database and Table or Custom SQL have been specified, additional report configuration tabs are shown and the live report is displayed.

Page Layout

The Page Layout settings determine the layout when the report is printed.

Setting Default Description
Paper Size Letter Select the paper size to be used when printing the report. If Custom is selected, the dimensions can be specified in the Custom Paper Size.
Custom Paper Size The paper dimensions if Custom is selected as the Paper Size.
Rows per page 40 The number of rows to display per page when printing the report.
Row Height 0 The row height in pixels when printing the report. The default is 0, which signifies that the rows should be autosized.
Show Grid Lines True Whether grid lines should be shown when printing the report.
Show Page Numbers True Whether page numbers (nPage of nPages) should be shown in the page footer when printing the report.
Show Date/Time True Whether the date and time should be shown in the page footer when printing the report.
Page Top Margin 50 The height in pixels of the page top margin when printing the report.
Page Bottom Margin 0 The height in pixels of the page bottom margin when printing the report.
Print scaling factor (%) 80 The percentage to scale the report by when printing in the web.
Record Offset 0 The starting record offset.
Max Records 0 The maximum number of records to be processed. The default is 0, which signifies that all records should be processed.
Max Groups 0 The maximum number of groups to be processed. The default is 0, which signifies that all groups should be processed.
Max Pages 0 The maximum number of pages to be processed when the report is previewed. The default is 0, which signifies that all pages should be processed.
Page Left Margin 0 The width in pixels of the page left margin when printing the report. From v7.1.
Page Right Margin 0 The width in pixels of the page right margin when printing the report. From v7.1.
Page Orientation Portrait The page orientation when printing the report: Portrait or Landscape. From v7.1.
Custom Options Semi-colon separated list of custom options: option=value. Click [...] to open an editor, where each option should be entered on a new line (semi-colon terminator not required. See the table below for available options. From v7.1.

Custom Options

Option Default Description
alternaterowcolor True Set to false to disable alternate row colors (gray/white) and display all rows on a white background.
columnheadingrowstyle Specify CSS for the column heading row. CSS declarations should be separated by '+', not ';'.
columnheadingstyle Specify CSS for the column headings. CSS declarations should be separated by '+', not ';'.
database Specify a database name or current to use the currently open database. This will override the database defined in the report.
groupsummarycaption Group Summary (N row/rows) Specify alternative text to be displayed in the Group Summary header row.
hidemainheading False Hide the Main Heading.
hidesubheading False Hide the Sub-Heading.
hidesubtotals False Hide the Sub-total rows.
hidesummaryheader False Hide the Group Summary header row.
hidetotals False Hide the Total row.
plaincolumnheadings False Set to true to display the column headings with a white background.
showfirsttotalascount False Set to true to display a row count in the first column of the Total row.
showsubtotalastotal False Display 'Total' instead of 'Sub-total' in the Sub-total rows.

Grouping

The Grouping settings determine the optional grouping and sort order columns for the report. These include options to allow the end user to select from a specified list of columns.

Setting Default Description
Group By The grouping column for the records in the report. A Sub-totals: footer will be displayed for each group. Individual columns can be flagged to be subtotaled in the Detail report configuration tab.
Sort By The sort order column for the records in the report.
Group Summary rowspan 10 The number of rows assigned to display the results of the Group Summary delegate if one is specified.
Group By List A | separated list of columns that the user can select from to set the grouping for the records in the report.
Sort By List A | separated list of columns that the user can select from to set the sort order for the records in the report.
Pagebreak After Summary False Insert a page break after the group summary. From v7.1.

Here the employees report has been altered to specify the Group By List as:

reportsto|lastname

and the Sort By List as:

lastname|firstname

The user can select from the lists to change the grouping and sort order columns active in the report.

Report Builder Grouping


Header

The Header settings allow the header and main and sub headings to be configured.

Report Header


Setting Default Description
Main Heading {reportname} report for user {username} The main heading. For the default main heading, the {} macros are expanded to display the reportname and current username.
Sub-Heading A sub-heading.
Header Height The height of the header (for image display) in pixels.
Header Image The name of an image file to be displayed as the background to the header.
Clicking on the [...] displays a file chooser.
Left Header Image The name of an image file to be displayed on the left-hand side of the header.
Clicking on the [...] displays a file chooser.
Right Header Image The name of an image file to be displayed on the right-hand side of the header.
Clicking on the [...] displays a file chooser.
Header Background Color #333333 The background color of the Main Heading.
Clicking on the [...] displays a color chooser.
Header Foreground Color White The foreground color of the Main Heading.
Clicking on the [...] displays a color chooser.
Sub-Header Background Color darkgray The background color of the Sub-Heading.
Clicking on the [...] displays a color chooser.
Sub-Header Foreground Color White The foreground color of the Sub-Heading.
Clicking on the [...] displays a color chooser.
Custom Header The name of a Lianja/VFP program or procedure to output HTML5 for the display of a custom header. e.g.
? "<div style='background:lightgray;height:1px;width:100%;'></div>"
? "<br>"
? "<center>-- Custom Header --</center>"
? "<br>"

Clicking on the [...] displays a popup custom code editor and automatically generates a filename.

Print Header On All Pages False Whether the header should be printed on all pages (True | False).

Note that when files are selected via the [...] buttons, they are added to the report's directory. For example, if the report is 'customers', its directory is customers in the reports sub-directory of the Lianja library directory. If file names are entered manually, the files should be located in the report's directory.

Windows

C:\lianja\library\reports\<reportname>

Linux

/opt/lianja/library/reports/<reportname>

macOS

Prior to v6.0:

/usr/local/lianja/library/reports/<reportname>

From v6.0:

/Users/Shared/Lianja/library/reports/<reportname>

Detail

The Detail settings allow the display of individual columns to be configured.

Setting Default Description
Column Select the column to be configured from the pulldown.
Column Expression The expression to be displayed in the column.
Heading The heading for the column.
Alignment The alignment for the column (Left | Center | Right).
Display Mask The display mask for the column.
Data Mapping The data mapping for the column. Note that double quotes should not be used in the definition; single quotes can be used.
Dynamic BackColor An expression returning a color value to be used as the background color, e.g.
'#ff0000'
'red'
iif({} > 15,'red','blue')
Dynamic ForeColor An expression returning a color value to be used as the foreground color, e.g.
'#0000ff'
'blue'
iif(startswith('{}','F'),'red','blue')
Sub-total False Whether the column's values should be sub-totalled and displayed in the group summary.
Min False Whether the column's minimum value should be calculated and displayed in the group summary.
Max False Whether the column's maximum value should be calculated and displayed in the group summary.
Avg False Whether the column's average value should be calculated and displayed in the group summary.
Hyperlink False Whether the column contents should be displayed as a hyperlink.
Column Width 0 Column width in pixels. The default is 0, which signifies that the column should be autosized.
Count False Whether the number of rows should be counted and displayed in the group summary. From v7.1.

Delegates

The Delegates settings define the event delegates for the report.

Clicking on the [...] displays a popup custom code editor and automatically generates a filename.

Setting Description
Before Generate Called before report is generated. This is a Lianja/VFP script (server-side for web/mobile Apps).
After Generate Called after report is generated. This is a Lianja/VFP script (server-side for web/mobile Apps).
Before Group Called before a group is processed. This is a Lianja/VFP script (server-side for web/mobile Apps).
After Group Called after a group is processed. This is a Lianja/VFP script (server-side for web/mobile Apps).
Before Open Called before table is opened or sql is executed. This is a Lianja/VFP script (server-side for web/mobile Apps).
After Open Called after table is opened or sql is executed. This is a Lianja/VFP script (server-side for web/mobile Apps).
Chart Clicked Called when a chart is clicked. This is a JavaScript script.
It is passed four arguments:
  • id - the chart ID
  • tag - the data tag e.g. the groupby value
  • name - the label of the element clicked
  • value - the value of the element clicked
Cell Renderer Called to render cells in the report. This is a Lianja/VFP script (server-side for web/mobile Apps).
It should output HTML using the ? or echo commands.
It is passed two arguments:
  • fieldname - the name of the field
  • fieldvalue - the value of the field
Row Clicked Called when a row is clicked. This is a JavaScript script.
It is passed one argument:
  • fieldvalues - a '|' separated list of fields in the row
Row Double Clicked Called when a row is double-clicked. This is a JavaScript script.
It is passed one argument:
  • fieldvalues - a '|' separated list of fields in the row
Hyperlink Called when a hyperlink is clicked. This is a JavaScript script.
It is passed two arguments:
  • fieldname - the name of the field clicked
  • fieldvalue - the value of the field clicked
Data Changed Called after each data row changes during processing. This is a Lianja/VFP script (server-side for web/mobile Apps).
Group Summary Called after the group summary panel is rendered. This is a Lianja/VFP script (server-side for web/mobile Apps).
It should output HTML using the ? or echo commands.
It is passed one argument:
  • p_groupvalue - the current group value

When a 'Group By' column and a 'Group Summary' delegate are specified, an index is automatically built on the 'Group By' column and this is the master index order. This allows the use of key based data analysis functions in the 'Group Summary' delegate code.

Row Renderer Called to render rows in the report. Set the row height in Page Layout and render rows in a custom HTML layout. From v7.1.
Before Row Called before a row is rendered in the report. From v7.1.
After Row Called after a row is rendered in the report. From v7.1.
Text Translator Called to translate text in the report. From v7.1.

Note that when files are created via the [...] buttons, they are added to the report's directory. See Header for further details on the report's directory location.

Sub Reports

The Sub Reports settings determine the display of up to 5 Sub Reports.

Setting Default Description
Sub Report # (1-5) Select an existing report from the pulldown.
Custom Renderer The name of a Lianja/VFP program or procedure to output HTML5 for the display of a custom generated sub-report. e.g.
? "<div style='background:lightgray;height:1px;width:100%;'></div>"
? "<center>--- Custom Rendered html --</center>"

Clicking on the [...] displays a popup custom code editor and automatically generates a filename.
When writing your own custom sub-reports you can access _plineno and _pageno if you need to check that there's enough space for you. If not you can execute the eject command which will print the footer (if one exists) on the current page, eject a page and print the header (if one is specified).
Hint: you can embed charts and other interesting content by generating an iframe with the src attribute containing a google charts url.

Filter The filter condition to be applied to the records, e.g. when the orders report is run as a sub report of the customers report, only records with a customerid matching the current value of the customer column in the customers report are included:
customerid='{customer}'
Run Select when the sub report should be run based on events in the main report:
  • After each row
  • Before each group
  • After each group
  • Before report
  • After report
Indent 0 The indent in pixels for the display of the sub report when the report is previewed or printed.

Footer

The Footer settings determine the display of the page footer when the report is printed/previewed.

Setting Default Description
Footer Height 25 The page footer height in pixels.
Footer Image The name of an image file to be displayed as the background to the footer.
Clicking on the [...] displays a file chooser.
Custom Footer The name of a Lianja/VFP program or procedure to output HTML5 for the display of a custom footer. e.g.
? "<div style='background:lightgray;height:1px;width:100%;'></div>"
? "<center>--- Custom Footer --</center>"

Clicking on the [...] displays a popup custom code editor and automatically generates a filename.

Footer Background Color White The background color of the footer.
Clicking on the [...] displays a color chooser.
Footer Print Offset 0 The height of the page footer margin offset in pixels (for scaling) when printing.

Note that when files are selected via the [...] buttons, they are added to the report's directory. See Header for further details on the report's directory location.