crystal report syntx
報表首頁首
群組首
細目
群組尾
報表尾
頁尾
---------------------------------------------------------------------------------
IF
Local CurrencyVar tax := 0;
Local CurrencyVar income := {Employee.Salary};
Local StringVar message := "";
If income In ["604", "250"] Then
(
tax := 0
)
Else If income >= 20000 And income < 35000 Then
(
message := "middle";
)
Else
(
message := "highest";
);
---------------------------------------------------------------------------------
FOR LOOP (Exit For)
Local StringVar str := "";
Local NumberVar strLen := Length ({Customer.CustomerName});
Local NumberVar i;
For i := 1 To strLen Step 1 Do
(
Local NumberVar charPos := strLen - i + 1;
str := str + {Customer.CustomerName}[charPos]
);
str
---------------------------------------------------------------------------------
SELECT CASE
Select {Employee.Salary}
Case 1,2,3, Is < 1 :
(
"low"
)
Case 4 To 6, 7, 8, 9 :
"medium"
Case 10 :
"high"
Default :
"extreme"
---------------------------------------------------------------------------------
SELECT CASE 2
select trim({DeliveryDetail.TAX_CODE})
case "稅外加5%" : Sum ({@稅額小計}, {DeliveryDetail.DELIVERY_ID})
case "稅內含5%" : 0
case "免稅" : 0
case "零稅" : 0
case "-" : 0
case "" : 0
default : 0
---------------------------------------------------------------------------------
WHILE LOOP
Local StringVar inString := "The7Dwarves";
Local NumberVar strLen := Length(inString);
Local NumberVar result := -1;
Local NumberVar i := 1;
While i <= strLen And result = -1 Do
(
Local StringVar c := inString[i]; //從字串取出字元
If NumericText(c) Then //是否為數字
result := i;
i := i + 1;
If i > {TABLE1.ORDER_NUMBER} Then
Exit While;
);
result
---------------------------------------------------------------------------------
函式/列印狀態:
GroupNumber 群組序號
PageNumber 頁次
RecordNumber 資料錄序號
TotalPageCount 總頁數
OnFirstRecord
OnLastRecord
PageNofM
---------------------------------------------------------------------------------
摘要:
Sum({Sales.QTY}) >sum of all QTY field.
Sum({orders.AMOUNT}, {orders.CUSTOMER ID}) >Sum in each group.
Average({orders.ORDER AMOUNT}) >average of all values.
Average ({orders.ORDER AMOUNT}, {customer.CUSTOMER ID}) >average to each group.
Count ({Sales.QTY}) >count
Count ({Sales.QTY},{Sales.GroupField}) >count per group
DistinctCount ({Sales.QTY}) >distinct count
DistinctCount ({Sales.QTY},{Sales.GroupField}) >distinct count and per group
Remainder(8, 3) => 2
Abs(number)
Round(number, decimals)
truncate(123.689) => 123
Miximum(number)
Minimum(number)
YEAR(CurrentDate)
MONTH(CurrentDate)
DAY(CurrentDate)
---------------------------------------------------------------------------------
字串:
mid (str,startIndex,length) //startIndex from 1
right(str,length)
left(str,length)
replace(str,oldStr,newStr)
len(str)
Instr(str,subStr) //startIndex from 1
Trim(str)
Space(x)
StrReverse (str)
UpperCase(str)
LowerCase(str)
Split(str,'分隔符號') //Split('A/B/C','/')[1]
VAL('123A') => 123
---------------------------------------------------------------------------------
EQUAL OPERATOR
equal (=), not equal (<>), less than (<), less than or equal (<=), greater than (>) , greater than or equal (>=)
IIF(condition, true_result, false_result)
Switch(condition1, result1, condition2, result2, ….)
NOT
AND
OR
IF Not( {INVOICE.ITEM} IN ['Move','Daywork','Repair','Turnkey'] ) THEN
---------------------------------------------------------------------------------
CONVERT DATA TYPES:
CBool(number), CBool(currency)
Convert to Boolean.
CCur(number), CCur(string)
Convert to Currency.
CDbl(currency), CDbl(string), CDbl(boolean)
Convert to Number. Equivalent to ToNumber().
CStr({Customer.BirthDate},'yyyy-MM-dd')
Convert to String. Equivalent to ToText().
CDate(string), CDate(year, month, day), CDate(DateTime)
Convert to Date.
CTime(string), CTime(hour, min, sec), CDate(DateTime)
Convert to Time.
CDateTime(string),CDateTime(date), CDateTime(date, time),CDateTime(year, month, day)
Convert to DateTime.
CDateTime(year, month, day, hour,min, sec)
Convert to DateTime.
ToNumber(string), ToNumber(boolean)
Convert to a Number.
ToText({Customer.BirthDate},'yyyy-MM-dd')
ToText({Customer.BirthDate}, 'HH:mm:ss')
ToText(12345.678,2) => 12345.67
ToText(12345.678,0) => 12345
ToText(12345.678, 2, ',','.') => 12,345.67 轉換數字/小數位數/千分位符號/小數位符號
IsNull(str) , NOT IsNull(str)
IsDate(string), IsTIme(), IsDateTime()
Test a string for being a valid date/time.
IsNumber(string)
Test a string for being a valid number.
ToWords(number), ToWords(number, decimals)
Convert a number to its word equivalent.
---------------------------------------------------------------------------------
basic syntax:
if isnull({TABLE1.COLUMN1}) then
formula = {TABLE1.COLUMN1}
else
formula = 0
end if
---------------------------------------------------------------------------------
crystal syntax:
if (len({@ToWord})-3 > 0) then
mid({@ToWord},len({@ToWord})-3,1)
else
"零"
---------------------------------------------------------------------------------
資料型態:
Local NumberVar x := 10.23 ;
Local StringVar y := "Hello World";
Local DateVar z := CDate (#8/6/1976#);
Local currencyVar a := NTS100.23
Local BooleanVar b := True
---------------------------------------------------------------------------------
顯示每頁資料筆數:
累加值欄位
摘要欄位
計數(count)
驗算:每個資料錄
重設:群組變更時
---------------------------------------------------------------------------------
顯示每頁小計資料:
累加值欄位
摘要欄位
總和(sum)
驗算:每個資料錄
重設:群組變更時
---------------------------------------------------------------------------------
使用WhilePrintingRecords函式來對 [每頁] 做出統計資料:
Crystal Report 的摘要只能針對整份報表或是群組
1)新增公式欄位 ResetPage：在每頁的 Page Header (頁首) 將小計變數重設為 0
WhilePrintingRecords;
Global currencyVar PageSum:= 0;
2)新增公式欄位 CalcPage：在 Details (細目) 累加每筆資料的值
WhilePrintingRecords;
Global currencyVar PageSum;
PageSum := PageSum + {客戶.業績};
3)新增公式欄位 ShowPage：在每頁的 Page Footer (頁尾) 顯示當頁統計結果
WhilePrintingRecords;
Global currencyVar PageSum;
PageSum;
---------------------------------------------------------------------------------
分頁: 設定每頁20筆資料
報表空白處/右鍵/區段格式設定/[細目]/在之後新增分頁/ 公式
IIF (OnLastRecord ,false ,if RecordNumber mod 20 = 0 then true else false );
OR
Truncate (RecordNumber/20)*20=RecordNumber and OnLastRecord=false
---------------------------------------------------------------------------------
分頁頁碼: 以群組為依據
群組尾/右鍵/區段專家 : [ 群組尾 #1 ]設定，「在之後重設頁碼」
[第N頁/共M頁] 放在頁首區域內
---------------------------------------------------------------------------------
群組換頁: 以群組為依據
1) 點選 [ 群組尾 #1 ] 所在的區塊標題，右鍵 --> 區段專家 --> 將 [ 在之後新增分頁(F) ] 的選項打勾
2) 利用公式判斷目前顯示的資料是否已到最後一筆，讓報表自動結束分頁
if OnLastRecord then false else true
---------------------------------------------------------------------------------
欄位設定:
隱藏欄位 格式/一般/抑制顯示
格式/字型/色彩/公式
IF {#SUMTEST} > 300 THEN
crPurple
ELSE
crGreen
---------------------------------------------------------------------------------
加入參數欄位:
1) 參數欄位/右鍵/新增
2) 報表空白處/右鍵/報表/編輯選擇公式/資料錄/{Customer.Name} = {?CustomerName}
---------------------------------------------------------------------------------
{?ORDER_NUMBER} 參數欄位
{#ORDER_NUMBER} 累加欄位
{@ORDER_NUMBER} 公式欄位
{TABLE1.COLUMN1} 資料庫欄位
---------------------------------------------------------------------------------
公式欄位 民國年月日
Trim(CStr(Year(CurrentDate) - 1911, '#00')) + '年' + CStr(Month(CurrentDate), '00') + '月' + CStr(Day(CurrentDate), '00') + '日'
---------------------------------------------------------------------------------
更新資料欄位(DataSet.xsd)
1.調整 xxx.xsd
2.報表設計\資料庫欄位\右鍵\設定位置\取代
或
報表設計\資料庫欄位\右鍵\驗證資料庫\重新整理
---------------------------------------------------------------------------------
顯示SQL查詢
當TABLE是使用資料庫來源時，才有此選項
若使用DataSet.xsd 則無此選項
顯示SQL查詢時,一張報表只有一個 SQL,多個TABLE 是以同一SQL JOIN而來
DataSet.xsd 也是以ADO.NET JOIN TABLE 後再給xsd
---------------------------------------------------------------------------------
根據命令/SQL 查詢定義虛擬資料表
https://msdn.microsoft.com/zh-tw/library/ms227153(v=vs.90).aspx#
撰寫自己的命令做為 Crystal Reports 中的 Table 物件。如此一來有經驗的資料庫使用者便可完全控制下推至資料庫伺服器的資料處理。一個有經驗的使用者可以撰寫高度最佳化的命令，以大幅縮減伺服器所傳回的資料組大小。
---------------------------------------------------------------------------------
---------------------------------------------------------------------------------
轉換成中文大寫 (會有誤差和遺漏)
ToWord()
22680 --> 貳萬貳千陸佰捌拾
---------------------------------------------------------------------------------
數字轉中式大寫的 Crystal Report 公式 (替代 ToWord())
NumberVar VNT := {@TOTAL_AMT};
StringVar VSU := '零壹貳參肆伍陸柒捌玖';
StringVar VST := '仟佰拾兆仟佰拾億仟佰拾萬仟佰拾元';
StringVar VNS := ToText(VNT, '0000000000000000');
NumberVar VSL := 17 - Length(TrimLeft(ToText(VNT, '################')));
StringVar VSS := '';
BooleanVar VSF;
BooleanVar VS0;
BooleanVar VS1;
BooleanVar VS2;
NumberVar VNI;
VSF := False;
for VNI := VSL to 16 do
(
VS1 := Mid(VNS, VNI, 1) <> '0';
VS2 := VS1 or (((VNI mod 4) = 0) and ((Mid(VNS, VNI - 3, 4) <> '0000') or (VNI = 16)));
VS0 := VSF and VS1;
VSF := not (VS0 or VS1 or VS2);
if VS0 then
VSS := VSS + '零';
if VS1 then
VSS := VSS + Mid(VSU, ToNumber(Mid(VNS, VNI, 1)) + 1, 1);
if VS2 then
VSS := VSS + Mid(VST, VNI, 1);
);
// 這是回傳值
VSS;
=====================
14,333
壹萬肆仟參佰參拾參元
2,310
貳仟參佰壹拾元
840
捌佰肆拾元
---------------------------------------------------------------------------------