10.2. Designing R3 Thin Client Reports

R3 Thin Client developers can design reports at will and link them to thin client menu items. End users can then either preview and print these reports or output the SQL results to file. Virtually all R/3 database tables can be used as the data source of reports.

10.2.1. Designing SQL for Report Template

Figure 10-1. Designing SQL for Report Template

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

SELECT name1 FROM kna1 WHERE ROWNUM <= 200 AND mandt = :r3client AND land1 = :nation
			

Here r3client and nation are two parameters. Switch to page Parameter and specify their purposes and meanings and then these parameters are ready for end user's input.

Special parameter purpose#:

R3 Client#: This parameter applies only to R/3 reports. R3 Thin Client will automatically fills this parameter with the client with which the end user currently connects to R/3 server.

Login Account: This parameter applies only to R3 Thin Client reports. R3 Thin Client will automatically fill user's login account into this parameter.

Language#: This parameter applies only to R3 Thin Client reports. R3 Thin Client will automatically fill end user's active language code into this parameter.

Note

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

10.2.2. Laying Out Report Templates

Figure 10-2. Design the Layout of Reports

The main data set name is mds.

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

Tip

Refer to Section 10.1 for information about setting phrases.

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

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

10.2.2.3. Variables Embeded In SQL Text

R3 Thin Client converts the variables embedded in SQL text (see Section 10.2.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.

10.2.3. Report Preview

Figure 10-3. Report Preview

Tip

Report outputs can be saved to files in Excel format.

10.2.4. Advanced Report Template Design

10.2.4.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# 9, Sub Report Example, attached on screen [MzF5].

10.2.4.2. Cross Table Report

Please refer to report# 44, Cross Table Example Report, attached on screen [MzF5].