Filter Data to Display in a Crystal Report Viewer Programmatically

This article describes how to filter data to display, export or print programmatically using the selection formula property of the crystal reports viewer control.

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>STO0000</CodeStockPK>
<Operation>Achat</Operation>
<Date>2007-11-26T22:07:13.5+01:00</Date>
<CodeLivreurFK>LIV0000</CodeLivreurFK>
<CodeReglementFK>REG0000</CodeReglementFK>
<Secteur>Bizerte</Secteur>
</Stock>
<Stock>
<CodeStockPK>STO0001</CodeStockPK>
<Operation>Achat</Operation>
<Date>2007-11-12T22:08:02+01:00</Date>
<CodeLivreurFK>LIV0000</CodeLivreurFK>
<CodeReglementFK>REG0001</CodeReglementFK>
<Secteur>Bizerte</Secteur>
</Stock>
<Stock>
<CodeStockPK>STO0002</CodeStockPK>
<Operation>Achat</Operation>
<Date>2007-11-26T22:08:30.5+01:00</Date>
<CodeLivreurFK>LIV0001</CodeLivreurFK>
<CodeReglementFK>REG0000</CodeReglementFK>
<Secteur>Sahel</Secteur>
</Stock>
<Stock>
<CodeStockPK>STO0003</CodeStockPK>
<Operation>Achat</Operation>
<Date>2007-11-29T17:48:42.921875+01:00</Date>
<CodeLivreurFK>LIV0000</CodeLivreurFK>
<CodeReglementFK>REG0000</CodeReglementFK>
<Secteur>Bizerte</Secteur>
</Stock>
<Stock>
<CodeStockPK>STO0004</CodeStockPK>
<Operation>Achat</Operation>
<Date>2007-11-29T17:49:16.265625+01:00</Date>
<CodeLivreurFK>LIV0000</CodeLivreurFK>
<CodeReglementFK>REG0000</CodeReglementFK>
<Secteur>Sfax</Secteur>
</Stock>
<Stock>
<CodeStockPK>STO0005</CodeStockPK>
<Operation>Vente</Operation>
<Date>2007-11-29T17:49:34.046875+01:00</Date>
<CodeLivreurFK>LIV0000</CodeLivreurFK>
<CodeReglementFK>REG0000</CodeReglementFK>
<Secteur>Bizerte</Secteur>
</Stock>
<Stock>
<CodeStockPK>STO0006</CodeStockPK>
<Operation>Vente</Operation>
<Date>2007-11-29T17:49:42.3125+01:00</Date>
<CodeLivreurFK>LIV0000</CodeLivreurFK>
<CodeReglementFK>REG0000</CodeReglementFK>
<Secteur>Sfax</Secteur>
</Stock>
<Stock>
<CodeStockPK>STO0007</CodeStockPK>
<Operation>Vente</Operation>
<Date>2007-11-29T17:49:52.09375+01:00</Date>
<CodeLivreurFK>LIV0000</CodeLivreurFK>
<CodeReglementFK>REG0000</CodeReglementFK>
<Secteur>Sfax</Secteur>
</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.