This document is useful for creating dynamic crystal report using stored procedure. Based on business requirement report should display according to user filter criteria and there are multiple require to export and print report in different format like: PDF, DOC, RPT, TXT, XLS. Developers generally waste their time to find out the way to fulfill all below mentioned requirement using crystal report. I would like to share my knowledge what I get from this project so that developers can save time.
This document covers the following
1) How to create crystal report using stored procedure
2) How to pass parameter to crystal report
3) How to pass formula to crystal report
4) How to export report in different format
5) How to print report in server side and client side
6) How to generates schema of the database table
Introduction:
Crystal Report is reporting tool provided by dot net, it generates report in .RPT format.
Visual Studio provides one container called crystal report viewer that can be bind with crystal report (.rpt file) to display in the web page.
There are multiple ways to display reports like: HTML static report, Display Report in data grid etc. But this option has lots of limitation and coding effort.
Creating crystal report is very simple but creating dynamic report using stored procedure is little difficult.
I have followed the approach below:
1) Create the schema and attach this schema with crystal report I mean drag and drop column from this schema.
2) Bind dataset with crystal report object (Update dataset in code based on your requirement either through stored procedure or SQL statement)
3) Pass parameter
4) Pass formula
5) Bind crystal report with Crystal report viewer
6) Export report
7) Print report
Source Code:
Follow the simple FIVE steps to create application. You can simply copy and past code from here in your Web Form
1) Create Schema (.xsd file) using visual studio.
For example:
Bank.xsd
This schema should match with your actual database table data type from where you want to fetch the data.
Sample XSD
<xs:complexType>
<xs:choice maxOccurs="unbounded">
<xs:element name="Table">
<xs:complexType>
<xs:sequence>
<xs:element name="Code" type="xs:string" minOccurs="0" />
<xs:element name="Name" type="xs:string" minOccurs="0" />
<xs:element name="BkDesc" type="xs:string" minOccurs="0" />
<xs:element name="add1" type="xs:string" minOccurs="0" />
</xs:sequence>
</xs:complexType>
</xs:element>
</xs:choice>
</xs:complexType>
</xs:element>
Private Sub CreateXSD()
Dim mcon_pub As SqlConnection
Dim mda_fld As SqlDataAdapter
Dim ds_fldDef As DataSet = New DataSet
Dim sqlstmt As String = "select * from Bank"
'Remember to change data source as applicable.
Dim constr As String = "Data source =SERVER NAME;Initial catalog=DB NAME;User ID=UID;password=PWD"
mcon_pub = New SqlConnection(constr)
mda_fld = New SqlDataAdapter(sqlstmt, mcon_pub)
ds_fldDef = New DataSet
mda_fld.Fill(ds_fldDef)
ds_fldDef.WriteXmlSchema("D:\Ritesh\SampleBank.xsd")
End Sub
2) Add the following reference in the project
1) CrystalDecisions.CrystalReports.Engine
2) CrystalDecisions.ReportSource
3) CrystalDecisions.Shared
4) CrystalDecisions.Web
3) Add the crystal report in your project say CrystalReport1.rpt.
Open the crystal report and add the database files (give the path for Bank.xsd stored) from the Database fields option in the Field explorer property. Click ok and close the window
Now what ever column specified in the schema files that will appear in the Fields Explorer window
Drag the columns you want to display in the crystal report Details section. Set the header footer as you want.
4) Add one new parameter in crystal report say “code”.
Drag this parameter to Page Header section in the crystal report. Now save the report.
Note: Here we are not going to use this parameter for data filter purpose I mean we are not using this parameter to pass stored procedure parameter. I would like to give you the way to pass parameter in the crystal report. That can be use for dynamic display purpose only.
5) Add one Web page in the project say “WebForm.aspx”
Drag the Crystal report viewer on the top of the page. (CrystalReportViewer1)
Write the following code in WebForm.vb page
Imports CrystalDecisions.CrystalReports.Engine
Imports CrystalDecisions.ReportSource
Imports CrystalDecisions.Shared
Imports System.Data.SqlClient
‘Declare Global variable
Private myDataSet As NewDataSet = New NewDataSet
Dim oReport As ReportDocument = New ReportDocument
‘UPDATE DATASET EXECUTE STORED PROCEDURE
‘Create function say SetDataSet – This function return updated dataset
‘You can add n number of parameter to the procedure
Private Function SetDataSet() As DataSet
Dim myConnection As New SqlClient.SqlConnection
Dim myCommand As New SqlClient.SqlCommand
Dim myDA As New SqlClient.SqlDataAdapter
Dim prm As SqlParameter
Dim ds As DataSet = New DataSet
myConnection.ConnectionString = "server= SERVER NAME;database=DB NAME;
User Id =UID;password=PWD"
myCommand.Connection = myConnection
myCommand.CommandText = "test_bank" ‘Procedure name
myCommand.CommandType = CommandType.StoredProcedure
prm = myCommand.Parameters.Add("@code", SqlDbType.VarChar, str.Length, "code")
prm.Value = “PARAM VALUE”
MyDA.SelectCommand = myCommand
'This is DataSet created at Design Time
MyDA.Fill(myDataSet)
Return myDataSet
End Function
‘SEND PARAMETER TO CRYSTAL REPORT
‘Create function to send parameter value if any in the crystal report
Private Sub SetParameter()
Dim paramName As String
Dim paramValue As String
Dim pList As CrystalDecisions.Shared.ParameterValues = New ParameterValues
Dim pV As CrystalDecisions.Shared.ParameterDiscreteValue = New ParameterDiscreteValue
Dim rCount As Integer
‘In this way we can send multiple parameters to the crystal report, this loop will take a 'parameter name one by one and set value on that
For rCount = 0 To oReport.DataDefinition.ParameterFields.Count - 1
paramName = oReport.DataDefinition.ParameterFields(rCount).Name
pV.Value = "PARAM VALUE" ‘Crystal report parameter value
pList.Add(pV) oReport.DataDefinition.ParameterFields(paramName).
ApplyCurrentValues(pList)
Next
End Sub
‘SEND FORMULA RUN TIME TO CRYSTAL REPORT
CrystalReportViewer1.SelectionFormula = "{Table.code} = 'LON01'"
CrystalReportViewer1.ReportSource = oReport
“Table” – is table name which you bind in crystal and
“code” - is a column name from table
Above line of code will filter record from the table and display those records where code name is ‘LON01’
‘SEND FORMULA VALUE RUN TIME TO CRYSTAL REPORT
Private Sub SetFormula()
Dim thisFormulaField As CrystalDecisions.CrystalReports.Engine.FormulaFieldDefinition
For Each thisFormulaField In oReport.DataDefinition.FormulaFields
If thisFormulaField.FormulaName = "{@Formula1}" Then
thisFormulaField.Text = "This formula value came runtime"
End If
Next
End Sub
‘DISPLAY REPORT
‘ This method will call crystal report from specific location and bind that to crystal report viewer
Private Sub GetReport()
Dim ds As DataSet = SetDataSet()
CrystalReportViewer1.Visible = False
lblDatafound.Text = "" ‘Lebel to display Error
If ds.Tables(0).Rows.Count > 0 Then
oReport.Load("C:\Inetpub\wwwroot\CrystalReportPOC\CrystalReport1.rpt")
‘Path where crystal report stored
oReport.Database.Tables(0).SetDataSource(myDataSet)
SetParameter() ‘Method to set Crystal report parameter
SetFormula() ‘Method to pass formula value
‘Note: ‘If your requirement to generate report without display report in the web page, other way Report should generate internally, export and print Then COMMENT following two line and UNCOMMENT
“ExportReport(“PDF”)” And “Printreport()” METHOD
CrystalReportViewer1.ReportSource = oReport
CrystalReportViewer1.Visible = True
‘ExportReport(“PDF”)
‘Printreport()
Else
lblDatafound.Text = "No data found."
End If
End Sub
Private Sub Page_Load(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles MyBase.Load
‘Call this method on page load
GetReport()
End Sub
‘EXPORT REPORT IN ‘PDF’,’EXCEL’,’MS WORD’,’TEXT’ and ‘RPT’
‘Create function to Export report in different format
Private Sub ExportReport(ByVal format As String)
Dim DiskOpts As CrystalDecisions.Shared.DiskFileDestinationOptions = New CrystalDecisions.Shared.DiskFileDestinationOptions
oReport.ExportOptions.ExportDestinationType = CrystalDecisions.[Shared].ExportDestinationType.DiskFile
Select Case format
Case "PDF"
oReport.ExportOptions.ExportFormatType = CrystalDecisions.[Shared].ExportFormatType.PortableDocFormat
‘Set the path where you want to save PDF file
DiskOpts.DiskFileName = "D:\Output1.pdf"
Case "Excel"
oReport.ExportOptions.ExportFormatType = CrystalDecisions.[Shared].ExportFormatType.Excel
DiskOpts.DiskFileName = "D:\Output2.xls"
Case "Text"
oReport.ExportOptions.ExportFormatType = CrystalDecisions.[Shared].ExportFormatType.RichText
DiskOpts.DiskFileName = "D:\Output3.txt"
Case "Word"
oReport.ExportOptions.ExportFormatType = CrystalDecisions.[Shared].ExportFormatType.WordForWindows
DiskOpts.DiskFileName = "D:\Output4.doc"
Case "RPT"
oReport.ExportOptions.ExportFormatType = CrystalDecisions.[Shared].ExportFormatType.CrystalReport
DiskOpts.DiskFileName = "D:\Output6.rpt"
Case Else
oReport.ExportOptions.ExportFormatType = CrystalDecisions.[Shared].ExportFormatType.WordForWindows
DiskOpts.DiskFileName = "D:\Output4.doc"
End Select
‘Set the report Destination and export
oReport.ExportOptions.DestinationOptions = DiskOpts
oReport.Export()
End Sub
‘PRINT THE REPORT
‘This method will print the report in SERVER SIDE
‘Note : To get all server side printer use
‘System.Drawing.Printing.PrinterSettings.InstalledPrinters
‘This will return Installed printer collection you can bind with
‘dropdownlist and populate in the screen for Example
‘DropDownList2.DataSource = ‘System.Drawing.Printing.PrinterSettings.InstalledPrinters
‘DropDownList2.DataBind()
Private Sub Printreport()
oReport.PrintOptions.PrinterName = “PRINTER NAME”
oReport.PrintToPrinter(1, False, 0, 0)
End Sub
‘LOAD PRINTER NAME
‘This method gives you the entire printer name which is installed in the server
Private Sub PopulateALLInstalledPrinter()
DropDownList1.DataSource = System.Drawing.Printing.PrinterSettings.InstalledPrinters
DropDownList1.DataBind()
End Sub
Server side printing limitation
One limitation of this method is that a printer name must be specified. You can set the default printer at design time in the report, and you can change the printer name at run time by setting the ReportDocument.PrintOptions.PrinterName property (the PrintOptions are also where you can assign page margins, portrait/landscape, etc.). Keep in mind that this method prints from the server itself, not from the client machine. This means that any printer you wish to use must be accessible from the server. You cannot print to a client's desktop printer using this method unless that printer is shared on the network and mapped to the server.
You can change the printer name at run time by setting the ReportDocument.PrintOptions.PrinterName property (the PrintOptions are also where you can assign page margins, portrait/landscape, etc.).
2) Printing in the server.
This method prints from the server, not from the client machine. Means that any printer you wish to use must be accessible from the server. You cannot print to a client's desktop printer using this method unless that printer is shared on the network and mapped to the server.
Client Side JavaScript: window.Print
For client side printing use the java script function window.Print() in the page OnLoad event. For more details please see the article in http://aspalliance.com/509 site.
Windows 2000, XP and above
VB.NET, C#.NET and ASP.NET, Visual Studio 2003 and above
Database
Oracle, SQL Server, DB2
From
Ritesh Kesharwani
Infosys,Pune
