Difference between revisions of "Lianja 5 Report Options"
Yvonne.milne (Talk | contribs) |
Yvonne.milne (Talk | contribs) |
||
Line 18: | Line 18: | ||
<br clear=all> | <br clear=all> | ||
− | == | + | ==Database and Table== |
− | + | The '''Details''' section at the top of the attributes for a Report Section holds the specification of the '''Database''' and '''Table''' to be used. | |
− | [[{{ns:file}}: | + | [[{{ns:file}}:l5_report_options.png|800px|border|left|link={{filepath:l5_report_options.png}}|Database and Table]] |
− | + | ||
− | + | ||
− | + | ||
− | + | ||
− | + | ||
− | + | ||
<br clear=all> | <br clear=all> | ||
− | The | + | The '''Table''' attribute is used for reports based on a single table or [[:Category:Third Party Database Connectivity|Virtual Table]]. Virtual Tables can return data from a SQL Select query or stored procedure from local or remote tables. |
− | + | ||
− | + | ||
− | + | ||
− | + | ||
+ | The [[#SQL statement|SQL statement]] Report Option below allows multiple tables to be joined to display Crosstab queries. | ||
+ | ==Report options== | ||
The Report Section options are described here: | The Report Section options are described here: | ||
Revision as of 10:26, 13 August 2019
Under Construction
Contents
- 1 See Also
- 2 Overview
- 3 Lianja Web UI Demo
- 4 Database and Table
- 5 Report options
- 5.1 Report type
- 5.2 Report
- 5.3 Connstr
- 5.4 SQL statement
- 5.5 Heading
- 5.6 Filter
- 5.7 Fields
- 5.8 Columns
- 5.9 Subtotals
- 5.10 Column headings
- 5.11 Column alignments
- 5.12 Column display masks
- 5.13 Column data mapping
- 5.14 Column dynamic background
- 5.15 Column dynamic foreground
- 5.16 Group by
- 5.17 Group by list
- 5.18 Sort by
- 5.19 Sort by list
- 5.20 Delegate argument
- 5.21 Hyperlinks
- 5.22 Show grid lines
- 5.23 Summary only
- 5.24 Sortable
- 5.25 Editable
- 5.26 Enable custom SQL
- 5.27 Include memos
- 5.28 Include images
- 5.29 Image width
- 5.30 Image height
- 5.31 Selectable
- 5.32 Needs filter
- 5.33 Max records
- 5.34 Paper size
- 5.35 Custom Paper size
- 5.36 Rows per page
- 5.37 Row height
- 5.38 Scale print size
- 5.39 Header height
- 5.40 Header margin
- 5.41 Header image
- 5.42 Footer height
- 5.43 Footer margin
- 5.44 Footer image
- 5.45 Footer page numbers
- 5.46 Footer date/time
- 5.47 CSS Stylesheet
- 5.48 Delegate library
- 5.49 Pagination
- 5.50 Pagination size
- 5.51 Other options
- 6 Delegate Behavior
- 7 Notes on Client Support
- 8 report.rsp
- 9 Reporting Alternatives
See Also
Developing Quick Reports, Report Section Attributes
Overview
Report Sections are used to display tabular reports.
Lianja Web UI Demo
The 'Lianja Web UI Demo' (example_webapp2) is included in the Lianja App Builder distribution.
It has four full page Report Sections.
Database and Table
The Details section at the top of the attributes for a Report Section holds the specification of the Database and Table to be used.
The Table attribute is used for reports based on a single table or Virtual Table. Virtual Tables can return data from a SQL Select query or stored procedure from local or remote tables.
The SQL statement Report Option below allows multiple tables to be joined to display Crosstab queries.
Report options
The Report Section options are described here:
Report type
This has been deprecated in Lianja v5.0.
Report
Select from a list of pre-built reports. From v5.0.
Connstr
The report data source connection string. From v5.0.
SQL statement
A SQL statement. This can join multiple tables together and optionally include GROUP BY, ORDER BY and aggregate functions to display Crosstab queries. Use {} macros in the WHERE condition to relate parent->child sections. From v5.0.
Heading
The Heading attribute specifies text to use as the report heading.
e.g.
Client Credit List (over limit)
The Heading attribute can be queried or changed programmatically using Lianja.getElementByID("page.section").reportHeading.
Filter
The Filter attribute specifies a logical expression used to restrict which records will be included in the report.
e.g. the Clients over credit limit report filters on the available field being less than zero:
available < 0
The Filter attribute can be queried or changed programmatically using Lianja.getElementByID("page.section").reportFilter.
Fields
The Fields attribute specifies the fields to be included in the report. This should be a comma separated list of field names or expressions.
e.g.
account_no, upper(concat(title," ",first_name,last_name)),limit
or, to select all fields in the table as in the example_webapp2 reports:
*
The Fields attribute can be queried or changed programmatically using Lianja.getElementByID("page.section").reportFields.
Columns
The Columns attribute specifies the column positions for the selected fields in the report. This should be a comma separated list of numeric column positions. If Columns is left blank, all fields are displayed and in the same order as in the Fields attribute.
e.g. the Clients over credit limit report omits field 1 (example.account_no) from the display and swaps the order of fields 3 (example.first_name) and 4 (example.last_name).
2,4,3,5,6,7,8,9,10,11
The Columns attribute can be queried or changed programmatically using Lianja.getElementByID("page.section").reportColumns.
Subtotals
The Subtotals attribute specifies the columns in the report to be sub-totalled. This should be a comma separated list of numeric column positions of numeric fields.
e.g.
9,10,11
The Subtotals attribute can be queried or changed programmatically using Lianja.getElementByID("page.section").reportSubTotals.
Column headings
The Column headings attribute specifies the text to be used as the report column headings. This should be a comma separated list of text strings.
If Column headings is left blank, the descriptions from the fields in the table are used. Unless Fields is *, the Column headings should be entered to match the chosen field selection and order.
e.g. if Fields is:
account_no, upper(concat(title," ",first_name,last_name)),limit
then Column headings is:
Account,Fullname,Credit Limit
The Column headings attribute can be queried or changed programmatically using Lianja.getElementByID("page.section").reportColumnHeadings or Lianja.getElementByID("page.section").reportHeadings.
Column alignments
A | separated list of column alignments from the fields selected
e.g. left|center|right. From v5.0.
Column display masks
A | separated list of column display masks from the fields selected
e.g. $|99.999|$. From v5.0.
Column data mapping
A | separated list of custom data mapping for columns when displaying data. From v5.0.
Column dynamic background
A | separated list of expressions that provides dynamic cell color formatting
e.g. ||iif({}>10000,"lightgreen","")||iif({}<1000,"pink","").
Note the use of {}, which is substituted with the current cell value.
From v5.0.
Column dynamic foreground
A | separated list of expressions that provides dynamic cell color formatting
e.g. ||iif({}>10000,"white","")||.
Note the use of {}, which is substituted with the current cell value.
From v5.0.
Group by
The Group by attribute specifies the name of the field or the expression that the report data will be grouped by for sub-totalling.
e.g.
state
The Group by attribute can be queried or changed programmatically using Lianja.getElementByID("page.section").reportGroupBy.
Group by list
A | separated choicelist of fields/expressions that an end user can group by. From v5.0.
Sort by
The field name or expression that data will be sorted by for subtotalling. From v5.0.
Sort by list
A | separated choicelist of fields/expressions that an end user can sort by. From v5.0.
Delegate argument
An expressionto be evaluated and passed as an argument to the click and dblclick delegates. From v5.0.
Hyperlinks
The Hyperlinks attribute specifies the field or fields that will be rendered as hyperlinks for searching. This should be a comma separated list of field names.
The Hyperlinks attribute can be queried or changed programmatically using Lianja.getElementByID("page.section").reportHyperlink.
Show grid lines
The Show grid lines attribute specifies whether grid lines should be displayed for each row of the report (True | False). Grid lines are currently always shown.
The Show grid lines attribute can be queried or changed programmatically using Lianja.getElementByID("page.section").reportGridLines.
Summary only
Display report summary lines only (True | False). From v5.0.
Sortable
Whether report is sortable by clicking on column headers. Shift+Click to sort by multiple columns (True | False). From v5.0.
Editable
Whether report is editable by end user (True | False). From v5.0.
Enable custom SQL
Whether custom SQL is enabled as a data source (True | False). From v5.0.
Include memos
Whether memo columns should be included in the report (True | False). From v5.0.
Include images
Whether image columns should be included in the report (True | False). From v5.0.
Image width
The width of the images in pixels. From v5.0.
Image height
The height of the images in pixels. From v5.0.
Selectable
Whether report is selectable by end user and operates as a report viewer (True | False). From v5.0.
Needs filter
Whether report requires a filter (use Query Builder) (True | False). From v5.0.
Max records
The maximum number of records to be processed. From v5.0.
Paper size
The paper size that the print will be printed on
(Letter | 8.5x13 | A4 | Custom). From v5.0.
Custom Paper size
The paper size that the print will be printed on e.g. 8.5x13. From v5.0.
Rows per page
The number of rows to print in each page. From v5.0.
Row height
The height of the rows in pixels. From v5.0.
Scale print size
The percentage by which to scale the report when printing in the web client. From v5.0.
Header height
The height of the page header in pixels when printing. From v5.0.
Header margin
The height of the page header margin in pixels when printing. From v5.0.
Header image
The background image to display in the header. From v5.0.
The height of the page footer in pixels when printing. From v5.0.
The height of the page footer margin in pixels when printing. From v5.0.
The background image to display in the footer. From v5.0.
Whether to show 'page n of count' in the footer (True | False). From v5.0.
Whether to show date/time in the footer (True | False). From v5.0.
CSS Stylesheet
You can override the CSS style for the report and style it yourself by specifying your own stylesheet. From v5.0.
Delegate library
The name of a custom library containing the server-side report delegates. This should be a Lianja/VFP procedure library. From v5.0.
Pagination
This has been deprecated in Lianja v5.0.
Pagination size
This has been deprecated in Lianja v5.0.
Other options
The Other report options attribute specifies additional option=value pairs to be passed to the report. These should be specified as an ampersand (&) separated list.
Note: to access these options within library:/report.rsp, add a getParameter() for each additional option.
If 'Other options' is set to:
username={username()}&myparam=some text
Library:/report.rsp should include:
private username = getParameter("username","")
private myparam = getParameter("myparam","")
The Other options attribute can be queried or changed programmatically using Lianja.getElementByID("page.section").reportOtherOptions.
Delegate Behavior
Delegate hyperlinks
Delegate hyperlinks in page to page and/or section (True | False).
Delegate page
Delegate page name to search (leave blank if current page).
Delegate section
Delegate section name (leave blank if default searchkey section on page).
Delegate script
Delegate script name (URL is passed as character string).
Notes on Client Support
Attribute | Notes |
---|---|
Report type | Deprecated in v5.0. |
Report | |
Connstr | |
SQL statement | |
Heading | Lianja.getElementByID("page.section").reportHeading is exposed on the Desktop client only (Lianja/VFP). |
Filter | Lianja.getElementByID("page.section").reportFilter is exposed on the Desktop client only (Lianja/VFP). |
Fields | Lianja.getElementByID("page.section").reportFields is exposed on the Desktop client only (Lianja/VFP). |
Columns | Lianja.getElementByID("page.section").reportColumns is exposed on the Desktop client only (Lianja/VFP). |
Subtotals | Lianja.getElementByID("page.section").reportSubTotals is exposed on the Desktop client only (Lianja/VFP). |
Column headings | Lianja.getElementByID("page.section").reportColumnHeadings and Lianja.getElementByID("page.section").reportHeadings are exposed on the Desktop client only (Lianja/VFP). |
Column alignments | |
Column display masks | |
Column data mapping | |
Column dynamic background | |
Column dynamic foreground | |
Group by | Lianja.getElementByID("page.section").reportGroupBy is exposed on the Desktop client only (Lianja/VFP). |
Group by list | |
Sort by | |
Sort by list | |
Delegate argument | |
Hyperlinks | Lianja.getElementByID("page.section").reportHyperlink is exposed on the Desktop client only (Lianja/VFP). |
Show grid lines | Grid lines are currently always shown. Lianja.getElementByID("page.section").reportGridLines is exposed on the Desktop client only (Lianja/VFP). |
Summary only | |
Sortable | |
Editable | |
Enable custom SQL | |
Include memos | |
Include images | |
Image width | |
Image height | |
Selectable | |
Needs filter | |
Max records | |
Paper size | |
Custom Paper size | |
Rows per page | |
Row height | |
Scale print size | |
Header height | |
Header margin | |
Header image | |
Footer height | |
Footer margin | |
Footer image | |
Footer page numbers | |
Footer date/time | |
CSS Stylesheet | |
Delegate library | |
Pagination | Deprecated in v5.0. |
Pagination size | Deprecated in v5.0. |
Other options | Lianja.getElementByID("page.section").reportOtherOptions is exposed on the Desktop client only (Lianja/VFP). |
Delegate hyperlinks | |
Delegate page | |
Delegate section | |
Delegate script |
report.rsp
The rsp Lianja Server Page script (report.rsp) used by the Report Section WebViewWidget is located in the Lianja Library directory.
Client | Location |
---|---|
App Builder | \lianja\library\ |
App Center | \lianja\cloudserver\tenants\public\library |
Web/Mobile Clients | \lianja\cloudserver\tenants\public\wwwroot\library |
It can be customized if required, but proceed with caution, as the modifications will apply to all Report Sections and will be overwritten by product upgrades.
Alternatively, report.rsp could be used as a template for your own rsp and specified as the URL/source for a WebView Section.
The Reports in the 'Lianja Demo' (lianjademo) App use quickreport.rsp in this way, e.g. the Report overdue payments report:
URL:quickreport.rsp?database=southwind&table=example&fields=*&groupby=state &filter=available<0&heading=Customer Credit List (over limit) &columns=2,4,3,5,6,7,8,9,10,11,12&subtotals=9,10,11&gridlines=true
Reporting Alternatives
The Lianja ODBC Driver, in conjunction with the Lianja SQL Server, can be used with third party report writers such as Crystal Reports or Microsoft Report Builder.