Asp. NET to create a chart using the Crystal

Asp.net| Create | Charts in many applications, reports are indispensable, a good report can visually let people grasp the situation of data, convenient decision-making. In this article, we will take a three-tier structure of the asp.net process as an example, introduce how to use the Crystal Report, to produce a statement, which introduces a lot of asp.net and Crystal report skills.

In this example, the application we envision is to produce a report for a sales department, where the manager can look at the sales over a period of time and reflect the sales trend in the form of a list or line chart. We'll use SQL Server 2000 as a database, using vb.net to write the middle-tier logic layer, while the presentation layer on the front end uses C #. Let's look at the structure of the database first.




The Tbitem table contains the items ordered in each order, Tbsales stores each order, Tblsalesperson is the salesperson's table, and every salesperson who holds the publisher.

Next, use SQL Server 2000来 to create these tables. The structure of the table looks like this:

CREATE TABLE [dbo]. [Tblitem] (
[ItemId] [INT] Not NULL,
[Description] [varchar] () Not NULL
) on [PRIMARY]
CREATE TABLE [dbo]. [Tblsalesperson] (
[SalesPersonID] [INT] Not NULL,
[UserName] [varchar] () not NULL,
[Password] [varchar] () Not NULL
) on [PRIMARY]
CREATE TABLE [dbo]. [Tblsales] (
[Saleid] [INT] IDENTITY (1, 1) not NULL,
[SalesPersonID] [INT] Not NULL,
[ItemId] [INT] Not NULL,
[Saledate] [DateTime] Not NULL,
[Amount] [INT] Not NULL
) on [PRIMARY]

and use the following code to create a constraint relationship between tables.

ALTER TABLE Tblitem
ADD CONSTRAINT Pk_itemid
PRIMARY KEY (ItemId)
Go
ALTER TABLE Tblsalesperson
ADD CONSTRAINT Pk_salespersonid
PRIMARY KEY (SalesPersonID)
Go
ALTER TABLE Tblsales
ADD CONSTRAINT Fk_itemid
FOREIGN KEY (ItemId) REFERENCES Tblitem (ItemId)
Go
ALTER TABLE Tblsales
ADD CONSTRAINT Fk_salespersonid
FOREIGN KEY (SalesPersonID) REFERENCES Tblsalesperson (SalesPersonID)
Go


Creating an intermediate logical layer

In the intermediate logic layer component, we create two classes for each table. For example, for Tblitems tables, create item and items classes. The item class records the details of each item sold and the items table records all the goods sold and the method of increasing the goods. In this way, there are six of the following classes:

Item and Items
Salesperson and Salespersons
Sale and Sales

Next, look at the properties in each class:

Item class

Includes the following properties
ITEMID (item ID number)
Description (description of goods)
Items

There is a method that returns an item object based on the item's number


Public Function Getallitems () as Collections.arraylist


Salesperson

The class has the following three properties:

SalesPersonID (Salesperson number)
Name (name)
Password (password)

Salespersons

There is a method, according to the salesperson entered the user name and password, in the database, verify that the salesperson's Landing is correct, if correct, then return zero.


Public Function ValidateUser (strUserName As String, strpassword as String) as Integer


Sale

There are 5 properties

· Saleid
· SalesPersonID
· ItemId
· Saledate
· Amount

Sales

There are two methods in which Getsales returns a collection of sales objects based on the parameters entered


Public Function getsales (Optional nsaleid As Integer = 0, Optional nsalespersonid As Integer = 0,optional nitemid as inte GER = 0) as Collections.arraylist


There is also a addsales method for adding a single order


Public Function AddSale (Objsale as Sale)

Create a Presentation layer page

Next, start creating the Presentation layer page. First, create a page that you can log in to the salesperson, as shown in the following illustration.



After the successful landing of the salesperson, you can give the salesperson the number of items sold, as shown below:



In addition, create the following page for the business supervisor to see a period of sales.



In this case, the business supervisor can choose to view the sales of a certain item at a certain period (select Start date, end date), and display it graphically.

It is particularly worth mentioning that the use of calendar controls. After you place the Calendar control in the page, set the following code:


<input type= "image" onclick= "Page_validationactive=false; src=http://www.163design.net/n/b/" Datepicker.gif "alt=" Show calender "runat=" Server "onserverclick=" ShowCal1 "id=" ImgCal1 "name=" ImgCal1 "


This sets the page_validationactive argument to False, so there is no need to resubmit the page, and, in the OnServerClick event, set the processing code as follows:


public void ShowCal1 (Object sender, System.Web.UI.ImageClickEventArgs e)
{//Show Calendar control
Dtpicker1.visible = true;
}


When the user selects the relevant date, the relevant date can be obtained in the text box:


private void Dtpicker1_selectionchanged (object sender, System.EventArgs e)
{
Txtstartdate.text = DtPicker1.SelectedDate.ToShortDateString ();
Dtpicker1.visible = false;
}


In the submitted page, the following code is processed:


private void Bsubmit_serverclick (object sender, System.EventArgs e)
{
Response.Redirect ("Viewreport.aspx?") Itemid= "+ cboItemType.SelectedItem.Value +" &startdate= "+ Txtstartdate.text +" &enddate= "+ Txtenddate.text);}


After the page is submitted, it jumps to the page viewreport.aspx of the browsing report, passing in the relevant parameters, such as the item ID, start and end date.

Create a report with a crystal Report

First, add the Crystal Report control to the form, and then you can design a report with the Crystal Report's Report Designer. Create a new Crystal Report file type, named Itemreport.rpt, then use the report design expert, and select the Design Standard report, click Next, the following screen appears:





We choose to use the ADO type of data, in the pop-up window, set up SQL Server login, login password, select a good database, choose to use the Tblsales table in the report, the following figure:





Next, select Saledate and amount in the fields that you want to display in the report. After that, always select Next, ignore the other related settings, and finally, choose a line chart in the diagram type, as shown in the following figure:





Finally click Finish, you can see the following report:





Select the report expert again, select the data page in the pop-up window, select Salesdate in the data available fields, and set the appropriate report title in the text page.





Because you want to dynamically display the report based on the date you enter and the item parameters, we want to set the parameter field. In Report Designer, in Field Explorer, select the parameter fields, right-click to select New, and create a new three-digit field as follows.


Name: Type:
ItemId number
StartDate Date
EndDate Date

　　
Finally, to set the relevant query formula, in addition to the report header area, the right mouse click, in the pop-up menu select "Report | Edit Select Formula | record", enter the following formula:





In the formula editor above, divided into the left and right three parts, the left side is the report field, the middle is the related function function, the rightmost is the operator, double-click the selected section, you can add to the lower formula display area. Finally, save the established formula.

Working with reports in your programs

Next, we can use code processing and report connection procedures in our programs. First, in the project, add references to the two namespaces below (note that you must also introduce them in your code using the using use):

CrystalDecisions.CrystalReports.Engine
Crystaldecisions.shared
In the Viewreport.aspx Page_Load event, add the following code

Parameters received for delivery
nitemid = Int. Parse (Request.QueryString.Get ("ItemId"));
Strstartdate = Request.QueryString.Get ("StartDate");
Strenddate = Request.QueryString.Get ("EndDate");
Declaring a report's data object
CrystalDecisions.CrystalReports.Engine.Database crdatabase; CrystalDecisions.CrystalReports.Engine.Table crtable;
TableLogOnInfo dbconn = new TableLogOnInfo ();
Create a Report object opt
Reportdocument orpt = new Reportdocument ();
Load reports that have been done
Orpt.load ("F:\\aspnet\\wroxweb\\itemreport.rpt");
Connect the database and get the relevant login information
Crdatabase = Orpt.database;
Defines an array of Arrtables objects
object[] Arrtables = new Object[1];
CrDatabase.Tables.CopyTo (arrtables, 0);
Crtable = (CrystalDecisions.CrystalReports.Engine.Table) arrtables[0]; Dbconn = Crtable.logoninfo;
Set up the relevant login database information
DbConn.ConnectionInfo.DatabaseName = "Wroxsellers"; DbConn.ConnectionInfo.ServerName = "localhost";
DbConn.ConnectionInfo.UserID = "sa";
DbConn.ConnectionInfo.Password = "Test";
Apply the logged information to the Crtable Table object
Crtable.applylogoninfo (Dbconn);
To bind report and report navigation controls
Crviewer.reportsource = orpt;
Passing parameters
Setreportparameters ();

In the above code, you first receive the date, the item number, and so on, and instantiate the database, table, and tablelogoninfo three classes that are required to establish a connection between the report and the database at run time. Re-use

Orpt.load ("F:\\aspnet\\wroxweb\\itemreport.rpt");

To load a report that is already done.

After you load the report, copy the table that you want to use in the database to an object array, and select the first TABLE element in the object array to convert it to the report object of the Crystal Report. Next, set the login database information in the Logoninfo. Finally, bind the report source to the report navigation control.

Pass parameters to Crystal Report

Define a new process, setreportparameters (), code as follows:

private void Setreportparameters ()
{
All the parameter fields would be added to this collection
Parameterfields paramfields = new Parameterfields ();
The parameter fields to is sent to the
Parameterfield pfitemid = new Parameterfield ();
Parameterfield pfstartdate = new Parameterfield ();
Parameterfield pfenddate = new Parameterfield ();
Set the name of the parameter field that will be accepted in the report
Pfitemid.parameterfieldname = "ItemId";
Pfstartdate.parameterfieldname = "StartDate";
Pfenddate.parameterfieldname = "EndDate";
Parameterdiscretevalue dcitemid = new Parameterdiscretevalue ();
Parameterdiscretevalue dcstartdate = new Parameterdiscretevalue (); Parameterdiscretevalue dcenddate = new Parameterdiscretevalue ();

Dcitemid.value = Nitemid;
Dcstartdate.value = DateTime.Parse (strstartdate);
Dcenddate.value = DateTime.Parse (strenddate);

PFITEMID.CURRENTVALUES.ADD (Dcitemid);
PFSTARTDATE.CURRENTVALUES.ADD (dcstartdate); PFENDDATE.CURRENTVALUES.ADD (dcenddate);
Paramfields.add (Pfitemid);
Paramfields.add (pfstartdate);
Paramfields.add (pfenddate);

To bind a parameter collection to a report navigation control
Crviewer.parameterfieldinfo = Paramfields;
}

Now let's explain the code above. In the Crystal Report, the browser control has a property parameterfieldsinfo that can pass the actual value of the relevant parameter to the report by binding the collection of parameterfields types. The Parameterfields type collection receives Parameterfield type objects through the Add method. Therefore, we first create Parameterfield types of objects for the itemid,startdate,enddate three parameters and set the names of their corresponding reports that accept the parameters:

Parameterfields paramfields = new Parameterfields ();
Parameterfield pfitemid = new Parameterfield ();
Parameterfield pfstartdate = new Parameterfield ();
Parameterfield pfenddate = new Parameterfield ();
Set the name of the parameter field that will be accepted in the report
Pfitemid.parameterfieldname = "ItemId";
Pfstartdate.parameterfieldname = "StartDate";
Pfenddate.parameterfieldname = "EndDate";

You can then set specific values for these parameter fields, but since Parameterfield must accept the Arameterdiscretevalue type of object, create an associated instance:

Parameterdiscretevalue dcitemid = new Parameterdiscretevalue ();
Parameterdiscretevalue dcstartdate = new Parameterdiscretevalue ();
Parameterdiscretevalue dcenddate = new Parameterdiscretevalue ();
Dcitemid.value = Nitemid;
Dcstartdate.value = DateTime.Parse (strstartdate);
Dcenddate.value = DateTime.Parse (strenddate);

Finally, you can set their values in the Currentvalues in the three Parameterfield objects and add the three Parameterfield objects to the Paramfields collection.

The results of the operation are as follows.



This procedure in Vs.net 2003,crystal more than 9 version can run correctly.