Filter data to display in a crystal report viewer programmatically

Some times we need to view, print or export filtered data

In this article, I will show how one can do this with the simplest manner. In the current sample we assume that we will use a crystal report model located in the C:\ directory with "C:\myCrReport.rpt" as File Name. This last one consumes data from an xml file called "C:\Resources.xml". And here is the given xml file content, just copy and paste it in a bloc note and save the contents as Ressources.xml in the "C:\" directory, of Course, in case when the code sample mentioned after would be tested

xml version="1.0" standalone="yes"?>
<Table>
<Stock>
<CodeStockPK>STO0000CodeStockPK>
<Operation>AchatOperation>
<Date>2007-11-26T22:07:13.5+01:00Date>
<CodeLivreurFK>LIV0000CodeLivreurFK>
<CodeReglementFK>REG0000CodeReglementFK>
<Secteur>BizerteSecteur>
Stock>
<Stock>
<CodeStockPK>STO0001CodeStockPK>
<Operation>AchatOperation>
<Date>2007-11-12T22:08:02+01:00Date>
<CodeLivreurFK>LIV0000CodeLivreurFK>
<CodeReglementFK>REG0001CodeReglementFK>
<Secteur>BizerteSecteur>
Stock>
<Stock>
<CodeStockPK>STO0002CodeStockPK>
<Operation>AchatOperation>
<Date>2007-11-26T22:08:30.5+01:00Date>
<CodeLivreurFK>LIV0001CodeLivreurFK>
<CodeReglementFK>REG0000CodeReglementFK>
<Secteur>SahelSecteur>
Stock>
<Stock>
<CodeStockPK>STO0003CodeStockPK>
<Operation>AchatOperation>
<Date>2007-11-29T17:48:42.921875+01:00Date>
<CodeLivreurFK>LIV0000CodeLivreurFK>
<CodeReglementFK>REG0000CodeReglementFK>
<Secteur>BizerteSecteur>
Stock>
<Stock>
<CodeStockPK>STO0004CodeStockPK>
<Operation>AchatOperation>
<Date>2007-11-29T17:49:16.265625+01:00Date>
<CodeLivreurFK>LIV0000CodeLivreurFK>
<CodeReglementFK>REG0000CodeReglementFK>
<Secteur>SfaxSecteur>
Stock>
<Stock>
<CodeStockPK>STO0005CodeStockPK>
<Operation>VenteOperation>
<Date>2007-11-29T17:49:34.046875+01:00Date>
<CodeLivreurFK>LIV0000CodeLivreurFK>
<CodeReglementFK>REG0000CodeReglementFK>
<Secteur>BizerteSecteur>
Stock>
<Stock>
<CodeStockPK>STO0006CodeStockPK>
<Operation>VenteOperation>
<Date>2007-11-29T17:49:42.3125+01:00Date>
<CodeLivreurFK>LIV0000CodeLivreurFK>
<CodeReglementFK>REG0000CodeReglementFK>
<Secteur>SfaxSecteur>
Stock>
<Stock>
<CodeStockPK>STO0007CodeStockPK>
<Operation>VenteOperation>
<Date>2007-11-29T17:49:52.09375+01:00Date>
<CodeLivreurFK>LIV0000CodeLivreurFK>
<CodeReglementFK>REG0000CodeReglementFK>
<Secteur>SfaxSecteur>
Stock>
Table>

To create the Crystal report model "myCrReport.rpt" and configure it so that it consumes data from the "C:\Ressource.xml" xml file, use the report wizard provided by the crystal report engine within the visual studio IDE, it makes the process easier and faster. After build it, browse to the Application directory, then copy and paste the given crystal report in the "C:\" directory.

Add a crystal report viewer into the project. It will be automatically named as "CrystalReportViewer1".



Figure 1

Here is a piece of code that gets filtered data within the Crystal viewer at the run time, in this case we have only the first four records to display, it is possible to copy and paste it in the load form event handler like the figure bellow:

private void Form1_Load(object sender, EventArgs e) { //Instantiate a new report document ReportDocument oDoc = new ReportDocument(); //load myCrReport oDoc.Load(@"C:\myCrReport.rpt"); /* Create a data set and populate it with * C:\Ressource.xml contents*/ DataSet myDataSet = new DataSet(); myDataSet.ReadXml(@"C:\Ressource.xml"); // Set the data source of the report document oDoc.SetDataSource(myDataSet.Tables["Table"]); //Create a query to fit the user filtering requirement string myQuery = "{Stock.CodeStockPK} < 'STO0004'"; crystalReportViewer1.SelectionFormula = myQuery; //Set the viewer report source crystalReportViewer1.ReportSource = oDoc; }

For someone that he is not an expert or do not have any idea about how to build formula like this "{Stock.CodeStockPK} < 'STO0004'" using the crystal syntax. I propose two alternatives. The first one is that he simply learns crystal syntax, and the second one is a little bit dirty, but it can be used until mastering crystal syntax.

Suppose that we want to build the previous formula without having any background in terms of crystal syntax, here are steps to perform this action.

• Open the "myCrReport.rpt" in the current project.
• Select the Crystal Reports menu.
• Select Report than Report Expert.

Figure2

• A dialog box as bellow is opened, select the "CodeStockPK" and then click OK.

Figure3

• A second dialog box is opened, select "less than" from the criteria combo box.

Figure4

• Then select the value that the "CodeStockPK" values could not exceed it, as the figure shows bellow.

Figure 5

• 
  
Click the button labeled "Show Formula"

And the formula will be displayed. The given formula can be simply copied and pasted into the code and that's it.

Now, run the application and you can view, print or export only the filtered data. In deed, it is possible to enable user filter data at the run time; by adding a combo box or a text box and passing it values to the formula just like this



Figure 6

"{Stock.CodeStockPK} < " + textBox.text or "{Stock.CodeStockPK} < " + ComboBox.text

In this case a possibility is given to user in order to enter data that dynamically changes the report viewer state to suite the requirements.

Reporting Services

Introduction

This article is intended to illustrate and discuss the development of reports using Crystal Reports (CR) and Reporting Services (RS). Although CR and RS are two different technologies, the produce the same results: a report. I will cover a side-by-side comparison and development of CR and RS reports.

It's remarkable to say that while a Crystal Report is the most used general-purpose reporting tool, Reporting Services is much easier to use because you can have a chart as well as a table in the same report section.

Developing reports in Crystal Reports and Reporting Services

We're going to develop a product profitability report which displays the information about products (cost, price and profit) stored in the table Production.Product in the AdventureWorks database. The underlying SQL query is show in Listing 1.

select ProductID, [Name], [ProductNumber], StandardCost, ListPrice, ListPrice-StandardCost as Profit
from Production.Product;

Listing 1

Please, open Visual Studio.NET and create a Windows application project named. Because I follow the Model-View-Controller (MVC) paradigm in my solutions architecture, we're going to add a class library where the data-access layer components will reside. Go to the File | New | Project from the main menu and then add new Class Library project to our solution (see Figure 1).

Figure 1

Let's move on and add a strongly typed dataset to this Class Library project representing the reporting query resultsets (see Figure 2).

Figure 2

Now let's add a table adapter and the underlying data table according to the query in Listing 1 using the TableAdapter Configuration Wizard (see Figure 3).

Figure 3

Now that we have developed the data-access layer component, then we move to the presentation layer. We're going to add two Windows Forms: one to display the CR report and the other to display the RS report. Then add the ReportViewer control to the RS Form by dragging Data|Report Viewer from the the toolbox and dropping it onto the design surface and finally dock this control to the form (see Figure 4). Similarly, add a CrystalReportViewer control to the CR Form by dragging Crystal Reports|CrystalReportViewer from the toolbox and dropping it onto the design surface and finally dock this control to the form (see Figure 5).

Figure 4

Figure 5

Now it's time to design the report layout. The report will show the name of report and company in the report header section and will display the list of products item (in the detail section).

Let's start by adding the RS report by right-clickling on Windows project in the Solution Explorer and selecting Add | New Item, and finally select Report from the Add New Item dialog box (see Figure 6).

Figure 6

In RS, you don't see the header and footer by default. Thus select Report|Page Header from the main menu in order to make visible the Page Header of the RS report.Add two Textbox control to the page header and a Table control to the header section from the Toolbox. Configure the Table to have six columns, each column of the table for each column of data. Then to add data field to the Table, we need to reference the definition of ProductProfitability dataset class. Go to the Data Sources windows and add a new object data source which references ProductProfitability dataset class (see Figure 7).

Figure 7

Now we can drag-and-drop data field from the definition of ProductProfitability dataset class in the Data Sources window into the report (see Figure 8).

Figure 8

Now add the fields to the Table Details and format the Table Header as bold and with a bottom line (see Figure 9).

Figure 9

Now let's add the CR report. In the Solution Explorer, select the Windows project and right-click on it and select Add|New Item, and select from Add New Item dialog box (see Figure 10).

Figure 10

When you add the CR report, then Crystal Reports Gallery dialog box will appear. Then select As a Blank Report option. You can see a new Toolbox named Field Explorer which displays the information available to design the CR report.

When you look around the two reports, you discover that the RS reports are free form in nature, so you can add the sections you need. In CR, you have by default headers and footers, even if you don't need them. You must notice that the CR has two sets of headers and footers: one for the page and other for the report as whole. We don't need to add a header or footer to the report, then drag-and-drop two Textbox items from the Toolbox onto the design surface of the header report.

Let's work on the detail (body) section of CR report now. In CR, we need to reference the data sources specifically the type data set. This step is very easy, just right-click inside any section, and select Database | Database Expert. Then the Database Expert dialog box will appear, and you must expand the node Create a New Connection | ADO.NET and a new ADO.NET dialog box will appear. You must browse to the place where the definition of you strongly dataset resides (inside the Class Library project, please see Figure 11).

Figure 11

Then click on the Finish button on the ADO.NET dialog box (see Figure 12).

Figure 12

Then on the Database Expert dialog box, you need to select the newly referenced dataset (see Figure 13).

Figure 13

Now mapping the newly referenced fields onto the detail section is very easy too. Drag and drop the fields from the Field Explorer window onto the details section of the report. You'll notice that as soon as you drop the field, the respective column header automatically appears in the page header section (see Figure 14).

Figure 14

Now let's configure the Report Viewers to bind the underlying data source to report definitions.

Let's begin with the RS reports. In this case, it's very simple. Open the RS Form and click on the smart tag of the ReportViewer control in order to select a RS report and bind the report data source with your application data source. When you select the RS report definition, then VS.NET also adds a binding source object to the Windows Form design surface. Now add an instance of ProductProfitabilityDS strongly typed dataset and an instance of ProductProfitabilityTableAdapter table adapter from the Toolbox onto the design surface (see Figure 15).

Figure 15

Finally reference the former dataset from the binding source object (see Figure 16).

Figure 16

The last step is to fill the instance of ProductProfitabilityDS strongly type dataset with data.

Now let's configure the CrystalReportViewer control. Open the CR Form, and click on the smart tag of the CrystalReportViewer control and select Choose a Crystal Report option (see Figure 17).

Figure 17

After this, a new ReportDocument object is added to the design surface. Now add an instance of ProductProfitabilityDS strongly typed dataset and an instance of ProductProfitabilityTableAdapter table adapter from the Toolbox onto the design surface (see Figure 18).

Figure 18

The last step is to fill the instance of ProductProfitabilityDS strongly type dataset with data and bind this data source to the ReportDocument instance (see Listing 2).

this.productProfitabilityTableAdapter1.Fill(this.productProfitabilityDS1.ProductProfitability);
this.ProductProfitability1.SetDataSource(this.productProfitabilityDS1.ProductProfitability as DataTable);

Listing 2

Conclusion

In this article, I discussed the two most important reporting technologies in the .NET world. I developed a simple report and explained the main step to achieve this goal using Crystal Reports and Reporting Services.

Parameter Fields in Crystal Reports

Introduction

This article is intended to illustrate how to use Parameter Fields in Crystal Reports. Parameter fields enable the creation of reports that could be used in a variety of ways, prompting the user for all kinds of information specifically for record selection and report titles. These parameters can be used in several ways such as to display some text on the report as well as in formulas.

Creating a Parameter Field

In order to illustrate the creation and use of Parameter Fields in Crystal Reports, let's create a Windows application. The go to Project | Add New Item and add a Crystal Report item to your application (see Figure 1).

Figure 1

Now drag and drop a CrystalReportViewer control onto the form design surface and leave room at the top for some additional parameter fields that we're going to add later. Bind the report item to this CrystalReportViewer control by selecting Choose a Crystal Report operation on the CrystalReportViewer control's property windows.

Now let's add parameters to our report. But first of all, let's talk about parameter fields. There are three types of parameters:

• Discrete. Accept single and discrete values.
• Ranged. Accept a lower and upper value in order to select everything in this range.
• Discrete and Ranged. Support the combination of the former two types.

By default, the parameters are of the following data types:

• String. For entering alphanumeric text.
• Currency. For entering a number with two decimal places.
• Date. For entering a date in the format Month/Day/Year.
• DateTime. For entering a date as well as time in the format Month/Day/Year/ Hour/Minute/Second/AM or PM
• Number. For entering a number with variable decimal places.
• Boolean. For prompting the users for true or false response.

When you create a parameter, you can also define a prompting text that appears whenever the Parameter Field dialog appears. Once you have created a parameter field, then the standard notation for this field is the field name enclosed in curly brackets preceded by the question mark, that is, {?ParameterField}.
In order to create a parameter to used in the report, right-click on the Parameter Fields node in the Field Explorer windows and select New. The Create Parameter Field window will appear. You can type a parameter name, prompting text and choose data type (see Figure 2).

Figure 2

Let's add a range parameter field to introduce a department identifier range (see Figure 3). Now you can drag and drop these two parameter fields onto the report design surface and run the application.

Figure 3

When the application is running, then you're prompted for entering the parameter fields. For the Caption parameter field, we have (see Figure 4).

Figure 4

For the Department Identifier range parameter field, we have the following figure (see Figure 5).

Figure 5

Now we're going to start by looking the implementation of setting value to parameter fields. When you bind the report item with the CrystalReportViewer control, a new object is added to the form design surface. This object represents an instance of the report. Set this object name to m_rptParameterFields. Parameters within a report are contained within the collection named ParameterFields. You can add programmatically new, configure and delete parameters.

In order to set a value to the ReportCaption parameter field, you must use the following code (see Listing 1).

ParameterValues pvValues = new ParameterValues();

ParameterDiscreteValue pdvDiscreteValue = new ParameterDiscreteValue();

pdvDiscreteValue.Value = "This is my first report caption.";

pvValues.Add(pdvDiscreteValue);

ParameterFieldDefinitions pfdDefinitions = this.m_rptParameterFields.DataDefinition.ParameterFields;

ParameterFieldDefinition prdDefinition = pfdDefinitions["ReportCaption"];

prdDefinition.ApplyCurrentValues(pvValues);
this.m_crvCrystalReportViewer.Refresh();

Listing 1

Conclusion

In this article, I covered how to work with ParameterFields in CrystalReports technology. You can apply this technique to your own business case.

Crystal Reports.NET

Introduction

This article is intended to illustrate how to build reports using Crystal Report.NET. Reports are important components of enterprise applications and are mainly used to provide a descriptive and summary view of your data in order to discover information in huge volume of data and make correct decisions. This is the principles of Business Intelligence in enterprise solutions.

First of all, let's analyze a methodology to develop reports. End-user interviews are the key of targeting the report's content. The end-user tells you how the report should be as a final product and what decisions are made based on the report's information. Then, you have a pretty good idea of what reports are required and how they will be used. You can document these requirements using tools such as user requirement statements and use cases.

Then you have to determine the underlying data sources. Most of the time, the data sources are will be relational database systems, but some times the data could reside on spreadsheets, flat files and OLAP data structures. Once you have found the data sources, you must determine the tables and views to be consumed. Sometimes, you have to develop additional views and stored procedures to consolidate the data prior to develop the report.

Next step is to design the report and set the required format for fields as well as the totals such sub-totals and grand-totals. It should closely match the report's final layout. You must check with the end-users and business analysts.

Finally, the report design must be bind with the underlying data source in order to generate the final report output. This step is realized by the report engines such as Crystal Reports and Reporting Services.

Getting started with Crystal Reports.

Let's create a Windows Forms application to display summary information about product profitability. The data source is the AdventureWorks database shipped with SQL Server 2005 (see Figure 1).

Figure 1

First step is to add a strongly typed data set to store the result set from the following query (see Listing 1).

select ProductID, [Name], ProductNumber, StandardCost as Cost, ListPrice as Price, ListPrice-StandardCost as Profit
from Production.Product;

Listing 1

Let's add a Crystal Report to our application. Select Project | Add New Item and choose Crystal Report item from the list of available templates. Enter ProductProfitability as the report's name. The Crystal Reports Gallery will appear (see Figure 2).

Figure 2

There are three options. Using the Report Wizard option enables to walk step by step through the creation of the project. As a Blank Report option is for experienced developers who are familiar with the Report Designer. And From an Existing Report option enables to select a report's template as the basis. We're going to select the As a Blank Report option.

Now let's add the data source. Right-click on the report and select Database | Database Expert. The Database Expert windows will appear (see Figure 3). There are different types of data sources.

Figure 3

Then the created strongly typed data set dtProductProfitability from Available Data Sources pane into Selected Tables pane (see Figure 4).

Figure 4

Let's go to the design of the report. By default, Crystal Reports .NET will have a number of properties preset such as font, formatting for fields, page size, margin and layout.

A Crystal Report comprises a number of sections. Each section has a set of properties and behaviors. If you want to see the Section Properties, right-click on the report and select Section Expert (see Figure 5).

Figure 5

We can find several sections. Report Header appears at the top of every first page of the report and usually suppressed by default. The Report Footer appears at the bottom of every last page of the report. Page Header appears at the top of each page and it's mainly used as for column headings, the report title, page count, etc. Page Footer appears at the bottom of each page and it's mainly used to display page numbers, print dates, times, grand totals and so on. Group Header appears at the head of each group. Group Footer appears at the end of each group and it's mainly used to display the subtotal and summaries associated to the group. Details Section, there's one for each record of the report and they're mainly used to display record information.

Report formatting is the most time consuming task of report design. You can change the format of each object in the report in order to control the color, font as well as data type formatting such as number, currency and date.

Let's add a Name Report and page counter to the report header. Right-click on the report and select Insert | Text Object and Insert | Special Object | Page N of M.

Now we're going to add the fields to the report. To do so, open the Field Explorer and drag and drop the fields onto the Details Section in the report design surface.

Let's do some fields formatting. Right-click on the ProductID field on the Details Section and select Format Object and then select the number format (see Figure 6).

Figure 6

Do the same step for the Cost, Price and Profit fields, but this time we format the number with decimal fractions and thousand separators (see Figure 7).

Figure 7

Finally, we're going to add grand totals to our report. Right-click on the report and select Insert | Summary, then the Insert Summary windows will appear. Then select the field to be summarized, in our case we select Product.Profit, the type of calculation and the location, in our case the scope of the summary is the report, but you can limit the scope to groups.

Now the report is created. In order to show the report, we need to add a CrystalReportViewer from the Toolbox on the main form design surface. Now tie the previously created report to the report viewer (see Figure 8).

Figure 8

Now, finally add the code to fill the dataset and bind this dataset to the Crystal Report document (see Listing 2).

this.m_taProduct.Fill(this.m_dsProductProfitability.Product);
this.m_rpProductProfitability.SetDataSource(this.m_dsProductProfitability.Product as DataTable);

Listing 2