Home arrow C# arrow Page 2 - Creating Graphical Reports With Crystal Reports in .NET
C#

Creating Graphical Reports With Crystal Reports in .NET


In this article Farhan Zia investigates some of the power that Microsoft .NET provides to accomplish converting business data into reports using Crystal Reports.

Author Info:
By: Wrox Team
Rating: 4 stars4 stars4 stars4 stars4 stars / 202
November 10, 2002
TABLE OF CONTENTS:
  1. · Creating Graphical Reports With Crystal Reports in .NET
  2. · The Database
  3. · Creating Web Pages
  4. · The Report
  5. · Creating Report Parameters
  6. · Passing Parameters to a Report
  7. · Conclusion

print this article
SEARCH DEVARTICLES

Creating Graphical Reports With Crystal Reports in .NET - The Database
(Page 2 of 7 )

Imagine we have a company called "WroxSellers". We need a report on monthly sales of any selected item between two given dates.

First we need to create our database. For the sake of simplicity, we have only three tables. Here are the three tables we need:



All the details about Items are stored in tblItems, and the details of Sales Persons are in tblSalesPersons. Whenever a salesman sells an item, he makes an entry to the system through our site. This activity will be stored in tblSales including the amount and the date.

Run the DBScript.sql code included in the download to create the tables on your own server.

CREATE TABLE [dbo].[tblItem] (
[ItemId] [int] NOT NULL ,
[Description] [varchar] (50) NOT NULL
) ON [PRIMARY]
GO




CREATE TABLE [dbo].[tblSalesPerson] (
[SalesPersonId] [int] NOT NULL ,
[UserName] [varchar] (50) NOT NULL ,
[Password] [varchar] (30) NOT NULL
) ON [PRIMARY]
GO




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]

GO




The code for the key constraints is shown below:

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


Then run the script DBData.sql to populate the tables with some sample data.

The Middle Tier Component
Our component is a simple one. We will follow a simple design model, with two objects for every entity. For example, the tblItems table will have two classes, Item and Items. Item will be the record level representation of an item and will store the details of a particular item, while Items will be a table level representation and will contain the manipulation and adding methods.

So, here are our classes, whose objects will be in action.

  • Item and Items
  • SalesPerson and SalesPersons
  • Sale and Sales
As the complete code is included in the attachment, we will just discuss the methods and properties available in these objects.

Item
Item contains two properties:
  • ItemId
  • Description
Items
This object has a single method. This method will return an Item object depending on the nItemId:

Public Function GetAllItems () As Collections.ArrayList

This method returns a collection of all Item objects.

SalesPerson
This object will have these three properties:
  • SalesPersonId
  • Name
  • Password
SalesPersons
This object will have a single method. It returns the SalesPersonId of the sales person and matches the UserName and Password inputted against that in the database. If there is no match, the function returns zero.

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

Sale
This object has these five properties:
  • SaleId
  • SalesPersonId
  • ItemId
  • SaleDate
  • Amount
Sales
This object has two methods. GetSales() returns a collection of Sales objects, depending on the received parameters.

Public Function GetSales (Optional nSaleId As Integer = 0, Optional nSalesPersonId As Integer = 0,
Optional nItemId As Integer = 0) As Collections.ArrayList


In addition to this method, AddSale() adds a new sale entry to the database.

Public Function AddSale (objSale As Sale)

In the download code you will find one more class called dbConnection. This class is used to store the database connection. It only has one method, GetdbConnection(), that is also shared (static) so that there is no need to instantiate this class. The GetdbConnection returns a connection object with connection details from the registry (we can also store such settings in web.config). You need to alter the registry according to your environment - details are in the class file as remarks.

Now add a new empty web project to the solution. Call it WroxSellers.

We need two pages for our site. The first authenticates our sales staff and allows then to enter records of sales, and the other enables us to select report parameters and view reports based on this data.
blog comments powered by Disqus
C# ARTICLES

- Introduction to Objects and Classes in C#, P...
- Visual C#.NET, Part 1: Introduction to Progr...
- C# - An Introduction
- Hotmail Exposed: Access Hotmail using C#
- Razor Sharp C#
- Introduction to Objects and Classes in C#
- Making Your Code CLS Compliant
- Programming with MySQL and .NET Technologies
- Socket Programming in C# - Part II
- Socket Programming in C# - Part I
- Creational Patterns in C#
- Type Conversions
- Creating Custom Delegates and Events in C#
- Inheritance and Polymorphism
- Understanding Properties in C#

Watch our Tech Videos 
Dev Articles Forums 
 RSS  Articles
 RSS  Forums
 RSS  All Feeds
Write For Us 
Weekly Newsletter
 
Developer Updates  
Free Website Content 
Contact Us 
Site Map 
Privacy Policy 
Support 

Developer Shed Affiliates

 




© 2003-2017 by Developer Shed. All rights reserved. DS Cluster - Follow our Sitemap
Popular Web Development Topics
All Web Development Tutorials