Filter Data to Display in a Crystal Report Viewer Programmatically

Bechir Bejaoui

Crystal Reports C#Crystal Report ViewerFilter DataFilter Data and Crystal Report

3 Comments

Join the conversation! Your thoughts help the community grow.

laxman s18y ago
I have Created a Stored procedure with For Xml AUTO,TYPE And retrieving data toa temp XMl file. and binding datasource to the report.Instead of creating this temp xml file .How can we bind this runtime generated xml file to the Report Dynamically
- Bechir Bejaoui18y ago
  The simpliest way is to use the the crystal report widzard witch offers you the possibility to connect to serverals types of data sources. When you add a new crystal report element a wizard form will be opened just at the left you can find a treewiew witch provides you either to establish a new connection or to use already created connection, when you develop the new connection node you can find a severals connections types. I mean caonnection to Excel, Access and others. So among them you can find connection to xml file then you follow instruction given by the widzard to do that, If you still encouter troubles just ask me. good luck!!!
laxman s18y ago
I have Created a Stored procedure with For Xml AUTO,TYPE And retrieving data toa temp XMl file. and binding datasource to the report.Instead of creating this temp xml file .How can we bind this runtime generated xml file to the Report Dynamically

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.