水晶报表的使用:
在设计好水晶报表后添加到VB的设计环境里,如图是设计好的云南昆石高速公路报表之一
然后再Param2字段上点击右健,选择格式字段,弹出格式化编辑器的对话框,注意Param2字段是整形数,我们要把它格式化成不同的字符串。
点击完毕出现设计公式工作室,如此继续对Param2进行编辑:
代码如下:
if {命令.EventTypeId}=53 and {命令.Param2}=0 then
"关闭"
else if {命令.EventTypeId}=53 and {命令.Param2}=1 then
"正转"
else if {命令.EventTypeId}=53 and {命令.Param2}=2 then
"反转"
else if {命令.EventTypeId}=62 and {命令.Param2}=0 then
"关"
else if {命令.EventTypeId}=62 and {命令.Param2}=1 then
"开"
else if {命令.EventTypeId}=71 and {命令.Param2}=0 then
"下"
else if {命令.EventTypeId}=71 and {命令.Param2}=1 then
"上"
else if {命令.EventTypeId}=71 and {命令.Param2}=2 then
"停"
else if {命令.EventTypeId}=74 and {命令.Param2}=0 then
"取消"
else if {命令.EventTypeId}=74 and {命令.Param2}=1 then
"确认"
else if {命令.EventTypeId}=75 then
CStr({命令.Param2}+1)+"模式"
else if {命令.EventTypeId}=76 and {命令.Param2}=0 then
"自动"
else if {命令.EventTypeId}=76 and {命令.Param2}=1 then
"手动"
不用我多做解释,聪明如你,一看就明白了。注意上面的代码必须将编辑器语法改为Crystal语法,如果读者熟悉Basic语法,就改为Basic语法,熟悉Delphi 的程序员对这个肯定有“似曾相似燕归来”的感觉,如果不明白为什么,可以问问Delphi程序员 :)。不光是语法上的相识,和在Delphi中的ADOQuery ,ADOTable等的GetText,和setText函数方式基本相似。
我们在定义好水晶报表以后怎样动态的显示我想要的报表,这要使用推模式。不多罗嗦,下面提供了几段VB源代码,可以对初学者有所启发。
Public Sub View_Report_LogSys()
Dim Report As New Report_Logsys
Report.Report_Title.SetText Report_ReportTitle
Report.Report_PrintDate.SetText Report_DateStart
Screen.MousePointer = vbHourglass
txtSQL = "Select * From VIEW_LOGSYS where Year (EventDate) = " & Report_Year
If Report_Type > 1 Then
temp = temp & " and Month(EventDate)=" & Report_Month
End If
If Report_Type > 2 Then
temp = temp & " and Day(EventDate)=" & Report_Day
End If
Select Case Report_EventType
Case 0
temp = temp
Case 1
temp = temp & " and EventTypeID between 101 and 105"
Case 2
temp = temp & " and EventTypeID between 201 and 205"
Case 3
temp = temp & " and EventTypeID between 301 and 304"
Case 4
temp = temp & " and EventTypeID between 401 and 402"
Case 5
temp = temp & " and EventTypeID between 501 and 504"
Case 6
temp = temp & " and EventTypeID between 601 and 604"
End Select
'temp = temp & " and EventTypeID =" & Report_EventType
txtSQL = txtSQL & temp
Set RstSql = New Recordset
RstSql.Open txtSQL, cnnMain, adOpenKeyset, adLockOptimistic, adCmdText
Report.Database.SetDataSource (RstSql)
CRViewer91.ReportSource = Report
CRViewer91.ViewReport
Screen.MousePointer = vbDefault
'dim report as New
End Sub
Public Sub View_Report_DC()
Dim temp As String
Dim Report As New Report_DC
temp = ""
Report.Report_Title.SetText Report_ReportTitle
Report.Report_PrintDate.SetText Report_DateStart
Report.Report_ZhuangHao.SetText Report_ZhuangHao
Screen.MousePointer = vbHourglass
txtSQL = "select * from View_ControlDevice where Year(EventDate) = " & Report_Year
If Report_Type > 1 Then
temp = temp & " and Month(EventDate)=" & Report_Month
End If
If Report_Type > 2 Then
temp = temp & " and Day(EventDate)=" & Report_Day
End If
Select Case Report_DeviceID
Case "74"
temp = temp & " and EventTypeID= 74"
Case "75"
temp = temp & " and EventTypeID =75"
Case "76"
temp = temp & " and EventTypeID =76"
Case Else
temp = temp & " and DeviceID =" & Report_DeviceID
End Select
txtSQL = txtSQL & temp
Set RstSql = New Recordset
RstSql.CursorType = adOpenKeyset
RstSql.LockType = adLockOptimistic
RstSql.Open txtSQL, cnnMain, adOpenKeyset, adLockOptimistic, adCmdText
Report.Database.SetDataSource (RstSql)
CRViewer91.ReportSource = Report
CRViewer91.ViewReport
Screen.MousePointer = vbDefault
End Sub
以下为数据库连接:SQLServer 2000配置文件为Ini文件.
Public Sub MakeConnect()
Dim strServer As String
Dim strUid As String
Dim strPwd As String
Dim strDB As String
Dim strSql As String
strServer = ReadFromINI(App.Path & "/" & "ODBC.ini", "odbc", "server")
strUid = ReadFromINI(App.Path & "/" & "ODBC.ini", "odbc", "uid")
strPwd = ReadFromINI(App.Path & "/" & "ODBC.ini", "odbc", "pwd")
strDB = ReadFromINI(App.Path & "/" & "ODBC.ini", "odbc", "database")
strSql = "Driver={SQL Server};Server=" + strServer + ";Address=" + strServer + ",1433;Network=DBMSSOCN;Database=" + strDB + ";Uid=" + strUid + ";Pwd=" + strPwd
//此段非明文密码,加解密省略
On Error GoTo ErrHandle
Set cnnMain = New ADODB.Connection
cnnMain.ConnectionTimeout = 30
cnnMain.Open strSql
Exit Sub
ErrHandle:
Call HandleError
End Sub
'ConnectionString为连接本地数据库
'Public Const ConnectionString = "Driver={SQL Server};" & _
' "Server=LIJUN;" & _
' "Database=JianKong;" & _
' "Uid=sa;Pwd=sa"
'
''str2为连接网络数据库
'Public Const str2 = "Driver={SQL Server};" & _
' "Server=218.195.60.3;" & _
' "Address=218.195.60.3,1433;" & _
' "Network=dbmssocn;" & _
' "Database=JianKong;" & _
' "Uid=sa;Pwd=sa"
'下面三个变量在每次执行SQL语句时使用
'MsgText 用来返回"查询到" & rst.RecordCount & " 条记录 "语句
'txtSQL 用来存放SQL语句
'mrcc 用来存放执行完txtSQL后返回的记录集
Public MsgText As String
Public txtSQL As String
Public mrcc As ADODB.Recordset
Public Function ExecuteSQL(ByVal SQL As String, MsgString As String) As ADODB.Recordset
'executes SQL and returns Recordset
Dim cnn As ADODB.Connection
Dim rst As ADODB.Recordset
Dim sTokens() As String
On Error GoTo ExecuteSQL_Error
sTokens = Split(SQL)
Set cnn = New ADODB.Connection
cnn.Open ConnetStr
If InStr("INSERT,DELETE,UPDATE,BACKUP", UCase$(sTokens(0))) Then
cnn.Execute SQL
MsgString = sTokens(0) & " query successful"
Else
Set rst = New ADODB.Recordset
rst.Open Trim$(SQL), cnn, adOpenKeyset, adLockOptimistic
'rst.MoveLast 'get RecordCount
Set ExecuteSQL = rst
MsgString = "查询到" & rst.RecordCount & " 条记录 "
' MsgBox rst.RecordCount
End If
ExecuteSQL_Exit:
Set rst = Nothing
Set cnn = Nothing
Exit Function
ExecuteSQL_Error:
MsgString = "查询错误: " & _
Err.Description
Resume ExecuteSQL_Exit
End Function