Filtering Report Data on the Server

I was recently made aware of a great suggestion involving filtering reports on the server side. Lets spend a minute looking into how to do this! Obviously situations will vary, but the general idea should be about the same when using TableAdapters.

Adding the Query

The first step is to add a specialized query to the existing table:

When adding the new query, you will be prompted with a setup wizard. The key here is the following step:

This type of query (with the question mark) will create the appropriate methods that will take in a category id and filter the data appropriately on the server side. Once this has been completed, you should see something like this:

The last step in the wizard asks for names of the two methods (one that fills a datatable and one that returns one). I chose FillByCategoryID and GetDataByCategoryID.

Report Parameters

I created a simple product report based on the Northwind Access Database. Once in the report, head on over to the Parameters section in the field list and add a new CategoryID parameter

and set the appropriate values:

In this instance, I am defaulting to the value 1. Make sure the parameter is visible! This setting is what dictates whether or not the report preview will add UI to ask for the parameter value. An important point to add here is the RequestParameters property on the report.

This important property dictates whether or not the document goes through its creation cycle before parameter values have been submitted. In this instance it should be set to true. In other words we don’t want the report to generate at all unless we have values for the parameters.

Some Code

Now to add some logic! There is a report event called ParametersRequestSubmit that is fired when values for the parameters have been set:

We will use this event to add the code required to fill our data table:

Notice that our table adapter now has a new method that corresponds to the query we added in the first step. Now for the actual code:

private void ProductReport_ParametersRequestSubmit(object sender, ParametersRequestEventArgs e)
{
    if (Parameters["CategoryID"] != null)
        productsTableAdapter.FillByCategoryID(northwind1.Products, (int)Parameters["CategoryID"].Value);
}

The Outcome

Now for the magic! The report will ask for a category ID:

and then the report will be generated:

The code to fire this off:

private void buttonShow_Click(object sender, EventArgs e)
{
    ProductReport report = new ProductReport();
    report.ShowPreviewDialog();
}

Hope this helps!

As always, if there are any comments and/or questions, feel free to get a hold of me!

Seth Juarez
Email: sethj@devexpress.com
Twitter: @SethJuarez

Let us know what you think of our Reporting Suite by rating it in the VS Gallery!

Free DevExpress Products – Get Your Copy Today