4.0 Creating a Sample Report
 

Let me take a report sample and lets go step by step in creating the report.

 

Create the following data structure.

 

1. Student Table

No.	Field Name	Field Type	Field Description
1.	Studid	Bigint	Student ID
2.	FirstName	Varchar(50)
3.	LastName	Varchar(50)
4.	Classid	Bigint
5.	Age	Int

2. Class Table

No.	Field Name	Field Type	Field Description
1.	ClassId	Bigint	ClassID
2.	ClassName	Varchar(50)

 

3. ClassSubjects Table

No.	Field Name	Field Type	Field Description
1.	SubjectId	Bigint	Subject id
2.	ClassID	Bigint
3.	SubjectName	Varchar(50)
4.	SubjectDesc	Varchar(100)

4. StudentMarks table

No.	Field Name	Field Type	Field Description
1.	MarkId	Bigint
2.	Studid	Bigint
3.	Mark	Int
4.	ExamID	Bigint
5.	SubjectID	bigint

5. Exam table

No.	Field Name	Field Type	Field Description
1.	ExamID	Bigint
2.	ExamTitle	Varchar(50)
3.	ExamDate	SmallDateTime

 

Now lets see how we are going to design the report card for the students. Requirements are as follows,

·        Each student report should be printed in a different page

·        Should display the students mark details in the following format

 

Progress Report of the XXXXXXX exam – YYYY(Year)
Name:			Issue Date
Class:
Mark Details
Slno	Subject			Mark		Pass/Fail
	Total
	Grade Obtained (O > 80%, A>60%, B >40%, F <40%)
	Parent Signatrure                             ______________________

Steps to create the report based on the above requirement. 

Step1: Create the excel template as shown below.

  

·        Create a black Excel document

·        Delete the Sheet2 and Sheet3

·        Select the cells A2 to G2 and Merge them

·        Do the remaining as shown in the above pic.

·        Select cells from A1 to G22, Select File menu è Print Area è Set Print Area

·        Print Area will be defined.

·        We are not going to write any data from sp in this section. This is the section were the data is going to displayed. Or this is the section that will be printed.

·        The remaining cells will be used to write the required data for the report they act as the report data source.

·        Lets define areas, where we are going to dump the data.

o       Select the cells J4 to M4

o       Insert è Name è Define (to define a new area)

o       “Define Name” dialog opens and name the region as “StudentInfo”

o       Similarly the cells J7 to K7 and name the region as “Marks”

o       We will be dumping the data in the regions only from the Stored procedure.

·        Lets see how we are going to set the data in the report print section

·        Printing the Header “Progress Report of the XXXXXXX exam – YYYY(Year)”

·        This should be achieved using the macro. Exam name and the Year of the exam should be obtained from the StudentInfo region

·        Lets define the Macro, Select “Tools è Macros è Visual Basic Editor” or press “ALT + F11”

·        VBA Window will open. Select the “Sheet1” node in the project explore, Right Click and Select “View Code”

·        Code window opens

·        Select “WorkSheet” object and select the Activate event.

·        Write the following code.

Cells(2, 1) = “Progress Report of the ” & Cells(5, 13) & ” - ” & Format(“YYYY”, Cells(5, 12))

·        We need to fill the Name, Exam Date, Class

·        Lets use the formula to fill these data

·        Select the cell “C4” in the formula bar type “=J5” and the select the cell c4, set the necessary format by right clicking on the cell and formatting the cell.

·        Now lets populates the marks.

o       Select the cell “B9” and in the formula type “=J9” repeat the same for other rows till “B14” assign each row with the corresponding J cells

o       Select the cell “C9” and in the formula type “=K9” repeat the same for the other rows till C14 assign each row with the corresponding K cells

o       Select the cell “D9” and in the formula type “=L9” repeat the same for the other rows till D14 assign each row with the corresponding L cells

o       Select the cell E16  and type the formula as “=SUM(E9:F13)” which will calculate the Total.

o       To print the grade lets create a macro.

If CInt(Cells(9, 12)) < 40 Or CInt(Cells(10, 12)) < 40 Or CInt(Cells(11, 12)) < 40 Or CInt(Cells(12, 13)) < 40 Or CInt(Cells(13, 13)) < 40 Then

    Cells(17, 5) = “F”

ElseIf CInt(Cells(16, 5)) > 400 Then

Cells(17, 5) = “O”

ElseIf CInt(Cells(16, 5)) > 300 Then

Cells(17, 5) = “A”

ElseIf CInt(Cells(16, 5)) > 200 Then

Cells(17, 5) = “B”

End If

·        Now we are all set with the report template

Step3: Create a page in ASP.NET as shown below.

 

 

Step3: populate the class drop down from the class table

 

Step4: populate the exam drop down from the exam table.

 

Step5: Next step is creating excel sheets (copy of the template created in step 1) for each student in the class, Also writing an sp which will dump the data in the excel sheets. The merge the excel sheet and display it as one excel sheet using java script.

 

This we will see in our next chapter.

Use the following SQL Script to generate the tables.

---

 if exists (select * from dbo.sysobjects where id = object_id(N’[dbo].[Class]‘) and OBJECTPROPERTY(id, N’IsUserTable’) = 1)
drop table [dbo].[Class]
GO

if exists (select * from dbo.sysobjects where id = object_id(N’[dbo].[ClassSubject]‘) and OBJECTPROPERTY(id, N’IsUserTable’) = 1)
drop table [dbo].[ClassSubject]
GO

if exists (select * from dbo.sysobjects where id = object_id(N’[dbo].[Exam]‘) and OBJECTPROPERTY(id, N’IsUserTable’) = 1)
drop table [dbo].[Exam]
GO

if exists (select * from dbo.sysobjects where id = object_id(N’[dbo].[Student]‘) and OBJECTPROPERTY(id, N’IsUserTable’) = 1)
drop table [dbo].[Student]
GO

if exists (select * from dbo.sysobjects where id = object_id(N’[dbo].[StudentMarks]‘) and OBJECTPROPERTY(id, N’IsUserTable’) = 1)
drop table [dbo].[StudentMarks]
GO

CREATE TABLE [dbo].[Class] (
 [ClassID] [bigint] IDENTITY (1, 1) NOT NULL ,
 [ClassName] [varchar] (50) COLLATE SQL_Latin1_General_CP1_CI_AS NULL
) ON [PRIMARY]
GO

CREATE TABLE [dbo].[ClassSubject] (
 [SubjectID] [bigint] IDENTITY (1, 1) NOT NULL ,
 [ClassID] [bigint] NULL ,
 [SubjectName] [varchar] (50) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
 [SubjectDesc] [varchar] (50) COLLATE SQL_Latin1_General_CP1_CI_AS NULL
) ON [PRIMARY]
GO

CREATE TABLE [dbo].[Exam] (
 [ExamID] [bigint] IDENTITY (1, 1) NOT NULL ,
 [ExamTitle] [varchar] (50) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
 [ExamDate] [smalldatetime] NULL
) ON [PRIMARY]
GO

CREATE TABLE [dbo].[Student] (
 [StudID] [bigint] IDENTITY (1, 1) NOT NULL ,
 [FirstName] [varchar] (50) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL ,
 [LastName] [varchar] (50) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL ,
 [ClassID] [bigint] NOT NULL ,
 [Age] [int] NOT NULL
) ON [PRIMARY]
GO

CREATE TABLE [dbo].[StudentMarks] (
 [MarkID] [bigint] IDENTITY (1, 1) NOT NULL ,
 [StudID] [bigint] NOT NULL ,
 [SubjectID] [bigint] NOT NULL ,
 [Mark] [int] NOT NULL ,
 [ExamID] [bigint] NOT NULL
) ON [PRIMARY]
GO

ALTER TABLE [dbo].[Class] ADD
 CONSTRAINT [PK_Class] PRIMARY KEY  CLUSTERED
 (
  [ClassID]
 )  ON [PRIMARY]
GO

ALTER TABLE [dbo].[ClassSubject] ADD
 CONSTRAINT [PK_ClassSubject] PRIMARY KEY  CLUSTERED
 (
  [SubjectID]
 )  ON [PRIMARY]
GO

ALTER TABLE [dbo].[Exam] ADD
 CONSTRAINT [PK_Exam] PRIMARY KEY  CLUSTERED
 (
  [ExamID]
 )  ON [PRIMARY]
GO

ALTER TABLE [dbo].[Student] ADD
 CONSTRAINT [PK_Student] PRIMARY KEY  CLUSTERED
 (
  [StudID]
 )  ON [PRIMARY]
GO

ALTER TABLE [dbo].[StudentMarks] ADD
 CONSTRAINT [DF_StudentMarks_Mark] DEFAULT (0) FOR [Mark],
 CONSTRAINT [PK_StudentMarks] PRIMARY KEY  CLUSTERED
 (
  [MarkID]
 )  ON [PRIMARY]
GO

 

---