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

Create the following data structure.

1. Student Table

2. Class Table

3. ClassSubjects Table

4. StudentMarks table

5. Exam table

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

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

Excel Reporting

We will be discussing the following features and will see some examples on how these features can be implemented in .Net.

• Modifying the structure of the report
• Carrying out analysis or calculations on the data available
• Formatting the data.

Advantages of Excel reporting

Microsoft Excel is very friendly with almost all segments of the corporate, even the top management/decision makers are very comfortable with excel, they can play around excel.  Apart from these we have the following advantages,

• Ease of use
• Ease of modification
• Ease of formatting
• Ease of Analysis
• Easy to manage

Excel reporting .NET

The solution, which I am providing now, is not a pure .NET solution, it is a combination of SQL stored procedure, Excel Macros and Java Script. .Net is just used to tie all of these together, generate the report and present it to the End user.

Before getting into the solution, first we should understand how we can write into an excel document.

Writing into an Excel document using SQL

3.2.1.1 Regions in Excel

A data region is an area on a report containing data from a data source. Types of data regions are charts, lists, tables, and matrices.  (Refer http://msdn2.microsoft.com/en-us/library/ms155976.aspx)

We can name a set of columns in Excel, which will be acting as a table inside the Excel sheet.  We can Inserts and updates to these Named Regions.

3.2.1.2Creating a Named region

·        Open a excel sheet

·        Enter the table header

·        Select the Column header as shown above.

·        Select Insert è Name èDefine

·    A window opens as shown above

·        Name it as “employee” and click “Add”

·        Click OK

·        Save the document and close it. (Name the document as Emp.xls and store it in c:\

Writing data into the named region using the stored procedure 

From SQL we can export the data to any database or data stores using DTS packages and Export wizard. Apart from this we can also export data from SQL to any database or datasource using a Query “Insert into OpenRowSet”.

We should all realize that Excel can also store data like access or any other database and Data can be retrieved or written into the excel document.

We are going to use this statement and export the data to excel docoument.

Create the following SP in Northwind database,

Create procedure dbo.ExportEmployeeData(@filePath as varchar(500))

as

begin

            DECLARE @Expr1 VARCHAR(200),@Expr2 VARCHAR(200),@Expr3 VARCHAR(200) 

            Declare @Sql1 AS VARCHAR(800), @Sql as varchar(800)

            SET @Expr1 = ”’Microsoft.Jet.OLEDB.4.0”’

            SET @Expr2 =  ”’Excel 8.0;Database=’ + @filePath + ‘;HDR=YES”’ 

            SET @Expr3 = ”’SELECT * FROM [Employee]”’

            set @Sql1 = @Expr1 + ‘,’+ @Expr2 + ‘,’ + @Expr3

            set @SQL=’insert into OPENROWSET(’+ @Expr1 + ‘,’+ @Expr2 + ‘,’ + @Expr3 + ‘) SELECT EmployeeID, FirstName, LastName, Title from employees

            Exec(@Sql)

end

In this SQL we are taking the excel name with path as the parameter and we are using Microsoft jet OLEDB drives to write the data into the SQL. Where [Employee] in the statement

SET @Expr3 = ”’SELECT * FROM [Employee]”’

Is the region we created in the Excel Sheet.

The statement “set @SQL=’insert into OPENROWSET(’+ @Expr1 + ‘,’+ @Expr2 + ‘,’ + @Expr3 + ‘) SELECT EmployeeID, FirstName, LastName, Title from employees” actually fetched the records from employees table and inserts it into the Excel sheet.

Now, try executing this stored procedure using the following statement,

ExportEmployeeData ‘c:\employee.xls’

 Notes:

1. There should not any data contained in the cells where we will be trying to write the data.

For example, In our case the region is from B4 to E4. and if we are going to insert 10 rows then there should not any data in the cells from B5 to E14.

If any data exists it will throw an error.

2. The excel file should be in the SQL Server system or else the folder should be shared with write permission and file share path should be given.
3. Also note that, SP is not the only way to write into an excel file, the same logic can be implemented in our java or .net code.

Is it not simple to write the data into an excel sheet. Of course it is.

Now, that we know how the data can be written into an excel sheet, the next step is formatting the data (Presentation). This can be achieved using Excel formulas and Macros.

Introduction

Report is a type of document written/created by someone or a group of people. It can also be called as an organized collection of data, prepared for viewing or printing.

What ever application we do either it is a small billing software or a complex Payroll system for a bigger corporate, and the end of the day/quarter the top decisions maker or not going to look at our software/application we developed, they are going to look at the reports which our application generated with some click of buttons. These reports should be,

They are going to use these reports for their organization needs or for an analysis. W should realize the importance of reporting in software development.

In this article, I am going to talk about the key features which can provided in Excel reporting and how .Net applications can generate the report in Excel format.

Excel Reporting

Developer would have come across several reporting methodologies like formatting the data with tabs and spaces and printing them, or using reporting packages like Data report for VB6.0 or Crystal packages.

But the reports generated by these methods, provides very minimal features to the end users.

Lets see some scenarios,

Scenario 1:

The end user generates the report with some clicks of button, he see the report on the screen. Now we wants to show a Graph which conveys some meaning full data or he feels that if a graph is added to this report it will add more meaning to report. So what he needs to do,

• Need to check the agreement with the software vendor
• Need to raise a Change request.
• Series of call with the business analyst to explain how the graph should be generated.
• Report designer starts working on the report completes the report
• Series of testing has to be done
• Then deploy the report

Scenario 2:

The end user is going to send some report to their client or present the report in their board meeting and feels that he should not show all the data available but only a part of it. So what should we do for this case?

• We create two report or en number of reports with the same set of data hiding some columns or making some structural changes to the report

 The report is not flexible to hide some columns or generate report to their immediate requirement.

The Solution

The best solution would be developing a feature, which helps the user to create report on fly.

Report on fly

• Users can given the option, what fields needs to be display
• The type of analysis or calculations they want to do on the data available
• Add some graphical format of the data
• And many more things 

Lets see how we can achieve some of these using excel.