Creating Graphical Reports With Crystal Reports in .NET - Creating Report Parameters

(Page 5 of 7 )

In the report, open the field explorer by clicking View | Other Windows | Document Outline or by simply pressing Ctrl + Alt + T. Right click on the parameter fields and click New. Here we need to provide the parameter name, and data type that it will receive. For our example create three parameters as follows:

• Name: ItemId
  Type: Number
• Name: StartDate
  Type: Date
• Name: EndDate
  Type: Date

In addition to Number and Date data type, we can also set Boolean, Currency, DateTime, Time, and String data types.

Now we should see these names under the Parameter Fields in the Field Explorer.



Finally, we need to create the query that will use these parameters. Right click anywhere on the report except the Report Header section. Select Report | Edit Selection Formula | Records. We will see a screen like this.



We can write the query in the provided white space (or simply click the fields above to create it). Paste these lines there and click on the tick button to check the syntax.

{tblSales.ItemId}={?ItemId} And
{tblSales.SaleDate} > DateAdd ("d", -1,{?StartDate}) And
{tblSales.SaleDate} < DateAdd ("d",1 , {?EndDate})

This can also be achieved by double clicking on the different visual tools provided. There are three portions above the query area. In the first pane are the table fields that we selected earlier during the report creation. Other fields and tables in the database are also available, if we expand the database server name. In addition to this, all the parameter fields are also present in this pane and are distinguishable by the '?' sign. Double click any one of these fields and they will be copied in the text area. Similarly, in the middle portion, we see all the functions we usually use in our queries. In the last pane, we find all the operators. Arithmetic, conversion, comparison, string etc. Double click on any of them to use them in the query.

As you can see, it is simple. We are just comparing our table fields with the parameter fields. Note that the parameter fields are referred with a question mark (?) sign. Click the Save button to save the query. Save the report and close it. Now we return to the code behind for ViewReport.aspx from where we will perform two tasks:

• Connect to the report we have just created
• Pass the arguments to our report

Connecting to the Report
Let us first connect to the report that we have previously created. As we will be using the crystal report object, we need to add a reference to it in our project. Click Project | Add Reference. You will be presented with a dialog box. Under the .NET Tab select two references:

• CrystalDecisions.CrystalReports.Engine
• CrystalDecisions.Shared

At the top of the page, we need to import these namespaces. Here is the actual code:

using System;
using System.Collections;
using System.ComponentModel;
using System.Data;
using System.Drawing;
using System.Web;
using System.Web.SessionState;
using System.Web.UI;
using System.Web.UI.WebControls;
using System.Web.UI.HtmlControls;
using CrystalDecisions.Shared;
using CrystalDecisions.CrystalReports.Engine;

Here is the code in the Page_Load event. Let us take a look at it and then we will dissect it.

private void Page_Load(object sender, System.EventArgs e)
{
// getting data from Request
nItemId = int.Parse(Request.QueryString.Get("ItemId"));
strStartDate = Request.QueryString.Get("StartDate");
strEndDate = Request.QueryString.Get("EndDate");

// object declration
CrystalDecisions.CrystalReports.Engine.Database crDatabase;
CrystalDecisions.CrystalReports.Engine.Table crTable;

TableLogOnInfo dbConn = new TableLogOnInfo();

// new report document object
ReportDocument oRpt = new ReportDocument();

// loading the ItemReport in report document
oRpt.Load("C:\\\\Inetpub\\\\wwwroot\\\\WroxSellersWeb\\\\ItemReport.rpt");

// getting the database, the table and the LogOnInfo object which holds login onformation
crDatabase = oRpt.Database;

// getting the table in an object array of one item
object[] arrTables = new object[1];
crDatabase.Tables.CopyTo(arrTables, 0);

// assigning the first item of array to crTable by downcasting the object to Table
crTable = (CrystalDecisions.CrystalReports.Engine.Table)arrTables[0];

dbConn = crTable.LogOnInfo;

// setting values
dbConn.ConnectionInfo.DatabaseName = "WroxSellers";
dbConn.ConnectionInfo.ServerName = "Intersoft";
dbConn.ConnectionInfo.UserID = "farhan";
dbConn.ConnectionInfo.Password = "farhan";

// applying login info to the table object
crTable.ApplyLogOnInfo(dbConn);

// defining report source
crViewer.ReportSource = oRpt;

// so uptill now we have created everything
// what remains is to pass parameters to our report, so it
// shows only selected records. so calling a method to set
// those parameters.
setReportParameters();

}

First of all we are getting data from the query string. Remember that we have sent the ItemId, StartDate and EndDate parameters from ManagerDefault.aspx.cs. As the second step, we instantiate Crystal Report's Database, Table and TableLogOnInfo objects. These objects will be used to load the database and table through which we have created our report ItemReport.rpt. Next we create a new Report document object and load our report into it. Notice the line for loading the report:

// loading the ItemReport in report document
oRpt.Load("C:\\\\Inetpub\\\\wwwroot\\\\WroxSellersWeb\\\\ItemReport.rpt");

Please change the path according to your system. Note that we want to return two backslashes, so have escaped both in the code above.

After loading the report we set our Crystal Report's Database object to the report document's database. Then we copy the tables in that database object to an array of objects, from where we select the first table and assign it to the crystal report's table object. Then we set the properties of the LogOnInfo object that will be used for authentication purposes. Again, we need to change it for our system accordingly. Finally we apply this LogOnInfo object to our table object. In the last line we specify the report source of the Crystal Report Object crViewer to our Report Document object:

// defining report source
crViewer.ReportSource = oRpt;

We are connected to our report now. What next?

The answer is: passing parameters to the report. We need to pass the ItemId, StartDate and EndDate to this report so that a report on the selected data can be displayed. If you remember, we have collected these values from a query string. Let us now pass them to our report.

Next: Passing Parameters to a Report >>
 

More C# Articles
More By Wrox Team

Comments On: Creating Graphical Reports With Crystal Reports in .NET

· Hello i want to download the code for following topic.Creating Graphical Reports...    · best one to know the technical details    · good one for starting..    · How can i download source of this article..   >>> Post your comment now!