Wednesday, January 10, 2007

Create Professional Crystal Report From VB DOTNET Application

Overview: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 procedure2) How to pass parameter to crystal report3) How to pass formula to crystal report4) How to export report in different format5) How to print report in server side and client side6) 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.

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

End Function‘SEND PARAMETER TO CRYSTAL REPORT‘Create function to send parameter value if any in the crystal reportPrivate Sub SetParameter()

Dim paramName As StringDim paramValue As StringDim pList As CrystalDecisions.Shared.ParameterValues = New ParameterValuesDim pV As CrystalDecisions.Shared.ParameterDiscreteValue = New ParameterDiscreteValueDim 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 thatFor rCount = 0 To oReport.DataDefinition.ParameterFields.Count - 1paramName = oReport.DataDefinition.ParameterFields(rCount).NamepV.Value = "PARAM VALUE" ‘Crystal report parameter valuepList.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 REPORTPrivate Sub SetFormula()

If ds.Tables(0).Rows.Count > 0 ThenoReport.Load("C:\Inetpub\wwwroot\CrystalReportPOC\CrystalReport1.rpt")‘Path where crystal report storedoReport.Database.Tables(0).SetDataSource(myDataSet)SetParameter() ‘Method to set Crystal report parameterSetFormula() ‘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()” METHODCrystalReportViewer1.ReportSource = oReportCrystalReportViewer1.Visible = True‘ExportReport(“PDF”)‘Printreport()ElselblDatafound.Text = "No data found."End If

‘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 serverPrivate Sub PopulateALLInstalledPrinter()DropDownList1.DataSource = System.Drawing.Printing.PrinterSettings.InstalledPrintersDropDownList1.DataBind()End Sub

Server side printing limitationOne 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.Using Server-Side ReportDocument.PrintToPrinter MethodReport.PrintToPrinter(<copies as int>, <collated as True/False>, <startpage as int>, <endpage as int>)As implemented in the Printreport() Method

Server side printing limitation

1) The printer name must be specified.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.PrintFor 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.