Crystal Reports Knowledge Base
Visual Studio 2003 Merge Module Setup
How to format a number turned to text…
totext({ScoreFile.Part4},0,"")
The above code will change the value 45.00 to 45
New code for Visual Studio 2008 Web
<%@ Page
Language="vb"
AutoEventWireup="false"
CodeBehind="Default.aspx.vb"
Inherits="cr._Default"
%>
<%@ Register
assembly="CrystalDecisions.Web,
Version=10.5.3700.0, Culture=neutral, PublicKeyToken=692fbea5521e1304"
namespace="CrystalDecisions.Web"
tagprefix="CR"
%>
<!DOCTYPE html PUBLIC
"-//W3C//DTD XHTML 1.0 Transitional//EN"
"http://www.w3.org/TR/xhtml1/DTD/xhtml1-transitional.dtd">
<html xmlns="http://www.w3.org/1999/xhtml"
>
<head runat="server">
<title></title>
</head>
<body>
<form id="form1" runat="server">
<div>
<CR:CrystalReportViewer ID="CrystalReportViewer1" runat="server" AutoDataBind="true" />
</div>
</form>
</body>
</html>
Imports
CrystalDecisions.CrystalReports.Engine
Imports
CrystalDecisions.Shared
Partial Public Class _Default
Inherits
System.Web.UI.Page
Protected Sub Page_Load(ByVal
sender As Object,
ByVal e As
System.EventArgs) Handles Me.Load
Dim
conn As New
ConnectionInfo
Dim log
As TableLogOnInfo
Dim tbl
As Table
With
conn
.ServerName = "PEAK10-SQL01\PEAK10SQL2008"
.DatabaseName = "smtbank-pics"
.UserID = "SMTBankUser"
.Password = "xxxxxxxx"
End With
Dim doc
As New
ReportDocument
doc.Load(Server.MapPath("Items.rpt"))
For Each myTable In
doc.Database.Tables
log = myTable.LogOnInfo
With
log.ConnectionInfo
.UserID = "SMTBankUser"
.Password = " xxxxxxxx"
End With
myTable.ApplyLogOnInfo(log)
Next
myTable
CrystalReportViewer1.ReportSource = doc
CrystalReportViewer1.SelectionFormula =
"{Items.ID_Item} < 20"
CrystalReportViewer1.RefreshReport()
End Sub
End Class
‘************* Simple Method 1 ***************************************
‘GOOD FOR .NET WINDOWS FORMS….
CrystalDecisions.CrystalReports.Engine and CrystalDecisions.Shared MUST BE ADDED AS A REFERENCE AS WELL AS IN THE ‘IMPORTS’ STATEMENT!
Dim doc As New CrystalDecisions.CrystalReports.Engine.ReportDocument()
doc.Load(vPath & "\" & "Passltr.rpt")
Dim conn As New ConnectionInfo()
Dim log As TableLogOnInfo
Dim tbl As Table
With conn
.ServerName = ""
.DatabaseName = vDatabase
.UserID = ""
.Password = ""
End With
For Each tbl In doc.Database.Tables
log = tbl.LogOnInfo
log.ConnectionInfo = conn
tbl.ApplyLogOnInfo(log)
Next
doc.SetParameterValue("MyParameterName", "Coolio")
doc.RecordSelectionFormula = "{Candidate.Overall_Status} = 'FAIL'”
CrystalReportViewer1.ReportSource = doc
‘*********************************************************************
‘************* Simple Method 2 ***************************************
‘GOOD FOR WEB FORMS SQL SERVER….
Imports CrystalDecisions.CrystalReports.Engine
Imports CrystalDecisions.Shared
Public Class WebForm1
Inherits System.Web.UI.Page
#Region " Web Form Designer Generated Code "
'This call is required by the Web Form Designer.
<System.Diagnostics.DebuggerStepThrough()> Private Sub InitializeComponent()
End Sub
Protected WithEvents CrystalReportViewer1 As CrystalDecisions.Web.CrystalReportViewer
'NOTE: The following placeholder declaration is required by the Web Form Designer.
'Do not delete or move it.
Private designerPlaceholderDeclaration As System.Object
Private Sub Page_Init(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles MyBase.Init
'CODEGEN: This method call is required by the Web Form Designer
'Do not modify it using the code editor.
InitializeComponent()
End Sub
#End Region
Private Sub Page_Load(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles MyBase.Load
'Crystal Reports with SQL Server...
Dim doc As CrystalDecisions.CrystalReports.Engine.ReportClass
If Session("ServiceOrMode") = "Service" Then
doc = New Report1
Else
doc = New Report1
End If
Dim conn As New ConnectionInfo
Dim log As TableLogOnInfo
Dim tbl As Table
Dim vDataBase As String
With conn
.ServerName = "SCHIMSKYPC"
.DatabaseName = "Exam"
.UserID = ""
.Password = ""
End With
For Each tbl In doc.Database.Tables
log = tbl.LogOnInfo
log.ConnectionInfo = conn
tbl.ApplyLogOnInfo(log)
Next
'doc.SetParameterValue("MyParameter", Session("CrystalParameterValue"))
'**************************************************************
doc.RecordSelectionFormula = "{Sites.Site_Name} = 'ISC2'"
CrystalReportViewer1.ReportSource = doc
'*********************************************************************
End Sub
End Class
‘************* Simple Method 3 ***************************************
‘GOOD FOR WEB FORMS ACCES DATABASE….
Dim doc As CrystalDecisions.CrystalReports.Engine.ReportClass
If Session("ServiceOrMode") = "Service" Then
doc = New WebFees1()
Else
doc = New WebFees2()
End If
Dim conn As New ConnectionInfo()
Dim log As TableLogOnInfo
Dim tbl As Table
Dim vDataBase As String
If Session("DataBase") <> "" Then
vDataBase = Session("DataBase")
Else
vDataBase = Server.MapPath("CP.mdb")
End If
With conn
.ServerName = ""
.DatabaseName = vDataBase
.UserID = ""
.Password = ""
End With
For Each tbl In doc.Database.Tables
log = tbl.LogOnInfo
log.ConnectionInfo = conn
tbl.ApplyLogOnInfo(log)
Next
doc.SetParameterValue("MyParameter", Session("CrystalParameterValue"))
'**************************************************************
doc.RecordSelectionFormula = Session("SelectionFormula")
CrystalReportViewer1.ReportSource = doc
‘*********************************************************************
‘************* Simple Method 4 ***************************************
Dim cr As New MyReport()
Me.Cursor = Cursors.WaitCursor
cr.RecordSelectionFormula = "{candidate.ssn} = '38070'"
CrystalReportViewer1.ReportSource = cr
Me.Cursor = Cursors.Default
MsgBox("Done")
‘***********************************************************************
VB6 Sample…
Private Sub Form_Load()
strParam = cmbLev.Text
Dim Application As New CRAXDRT.Application
Dim Report As CRAXDRT.Report
Dim ConnectionInfo As CRAXDRT.ConnectionProperties
Set Report = Application.OpenReport("J:\can\isc2sql\Plaque6a1.rpc")
For tc = 1 To Report.Database.Tables.Count
Set ConnectionInfo = Report.Database.Tables(tc).ConnectionProperties
ConnectionInfo.Item("Data Source") = "Aragorn"
ConnectionInfo.Item("Initial Catalog") = "CP"
ConnectionInfo.Item("User ID") = "CPUser"
ConnectionInfo.Item("Password") = "CPUser101"
Next tc
' Report.ParameterFields.Item(1).AddCurrentValue (strParam)
frmShowReport.CRViewer91.ReportSource = Report
frmShowReport.CRViewer91.DisplayGroupTree = False
frmShowReport.CRViewer91.ViewReport
frmShowReport.CRViewer91.Zoom (75)
frmShowReport.Show 1
End Sub
VB6 Crystal Report Sample
Reference=*\G{00020430-0000-0000-C000-000000000046}#2.0#0#WINDOWS\System32\stdole2.tlb#OLE Automation Object={8767A745-088E-4CA6-8594-073D6D2DE57A}#9.2#0; crviewer9.dll
Reference=*\G{AF376990-6120-4E28-96DD-63FD2DC27B7A}#9.0#0#ProgramFiles\Crystal Decisions\Report DesignerComponent\craxdrt9.dll#Crystal Reports 9 ActiveX Designer Run TimeLibrary
Reference=*\G{2B78E590-E7D0-49BF-8F16-CFD6324DB307}#9.0#0#ProgramFiles\Common Files\Crystal Decisions\2.0\bin\craxddrt9.dll#CrystalReports 9 ActiveX Designer Design and Runtime Library
Dim Application As New CRAXDRT.Application
Dim Report As CRAXDRT.Report
Set Report = Application.OpenReport("c:\report1.rpt")
'********************************************************************
'Change the mdb location at runtime... '*
'This report uses the Candidate & Registry Tables, 2 tables... '*
Report.Database.Tables(1).Location = "c:\cp.mdb" '*
Report.Database.Tables(2).Location = "c:\cp.mdb" '*
'********************************************************************
'Report.SQLQueryString = "SELECT CANDIDATE.*, REGISTRY.* FROM CANDIDATE INNER JOIN REGISTRY ON CANDIDATE.SSN = REGISTRY.SSN;"
'Report.RecordSelectionFormula = "{Candidate.Lname} like 'SM*'"
'Supply Parameter Field Info...
'Report.ParameterFields.Item(1).AddCurrentValue ("Hello World")
CRViewer91.ReportSource = Report
CRViewer91.DisplayGroupTree = False
CRViewer91.ViewReport
CRViewer91.Zoom (75)
Public Class WebForm1
Inherits System.Web.UI.Page
Protected WithEvents CrystalReportViewer1 As CrystalDecisions.Web.CrystalReportViewer
Private Sub cmdReport_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles cmdReport.Click
If Not IsDate(txtExamDate.Text) Or txtSiteCode.Text.Trim = "" Then
MessageBox.Show("Enter A Valid Exam Date And Site Code", "Error", MessageBoxButtons.OK, MessageBoxIcon.Information)
Exit Sub
End If
Me.Cursor = Cursors.WaitCursor
Dim cr As New RosterRpt()
Dim conn As New ConnectionInfo()
Dim log As TableLogOnInfo
Dim tbl As Table
With conn
.ServerName = ""
.DatabaseName = vDatabase
.UserID = ""
.Password = ""
End With
For Each tbl In cr.Database.Tables
log = tbl.LogOnInfo
log.ConnectionInfo = conn
tbl.ApplyLogOnInfo(log)
Next
'************* Load the paramter **************************
Dim paramvals As ParameterValues
Dim paramDVal As ParameterDiscreteValue
Dim paramFlds As ParameterFieldDefinitions
Dim paramFld As ParameterFieldDefinition
paramFlds = cr.DataDefinition.ParameterFields
'The Name Of The Paramater Field....
paramFld = paramFlds.Item("CISSPorSSCP")
paramDVal = New ParameterDiscreteValue()
paramDVal.Value = "Exam Date: " & txtExamDate.Text
paramvals = paramFld.CurrentValues
paramvals.Add(paramDVal)
paramFld.ApplyCurrentValues(paramvals)
'**************************************************************
cr.RecordSelectionFormula = "{candidate.ExamDate} = " & CrystalDate(txtExamDate.Text) & " and {candidate.SiteCode} = '" & txtSiteCode.Text & "'"
CrystalReportViewer1.ReportSource = cr
Me.Cursor = Cursors.Default
End Sub
End Class
Public Function CrystalDate(ByVal strD As String)
'Function accepts a date string and returns a Crystal Reports Formatted Date...
Dim d As Date
d = CType(strD, Date)
Dim str As String
str = "Date(" & d.Year.ToString & "," & d.Month.ToString & "," & d.Day.ToString & ")"
Return str
End Function
Formula PassFailCount. Place in the Details section
‘This formula will calculate the total pass and fail records in a group, as well as the pass percentage.
Global gblPass as number
Global gblFail as number
global gblSchool as string
Global gblPassPercent as number
Whilereadingrecords
'******Initialize the global variables on a group change ******
if gblSchool <> {IDSchool.School} then
gblPass = 0
gblFail = 0
gblSchool = {IDSchool.School}
end if
'**************************************************************
if {ScoreFile.STATUS} = "PASS" then
gblPass = gblPass + 1
end if
if {ScoreFile.STATUS} = "FAIL" then
gblFail = gblFail + 1
end if
'*** Here we are constantly upating the gblPassPercnet based on the new ****
'*** Pass & Fail Counts
if gblPass + gblFail > 0 then
gblPassPercent = (gblPass/(gblPass + gblFail)) * 100
end if
'****************************************************************************
'Return Nothing
formula = ""
Formula ShowPassPercent. Place in the Group Footer section
global gblPassPercent as number
Whilereadingrecords (Whileprintingrecords seems to work better)
formula = gblPassPercent
Formula ShowPass. Place in the Group Footer section
Global gblPass as number
Whilereadingrecords (Whileprintingrecords seems to work better)
formula = gblPass
Formula ShowFail. Place in the Group Footer section
Global gblFail as number
Whilereadingrecords (Whileprintingrecords seems to work better)
formula = gblFail
How to create alternating color stripes
Right-Mouse click in the left margin of the “Details Section”.
Choose “Section Expert…” from the menu.
Select the “Color” Tab from the dialog box.
Click on the Create Formula Button.
Here’s the formula to enter….
WhilePrintingRecords
global MyCounter as number
MyCounter = MyCounter + 1
if remainder(MyCounter,2) = 0 then
formula = crRed
else
formula = crWhite
end if
Convert XML String Date into A Real Date
dim strDate as string
dim MyDate as date
'Get the date from the xml data...
strDate = {TableXML.EXAMDATE}
'Parse out just the date portion of the xml string...
strDate = mid(strDate,6,2) + "-" + mid(strDate,9,2) + "-" + mid(strDate,1,4)
'Convert the string to a date...
myDate = CDate(strDate)
'Add 5 days to the date....
MyDate = MyDate + 5
formula = MyDate
Global variables use the same memory block to store a value throughout the main report. This value is then available to all formulas that declare the variable, except for those in subreports. Declare a global variable as in the following example:
Global y As String
Since global variables share their values throughout the main report, you cannot declare a global variable in one formula with one type and then declare a global variable with the same name in a different formula with a different type.
When to Use Global Variables
Global variables are often used to perform complex calculations where the results of a formula depend upon the grouping and page layout of the actual printed report. This is accomplished by creating several formulas, placing them in different sections of the report, and having the different formulas interact via global variables.
Example
Rem Formula C
Global x as Number
x = 10
formula = x
Rem Formula D
'call the function WhileReadingRecords
WhileReadingRecords
Global x as Number
x = x + 1
formula = x
If Formula C is placed in the Report Header and then Formula D is placed in a detail section, Formula C will be evaluated before Formula D. Formula C will be evaluated once and then Formula D will be evaluated for each record appearing in the detail section. Formula C returns 10. For the first detail record, Formula D returns 11. This is because the value 10 of x is retained from when it was set by Formula C. Formula D then adds 1 to this value, setting x to 11 and then returns 11. For the second detail record, formula D return 12, adding 1 to the previously retained value of x which was 11. This process continues for the remaining detail records.
The call to WhileReadingRecords tells Crystal Reports to re-evaluate Formula D as it reads in each record of the report. Otherwise, since the formula does not contain any database fields, the program will evaluate it only once before reading the records from the database. The formula will then return the value 11 instead of 11, 12, 13, ... as the successive records are processed.
If the statement x = x + 1 is replaced by x = x + {Orders Detail.Quantity}, you create the effect of a running total based on {Orders Detail.Quantity}, although it is one starting at 10 rather than 0 because of Formula C. In this case, you can omit the call to WhileReadingRecords, since it will automatically occur because the formula contains a database field.
Crystal Reports
Private Sub Page_Load(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles MyBase.Load
'Put user code to initialize the page here
Dim objConn As New OleDbConnection()
Dim strSQL As String
If Session("DataBase") <> "" Then
objConn = New System.Data.OleDb.OleDbConnection("provider=Microsoft.Jet.OLEDB.4.0;Data Source=" & Session("DataBase"))
Else
objConn = New System.Data.OleDb.OleDbConnection("provider=Microsoft.Jet.OLEDB.4.0;Data Source=" & Server.MapPath("CP.mdb"))
End If
Dim DsRPT As New DataSet()
strSQL = Session("ReportSQL")
Dim DAReport As New OleDbDataAdapter(strSQL, objConn)
DAReport.Fill(DsRPT, "Candidates")
‘THIS XML FILE WILL BE USED AS AN INPUT TO THE CRYSTAL REPORT DATA CONNECTION WIZARD
‘THEREFORE IT MUST BE CREATED PRIOR TO CREATING THE CRYSTAL REPORT DESIGN
DsRPT.WriteXmlSchema(Server.MapPath("FeesReport.xml"))
‘************************************************************************************
‘These next lines should be added AFTER the Crystal Report Design Work Is Done…
Dim CR As New FeesReport()
CR.SetDataSource(DsRPT)
CrystalReportViewer1.ReportSource = CR
End Sub
Once the XML File has been generated, the Crystal Report design creation can begin. Start crystal reports, (either as a stand alone or within your ASP.NET project) and select the newly created XML file as the New Connection, as shown below.
This XML Schema will allow you to design the report with the necessary fields. After you design the report, these lines:
Dim CR As New FeesReport() ‘THIS IS THE NAME OF THE REPORT YOU JUST DESIGNED
CR.SetDataSource(DsRPT)
CrystalReportViewer1.ReportSource = CR
Can be added to the Form_Load procedure.
How to set a parameter in Crystal Report:
Imports System.Data.OleDb Imports CrystalDecisions.CrystalReports.Engine Imports CrystalDecisions.Shared
Private Sub Page_Load(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles MyBase.Load 'Put user code to initialize the page here Dim objConn As New OleDbConnection() Dim strSQL As String
If Session("DataBase") <> "" Then objConn = New System.Data.OleDb.OleDbConnection("provider=Microsoft.Jet.OLEDB.4.0;Data Source=" & Session("DataBase")) Else objConn = New System.Data.OleDb.OleDbConnection("provider=Microsoft.Jet.OLEDB.4.0;Data Source=" & Server.MapPath("CP.mdb")) End If Dim DsRPT As New DataSet() strSQL = Session("ReportSQL") Dim DAReport As New OleDbDataAdapter(strSQL, objConn) DAReport.Fill(DsRPT, "AllScr") 'DsRPT.WriteXmlSchema(Server.MapPath("FeesReport.xml")) objConn.Close()
Dim CR As New AllScr()
Dim paramvals As ParameterValues Dim paramDVal As ParameterDiscreteValue Dim paramFlds As ParameterFieldDefinitions Dim paramFld As ParameterFieldDefinition
paramFlds = CR.DataDefinition.ParameterFields paramFld = paramFlds.Item(0)
paramDVal = New ParameterDiscreteValue() paramDVal.Value = Session("CrystalParamterValue") paramvals = paramFld.CurrentValues paramvals.Add(paramDVal) paramFld.ApplyCurrentValues(paramvals)
CR.SetDataSource(DsRPT) CrystalReportViewer1.ReportSource = CR
End Sub |
VB6 Crystal Report With Password…
Dim Application As New CRAXDRT.Application
Dim Report As CRAXDRT.Report
Set Report = Application.OpenReport("J:\can\isc2\ConPlaq6.rpc")
'Declare a Connection Info Object
Dim ConnectionInfo As CRAXDRT.ConnectionProperties
For tc = 1 To Report.Database.Tables.Count
Set ConnectionInfo = Report.Database.Tables(tc).ConnectionProperties
'Set the OLE DB provider
'ConnectionInfo.Item("Provider") = "Microsoft.Jet.OLE DB.4.0"
'Set the path and name of the Access database file
'ConnectionInfo.Item("Data Source") = "J:\can\isc2\cp.mdb"
ConnectionInfo.Item("Password") = "daisy"
'If using session level security:
'Set the user name ConnectionInfo.Item(“User ID”) = “User”
'Set the system level password ConnectionInfo.Item(“Password”) = “Password”
'Set the location of the system database ConnectionInfo.Item(“Jet System Database”) = “C:\Folder\System.mdw”
'Set the Connection Info to Connection Properties of the table object
Next tc
Report.ParameterFields.Item(1).AddCurrentValue (strParam)
frmShowReport.CRViewer91.ReportSource = Report
frmShowReport.CRViewer91.ViewReport
frmShowReport.CRViewer91.Zoom (75)
frmShowReport.Show 1
Strength and Weakness Formula
dim s as string
dim i as number
dim m as
number
m = int({AL095128.SCALE1}/10)
for i
= 1 to m
s
= s + "▄▄▄"
next i
formula = s
Export To PDF
Imports CrystalDecisions.CrystalReports.Engine
Imports CrystalDecisions.Shared
Public Class Form1
Inherits System.Windows.Forms.Form
Private Sub Button2_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles Button2.Click
Dim CrystalReportDocument As ReportDocument
Dim CrystalExportOptions As ExportOptions
Dim CrystalDiskFileDestinationOptions As DiskFileDestinationOptions
Dim Filename As String
CrystalReportDocument = New ReportDocument
CrystalReportDocument.Load("J:\scoring\nic95\dev\SchoolReport.rpt")
Filename = "C:\MyPDF.pdf"
CrystalDiskFileDestinationOptions = New DiskFileDestinationOptions
CrystalDiskFileDestinationOptions.DiskFileName = Filename
CrystalExportOptions = CrystalReportDocument.ExportOptions
With CrystalExportOptions
.DestinationOptions = CrystalDiskFileDestinationOptions
.ExportDestinationType = ExportDestinationType.DiskFile
.ExportFormatType = ExportFormatType.PortableDocFormat
End With
'CrystalReportDocument.RecordSelectionFormula = "{XXMASS.STATE_ID} = 'ME'"
CrystalReportDocument.SetParameterValue("StartDate", "10/1/2005")
CrystalReportDocument.SetParameterValue("EndDate", "10/31/2005")
CrystalReportDocument.SetParameterValue("State", "ME")
CrystalReportDocument.Export()
Process.Start("c:\MyPDF.pdf")
End Sub
End Class
How to sort on a field…
Dim fld As CRAXDRT.DatabaseFieldDefinition
Set fld = Report.Database.Tables(2).Fields(4) 'ScoringArea field
in my Table
Report.RecordSortFields.Add fld, crAscendingOrder
A Crystal Report Based On a SQLServer Stored Procedure….
Imports CrystalDecisions.CrystalReports.Engine
Imports CrystalDecisions.Shared
Public Class WebForm1
Inherits System.Web.UI.Page
Private Sub Page_Load(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles MyBase.Load
Dim crReportDocument As New Report1 'This is the report name.
Dim crParameterFieldDefinitions As CrystalDecisions.CrystalReports.Engine.ParameterFieldDefinitions
Dim crParameterFieldDefinition As CrystalDecisions.CrystalReports.Engine.ParameterFieldDefinition
Dim crParameterValues As ParameterValues
Dim crParameterDiscreteValue As ParameterDiscreteValue
crParameterFieldDefinitions = crReportDocument.DataDefinition.ParameterFields
crParameterFieldDefinition = crParameterFieldDefinitions.Item("@Name_Last") 'Stored proc parameter.
crParameterValues = crParameterFieldDefinition.CurrentValues
crParameterValues.Clear()
crParameterDiscreteValue = New ParameterDiscreteValue
crParameterDiscreteValue.Value = Trim(Session("CandidateId")) 'Parameter value
crParameterValues.Add(crParameterDiscreteValue)
crParameterFieldDefinition.ApplyCurrentValues(crParameterValues)
CrystalReportViewer1.ReportSource = crReportDocument
End Sub
End Class
Open a report with a SQLServer Login and Password…
Dim crReportDocument As New School_Summary
Dim myTable As CrystalDecisions.CrystalReports.Engine.Table
Dim myLogonInfo As CrystalDecisions.Shared.TableLogOnInfo
For Each myTable In crReportDocument.Database.Tables
myLogonInfo = myTable.LogOnInfo
With myLogonInfo.ConnectionInfo
.UserID = "Web_AAAAICans"
.Password = "RV47sLl7VLlBHXcyiuksd6wvh"
End With
myTable.ApplyLogOnInfo(myLogonInfo)
Next myTable
'Dim doc As CrystalDecisions.CrystalReports.Engine.ReportClass
'doc = New CERTIF
Dim doc As New ReportDocument
doc.Load(Server.MapPath("certif.rpt"))