Creating Reports in C# using Crystal Reports with Xml data Definitions

Abstract

This article explains how to extract data into a Crystal Report created outside a C# project using xml data definitions and data sets.

Introduction

This requirement was a part of a main C#.net project, where we wanted to develop reports using crystal reports using field definitions. As we took a great deal of effort in arriving at a solution, and information available in the C# corner helped us to a great extent, we decided to submit this code for the benefit of the fellow programmers.

In this project,

• The report is formatted outside the project via Crystal Reports (the .rpt file)
• The data to the report is extracted from a field definition file created as a .xsd extension (as compatible with ADO.Net)
• The report is called and displayed through a form using the crystal report viewer.

The example overview

We have taken the Authors table in the pubs database where au_id, au_lname, au_fname will be printed on the report.

The main requirement is to create the Xml schema file with .xsd extension and bind the data to the .rpt file.

The example handles this in one form with two buttons: The XSD Button to create the .xsd field definition file and the VIEW button to generate the report.

So here is the step by step procedure to arrive at this.

1. Create the .xsd field definitions file depending on the data that we want to extract from a database.

Insert the following code in the click event of XSD button, to create the .xsd file. In our example this file is called the 'sampledatadef.xsd'. This is created in c:\

By inserting the following code into the start form of your project and clicking the XSD button, you will see the sampledatadef.xsd created in the root directory of your c:\

// References used
using System;
using System.Drawing;
using System.Collections;
using System.ComponentModel;
using System.Windows.Forms;
using System.Data;
using System.Data.SqlClient;
// These are auto generated
private System.Windows.Forms.Button button1;
private System.Windows.Forms.Button button2;
private System.ComponentModel.Container components = null;
///

/// This is to generate the field definition file
/// This code needs to be executed, only when there had been a change to the
/// Data fields that are to be extracted.
///

private void button1_Click(object sender, System.EventArgs e)
{
SqlConnection mcon_pub;
SqlDataAdapter mda_fld;
// Remember to change data source as applicable.
string constr = "Data source =SERVER;Initial catalog=pubs;User ID=sa;password=";
string sqlstmt = "select au_id, au_lname, au_fname from Authors";
DataSet ds_fldDef = new DataSet();
mcon_pub = new SqlConnection(constr);
mda_fld = new SqlDataAdapter(sqlstmt,mcon_pub);
ds_fldDef = new DataSet();
mda_fld.Fill(ds_fldDef);
ds_fldDef.WriteXmlSchema(@"c:\sampledatadef.xsd");
MessageBox.Show ("Field Definitions Written Successfully");
}
private void button2_Click(object sender, System.EventArgs e)
{
ViewReport mfrm_view = new ViewReport();
mfrm_view.Show();
}

2. Now create the .rpt file in crystal reports using the .xsd field definitions file.
You need to select 'Create new Connections' and take option - Field Defintions. Select the ADO.NET option. In the dialogue box you will be asked to enter the xsd file name. Select the path and click the 'Finish' button.

We have named this report as crystalsample.rpt (saved in c:\)

If the ADO.Net option is not available, you will have to add new components to the installed crystal reports using 'Add/Remove' programs option and include the necessary component to the Crystal Reports installation.

3. Generate the class to hold the data set for the .xsd datadefiniton
This is an important step. The .xsd definitions do not recognize the datasets generated through the normal DataSet class. The report will be displayed only with the headings, if this step is not accomplished!!! (This was a weird experience to us).

This is done though executing the following command through the command prompt tool of the Visual Studio .Net Tools.

xsd.exe /d /l:C# sampledatadef.xsd

(The /d directive tells the tool to generate DataSets, /l specifies the language to use)

This command generates the DataSet class compatible with the .xsd file you created in the path in which you executed the command. The name of the source file will be sampledatadef.cs.

4. Now add this class to your project.
In the source file, the name of the class will be generated as 'NewDataSet'. Rename this name to a name that you desire in the source file. We have named it as ds_SampleDataSet

5. Now generate the code to extract the data from field definition
In our example we have used a new form for this, which is loaded with the click event of the VIEW button.

This is done in an easy set of steps.

1. Define a dataset of the newly generated type. In this example
   Private ds_sampledataset mydataset = New ds_sampledataset()
2. Fill this dataset with the identical data fields that you created for the xsd definition file. In this example they are au_id, au_lname, au_fname of Authors.
3. Open a new ReportDocument type class and call the .rpt file you created into it. (Make sure that the necessary references are added to the project - CrystalDecisions.CrystalReports.Engine CrystalDecisions.Shared )

The form to load the report is named as ViewReport and the CrystalReportViewer is added to the form

The essential coding to the form goes as follows:

// References used
using System;
using System.Drawing;
using System.Collections;
using System.ComponentModel;
using System.Windows.Forms;
using System.Data;
using System.Xml;
using System.Data.SqlClient;
using CrystalDecisions.CrystalReports.Engine;
using CrystalDecisions.Shared;
// private variables defined
private SqlConnection mcon_pub;
private SqlDataAdapter mda_Rpt;
private ds_SampleDataSet ds_xml = new ds_SampleDataSet();
// These are auto generated
private CrystalDecisions.Windows.Forms.CrystalReportViewer crystalReportViewer1;
private System.ComponentModel.Container components = null;
///

/// Connects to the database and extracts data into the new dataset type class
///

private void SetConnection()
{string sqlstmt = "select au_id, au_lname, au_fname from Authors";
// Remember to change data source as applicable.
string constr = "Data source =SERVER;Initial catalog=pubs;User ID=sa;password=";mcon_pub = new SqlConnection(constr);
mda_Rpt = new SqlDataAdapter(sqlstmt,mcon_pub);
// Note that the data is filled into the new xml type
datasetmda_Rpt.Fill(ds_xml);
}
private void SetReport()
{
ReportDocument rpt = new ReportDocument();
rpt.Load(@"C:\crystalsample.rpt");
rpt.Database.Tables[0].SetDataSource(ds_xml);
crystalReportViewer1.ReportSource = rpt;
}
///

/// The load event of this form is named as Form1_Load
///

private void Form1_Load(object sender, System.EventArgs e)
{
SetConnection();
SetReport();
}

Report Generation in .NET

1. Introduction

Report generation is an integral part of enterprise application development. Choosing the right kind of report generation strategy decides the success of the application in terms of cost and performance. The whitepaper discusses some of the key factors that need to be considered when architecting reporting systems.

Picking the right kind of reporting tools also plays a significant role in deciding the scalability and cost of the system. We will discuss some of the reporting tools which are widely used in the existing reporting systems. Selecting the best out of these tools is a crucial decision. SQL reporting service is one of the hot reporting tools; the white paper gives an introduction to the tool.

Comparing reporting tools in terms of performance and cost is a really difficult job; and these measures will vary from architecture to architecture. But comparing the .NET features available in these tools is very interesting for a .NET chap. The white paper attempts to perform a comparative study of the common tools available in the market.

Excel reporting is an integral part of many financial applications. Many retail applications require Excel reports with fairly large amount of data and good format. The simple approach which comes into our mind will be to uses Excel component. But Microsoft doesn't recommend this approach. One of the best alternatives is explained in the Excel Reporting section.

Securing the data exposed by reports is also a critical decision. The white paper discusses some of the common strategies used in large enterprise systems to secure the data exposed by reports.

2. Reporting Tools

Let's discuss some of the reporting tools which are widely used in the existing reporting systems. Selecting the best out of these tools is a crucial decision. Three main reporting tools available in the .NET code space are:-

a) Crystal Reports

Crystal Reports (for Visual Studio .NET) is the standard reporting tool for Visual Studio .NET. It brings the ability to create interactive, presentation-quality content; to the .NET platform. Crystal report is an integral part of the Visual Studio .NET environment.

Using Crystal reports, developers can host reports on web and windows platforms and publish Crystal reports as report web services on a web server. Crystal Reports for Visual Studio .NET is based on the framework of Crystal Reports 8.0, but has been developed for the .NET platform to provide .NET developers with a richest API.

Crystal Reports for Visual Studio .NET provides developers with data visualization and analysis capabilities through seamless integration within all the Visual Studio languages targeting .NET. It uses an open and flexible architecture, with standards like XML, to share reports and information over the Web. Report presentation in both Windows and HTML are available.

Using Visual Studio .NET it is possible to create a new Crystal report or add an existing Crystal report to a project. Reports can be kept on a local machine or published as a web service on a web server. Depending on whether it is a windows or web application, it is possible to bind the report with either the Windows Forms Viewer or the Web Forms Viewer. Users can run the Windows application on a Windows platform or deploy the web application on a client browser to view the Crystal report.

Crystal Reports for Visual Studio .NET works in a single-tier, two-tier, and three-tier architecture on both Windows Forms and Web Forms platforms.

Single-Tier Applications

A Windows application that contains a local report is based on a single-tier approach where all the components are installed on each user's machine. Report components, including the report file (.rpt file) and the Report Engine, are installed with the executable file on the user's machine.

Scalability

Windows applications with local reports are best suited for small-scale deployment since the application is installed on each user's machine. This type of deployment relies heavily on client-side processing. If any changes to the report are required, the application needs to be reinstalled on the user's machine.

Two-Tier Applications

In the case of a web application that hosts a local report (.rpt file); the processing is based on a two-tier relationship where any events raised on the client are handled on the Web server.

Scalability

Users require only a browser on their machines. All report components reside on the server and can be updated centrally.

Three-Tier Applications

A Report Web Service is a Crystal report that has been published, or exposed, for consumption by an application. Over the Internet, a Windows or Web application can connect to a Report Web Service, consume the exposed Crystal report, and display the report for users by hosting it in a report viewer. It is supported with a Report Web Service that is part of the architecture, or can access a Report Web Service that is part of architecture.

Scalability

Depending on the type of application, users require only a Windows Forms Viewer or a Web browser to view Report Web Services. All other report components reside with the Report Web Service on a remote server. Any changes made to the Report Web Service are reflected immediately in the application.

Enterprise Applications

If a distributing application needs to support report viewing and delivery across a large company, it requires more than one report engine to handle all users' report queries. Crystal Enterprise provides organizations with a scalable, secure Web-based management and delivery system for Crystal .rpt files to any authorized user, inside the organization or beyond.

Using Visual Studio .NET, it is possible to build applications which can access Crystal Enterprise servers. This enables designers to build a small client which can be distributed to thousands of users and use the Crystal Enterprise servers to handle report management, processing, and scheduling.

Scalability

Crystal Enterprise allows organizations to scale up (add more processors) or scale out (add more physical machines) to meet growing user demands. This system delivers web speed performance for both on-demand and scheduled reports. Crystal Enterprise is designed to scale to the enterprise and deliver rich, interactive reports to a broad range of users.

Licensing

Now, let's have a look at the licensing details related with Crystal reports. Crystal Reports have mainly four editions; Crystal reports integrated with .NET, Developer, Advanced and Professional. Major features of Crystal Developer/ Advanced reports are mobile device support, additional export formats, and improved flexibility while connecting and retrieving data from data bases.

'Crystal reports for .NET' is mainly suited for designing and testing server or web based applications. It is also suited for thick client windows applications i.e. applications in which reporting engine is installed and run from the end user's client machine. It is not suited for a Web farm environment. To redistribute a server or web application that uses the .NET reporting component to third parties (outside the organization), requires a written authorization from Crystal. This procedure does not associate any extra cost.

b) Crystal Enterprise Reports

Crystal Enterprise is a web-based enterprise report management and distribution system. Crystal Enterprise can add value to the existing .NET reporting functionality since it provides a secure, scalable, multi-tier, web-based, reporting solution. With this framework, users throughout the organization, business partners, and customers can access critical information via the web. Some of the main features of Crystal Enterprise are described below.

Security
Unlike Crystal Reports, Crystal Enterprise incorporates a security system for managing content delivery to both internal and external users. It offers both user level and object level security. It also offers the ability to log all actions on the Crystal Enterprise server for security and auditing purposes.

Scheduling
Crystal Enterprise includes a scheduling system that enables report processing at specific times or on recurring schedules. Scheduling is done on a clusterable server component to ensure that crucial processing schedules are not lost or ignored in the event of hardware failure. Scheduling can increase system performance by having the reports ready to view before they are needed.

Load Balancing
In a multi-server environment, balancing the load efficiently across multiple machines greatly enhances scalability and end user response time. Load balancing also ensures effective use of hardware and decreases performance bottlenecks. Crystal Enterprise includes built-in load balancing across all system management and report processing functions.

Fail-Over
A key component of the Enterprise architecture is built-in support for fault-tolerance. This involves the ability to cluster key components of the system to ensure maximum uptime and responsiveness, and the ability to fail-over between machines in a cluster in the event of hardware failure.

Caching
This Web optimization approach increases the responsiveness of Crystal Enterprise. Caching stores previously accessed report pages in a rapid access file system so that they can be dispatched to other users. This is faster then processing an entire report again.

Integrating Crystal Enterprise with .NET applications
Crystal Enterprise can be incorporated into .NET application using SOAP or COM. Depending on the method used for calling the Crystal Enterprise, different functionality can be retrieved.

Calling Crystal Enterprise through SOAP allows us to get a listing of reports and to view the reports with either the Web Forms Viewers or the Windows Forms Viewers controls.

Crystal Enterprise also exposes a full COM-based object model to enable a more complete method of integration. This COM-based SDK offers a single entry point to the Crystal Enterprise Web services. This object model encapsulates all the calls needed to extract report listings, control the processing and delivery of reports, view reports, and generally interact with the Crystal Enterprise services.

c) Active Reports

Active Reports for .NET from Data Dynamics can be used along with the Visual Studio editor to write code in either C# or Visual Basic.NET to generate reports. Active Reports can be viewed in web browsers. Active Report component includes an ASP.NET server control to setup web client viewer options as well as a Windows Forms viewer control that offers split and multi-page views, text-searches, table of contents and a customizable toolbar. Since Active Reports is not the default Visual Studio item, it is wise to have a look at the key features provided by it.

Features:

ASP.NET Support
Active Reports can be used in web applications by using the ASP.NET Server Control. This control supports web report distribution using HTML, ActiveX, .NET and PDF viewers. The Web Viewer control allows quick viewing of Active Reports on the web as well as printing capability with the ActiveX viewer and Acrobat Reader viewer Type properties. RPX files HTTP Handler allows developers to simply place a report on the web server and make it available to the Internet users in HTML or PDF without writing any code.

Integration with Visual Studio.NET
Microsoft Visual Studio.NET editor can be used to write C# or VB.NET code to customize reports. It is possible to build reports dynamically. Report Creation API provides complete runtime access to report objects. Report Wizards are available to generate simple and quick reports. The end user designer can be used to host the designer in development application and provide end user report editing capabilities. The designer component can be used for customizing the display.

Deployment
Easy to deploy Reporting Engine is a single managed strong named assembly. Assemblies can be distributed using XCopy or placed in the global assembly cache (GAC). Professional Edition includes - HTTP handlers and server side web control for ASP.NET reporting

Licensing
Active Reports come in two flavors; Professional and Standard edition. Choosing the right approach depends up on the cost and application needs. End User Report Designer, ASP.NET integration, HTTP handlers and Web Viewer control are the major features of Active Reports Professional edition compared to Standard edition. See the comparison section for more details.

d) SQL Reporting Service

SQL Server Reporting Services is a comprehensive server-based solution that enables the creation, management, and delivery of both traditional, paper-oriented reports and interactive web-based reports. An integrated part of the Microsoft business intelligence framework, Reporting Services combines the data management capabilities of SQL Server and Microsoft Windows Server with familiar and powerful Microsoft Office System applications to deliver real-time information to support daily operations and drive decisions. SQL Server reporting services is strongly integrated with SQL Server. But it doesn't mean that report data source should be SQL Server. It can be Oracle DB2, Informix, and Sybase etc.

It comes in two flavors; Standard and Enterprise editions. Enterprise edition offers more features such as web farm configurations, security extension APIs, supports more than four CPU, supports more than 2GB RAM etc.

Strong Integration with XML

The entire report and the data source are stored in an XML file. This allows more flexibility for the developers in generating dynamic reports. The report design is strongly integrated with Visual Studio .NET environment. Report developers can create reports to be published to the Report Server using Microsoft or third-party design tools that use Report Definition Language (RDL), an XML-based industry standard used to define reports.

Security

Supports Windows based authentication and role based authentication. Better security model compared to any other reporting tool.
Output Formats

SQL Reporting Services support Excel, TIFF, HTML, PDF, CSV and XML formats. It also supports Office Web Components and Web Archives.

Report Management

Report definitions, folders, and resources are published and managed as a Web service. Managed reports can be executed either on demand or on a specified schedule, and are cached for consistency and performance. SQL Server Reporting Services supports both on-demand (pull) and event-based (push) delivery of reports. Users can view reports in a Web-based format or in e-mail. Scheduling of reports, web farm support, report history are some other major features

It is also characterized with a well defined set of APIs which can be used for customizing report management, securing and displaying reports. Subscription and delivery support, Visual Studio.NET integrated report designer, featured and powerful report processing etc are some of the other features.

e) Comparative Study

It is nice to have a comparative study of the features of the reporting tools, we have discussed so far. Since Crystal Enterprise focus is more towards enterprise reporting, here we are comparing Active Reports standard, Active Reports Professional, Crystal Reports, and Crystal Reports Developer edition.

The following table gives a rough idea regarding the advantages and disadvantages of various reporting tools over the other. The comparison doesn't look into the reporting features; instead concentrate more on the .NET support.

3. Effective Excel Reporting

Enterprise applications often depend on Excel reports for interacting with other ERP systems such as JD Edwards. Projects which fall under the financial and retail verticals use Excel spreadsheets heavily. Excel automation in a client side environment usually makes use the Excel object. But this section focuses mainly on how to take advantage of Excel in ASP.NET environment.

Implementing Excel automation in the server side is not that difficult with ASP.NET. But making it scalable and deployable is not an easy job. Using Excel object in an ASP.NET environment creates many concerns in the long run. In fact, Microsoft also doesn't support or recommend server side automation of Office.

Problems Using Automation of Office Server-Side

Designers need to be aware of six major concerns associated with Excel report generation using Office automation. A designer should be aware of how to address these concerns and how to minimize their effects as much as possible. Consider these items carefully while automating Excel or Office because no one solution can address all of them, and different designs require us to prioritize the elements differently.

a. User Identity: Office Applications assume a user identity when they are run, even when they are started by Automation. They attempt to initialize toolbars, menus, options, printers, and some add-ins based on settings in the user registry hive for the user who launches the application. ASP.NET runs under 'ASPNET' user account, which has got limited access privileges, and therefore Office may fail to initialize properly on startup. Even if the Office application can be started, usually it doesn't happen, other functions may fail to work properly. So we have to perform impersonation either through code or through web configuration file. Implementing impersonation using code also dangles security concerns.

b. Interactivity with the Desktop: Office Applications assume that they are being run under an interactive desktop, and may in some circumstances need to be made visible for certain automation functions to work properly. If an unexpected error occurs, or an unspecified parameter is needed to complete a function, Office is designed to prompt the user with a modal dialog box that asks the user what they want to do. A modal dialog box on a non-interactive desktop cannot be dismissed, which causes that thread to hang. Although certain coding practices can help reduce the likelihood of this occurring, they cannot prevent it entirely.

c. Reentrancy and Scalability: Server-side components need to be highly reentrant, multi-threaded COM components with minimum overhead and high throughput for multiple clients. Office Applications are in almost all respects the exact opposite. They are non-reentrant, STA-based Automation servers that are designed to provide diverse but resource-intensive functionality for a single client. They offer little scalability as a server-side solution, and have fixed limits to important elements, such as memory, which cannot be changed through configuration. More importantly, they use global resources (such as memory mapped files, and shared Automation servers), which can limit the number of instances that can run concurrently and lead to race conditions if they are configured in a multi-client environment. Developers who plan to run more then one instance of any Office Application at the same time need to consider "pooling" or serializing access to the Office Application to avoid potential deadlocks or data corruption.

d. Resiliency and Stability: Office 2000, Office XP, and Office 2003 use Microsoft Windows Installer (MSI) technology to make installation and self-repair easier for an end user. MSI introduces the concept of "install on first use", which allows features to be dynamically installed or configured at runtime (for the system, or more often for a particular user). In a server-side environment this both slows down performance and increases the likelihood that a dialog box may appear that asks for the user to approve the install or provide an appropriate install disk. Although it is designed to increase the resiliency of Office as an end-user product, Office's implementation of MSI capabilities is counterproductive in a server-side environment. Furthermore, the stability of Office in general cannot be assured when runs in the server side because it has not been designed or tested for this type of use. Using Office as a service component on a network server may reduce the stability of that machine and as a consequence the network as a whole. If the plan is to automate Office server-side, attempt to isolate the program to a dedicated computer that cannot affect critical functions, and that can be restarted as needed.

e. Server-Side Security: Office Applications were never intended for use server-side, and therefore do not take into consideration the security problems that are faced by distributed components. Office does not authenticate incoming requests, and does not protect from unintentionally running macros, or starting another server that might run macros, from the server-side code. Do not open files that are uploaded to the server from an anonymous Web! Based on the security settings that were last set, the server can run macros under an Administrator or System context with full privileges. In addition, Office uses many client-side components (such as Simple MAPI, MSDAIPP) that can cache client authentication information in order to speed up processing. If Office is being automated server-side, one instance may service more than one client, and because authentication information has been cached for that session, it is possible that one client can use the cached credentials of another client, and thereby gain non-granted access permissions by impersonating other users.

f. Performance Cost: Using Excel object in the server side ASP.NET code to automate Excel automation also reduces performance throughput compared to alternative approaches. Each excel operation involves a COM call, which involves Runtime Callable Wrapper as the intermediate, significantly affects the speed while generating relatively large reports.

Besides these technical concerns, a designer should also consider the feasibility of such a design with respect to licensing. Current licensing guidelines prevent Office Applications from being used on a server to service client requests, unless those clients themselves have licensed copies of Office. Using server-side Automation to provide Office functionality to unlicensed workstations is not covered by the End User License Agreement ().

Alternative approaches

Microsoft strongly recommends that we, developers, should find alternatives to Automation of Office while developing server-side solutions. Because of the limitations to Office's design, changes to Office configuration are not enough to resolve all issues. Microsoft recommends a number of alternatives that do not require Office to be installed server-side, and that can perform most common tasks more efficiently and quickly than Automation.

The most recommended approach is to generate a XML file or HTML file, which in turn represents the spreadsheet. Because Office 2000 and later support HTML as a native document format, most documents can be created in HTML, using XML markup when needed, and streamed to a client by using a MIME type so that the resulting text is displayed in Office. The document can be edited, saved, and even returned to the server when needed, by using nothing more than ASP.NET on the server. For earlier versions of Office, other easily manipulated text formats can be used.

Some native binary formats can be edited by using the Office Web Components or ActiveX Data Objects (ADO) with much greater speed and scalability. Office Web Components can be used to generate spreadsheets with limited features. But the performance cost associated with these components is less compared to directly using Office Component. Document properties can be viewed or changed without Automation, and file management and versioning is possible by using FrontPage Server Extensions or Distributed Authoring and Versioning. When Automation is essential, most tasks can be offloaded to the client, providing better stability and scalability for the system because each user runs the task in their own context, with their own settings.

Case Study

In our last project we were asked to generate Excel reports for integration with JD Edwards. Initially we followed the traditional approach to generate the Excel reports. These reports contain lot of formulas, comments and formatting so that the users can directly work on these Excel reports.

The following code depicts the approach that we have used to generate the Excel report:-

Excel.Application excelApplication;
Excel._Workbook excelWorkBook;
Excel._Worksheet excelWorkSheet;
Excel.Range excelRange;
// Perform garbage collection to free existing resources.
C.Collect();
// Create the excel object
excelApplication = new Excel.Application();
excelApplication.Visible = false;
// Create a new workbook.
excelWorkBook = (Excel._Workbook)
(excelWorkBook.Workbooks.Add(Type.Missing));
// Get the active sheet
excelWorkSheet = (Excel._Worksheet)excelWorkBook.ActiveSheet;
// Print the details
for(int index=0; index <>// Do some processing
excelWorkSheet.Cells[index, 1] = "RecordValue";
// Code to perform more formatting
}
//AutoFit columns A:Z.
excelRange = excelWorkSheet.get_Range("A1", "Z1");
excelRange.EntireColumn.AutoFit();
excelApplication.Visible = false;
excelApplication.UserControl = false;
excelWorkBook.SaveAs(temporaryFileName,
Excel.XlFileFormat.xlWorkbookNormal, null, null, false, false, Excel.XlSaveAsAccessMode.xlShared,
false, false, null, null, null);
// Need all following code to clean up and free all references!!!
excelWorkBook.Close(null,null,null);
excelApplication.Workbooks.Close();
// Free the resources
excelApplication.Quit();
System.Runtime.InteropServices.Marshal.ReleaseComObject(excelRange);
System.Runtime.InteropServices.Marshal.ReleaseComObject(excelApplicatn)System.Runtime.InteropServices.Marshal.ReleaseComObject(excelWorkShet);
System.Runtime.InteropServices.Marshal.ReleaseComObject(excelWorkBook);
// Set the object values to null
excelWorkSheet = null;
excelWorkBook = null;
excelApplication = null;
// Force Garbage collection
GC.Collect();

The Excel object used was 'Microsoft Excel 11 library'. From the code, it can be understand that most of the formatting involves a RCW call. For printing around 6000 rows, it took 2-3 minutes on a 700 MHz Pentium IV computer.

The performance of the report was according to the Customer expectations. So we started thinking about other possibilities. Luckily, before delivering the project to the client we could find an alternative solution. Thanks to Microsoft!

We got the solution from one of the MSDN links. Instead of generating the Excel reports using Excel object, we generated XML files which is supported by Office XP. Excel file can be saved to XML format or HTML format in Office XP. We have also thought about the option of generating HTML files instead of XML files. Even though HTML is an ideal file format for displaying Excel data in Web browsers, in order to persist Excel-specific features and have Excel workbooks displayed well in a Web browser, Excel HTML is highly complex. This results in files that are only read by Excel. XML helps separate data and view formatting, which results in a cleaner, more robust file format and, more importantly, makes Excel data more available to other applications.

We have decided to (or forced to) experiment with the new approach. All what is happening is good for the World; we found the new approach the most scalable and performance driven approach. We could generate reports which contain fairly large amount of data (more than 10,000 records) with in 30-40 seconds.

Features of XML Approach

Some of the features of the XML implementation in Excel and the Microsoft Office XP Spreadsheet component include:

• "Round-tripping" information through Excel and the Spreadsheet component
• Opening non-Excel, non-Spreadsheet component generated XML data files
• Opening and "flattening" arbitrary, well-formed XML
• Opening hand-authored XML Spreadsheet documents
• Publishing data from Excel to the Spreadsheet component
• Exporting data from the Spreadsheet component to Excel
• Copying and pasting data from Excel to the Spreadsheet component, and vice versa
• Using XML-based QueryTable objects in Excel
• Persisting Range objects as XML in Excel and the Spreadsheet component

Limitations of XML Approach

• It doesn't support Excel macros.

There may me more limitations; but for generating fairly complex and well formed reports, XML approach is the best one. Since the code for generating XML files involves lot of repeatable code, we recommend to implement XML generation part as a class library so that it can be used in similar projects; saving your time and cost.

4. Security

Security is one of the key points to consider while architecting a reporting system. Managing security of the data exposed by the reports is a vital part. .NET Framework's robust support for Active Directory (AD) allows developers to easily implement security across the reporting system by applying AD permissions to report files themselves and granting permission to report processing objects only to those groups that should have access to specific data. Then the administration of all security - including the reporting system - can be managed by administering group membership.

While creating Excel reports in .NET, Office Automation is not the right approach. Still you prefer to use it, think serious about security loop holes exposed by your reporting architecture. Manage the permissions to anonymous account and ASPNET account very carefully.

One of the security approaches done in large security oriented database systems is to have a separate reporting server; in addition to the deployment server. It improves performance and security of the data. The data will be exposed to the out side world as views and these views will follow SQL/Windows authentication mechanisms.

5. Conclusion

Selecting the right tool for report generation depends on the type of application and cost overhead. The architecture to be decided, the component to be used etc are dependent on your deployment strategy and application architecture.
Think about the deployment strategy before deciding a reporting strategy. While generating Excel reports it is not wise to use Excel automation. Generating Excel reports as XML files sounds better in terms of scalability and performance. PDF and Crystal report generation are not difficult jobs in .NET; but be careful about the licensing issues in a distributed environment. Security in reports can be achieved through Active Directory Services and IIS.

6. References

http://www.businessobjects.com
http://www.datadynamics.com/default.aspx
http://msdn.microsoft.com/library/default.asp?url=/library/en-us/dnexcl2k2/html/odc_xlsmlinss.asp
http://www.microsoft.com/sql/reporting/default.asp
MSDN Articles

7. Glossary

Understanding Crystal Report Viewers

The integration of Crystal Reports in Visual Studio .NET and availability of wizards and tools has made writing reports a piece of cake for developers. Visual Studio .NET hides all the details from developers and provides them easy to use user interface so they can concentrate on designing the layouts of the reports. But it's always a good idea to understand what goes under the hood. In this article, I will explore the Crystal Report Viewers and their functionality.

Crystal Report Viewers work as a container of a report and allow us to view a crystal report in Windows and Web applications. Crystal Reports for Visual Studio .NET provides two report viewers - Windows Forms Viewer and Web Forms Viewer. As you can guess from these names, Windows Forms Viewer control is used to view reports in Windows Forms applications and Web Forms Viewer is used to display reports in Web Forms applications.

Crystal Reports Viewer for Windows Forms

The CrystalReportViewer class defined in the CrystalDecisions.Windows.Forms namespace represents a Crystal Report Viewer for Windows Forms (viewer afterwards). This class is a user control and can be used as any other user controls. This control is available in the Toolbox and you can simply drag it to a Form to add the control to the form.

To view a report in the viewer, you simply need to set the ReportSource property of the control. The ReportSource property can be either a full name of the report file, a strongly typed report, or a ReportDocument object.

The following code snippet binds a report with hard coded full path of the report to the viewer:

crystalReportViewer1.ReportSource = @"C:\temp\Customers.rpt";

The following code snippet binds a strongly typed report to the viewer:

Customers custReport = new Customers();
crystalReportViewer1.ReportSource = custReport;

The following code snippet creates a ReportDocument object, loads a report using the Load method and binds the ReportDocument object to the viewer:

CrystalDecisions.CrystalReports.Engine.ReportDocument doc =
new CrystalDecisions.CrystalReports.Engine.ReportDocument();
doc.Load(@"C:\temp\Customers.rpt");
crystalReportViewer1.ReportSource = doc;

See my article Introduction to Crystal Reports in .NET to understand how to add a Crystal Report Viewer to a Windows Forms application and how to generate a strongly typed report.

Customizing the Behavior of Crystal Report Viewer

Using the CrystalReportViewer class members, we can customize the behavior of the viewer such as disabling or enabling viewer toolbar buttons, adding custom zoom factor, or moving to different views of the report programmatically. The default Crystal Report Viewer looks like the following:

The first four buttons on the viewer allow us to move first, previous, next, and last page of the report. The next button can be used to jump to a particular page. Next three buttons are used to close the view, print the report, and refresh the report respectively.

Next button allows you to export reports as to various files such as a pdf, tif, doc, or rtf. The next button is a toggle for grouping if a report has groups. Next two buttons are for zooming and search.

By using the properties and methods of CrystalReportViewer , we can customize the default settings of the viewer. Some of the common properties of CrystalReportViewer are following:

ActiveViewIndex and ViewCount

A page in the report is called a view. The active page is called active view. The ActiveViewIndex property gets or sets the active view in the viewer and ViewCount returns total number of views in a report.

DisplayToolbar

You can display or hide the toolbar using this property. The following code snippet hides the toolbar.

crystalReportViewer1.DisplayToolBar = false;

ShowCloseButton, ShowExportButton, ShowGotoPageButton, ShowGroupTreeButton, ShowPrintButton, ShowRefreshButton, ShowTextSearchButton, and ShowZoomButton properties can be used to show or hide the respective buttons.

CrystalReportViewer Methods

We can manage the functionality of the viewer buttons programmatically using its members. The CrystalReportViewer class provides methods to export, print, refresh, close, search, zoom, and navigation functionality. The following table lists the CrystalReportViewer class methods:

CloseView	Closes a view tab in the viewer.
DrillDownOnGroup	Drills down on a group.
ExportReport	Exports the report displayed in the viewer.
GetCurrentPageNumber	Gets the current page number of the report.
PrintReport	Prints the report displayed in the viewer.
RefreshReport	Refreshes the report displayed in the viewer.
SearchForText	Searches the report for the given text.
ShowFirstPage	Shows the first page of the report.
ShowGroupTree	Shows the group tree in the viewer.
ShowLastPage	Shows the last page of the report.
ShowNextPage	Shows the next page of the report.
ShowNthPage	Shows the specified page of the report.
ShowPreviousPage	Shows the previous page of the report.
Zoom	Changes the magnification level of the viewer

How do I pass the required parameters of a Stored Procedure in Crystal Reports?

In many of our applications, we need to generate a report using Crystal Report(CR) to retrieve data into a certain format. The report can have as data source a XML Document, a DataSet, a Table or even a Stored Procedure from a database. This article describes how to pass the required parameters of a Stored Procedure that is being used as data source by a CR report. If these parameters are not passed through code when loading the report viewer (crystalReportViewer class), a dialog will be shown requesting each necessary parameter for execute the Store Procedure.

If we want, we can avoid this dialog and dynamically enter all the parameters in the code.

The fallowing C# code avoid that:

//
// We need declare this namespace
//
using CrystalDecisions.Shared;

The code of below needs to be written after the ReportSource porperty of the crystalReportViewer has been set and before the control to which belongs finishes being loaded.In this case, we are adding two parameters

ParameterField field1 = this.crystalReportViewer1.ParameterFieldInfo[0];
ParameterField field2 = this.crystalReportViewer1.ParameterFieldInfo[1];
ParameterDiscreteValue val1 = new ParameterDiscreteValue();
ParameterDiscreteValue val2 = new ParameterDiscreteValue();
val1.Value = "parameter1";
val2.Value = "parameter2";
field1.CurrentValues.Add(val1);
field2.CurrentValues.Add(val2);

Using Crystal Reports 9 in Windows Application

This application uses Microsoft Access as the backend database for storing the data.

It uses crystal reports to report data to the user. In my project I have used the 3-tier architecture to differentiate the different layers.

The main form is as shown below. Using this form you can navigate between the records either using the menu or using the buttons.

When you click the "Generate Report",the following window is displayed.

There are 3 options specified to output the reports.

To change the data, you can access the Employees.mdb in the project directory.

Reporting Hierarchical Recursive Data with MS Reporting Services using C#

Introduction

I will start with a question here. How many of you had chance to interact with Employee table from sample database Northwind? There you go... I can imagine countless hands in air, and why not it is one of the standard databases comes with both Access and SQL server. All right, are we going to discuses Northwind database here? NO. Is Employee table is something special? I would say YES to this. Why special? Well, if you pay a close attention, it is just like any other standard table, however, two fields from the table, "EmployeeID" and "ReportsTo" are related to each other in an interesting way! Yes, you got it right; I am talking about Hierarchical relationship which we also call commonly as Recursive data. I am trying to shade some light on reporting of data which is recursive in nature.

What is Recursive Data?

I am sure you must have come face to face with this challenge called Recursive Data if you have to deal with databases. Hierarchical data which defines the level of association with a defined relationship ca be called recursive in nature. A typical example would be to take Accounting Application Database which has a table called ChartOfAccounts, the Primary Key "Account_Id" will have foreign key relationship with another column called "Reporting_Account_Id". Another example is the one which I am using in this article is that each Employee has a Manager assigned.

Do you recall "Self-Join"? As you can see in above image, this is one way we display the recursive nature of data, just putting here for some better understanding.

Reporting Challenge for Recursive Data

I am ready with my second question here, before I ask you; I would like your kind attention to the image with Employee level output. Now the question: Do you think generating report like that without any custom code is piece of cake? I am pretty sure this time I will see many less hands in air compare to my first question! Or you can say yes it is piece of cake if you have already tried your hands on MS Reporting Services.

It is very common that when we have to deal with a situation like this, we do end up writing some sort of custom code in order to find out the level of hierarchy etc. etc. A typical developer mindset will always have endless list of wishes for software vendors, one of my wish was if something was done to address this issue of handling recursive data built right into the reporting engine. Some how I felt my telepathy worked and guys at Microsoft put this feature into reporting services and here I am acknowledging them by writing few lines here. Though, I would like to clarify one point here, I have worked with several other reporting engines and did enjoyed them, however, since I started to work with Reporting Services, I personally felt much at ease.

Let's wear our Report Writing hat now...

When I look at different reporting engines out there in market, the underlying concept remains very much same; I am talking about header, footer, data region, data grouping, summary etc. So, even if you have not yet exposed to reporting services, don't worry if you have working knowledge of any reporting engine, you will not have much difficulty to grasp the concept laid down in this article.

With this article I would also like to show the reader how reporting services can be used with smart client windows forms application in a client environment.

I assume the reader of this article is comfortable using Visual Studio 2005, C#, Sql Server 2000 and Windows Forms. Article is not at all a "Reporting Services 101", hence I will assume that you will try to play with attached code and figure out secrets hidden with it.

Implementing reporting services into smart client is like 1.2.3...

1. Create a DataSet
2. Create Report
3. Use Preview control to generate report with ADO.NET code interface

1. DataSet at your service

In order to create a dataset, just click Add New Item from Solution Explorer. Select DataSet from Visual Studio installed templates and give it a proper name. After Dataset is created, open it in a designer window and add a DataTable to it. After you have added DataTable add required columns to it, in this example I have three columns added, namely, EmployeeName, EmployeeID and Reports_to, please make sure to set the DataType property of each column to String, Int32 and Int32 respectively.

The DataSet should typically look like the above image. Now that we have our DataSet ready, you will shortly see a fun way to fill it using a new technique introduced in ADO.NET of using a SqlDataReader to Fill a Dataset (I guess my telepathy worked here tooJ.

2. Report Design

As we did with DataSet, just click Add New Item from Solution Explorer. Select Report from Visual Studio installed templates and give it a proper name. As I clarified earlier, I am not going into details for each and every control/elements of Report Designer; instead I will only point you to important location which needs attention in order to create Report which is using recursive data.

As you can see in the above image, this is how my reports look like in designer. Typical of report writing tool, reporting services also have interface where you can define header and footer to begin with and move on to report body etc. In the header section I have the Report Title (Magenta color) and Run Date (Blue color).

The most interesting part which I felt is the Body section, also called data region. Data region allows you to put several new exiting controls which basically decide how the data will be outputted. I have used "Table" control here which comes with ready header and footer for it when placed on designer surface for first time.

TextBox contol is used heavily to display information, if you look at the image you can see that I just placed a textbox control and simply typed the report title inside. When it comes to specify expression, all you have to do is start with "=" sign in front. You can check the Run Date example, in which I am concatenating string "Run Date" and VB.NET function "Today" to return current date.

After putting all the required control on the designer surface and making sure the layout meets out taste, it is time to spell the magic beans which will automatically handle the recursive nature of data and manage hierarchy level etc.

The trick is to put the grouping on detail section (make sure to select detail band and right click to access group menu choice), by specifying group on "EmployeeID" and parent group "ReportsTo" as per image mentioned below:

Report writer has useful inbuilt function like "Level", which returns the current level of depth in a recursive hierarchy.

For next output column in report Level, we will specify following expression:

=Level("tableEmployee_Details_Group") + 1

Level function return integer starting with 0 for fist level; hence I have added a 1 to end result here. So, in our example employee "Andrew Fuller", is topmost level, you can easily use function like Switch() or IIF() to take this level number and substitute with something like "CEO", "General Manager" etc.

The third and last column in report displays the count of all the employees who are reporting to given employee record. The following expression does the trick for us:

=Count(Fields!EmployeeID.Value, "tableEmployee_Details_Group", Recursive) - 1

For both the expression "tableEmployee_Details_Group" is used as reference name to group definition which we applied on detail band of data.

Did you also noticed on interesting thing about the Hierarchical formatting of EmployeeName in report output? This is also done fairly easily with following expression which you need to specify in Padding->Left property:

=Level("tableEmployee_Details_Group") * 20 & "pt"

Based on each incremental level it will add 20 pt to left side of EmployeeName and the output will look like a try structure.

3. Show me the Report!

I know after going through all that preparation, we are eager to see the output for report, aren't we? Following code will just do that!

You can start by putting ToolBox->Data->ReportViewer control on a standard windows form. I am using C# here within windows forms application framework, the same can be manipulated easily for a ASP.NET application framework and further, could can be easily converted to VB.NET if that is what you use as your primary scripting language.

Make sure you have the code behind Form Load method as follows:

private void Form1_Load(object sender, EventArgs e)

{

//declare connection string

string cnString = @"Data Source=(local);Initial Catalog=northwind;" + "User Id=northwind;Password=northwind";

//use following if you use standard security

//string cnString = @"Data Source=(local);Initial

//Catalog=northwind; Integrated Security=SSPI";

//declare Connection, command and other related objects

SqlConnection conReport = new SqlConnection(cnString);

SqlCommand cmdReport = new SqlCommand();

SqlDataReader drReport;

DataSet dsReport = new dsEmployee();

try

{

//open connection

conReport.Open();

//prepare connection object to get the data through reader and

// populate into dataset

cmdReport.CommandType = CommandType.Text;

cmdReport.Connection = conReport;

cmdReport.CommandText = "Select FirstName + ' ' + Lastname AS EmployeeName, EmployeeID, ReportsTo From Employees";

//read data from command object

drReport = cmdReport.ExecuteReader();

//new cool thing with ADO.NET... load data directly from reader

// to dataset

dsReport.Tables[0].Load(drReport);

//close reader and connection

drReport.Close();

conReport.Close();

//provide local report information to viewer

reportViewer.LocalReport.ReportEmbeddedResource =

"RecursiveData.rptRecursiveData.rdlc";

//prepare report data source

ReportDataSource rds = new ReportDataSource();

rds.Name = "dsEmployee_dtEmployee";

rds.Value = dsReport.Tables[0];

reportViewer.LocalReport.DataSources.Add(rds);

//load report viewer

reportViewer.RefreshReport();

}

catch (Exception ex)

{

//display generic error message back to user

MessageBox.Show(ex.Message);

}

finally

{

//check if connection is still open then attempt to close it

if (conReport.State == ConnectionState.Open)

{

conReport.Close();

}

}

}

How to use Multiple Page Headers in Crystal Reports?

Recently, I came across a problem in Crystal Reports. I wanted to display a separate page header on the first page of the report than the rest of the page. On first page of the report, I wanted to display a big company logo with some instructions on it and on rest of the pages; I wanted to display a small company logo.

You can achieve this by adding two page headers sections to the report and adding a formula for suppressing the section. I created two page header sections and formatted them the way I wanted on my first page and rest of the pages. The idea is you want to suppress the second header on the first page and suppress first header on the rest of the pages.

To do so, you create a formula (see X-2) for Suppress option. The formula is: PageNumber<>1. For rest of the pages, you create formula PageNumber = 1.

That's all.

How to format a column value of a Report programmatically?

Ok in one of my reports, I had to format the column depending on the value of the column. For example, I had to make column bold if the value of the column is greather than 0.

You can do this by adding a formula. Follow these steps:

Right click on field (column) you want to make bold and select Format Field and go to Font page in the Format Editor. Click on Formula sign next to the Style drop down and write the following formula:

If columnName > 0 Then
crBold
Else
crRegular

Using similar process, you can change the font, color, size and other properties of the column text. For example, if you want to make the column color to red, you create a formula for the Color field and return Red if value is greater than 0, else return black.

Generate ASP.NET Crystal Report using DataSet

This article will give a clear idea of how to create Crystal Report in ASP.NET/C#. We can create a crystal report.net using the following steps:

1. Create a Dataset and define the schema by drag and drop the database table from Server Explorer.
   If there are multiple tables then put all the tables within one dataset itself.
   
   STEPS:
   Right Click Solution Explorer -> Add -> Add New Item -> choose DataSet under the Categories (Web Project Items - data).
   
   Add new Connection in the Server Explorer and expand the connection to retrieve the database tables and choose the required table and drag and drop it in the Dataset xsd pane.
   
   
2. Generate Dataset from the Dataset XSD.
   
   STEPS:
   Right click on the dataset xsd pane and click Generate Dataset
   
   
3. Create Crystal Report.
   
   STEPS:
   Right Click Solution Explorer -> Add -> Add New Item -> choose Crystal Report under the Categories (Web Project Items).
   
   
4. Configure the Crystal Report.
   
   STEPS:
   
   1. Select Report Layout, ProjectData, ADO.NET DataSets
   2. Expand ADO.NET DataSets and select the table
   3. Select the fields
      
      
5. Create a WebForm and drag and drop the CrystalReportViewer control from the Toolbox(General).
   
   
6. Put a textbox and button
   
   
7. Open the Code window of the WebForm and write the following code.
   
   And change the value of the connectionstring variable sqlConn.

Webform1.aspx.cs

using System;

using System.Collections;

using System.ComponentModel;

using System.Data;

using System.Drawing;

using System.Web;

using System.Web.SessionState;

using System.Web.UI;

using System.Web.UI.WebControls;

using System.Web.UI.HtmlControls;

using System.Data.SqlClient;

namespace CrystalReportEg

{

///

/// Summary description for WebForm1.

///

public class WebForm1 : System.Web.UI.Page

{

protected CrystalDecisions.Web.CrystalReportViewer CrystalReportViewer1;

protected System.Web.UI.WebControls.Label Label1;

protected System.Web.UI.WebControls.TextBox TextBox1;

protected System.Web.UI.WebControls.Button Button1;

public Customer oRpt = null;

private void Page_Load(object sender, System.EventArgs e)

{

// Put user code to initialize the page here

}

#region Web Form Designer generated code

override protected void OnInit(EventArgs e)

{

//

// CODEGEN: This call is required by the ASP.NET Web Form Designer.

//

InitializeComponent();

base.OnInit(e);

oRpt = new Customer();

GenerateReport();

}

///

/// Required method for Designer support - do not modify

/// the contents of this method with the code editor.

///

private void InitializeComponent()

{

this.Button1.Click += new System.EventHandler(this.Button1_Click);

this.Load += new System.EventHandler(this.Page_Load);

}

#endregion

private void Button1_Click(object sender, System.EventArgs e)

{

GenerateReport();

}

protected void GenerateReport()

{

SqlConnection sqlConn = new SqlConnection("Server=localhost;uid=sa;password=;initial

catalog=Northwind;");

SqlCommand comd;

comd = new SqlCommand();

comd.Connection = sqlConn;

comd.CommandType = CommandType.StoredProcedure;

comd.CommandText = "up_GetAllCustomer";

comd.Parameters.Add("@Companyname",SqlDbType.VarChar,50);

if(TextBox1.Text.Trim()!="")

comd.Parameters[0].Value=TextBox1.Text;

else

comd.Parameters[0].Value=DBNull.Value;

SqlDataAdapter sqlAdapter = new SqlDataAdapter();

sqlAdapter.SelectCommand = comd;

Dataset1 ds = new Dataset1();

sqlAdapter.Fill(ds, "Customers");

oRpt.SetDataSource (ds);

CrystalReportViewer1.Visible=true;

CrystalReportViewer1.ReportSource = oRpt;

}

}

}

Hope this will give you a clear picture of the web crystal report generation.

How to run Dynamic Crystal Report using Crystal Report Viewer in VS.NET 2005

Some time we have multiple reports for the application and we need to load the report based on the user's selection. The following piece of code will explain how to achieve this.

using System;

using System.Drawing;

using System.Collections;

using System.ComponentModel;

using System.Windows.Forms;

using System.Data;

using System.IO;

namespace DyanamicReport

{

///

/// Summary description for Form1.

///

public class Form1 : System.Windows.Forms.Form

{

private System.Windows.Forms.ComboBox CboReport;

private System.Windows.Forms.Panel panel1;

private CrystalDecisions.Windows.Forms.CrystalReportViewer cptReports;

///

/// Required designer variable.

///

private System.ComponentModel.Container components = null;

public Form1()

{

//

// Required for Windows Form Designer support

//

InitializeComponent();

//

// TODO: Add any constructor code after InitializeComponent call

//

}

///

/// Clean up any resources being used.

///

protected override void Dispose( bool disposing )

{

if( disposing )

{

if (components != null)

{

components.Dispose();

}

}

base.Dispose( disposing );

}

#region Windows Form Designer generated code

///

/// Required method for Designer support - do not modify

/// the contents of this method with the code editor.

///

private void InitializeComponent()

{

this.CboReport = new System.Windows.Forms.ComboBox();

this.cptReports = new CrystalDecisions.Windows.Forms.CrystalReportViewer();

this.panel1 = new System.Windows.Forms.Panel();

this.panel1.SuspendLayout();

this.SuspendLayout();

//

// CboReport

//

this.CboReport.Location = new System.Drawing.Point(0, 0);

this.CboReport.Name = "CboReport";

this.CboReport.Size = new System.Drawing.Size(496, 21);

this.CboReport.TabIndex = 0;

this.CboReport.SelectedIndexChanged += new System.EventHandler
(this.CboReport_SelectedIndexChanged_1);

//

// cptReports

//

this.cptReports.ActiveViewIndex = -1;

this.cptReports.BorderStyle = System.Windows.Forms.BorderStyle.FixedSingle;

this.cptReports.Location = new System.Drawing.Point(4, 3);

this.cptReports.Name = "cptReports";

this.cptReports.SelectionFormula = "";

this.cptReports.Size = new System.Drawing.Size(717, 367);

this.cptReports.TabIndex = 1;

this.cptReports.ViewTimeSelectionFormula = "";

//

// panel1

//

this.panel1.Controls.Add(this.cptReports);

this.panel1.Location = new System.Drawing.Point(8, 40);

this.panel1.Name = "panel1";

this.panel1.Size = new System.Drawing.Size(724, 383);

this.panel1.TabIndex = 2;

//

// Form1

//

this.AutoScaleBaseSize = new System.Drawing.Size(5, 13);

this.ClientSize = new System.Drawing.Size(744, 422);

this.Controls.Add(this.panel1);

this.Controls.Add(this.CboReport);

this.Name = "Form1";

this.StartPosition = System.Windows.Forms.FormStartPosition.CenterScreen;

this.Text = "Special Reports";

this.WindowState = System.Windows.Forms.FormWindowState.Maximized;

this.Load += new System.EventHandler(this.Form1_Load);

this.panel1.ResumeLayout(false);

this.ResumeLayout(false);

}

#endregion

///

/// The main entry point for the application.

///

[STAThread]

static void Main()

{

Application.Run(new Form1());

}

private void Form1_Load(object sender, System.EventArgs e)

{

CboReport_Fill();

}

private void CboReport_Fill()

{

//Create a temp table to hold the reports description and path

DataTable dt = new DataTable ();

dt.Columns.Add ("Description", System.Type.GetType("System.String"));

dt.Columns.Add ("Path",System.Type.GetType("System.String"));

//Create the first row of the temp table to tell users what to do

string[] FirstRow = {" -- select a report to view --", "-1"};

dt.Rows.Add (FirstRow);

//Pickup all the crystal reports in the Reports subdirectory of the program

string[] fileList = Directory.GetFiles (Directory.GetCurrentDirectory() + @"\Reports",
"*.rpt");

foreach (string item in fileList)

{

//add all the friendly report names into the temp table

int startPt = item.ToString().LastIndexOf(@"\Reports");

string[] rowData = {item.Substring(startPt + @"\Reports".Length + 1, item.ToString

().Length - (startPt + @"\Reports".Length + 1)),item};

dt.Rows.Add (rowData);

}

//assign the temp table to the combo box

CboReport.DataSource = dt;

CboReport.DisplayMember = "Description";

CboReport.ValueMember = "Path";

}

private void CboReport_SelectedIndexChanged_1(object sender, System.EventArgs e)

{

//identify if the first combo box item is not selected

switch (CboReport.SelectedIndex > 0)

{

//if another line in the combo box is selected then view the report

case true:

cptReports.ReportSource = CboReport.SelectedValue ;

cptReports.Zoom (25);

break;

//if the first line in the combo box is selected then clear the report

case false:

cptReports.ReportSource = null;

break;

}

cptReports.Zoom(100);

}

}

}

OutPut:-

How to Upload or Change an image in the Crystal Report at runtime

I decided to write this article after realizing how many people have trouble with letting the user upload an image to their crystal report. Say for an example if the user wants to change or upload their logo onto the crystal report. How can you do this? How the developers can provide this facility to the users?

We all know that users cannot customize crystal reports much. Once the crystal report is generated, users don't have much of a choice to change the fields as they wish. This article is going to demonstrate how can the user upload or change an image on the crystal report.

This is actually a very simple example. Even if you have a novice knowledge about Crystal reports you will be able to understand how this works. I am using Crystal reports 11 for this demonstration.

To start off from the beginning, create a new windows application and add a crystal report viewer and a browse button.

This browse button will allow the user to add an image to the report. Add a crystal report into your project.

Report Schema:

We all know that we need to provide a schema for the crystal repot. Here is an XML view of the report schema that we are going to supply for the report.

xml version="1.0" standalone="yes"?>
<xs:schema id="NewDataSet" xmlns="" xmlns:xs="http://www.w3.org/2001/XMLSchema" xmlns:msdata="urn:schemas-
microsoft-com:xml-msdata">
<xs:element name="NewDataSet" msdata:IsDataSet="true" msdata:Locale="en-AU">
<xs:complexType>
<xs:choice maxOccurs="unbounded">
<xs:element name="Images">
<xs:complexType>
<xs:sequence>
<xs:element name="path" type="xs:string" minOccurs="0" />
<xs:element name="image" type="xs:base64Binary" minOccurs="0" />
xs:sequence>
xs:complexType>
xs:element>
xs:choice>
xs:complexType>
xs:element>
xs:schema>

BLOB field:

In order to add an image to the crystal report (considering the image field is coming from the database) we need to have a BLOB field in the schema. When you want to store images, documents or different custom types in the database you use a BLOB field. BLOB stands for Binary Large Object.

<xs:element name="path" type="xs:string" minOccurs="0" />
<xs:element name="image" type="xs:base64Binary" minOccurs="0" />

If you inspect this schema we can see that we have a table called "Images" and two columns "path" and "image". "image" column is type Base64 Binary. This is our BLOB field.What we are going to do in our program is when the user selects the image to upload we store that image in a BLOB field as a stream of bytes and then supply that dataset to the report.

CreateTable() method will illustrate how to generate this schema in the program.

Generating the schema for the report:

There are other ways you can create the schema but this will give you a clearer idea about the column fields.

Creating the table:

private void CreateTable()

{

//create a new data set.

this.DsImages = new DataSet();

//create a new table with two columns and add the table to the
dataset

DataTable ImageTable = new DataTable("Images");

//in here the "path" column is not really needed. Image column is
just enough.

ImageTable.Columns.Add(new DataColumn("path",typeof(string)));

//Important note

//Note the type of the image column. You want to give this column
as a blob field to the crystal report.

//therefore define the column type as System.Byte[]

ImageTable.Columns.Add(new DataColumn("image",typeof(System.Byte[])));

this.DsImages.Tables.Add(ImageTable);

}

If you notice the "image" column you can see that the type of that column is System.Byte[]. This is pretty straight forward. Just create a table with two columns. Then add it to the dataset. Now you can create the schema using this line:

this.DsImages.WriteXmlSchema(@"c:\temp\ImagesSchema.xsd");

Once we have the schema ready we can provide it to the crystal report.

Image 1:

Inspect the Image 1, in the field explorer we can see Images table and the two columns path and image. When you drag the image column onto the report you can see the type of that field is IBlobFieldObject. These fields will read a stream of bytes and convert it back to an image. So our task is pretty much done. The code below shows you how it can save the image as a stream of bytes in the BLOB field.

private void openFileDialog1_FileOk(object sender, System.ComponentModel.CancelEventArgs e)

{

try

{

//get the image file into a stream reader.

FileStream FilStr = new FileStream(this.openFileDialog1.FileName, FileMode.Open);

BinaryReader BinRed = new BinaryReader(FilStr);

//Adding the values to the columns

// adding the image path to the path column

DataRow dr = this.DsImages.Tables["images"].NewRow();

dr["path"] = this.openFileDialog1.FileName;

//Important:

// Here you use ReadBytes method to add a byte array of the image stream.

//so the image column will hold a byte array.

dr["image"] = BinRed.ReadBytes((int)BinRed.BaseStream.Length);

this.DsImages.Tables["images"].Rows.Add(dr);

FilStr.Close();

BinRed.Close();

//create the report object

DynamicImageExample DyImg = new DynamicImageExample();

// feed the dataset to the report.

DyImg.SetDataSource(this.DsImages);

this.crystalReportViewer1.ReportSource = DyImg;

}

catch(Exception er)

{

MessageBox.Show(er.Message,"Error");

}

}

You write this in the FileOk method of the openFileDialog. You use the BinaryReader.ReadBytes method to read the byte array.

dr["image"] = BinRed.ReadBytes((int)BinRed.BaseStream.Length);

If you inspect this line of code you can see that we have assigned a byte array in the "image" column of the table. This byte array is the image that the user selected. So when you supply this dataset to the crystal report the IBlobFieldObject in the report will convert this byte array back to the image.

That is all you need to understand in this program. Below I have listed all the important steps you need to understand.

• User selects an image.
• We save the image in a blob field as a stream of bytes.
• Supply the dataset to the report which contains the BLOB field.
• Report will convert the stream of bytes back to the image and show it.

Conclusion:

Using this example in we can give the user to customize the images they want to upload to the report. This would be a good implementation in situations like if the users want to change the images on the report or upload their logos by them selves. Users don't have to contact their software development team every time they want to change an image on the report. Customizing the crystal report at the user end is not possible when it comes to moving the fields across the report or adding new fields. This article only helps you to understand how you can upload an image at run time. It does not tell how you can customize a crystal report at runtime. I hope you understood the concept of this article.