17.9. Designing Reports

PostERP developers can design reports at will and seamlessly merge them to thin client by attaching them to any screen. End users can then either preview and print these reports or output the SQL results to file. Virtually all database tables can be used as the data source of reports.

17.9.1. Designing SQL for Report Template

Figure 17-7. Designing SQL for Report Template

Tip

To embed parameters in SQL for end users' input, add symbol : in front of that symbol like this:

SELECT f1,f2 FROM t52 WHERE f2 BETWEEN :bd AND :ed
				

Now bd and ed are two parameters. Switch to page Parameter and specify their purposes and meanings and then these parameters are ready for end user's input.

Note

If type casting PostgreSQL columns is required, type cast it like this: CAST(f1 AS TEXT) rather than this: f1::TEXT, which will confuse PostERP thin client program (and cloud version's report server).

Tip

PostERP provides handy PostgreSQL fuctions MonthBegin() and MonthEnd() as described in Chapter 8.

Note

PostERP protects SQL injection attack. Because end users can only enter parameters for reporting. PostERP subsequently insert these parameters into SQL text. PostgreSQL aborts the SQL execution when it encounters an invalid SQL text.

17.9.2. Laying Out Report Templates

Note

System development tasks can be performed using either cloud version or desktop version of PostERP. There is, however, one exception - designing report templates must be carried out on desktop version.

Tera Rows's partner developers will therefore download thin client program tc.exe from GitLab and use it to design report templates. Run the tc.exe and connect to your development server whose IP address is shown on this web page after you sign in as a partner developer.

Figure 17-8. Design the Layout of Reports

The main data set name is mds.

Leverage PostERP internationalization features to design report templates for end users speaking various languages by utilizing these techniques:

Tip

Refer to Section 17.1 for information about setting phrases.

17.9.2.1. Caption Codes

Imaging you are designing a report template which has a field title "Employee Name" in English. Because this report template will be used by global users speaking various languages, directly embedding constant literal Employee Name in this template apparently will not work for non-English speaking users.

The correct approach is adding a report field with value C245. Here literal C is constant and must not be changed, and number 245, which immediately follows constant literal C, is the phrase# (not the long description#). With this set, end users will get Employee Name in their own languages when running this report.

17.9.2.2. Variable ReportTitle

This variable (case sensitive) will be substituted by the report name (not the long report description) when end users run reports.

17.9.2.3. Variables Embeded In SQL Text

PostERP converts the variables embedded in SQL text (see Section 17.9.1) to report variables (case sensitive). The values entered by end users when running reports are taken by report variables and displayed as the corresponding report field values.

17.9.2.4. Variable COMPANY

This variable (case sensitive) will be substituted by the working company name in active language when end users run reports. This variable is most frequently used as report title.

17.9.3. Granting Report Accessibility to End Users

Figure 17-9. Granting report accessibility to end users

17.9.4. Report Preview

Figure 17-10. Report Preview

Tip

Report outputs can be saved to files in Excel format.

17.9.5. Advanced Report Template Design

17.9.5.1. Sub Report

Some report template design can not be achieved by using only one SQL.

Take accounting journal in PostERP as an example. Amount records are indexed by sequence#. Every sequence# associates with multiple tag. Given such schema, if we place the data set extracted by a single SELECT command on report detail band and sub-total the amount field, we will get wrong amount sub-total, which is the sub-total of amount in detail records of duplicated occurrences determined by the number of tag rows associated with its sequence#.

The correct solution for designing such reports is utilizing sub report technique.

Please refer to report# 3 attached on screen [MzF5].

17.9.5.2. Cross Table Report

Please refer to report# 1 and report# 2 attached on screen [MzF5].