Developing Quick Reports

From Lianjapedia
Jump to: navigation, search

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.

Example Order Details Quick Report with links to Order Header, Customer and Employee


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
Open a Database and add a Report Section


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.

Report Section Attributes: Table


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: 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
Retrieving a document from an Attachments Section


Now, reopen the Report Section Attributes dialog and scroll down to Behavior then check the Delegate hyperlinks checkbox.

Report Section Attributes: Delegate Hyperlinks Behavior


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.

Desktop App View: Delegate Hyperlinks Behavior


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.

Report Section Attributes: Delegate Page


Now, when the Report Hyperlink is clicked, the new Page will be opened and the order header for the selected order shown.

Desktop App View: Delegate Hyperlinks Behavior


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.

Report Section Attributes: Delegate Script


////////////////////////////////////////////////////////////////
// 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.

Extending your Quick Report App


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.

Library - report.rsp code