Filtering Report Data on the Server

DevExpress Data Blog
04 July 2011

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:

AddQuery

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

Query

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:

NewTable

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

AddParam

and set the appropriate values:

ParamSettings

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.

RequestParams

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:

ParametersRequestSubmit

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

FillByCategory

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:

Params

and then the report will be generated:

report

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

Want The Best Reporting Tool Ever?

Get The No-Compromise Reporting Tool for WinForms, ASP.NET, Silverlight and WPF! - Native integration with DevExpress WinForms and ASP.NET Controls, unequalled design-time productivity, industrial-grade features. Try a fully-functional version of DXperience for free now: http://www.devexpress.com/Downloads/NET/

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

Follow SethJuarez on Twitter

4 comment(s)
Robert Fuchs
Robert Fuchs

How would I do this in XAF (using XPO)?

Thanks, Robert

4 July, 2011
Holger Böcher
Holger Böcher

XAF does it by nature.

documentation.devexpress.com

5 July, 2011
Dennis (DevExpress Support)
Dennis (DevExpress Support)

Thank you for your answer, Holger. You are absolutely correct.

In addition, it is also possible to use a native report parameter similar to what Seth demonstrated here. The only difference is that you have to handle the ParametersRequestSubmit event via report scripts, and call a slightly different filtering code:

xafReport1.SetFilteringObject(new LocalizedCriteriaWrapper(xafReport1.DataType, CriteriaOperator.Parse("CategoryID = ?", Parameters["CategoryID"].Value)));

So, as you see, XAF provides more flexibility in such scenarios. It will also be possible to implement something very close to the approach demonstrated in this blog after the suggestion is implemented.

5 July, 2011
Robert Fuchs
Robert Fuchs

> So, as you see, XAF provides more flexibility in such scenarios.

Yes - didn't see the forest for all the trees :)

5 July, 2011

Please login or register to post comments.