Developing Quick Reports
Contents
See Also
Report Section Attributes, Report Options
Overview
This article explains how to use Report Sections to develop Quick Reports. The target audience is beginner to intermediate developers who have read through and understood the Category:Getting Started. Advanced developers may also be interested in the Ideas for Further Development at the end of the article.
Lianja Apps are made up of Pages. Pages are made up of Sections.
Previously we have seen how to build Pages and add Form, Grid and ImageStrip Sections and quickly populate them using drag and drop. The Lianja App Builder also provides a range of other easy to use Section types for a rich and functional user interface. All Sections can be added from the Form Tools Section toolbutton. Report Sections provide a quick and easy way to build a tabular report with groups, subtotals, customized headings and links to other Pages and Sections in your App.
Creating a Report Section
So, let's get building:
- Create a new App or open an existing one
- Open a database
- Add a Report Section to a Page in your App
Double-click on the Report Section Header (or click on the cog icon) to open the Section Attributes.
First, in the Details subsection, define the data Table on which the Report will be based; remember the tables in the open database are listed in the Sidebar. Here, I'm going to report on the order_details table. I've also added in a Caption for the Report Section.
Note: If you click on the table name in the Sidebar, its list of field descriptions will be loaded into the Columns Explorer. Just hover over a field description with your mouse to display the field name, data type and width.
Next, scroll down to the Report Options. By default, all the fields in the selected table will appear in the Report, but you can select which fields you want and include expressions. Just type in a comma-separated list of the fields and expressions to be included in the Fields Attribute. Here, I am selecting the Order ID, the Product ID, the Unit Price and the Quantity and also calculating the Unit Price multiplied by the Quantity.
| Attribute | Value |
|---|---|
| Fields | orderid,productid,unitprice,quantity,(unitprice*quantity) |
To change the order of the fields, enter the numbers of the fields from your Field list in the Columns Attribute. I can swap the positions of the Unit Price (3rd in Fields) and Quantity (4th in Fields) like this:
| Attribute | Value |
|---|---|
| Columns | 1,2,4,3,5 |
I can modify the Column headings to reflect my selected data and column order:
| Attribute | Value |
|---|---|
| Column headings | Order Number,Product Number,Quantity,Unit Price,Product Subtotal |
The Report itself can be given a customized heading too:
| Attribute | Value |
|---|---|
| Heading | Order Details |
The data can be filtered to restrict the records displayed:
| Attribute | Value |
|---|---|
| Filter | orderid < 20000 |
Records can also be grouped by a field or expression. I'm grouping the order details records based on their order id number:
| Attribute | Value |
|---|---|
| Group by | orderid |
With a Group by set, the Subtotals Attribute allows you to total one or more numeric fields for each group of records. The following will give us the order total by summing all the Product Subtotal values:
| Attribute | Value |
|---|---|
| Subtotals | 5 |
Finally, I want to define a Hyperlink so that I can click on an Order ID Number in my Report and link to a Form Section with Order Header information:
| Attribute | Value |
|---|---|
| Hyperlinks | orderid |
So, along with checking the Show grid lines Attribute, this gives me the following Report options:
Report Section Attributes and Descriptions Summary
| Attribute | Description |
|---|---|
| Table | The name of the table used in the report |
| Heading | The heading for the report |
| Filter | A filter condition |
| Fields | * for all fields, or a comma-separated list of fields and/or expressions |
| Columns | A comma-separated list of numeric report column positions based on the selected column number order in Fields |
| Column headings | A comma-separated list of column headings |
| Subtotals | A comma-separated list of column numbers to subtotal based on the selected column number order in Fields |
| Group by | The field/expression to group by |
| Hyperlinks | A comma-separated list of fields that will be highlighted as hyperlinks |
| Show grid lines | Toggle to check grid lines on or off |
| Other options | An ampersand (&) separated list of option=value pairs to be passed to the report. 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","") |
Note: If you are familiar with SQL Select statements, you can think of the Filter as the WHERE clause, the Fields as the select list and the Group by as the GROUP BY clause.
Hyperlink Delegates
The order_details.orderid field has been set as a Hyperlink for the Report, but at the moment has nothing to link to. What we need is a Form Section with information from the orders table with its Search Field set to the orders.orderid field.
- Select orders in the Tables Explorer of the Sidebar and drag and drop it onto the Page to create a Form Section
- Double-click on the Order ID field Caption to open the Field Attributes and check the Search field box. Click Done to save
Now, reopen the Report Section Attributes dialog and scroll down to Behavior then check the Delegate hyperlinks checkbox.
Since the Hyperlink is linking to the Search Field in a Form Section on the current Page, that's all we need to do - clicking on an Order Number in the Report will load the matching Order Header information into the Form Section. The Summary table below describes other Hyperlink targets.
Alternatively, add a second Page to the App and drag and drop the orders table on to the new Page to create a Form Section and set the Order ID field to be the Search field as described above. Then, enter the name of the new Page as the Delegate page for the Report Hyperlink.
Now, when the Report Hyperlink is clicked, the new Page will be opened and the order header for the selected order shown.
The third Delegate option is to specify a Delegate script. The Hyperlink value will be passed as an argument to your script - just click the [...] button on the right-hand side of the Delegate script attribute to generate the script name automatically and start editing. In the example below, when the orderid Hyperlink is clicked, a new PDF report is created in the background to list the order details and then displayed.
////////////////////////////////////////////////////////////////
// Event delegate for 'linkclicked' event
proc page1_section1_linkclicked(arg)
// Generate and display a PDF report on the order details
// using a hidden WebView Section and the Library report.rsp
myreport = createObject("webview")
myreport.url = "lib:/report.rsp?database=southwind&table=order_details";
+"&fields=*&filter=orderid="+etos(arg);
+"&heading=Order Details for Order "+etos(arg)
myreport.refresh()
cTempfile = sys(3) + ".pdf"
myreport.print('', 'Portrait', 'PDF', cTempfile)
showdocument("file:///"+cTempfile)
endproc
Delegate Hyperlinks Behavior Summary
| Attribute | Description |
|---|---|
| Delegate page | The name of the page to be selected when a hyperlink is clicked |
| Delegate section | The name of the section to be selected when a hyperlink is clicked |
| Delegate script | The name of a script to be called (and passed the hyperlink value as a parameter) when a hyperlink is clicked |
Completing the Single Page Example
The screenshot at the top of this article also includes information from the employees and customers tables. These are just additional Form Sections (select the table in the Tables Explorer of the Sidebar and drag and drop it onto the Page) related to the orders Form Section. Using the Relationship Builder to visually set up relations between Sections is explained in the Getting Started Guide.
To make the most of the space on the Page, the Page Accordion behavior Attribute has been set to True. To ensure the Report and Order Header Sections remain displayed at all times their Section Exclude from accordion Attribute has been set to True.
Ideas for Further Development
Report Sections are based on the script file report.rsp. You can see the code for report.rsp in the Library Workspace if you are interested in looking under the hood and could also use it as a template for your own rsp page in conjunction with a WebView Section if you want to customize the output. To see how to call an rsp page in a WebView Section (URL attribute), take a look at the reports in the Lianjademo App.
