Thiết kế báo cáo với MS Word

đăng 04:04 21-09-2011 bởi Ngo Thanh Tung [ đã cập nhật 11:00 21-09-2011 ]

Tương tự thiết kế báo cáo trong môi trường Excel. Phoebus có thể sử dụng MS Word làm môi trường thiết kế báo cáo. Ưu điểm của MS Word là định dạng tài liệu dễ dàng hơn nhiều . Đặc biệt khi in ra các mẫu hợp đồng, các loại tài liệu không đòi hỏi tính toán phức tạp như Excel template.

Các bước thiết kế báo cáo tương tự như Excel

Tạo template (mẫu báo cáo) từ chương trình MS Word hoặc bất kỳ trình soạn thảo văn bản nào. Mẫu báo cáo sẽ phải lưu dưới định dạng *.rtf (rich text format).
Copy mẫu báo cáo này vào template folder tại My Documents\Phoebus\Report Templates\

Bạn có thể để Phoebus tự tạo mẫu báo cáo cho bạn bằng cách chạy chức năng báo cáo ra Word. Nếu template chưa tồn tại Phoebus sẽ tự tạo template cho bạn vào vị trí My Documents\Phoebus\Report Templates\

Word template có thể sử dụng với QD - Query Designer và tất cả các mẩu tin reference data.

Ví dụ : Tạo báo cáo in một hợp đồng từ chức năng quản lý HĐ lao động trong phân hệ Nhân sự

Truy cập chức năng ELC - (Employee Labor Contract)
Ở ô lệnh. nhập lệnh W và bấm enter. Nếu bạn chưa có mẫu HĐ nào thì Phoebus sẽ tự tạo 1 mẫu cho bạn

Ví dụ mẫu báo cáo Phoebus tự sinh ra download ở đây

Dựa vào mẫu báo cáo bạn chỉnh sửa lại nội dung hợp đồng và chèn các tag dữ liệu vào vị trí phù hợp.

Report Design Reference

In a report template all controlling words, variables and data fields must be comprised between symbols "\" (back slash), for instance: \date\ or \data.EmplCode\

Reference to a field name may be created, using its number. For example: \data.(0)\, \data.(5)\

Report generator ignores spaces in field names and keywords. However, if you want to use name with spaces, you may write it between the chars "[" and "]" for example \Table1:[Field name with spaces]\

If there is a report variable declared as CustomerName, in the pattern of the report it may be referenced as \CustomerName\. Variables are stored in VarList collection.

There are several types of constants in Phoebus RTF report template. String constants are defined with double quotes, single quotes, or "~" symbols.

For example: \"constant string 1"\, \'constant string 2' \, \~constant string 3~\. Constants are often used as parameters for user defined functions. If you need to place text with a "\" symbol in report template, you also may use a string constant, for example: \"c:\My Documents\"\.

Numeric constants are numbers in decimal or exponential form: \2\, \2.5\, \3E-1\. Decimal separator is a dot in input template, however in output results it appears according with machine regional settings.

Date constants are always written in a form \{yyyy-mm-dd}\, for example \{2007-06-20}\.

Boolean constants are \True\ or \False\. If you have EK RTF component properties TrueValue and FalseValue set for example to "Yes" and "No", in a report results you will see Yes and No instead of True and False.

In addition to operating with database fields and variables, you may create a user defined function, for example \myfunc(a:field1, a:field2)\.

Operators and functions in report

Different arithmetical and logical expressions may be used in report template.

Arithmetical operations: >, <, =, <=, >=, <>, != (not equal), +, -, *, /, % (mod)
Logical operations: && (and), || (or), ! (not)

For example:

\IF( (table1:field1>b+1) || (table1:field1=0) )\
............
\ENDIF\

The next built-in functions are supported by report language:

Variables and conditions: VAR, SET, IIF, :=
String functions: STR, VAL, UPPER, LOWER, COPY, MID, SUBSTR, POS, TRIM, FORMATFLOAT
Data tables navigation: EOF, BOF, LAST, FIRST, NEXT, PRIOR
Numeric functions: ROUND, INT, FRAC, POWER, INTPOWER
Date/time functions: NOW, DATE, TIME, DATETOSTR, DATETIMETOSTR, TIMETOSTR, STRTODATE, STRTODATETIME, STRTOTIME, YEAR, MONTH, DAY, SYEAR, SMONTH, SDAY, DTOS, STOD

Variables and conditions

VAR(VAR1, ... , VAR N) - Creates variables VAR1, ... VAR N if they don't exist.

VarName := Value - Assigns Value to variable VarName, for example \a:=4\

SET(VarName, Value) - Assigns Value to variable VarName.

IIF(Logical_expr, Value1, Value2) - Returns one of two values depending on the value of a logical expression. This function, also known as Immediate IF, evaluates a logical expression and then returns one of two expressions. If the logical expression evaluates to True, IIF( ) returns the first expression. If the logical expression evaluates to False, IIF( ) returns the second expression.

String functions.

COPY(S,StartPos,[Optional count])
MID(S,StartPos,[Optional count])
SUBSTR(S,Startpos,[Optional count])

These functions return a characters from the given source string s. Parameter s specifies the character expression from which the character string is returned. StartPos specifies the position in the character expression from where the character string is returned. The first character of s is position 1. If StartPos is greater than the number of characters in source string, the empty string is returned. Optional count specifies the number of characters to return from string. If you omit count, characters are returned until the end of the source string is reached.

FORMATFLOAT(FormatString, Value) FormatFloat formats the floating-point value given by Value using the string given by FormatString. This function works the same as Delphi FormatFloat function. Included for compatibility with EK RTF for Delphi Win32 versions.

STR(Number,[Length, Decimals]) Returns the character equivalent of a specified numeric expression. Number specifies the numeric expression STR( ) evaluates. Length specifies the length of the character string STR( ) returns. The length includes one character for the decimal point and one character for each digit to the right of the decimal point.

STR( ) pads the character string it returns with leading spaces if you specify a length larger than the number of digits to the left of the decimal point. STR( ) doesn,t cut a string, if you specify a length less than the number of digits. If Length isn't included, the length of the character string is number of actual digits in source numeric expression.

Decimals specifies the number of decimal places in the character string STR( ) returns. If you specify fewer decimal places than are in numeric expression, the return value is rounded up. If Decimals isn't included, the number of decimal places defaults to zero.

If Length set to 0, but the same time decimals is not zero, result string is trimmed with trim() function.

POS(Substr, S) Pos searches for Substr within S and returns an integer value that is the index of the first character of Substr within S. Pos is case-sensitive. If Substr is not found, Pos returns zero.

VAL(s) - Converts the string value s to its numeric representation. If s is not a valid number, exception is raised.

UPPER(s) - Returns the specified character expression in uppercase.

LOWER(s) - Returns the specified character expression in lowercase.

TRIM(s) - Returns the specified character expression with all trailing blanks removed.

Data navigation

When DataTable, DataView or other ReportData item attached to the EK RTF report, it may be accessed using cursor navigation commands and functions. These functions are described below.

EOF(Table) - Indicates whether or not a cursor is positioned at the last record in a Table.

BOF(Table) - Indicates whether or not a cursor is positioned at the first record in a Table.

LAST(Table) - Positions the cursor on the last record in the Table.

FIRST(Table) - Positions the cursor on the first record in the Table.

NEXT(Table) - Positions the cursor on the next record in the Table.

PRIOR(Table) - Positions the cursor on the previous record in the Table.

For example \eof(a)\, \next(table1)\.
NOTE: You should not use these functions at the same time with SCAN having data table name. You may use these functions in SCAN with no name mentioned, for example:
\Scan(), while( ! eof(table1))\
.....
\endscan, next(table1)\

Numeric report functions.

ROUND(n,decimals) - The Round function rounds a real-type value to an integer-type value. 0.5 is always processed to largest integer number. This is NOT banker rounding.

INT(number) - Returns the integer part of a real number.

FRAC(number) - Returns the fractional part of a real number.

POWER(base, exponent) - Raises Base to any power. For fractional exponents or exponents greater than MaxInt, Base must be greater than 0.

INTPOWER(base, exponent) - Calculates the integral power of a base value. IntPower raises Base to the power specified by Exponent.

Date and time report functions.

NOW() - Returns the current date and time.

DATE() - Returns the current date.

TIME() - Returns the current time.

DATETOSTR(date) - Converts a date constituent of DateTime value to a string.

DATETIMETOSTR(datetime) - Converts a DateTime value to a string.

TIMETOSTR(time) - Converts a time constituent of DateTime value to a string.

STRTODATE(string) - Converts a string to a DateTime value. Time part is set to 0.

STRTODATETIME(string) - Converts a string to a DateTime value.

STRTOTIME(string) - Converts a time string to a DateTime value.

YEAR(date) - Returns the year of specified date.

MONTH(date) - Returns the month of specified date.

DAY(date) - Returns the day of specified date.

SYEAR(date) - Returns the year of date in string representation.

SMONTH(date) - Returns the month of date in string representation. Month which is less than 10 have zero in place of first symbol - "01", "02" and so on ...

SDAY(date) - Returns the day of date in string representation. Day which is less than 10 have zero in place of first symbol - "01", "02" and so on ...

DTOS(date) - Converts date to the string formatted as yyyymmdd.

STOD(string) - Converts string formatted as yyyymmdd to date value.

Format of IF-ENDIF construction is:

\If(<boolean value>)\
.............................................
\elsif(<boolean value>)\
.............................................
\elsif(<boolean value>)\
.............................................
\else\
.............................................
\endif\
\elsif\ and \else\ are optional.
<boolean value> may be a variable, data field or user defined function.

Cycle SCAN-ENDSCAN construction in report

All records of a data table, attached to the ReportData collection, may be inserted in a document as a table rows or in any free form. For this use keywords \Scan(DataTable)\ and \Endscan\. Inside cycle scan-endscan may be located a block of text with data fields, variables and constants, for example:

Lines with words "Scan", "Endscan" are excluded from the result document. However, if in step of designing a report you want to see as will look a result, you may set an attribute "hidden font" for words "Scan", "Endscan".
Full format of scan block is:

\Scan(DataTable) [, while(<boolean value>)] [,page] [,noeof] [,function1,...,functionN]\
............................................
\Scanentry [,function1,...,functionN]\
............................................
\Scanfooter [,function1,...,functionN]\
............................................
\Endscan [,function1,...,functionN]\

If keyword "while" defined in a scan expression, scan block will be terminated when <boolean value> returns false result. "While" is often used with records grouped by some data field. <boolean value> may be a report variable, data field or user defined function.
Option "page" forces to begin every record of scanned DataTable (besides first) from a new page.
If you use option "noeof" report generator will skip entire scan block if scanned DataTable have no any records. This option is useful when making master-details reports.
Attention
: with option "noeof" scan block will start from the current record. DataTable will not be moved to the first record.
Words "Scanentry" and "Scanfooter" are optional. You may add them when using option "noeof" in "scan" keyword, or if you want to develop some special functionality, calling optional scan block functions.
Use option "noeof" with keyword \Scanentry\ to manage scan block with some header section. Every new record of DataTable will return control to the position of \Scanentry\ keyword. However, if DataTable has no any records, entire block from "scan" to the "endscan" will be missed. For example:

Keyword \Scanfooter\ may be used to manage scan block with some footer section. Every time when report generator gets "Scanfooter", it returns control to the position of \Scanentry\ or \Scan\ keyword. If DataTable has no any records, entire block from "Scan" to the "Endscan" will be missed. For example:

\Scanentry\ and \Scanfooter\ may be used simultaneously:

NOTE

: You must type keywords \scan(...)\, \scanentry\ and \endscan\ all with the same format attributes, for example with font Arial, 10, regular (or other that you like). It guarantees that format attributes inside block scan-endscan will be correct in output document.
If you use "page" option and a table immediately after "scan" keyword in report template, keep in mind that you should have at least one paragraph (empty line) before the table in the RTF document, otherwise, RTF editor such as MS Word ignores "new page" control.

Recommended technique:
When editing report template, place keywords \scan(...)\, \endscan\ outside of table or in the same cell to prevent corrupting of RTF table structure.

Sum, count data inside a cycle

Optional functions may be used with commands "scan", "scanentry", "scanfooter", "endscan".
These functions are SUM, CTN, CTS. You may use these functions to sum or count values of data fields and report variables. Common format is:
\scancommand, ...., function1(source,destination)...functionN(...)\
Each function is performed when report generator gets corresponded scan command.
The first argument in each function is "source" data field or report variable. Result of each function is stored in a report variable, which is "destination" argument. Function may have noreset option - in this case its result will not be initiated with zero value if function was computed at least once. If result variable does not exists it will be created automatically.
Besides SUM, CTN, CTS functions you may call user defined functions.
Sometimes it is necessary to declare report variable inside the report template, especially if this variable is an argument for user defined function. For this use VAR function.
Sum function

SUM(source, destination [, noreset]) - totals data field or report variable.

source - data field or report variable to sum.
destination - report variable to store result of the function.
noreset - use this option if you don't want to initialize destination variable with zero value. New values of the source field will be added to the previous result of the function.

Example of a report:

\scan(a)\
\a:number\ - \a:value\
\endscan, sum(a:value,s_value)\
total: \s_value\

\scan(b)\
\b:number\ - \b:value\
\endscan, sum(b:value,s_value,noreset)\
All totals: \s_value\