Shulou(Shulou.com)06/02 Report--

This article introduces the relevant knowledge of "how to achieve Crystal report in ASP.NET". In the operation of actual cases, many people will encounter such a dilemma. Next, let the editor lead you to learn how to deal with these situations. I hope you can read it carefully and be able to achieve something!

In this example, the application we envisioned is to make a report for a sales department, where the manager can view the sales over a certain period of time and reflect the sales trend in the form of a list or line chart. We will use SQLServer 2000 as the database, VB.NET to write the middle-tier logic layer, and the front-end presentation layer to use C #.

Before introducing the ASP.NET Crystal reporting tutorial, let's take a look at the structure of the database.

Among them, the tbitem table stores the goods ordered in each order, tbsales stores each order, and tblsalesperson is the salesperson table, which stores each salesperson of the publishing house.

Next, use SQLServer 2000 to create these tables. The structure of the table is as follows:

CREATETABLE.[ tblItem] ([ItemId] [int] NOTNULL, [Description] [varchar] (50) NOTNULL) ON [PRIMARY] CREATETABLE.tblSalesPerson] ([SalesPersonId] [int] NOTNULL, [UserName] [varchar] (50) NOTNULL, [Password] [varchar] (30) NOTNULL) ON [PRIMARY] CREATETABLE [DBO] .[ tblSales] ([SaleId] [int] IDENTITY (1jue 1) NOTNULL, [SalesPersonId] [int] NOTNULL, [ItemId] [int] NOTNULL [SaleDate] [datetime] NOTNULL, [Amount] [int] NOTNULL) ON [PRIMARY]

And use the following code to create constraint relationships between tables.

ALTERTABLEtblItem ADDCONSTRAINTPK_ItemId PRIMARYKEY (ItemId) GO ALTERTABLEtblSalesPerson ADDCONSTRAINTPK_SalesPersonId PRIMARYKEY (SalesPersonId) GO ALTERTABLEtblSales ADDCONSTRAINTFK_ItemId FOREIGNKEY (ItemId) REFERENCEStblItem (ItemId) GO ALTERTABLEtblSales ADDCONSTRAINTFK_SalesPersonId FOREIGNKEY (SalesPersonId) REFERENCEStblSalesPerson (SalesPersonId) GO

The creation of the intermediate logic layer of the ASP.NET Crystal report tutorial

In the middle logic tier component, we create two classes for each table. For example, for the tblItems table, create the item and items classes. The Item class records the details of each item sold, while the items table records all items sold and has ways to add them. In this way, there are six classes:

Item and Items

SalesPerson and SalesPersons

Sale and Sales

Next, take a look at the properties in each class:

Item class

Include the following attributes

ItemId (item id number)

Description (description of goods)

Items

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

PublicFunctionGetAllItems () AsCollections.ArrayList

SalesPerson

This class has the following three properties:

SalesPersonId (salesperson number)

Name (name)

Password (password)

SalesPersons

There is a way to verify that the salesperson logged in correctly in the database according to the user name and password entered when the salesperson logged in, and if so, return zero.

PublicFunctionValidateUser (strUserNameasString,strPasswordasString) AsInteger

Sale

There are five attributes as follows

SaleId

SalesPersonId

ItemId

SaleDate

Amount

Sales

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

PublicFunctionGetSales (OptionalnSaleIdAsInteger=0,OptionalnSalesPersonIdAsInteger=0,OptionalnItemIdAsInteger=0) AsCollections.ArrayList

There is also an addsales method to add an order

PublicFunctionAddSale (objSaleAsSale)

Create presentation layer page of ASP.NET Crystal report tutorial

Next, start creating the presentation layer page. First, create a page that can be logged in to the salesperson, as shown in the following figure.

After the salesperson has successfully logged in, you can enter the sales quantity of a certain item to the salesperson, as shown in the following figure:

In addition, in order to show the business director the sales situation for a certain period, create the following page.

Among them, the business director can choose to view the sales of a certain item during a certain period (choose a start date, an end date) and display it in a chart.

Particularly worth mentioning here is the use of calendar controls. After placing the calendar control on the page, set the following code:

< inputtype= image "onclick=" Page_ValidationActive=false; "src=" datepicker.gif "alt=" ShowCalender "runat=" server "onserverclick=" ShowCal1 "id=" ImgCal1 "name=" ImgCal1 ">

Here, set the page_validationactive parameter to false, so there is no need to resubmit the page, and, in the onserverclick event, set the handling code as follows:

PublicvoidShowCal1 (Objectsender,System.Web.UI.ImageClickEventArgse) {/ / display Calendar Control DtPicker1.Visible=true;}

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

PrivatevoidDtPicker1_SelectionChanged (objectsender,System.EventArgse) {txtStartDate.Text=DtPicker1.SelectedDate.ToShortDateString (); DtPicker1.Visible=false;}

On the submitted page, the processing code is as follows:

PrivatevoidbSubmit_ServerClick (objectsender,System.EventArgse) {Response.Redirect ("ViewReport.aspx?ItemId=" + cboItemType.SelectedItem.Value+ "& StartDate=" + txtStartDate.Text+ "& EndDate=" + txtEndDate.Text);}

After submitting the page, you will jump to the page viewreport.aspx that browses the report, passing in the relevant parameters, such as item ID, start and end dates.

Report creation of ASP.NET Crystal report tutorial

First, add a Crystal report control to the form, and then you can design a report using Crystal report's report designer. Create a new crystal report file type, name it itemreport.rpt, then use the report designer, and select the design standard report. Click next and the following screen appears:

We choose to use the data of ADO type. In the pop-up window, set the login name and password of sqlserver. After selecting the database, we choose to use the tblsales table in the report, as shown below:

Then select Next, and in the fields you want to display in the report, select SaleDate and Amount. After that, always select NEXT, ignore other relevant settings, and select a line chart in the chart type, as shown below:

* Click finish to 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 you can set the appropriate report title in the text page.

Because we want to dynamically display the report according to the date and item parameters entered, we need to set the parameter field. In report designer, in Field Explorer, select the parameter field, right-click and select New, and create the following three parameter fields.

Name: type: ItemIdNumberStartDateDateEndDateDate

* to set the relevant query formula, click the right mouse button in the area except the report header, select "report | Edit selection formula | record" in the pop-up menu, and enter the following formula:

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

ASP.NET Crystal report tutorial using reports in programs

Next, we can use code to deal with the connection process with the report in the program. First, in the engineering project, add references to the following two namespaces (note that they must also be introduced with using in the code):

CrystalDecisions.CrystalReports.Engine CrystalDecisions.Shared

In the Page_load event of viewreport.aspx, add the following code

/ / receive the passed parameters nItemId=int.Parse (Request.QueryString.Get ("ItemId")); strStartDate=Request.QueryString.Get ("StartDate"); strEndDate=Request.QueryString.Get ("EndDate"); / / declare the data object of the report CrystalDecisions.CrystalReports.Engine.DatabasecrDatabase;CrystalDecisions.CrystalReports.Engine.TablecrTable; TableLogOnInfodbConn=newTableLogOnInfo (); / / create the report object opt ReportDocumentoRpt=newReportDocument (); / / load the completed report oRpt.Load ("F:\ aspnet\\ WroxWeb\\ ItemReport.rpt") / / Connect to the database to obtain relevant login information crDatabase=oRpt.Database; / / define an array of arrtables objects object [] arrTables=newobject [1]; crDatabase.Tables.CopyTo (arrTables,0); crTable= (CrystalDecisions.CrystalReports.Engine.Table) arrTables [0]; dbConn=crTable.LogOnInfo; / / set the relevant login database information dbConn.ConnectionInfo.DatabaseName= "WroxSellers"; dbConn.ConnectionInfo.ServerName= "localhost"; dbConn.ConnectionInfo.UserID= "sa"; dbConn.ConnectionInfo.Password= "test" / / apply the login information to the crtable table object crTable.ApplyLogOnInfo (dbConn); / / bind the report and report browsing controls to crViewer.ReportSource=oRpt; / / pass the parameter setReportParameters ()

In the above code, we first receive parameters such as date and item number, and instantiate three classes Database,Table and TableLogOnInfo, which are necessary to establish a connection between the report and the database at run time. Reuse

ORpt.Load ("F:\ aspnet\\ WroxWeb\\ ItemReport.rpt")

To load the report that has been done.

After loading the report, copy the table to be used in the database into an object array, and select the * * table elements in the object array to convert them into the report object of the crystal report. Next, set the login database information in LOGONINFO. * bind the report source to the report browsing control.

Transfer parameters from ASP.NET Crystal report tutorial to ASP.NET Crystal report

Define a new procedure, setReportParameters (), with the following code:

PrivatevoidsetReportParameters () {/ / alltheparameterfieldswillbeaddedtothiscollection ParameterFieldsparamFields=newParameterFields (); / / theparameterfieldstobesenttothereport ParameterFieldpfItemId=newParameterField (); ParameterFieldpfStartDate=newParameterField (); ParameterFieldpfEndDate=newParameterField (); / / set in the report, the name of the parameter field to be accepted is pfItemId.ParameterFieldName= "ItemId"; pfStartDate.ParameterFieldName= "StartDate"; pfEndDate.ParameterFieldName= "EndDate"; ParameterDiscreteValuedcItemId=newParameterDiscreteValue (); ParameterDiscreteValuedcStartDate=newParameterDiscreteValue (); ParameterDiscreteValuedcEndDate=newParameterDiscreteValue (); 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); / / bind the parameter collection to the report browsing control crViewer.ParameterFieldInfo=paramFields;}

Now let's explain the above code. In a crystal report, the browser control has a property parameterfieldsinfo, which can be passed to the report by binding a collection of parameterfields types. The collection of Parameterfields types receives objects of type Parameterfield through the method of add. Therefore, we first create objects of type parameterfield for the three parameters of itemid,startdate,enddate and set them to correspond to the names of the parameters accepted in the report:

ParameterFieldsparamFields=newParameterFields (); ParameterFieldpfItemId=newParameterField (); ParameterFieldpfStartDate=newParameterField (); ParameterFieldpfEndDate=newParameterField (); / / set in the report, the name of the parameter field to be accepted is pfItemId.ParameterFieldName= "ItemId"; pfStartDate.ParameterFieldName= "StartDate"; pfEndDate.ParameterFieldName= "EndDate"

Next, you can set specific values for these parameter fields, but since parameterfield must accept objects of type ParameterDiscreteValue, create related instances:

ParameterDiscreteValuedcItemId=newParameterDiscreteValue (); ParameterDiscreteValuedcStartDate=newParameterDiscreteValue (); ParameterDiscreteValuedcEndDate=newParameterDiscreteValue (); dcItemId.Value=nItemId; dcStartDate.Value=DateTime.Parse (strStartDate); dcEndDate.Value=DateTime.Parse (strEndDate)

* *, you can set their values in the currentvalues of the three parameterfield objects and add the three parameterfield objects to the paramFields collection.

The result of the run is as follows.

This is the end of the content of "how to achieve Crystal report in ASP.NET". Thank you for your reading. If you want to know more about the industry, you can follow the website, the editor will output more high-quality practical articles for you!

Welcome to subscribe "Shulou Technology Information " to get latest news, interesting things and hot topics in the IT industry, and controls the hottest and latest Internet news, technology news and IT industry trends.