一般我们可以使用公式来实现一些特殊的功能,但是对于一些需要重复处理的情况
可以使用自定义函数来实现
进入公式编辑界面 ,可以看到在左上角有两个项目:
报表自定义函数和储备库自定义函数
报表自定义函数只存在于单一报表模板内,不同模板间不可以共用。
储备库自定义函数对于单独的水晶报表和.Net自带的版本是不可用的,这是水晶报表服务器版本或BOE的一个功能
将函数转换为储备库自定义函数后,该函数将存储到系统数据库,从而不同报表可以进行函数共用。
不过对于单一函数功能来说并没有什么影响。
现在我结合一个实例,说一下自定义函数的使用。
有一个命题是这样的,将一个日期型的字段【2008-10-28】,显示为【二零零八年十月二十八日】
使用自定义日期格式是达不到这种效果的,虽然我们可以通过公式来实现,但是对于多个这种的字段,每次都写公式还是很麻烦的。
所以这里选择用自定义函数实现。
切换到公式编辑器界面,在【报表自定义函数】上点右键,新建一个函数,名称为mydate。
将语法切换为Basic(当然你也可以用Crystal语法),然后编写函数体
具体的函数内容我就不做说明了,这里我用了两个函数来是实现此功能。
另一个函数是ChNumber,用来处理mydate中的复用。
函数体如下
'进行日期转换
Function myDate (date1 as date)
Dim sY as string
dim sM as string
Dim sD as string
'分别取出年月日
sY=totext(year(date1),0)
sM=totext(month(date1),0)
sD=totext(day(date1),0)
myDate =chNumber(sY,"0") & "年" & chNumber(sM,"1") & "月" & chNumber(sD,"1") & "日"
End Function
Function myDate (date1 as date)
Dim sY as string
dim sM as string
Dim sD as string
'分别取出年月日
sY=totext(year(date1),0)
sM=totext(month(date1),0)
sD=totext(day(date1),0)
myDate =chNumber(sY,"0") & "年" & chNumber(sM,"1") & "月" & chNumber(sD,"1") & "日"
End Function
's待处理的参数,t参数类型。0为年份,逐个字符替换,1为月份和日期,10进位
Function ChNumber ( s as string, t as string)
Dim s0 as string
'先全部替换
s0=Replace(s,",","")
s0=Replace(s0,"0","零")
s0=Replace(s0,"1","一")
s0=Replace(s0,"2","二")
s0=Replace(s0,"3","三")
s0=Replace(s0,"4","四")
s0=Replace(s0,"5","五")
s0=Replace(s0,"6","六")
s0=Replace(s0,"7","七")
s0=Replace(s0,"8","八")
s0=Replace(s0,"9","九")
'进行月日的十进位处理
If t="1" then
'十进位处理,在两个数字中间加入一个“十”字
If Len(s0)=2 Then
s0=left(s0,1) & "十" & right(s0,1)
'处理刚好为10、20、30的情况
if left(s0,1)="一" then s0=replace(s0,"一十","十")
if right(s0,1)="零" then s0=replace(s0,"零","")
end if
end if
ChNumber =s0
End Function
Function ChNumber ( s as string, t as string)
Dim s0 as string
'先全部替换
s0=Replace(s,",","")
s0=Replace(s0,"0","零")
s0=Replace(s0,"1","一")
s0=Replace(s0,"2","二")
s0=Replace(s0,"3","三")
s0=Replace(s0,"4","四")
s0=Replace(s0,"5","五")
s0=Replace(s0,"6","六")
s0=Replace(s0,"7","七")
s0=Replace(s0,"8","八")
s0=Replace(s0,"9","九")
'进行月日的十进位处理
If t="1" then
'十进位处理,在两个数字中间加入一个“十”字
If Len(s0)=2 Then
s0=left(s0,1) & "十" & right(s0,1)
'处理刚好为10、20、30的情况
if left(s0,1)="一" then s0=replace(s0,"一十","十")
if right(s0,1)="零" then s0=replace(s0,"零","")
end if
end if
ChNumber =s0
End Function
然后我们来做个简单的测试:
新建立一个日期型的参数xx,
然后做一个公式yy,公式内容为:mydate({?xx})
显示为:二零零八年十月二十八日
@yimeng 刚好前几天我自己写了个自定义函数,给你试试看行不?getChinaNum(11820)就可以得到一万一千八百二十
getChinaNum(11820,true) 壹万壹仟捌佰贰十元整
——————————————————————————下面是函数部分
Function getChinaNum (othernum as double,optional isrmb as boolean=false, optional dotnum as number =0) as string
Dim num As String
num=replace(Trim(totext(otherNum,0)),",","")
num=replace(num," ","")
Dim numwei As String
Dim numshu As String
Dim i As number
Dim bstr As String
Dim numrmb As String
if isrmb then
numwei = "拾佰仟万拾佰仟亿拾佰仟"
numshu = "零壹贰叁肆伍陆柒捌玖拾"
else
numwei = "十百千万十百千亿十百千"
numshu = "零一二三四五六七八九十"
end if
If otherNum < 20 And otherNum >= 10 Then
num = Right(num, 1)
GetChinaNum = Left(numwei, 1)
End If
For i = 1 To Len(num)
bstr = Mid(num, i, 1)
GetChinaNum = GetChinaNum + Mid(numshu, Val(bstr) + 1, 1)
if len(num)=i then
do while right(getchinanum,1)="零"
getchinanum=left(getchinanum,len(getchinanum)-1)
loop
else
If bstr = "0" Then
If Mid(numwei, Len(num) - i, 1) = "万" Or Mid(numwei, Len(num) - i, 1) = "亿" Then
Do While Right(GetChinaNum, 1) = "零"
GetChinaNum = Left(GetChinaNum, Len(GetChinaNum) - 1)
Loop
GetChinaNum = GetChinaNum + Mid(numwei, Len(num) - i, 1)
End If
Else
GetChinaNum = GetChinaNum + Mid(numwei,len(num)-i ,1)
End If
end if
GetChinaNum = Replace(GetChinaNum, "零零", "零")
Next
If isRMB Then
numrmb = "元角分厘毫"
GetChinaNum = GetChinaNum + Mid(numrmb, 1, 1)
If Val(num) <> otherNum Then
dotNum = 4
num = Trim(CStr(Round(otherNum - tonumber(totext(otherNum,0)), dotNum)))
For i = 3 To Len(num)
bstr = Mid(num, i, 1)
GetChinaNum = GetChinaNum + Mid(numshu, Val(bstr) + 1, 1) + Mid(numrmb, i - 1, 1)
Next i
Else
GetChinaNum = GetChinaNum + "整"
End If
Else
If tonumber(num) <> otherNum Then
If dotNum = 0 Then dotNum = 4
num = Trim(CStr(Round(otherNum - Val(num), dotNum)))
If GetChinaNum = "" Then
GetChinaNum = "零"
Else
GetChinaNum = GetChinaNum + "点"
End If
For i = 3 To Len(num)
bstr = Mid(num, i, 1)
GetChinaNum = GetChinaNum + Mid(numshu, Val(bstr) + 1, 1)
Next i
End If
end if
End Function
回复 引用
getChinaNum(11820,true) 壹万壹仟捌佰贰十元整
——————————————————————————下面是函数部分
Function getChinaNum (othernum as double,optional isrmb as boolean=false, optional dotnum as number =0) as string
Dim num As String
num=replace(Trim(totext(otherNum,0)),",","")
num=replace(num," ","")
Dim numwei As String
Dim numshu As String
Dim i As number
Dim bstr As String
Dim numrmb As String
if isrmb then
numwei = "拾佰仟万拾佰仟亿拾佰仟"
numshu = "零壹贰叁肆伍陆柒捌玖拾"
else
numwei = "十百千万十百千亿十百千"
numshu = "零一二三四五六七八九十"
end if
If otherNum < 20 And otherNum >= 10 Then
num = Right(num, 1)
GetChinaNum = Left(numwei, 1)
End If
For i = 1 To Len(num)
bstr = Mid(num, i, 1)
GetChinaNum = GetChinaNum + Mid(numshu, Val(bstr) + 1, 1)
if len(num)=i then
do while right(getchinanum,1)="零"
getchinanum=left(getchinanum,len(getchinanum)-1)
loop
else
If bstr = "0" Then
If Mid(numwei, Len(num) - i, 1) = "万" Or Mid(numwei, Len(num) - i, 1) = "亿" Then
Do While Right(GetChinaNum, 1) = "零"
GetChinaNum = Left(GetChinaNum, Len(GetChinaNum) - 1)
Loop
GetChinaNum = GetChinaNum + Mid(numwei, Len(num) - i, 1)
End If
Else
GetChinaNum = GetChinaNum + Mid(numwei,len(num)-i ,1)
End If
end if
GetChinaNum = Replace(GetChinaNum, "零零", "零")
Next
If isRMB Then
numrmb = "元角分厘毫"
GetChinaNum = GetChinaNum + Mid(numrmb, 1, 1)
If Val(num) <> otherNum Then
dotNum = 4
num = Trim(CStr(Round(otherNum - tonumber(totext(otherNum,0)), dotNum)))
For i = 3 To Len(num)
bstr = Mid(num, i, 1)
GetChinaNum = GetChinaNum + Mid(numshu, Val(bstr) + 1, 1) + Mid(numrmb, i - 1, 1)
Next i
Else
GetChinaNum = GetChinaNum + "整"
End If
Else
If tonumber(num) <> otherNum Then
If dotNum = 0 Then dotNum = 4
num = Trim(CStr(Round(otherNum - Val(num), dotNum)))
If GetChinaNum = "" Then
GetChinaNum = "零"
Else
GetChinaNum = GetChinaNum + "点"
End If
For i = 3 To Len(num)
bstr = Mid(num, i, 1)
GetChinaNum = GetChinaNum + Mid(numshu, Val(bstr) + 1, 1)
Next i
End If
end if
End Function
回复 引用
我在用CR2008做一个报表,不设计编码的。
在一个表上有如下字段:供货商、日期、发票号码
现在做了一个级联参数供货商 --> 日期 --> 单号
希望通过选择供货商和日期来缩小单号的选择范围。
供货商和日期都设置了允许多个值
现在表里有如下数据
vendor date purchasing num
A 20090623 001
A 20090624 002
B 20090623 003
B 20090624 004
问题是,如果供货商先选了A然后再加入B,日期选了20090623,单号仅仅出现了001,也就是供货商A相关的
反过来如果供货商先选了B然后再加入A,日期选了20090623,单号仅仅出现了003,也就是供货商B相关的
如果供货商一下子全选,日期选了20090623,单号仅仅出现了001,也就是供货商A相关的
也就是说,虽然供货商里选择了多个值,但最后单号显示的,仅仅和第一个供货商值相关而已
这是bug还是哪儿参数设置错误的问题呢?
回复 引用
在一个表上有如下字段:供货商、日期、发票号码
现在做了一个级联参数供货商 --> 日期 --> 单号
希望通过选择供货商和日期来缩小单号的选择范围。
供货商和日期都设置了允许多个值
现在表里有如下数据
vendor date purchasing num
A 20090623 001
A 20090624 002
B 20090623 003
B 20090624 004
问题是,如果供货商先选了A然后再加入B,日期选了20090623,单号仅仅出现了001,也就是供货商A相关的
反过来如果供货商先选了B然后再加入A,日期选了20090623,单号仅仅出现了003,也就是供货商B相关的
如果供货商一下子全选,日期选了20090623,单号仅仅出现了001,也就是供货商A相关的
也就是说,虽然供货商里选择了多个值,但最后单号显示的,仅仅和第一个供货商值相关而已
这是bug还是哪儿参数设置错误的问题呢?
回复 引用