Reader Level:
ARTICLE

Filter Data to Display in a Crystal Report Viewer Programmatically

Posted by Bechir Bejaoui Articles | Crystal Reports C# December 10, 2007
This article describes how to filter data to display, export or print programmatically using the selection formula property of the crystal reports viewer control.
  • 0
  • 0
  • 100671

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".

1.gif

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. 

2.gif

Figure2

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

3.gif

Figure3

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

4.gif

Figure4

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

5.gif

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

6.gif

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.

COMMENT USING

Trending up