How do you handle reporting filters in ASP.NET MVC?

Question

For a given report, the user will want to have multiple filtering options. This isn't bad when the options are enumerations, and other 'static' data types, however things can get silly fast when you need a select list that is populated by fields stored in a table in the backend.

How do you handle this scenario? I find myself constantly reshaping the View data to accomodate the additional filter fields, but it really is starting to be a bit much tracking not only the selected options, but also the options themselves...

is there not a better way?

Answer 1

You could go and retrieve the data asynchronously on the screen using jQuery and JsonResults from your MVC application, this is how we populate all of our lists and searches in our applications. I have an example of how it is done here.

This way the view data is loaded on demand, if they don't use the extra filters then they don't have to get the view data and if one selection relates to another then it's clear which set of data you need to retrieve.

Another option, though I don't like this one as much but jQuery solution may not suit you, is to have your model object for your view contain all the view data so that all you need to do is set the single model object and all the lists are loaded directly and strongly typed. This will simplify the view and the back end code because it will be more clear that for this view the only thing you need is a complete version of this model object.

For example if you had two lists for combo boxes then your model might look like:

public class MyViewMode
{
    public int MyProperty { get; set; }
    public string SomeString { get; set; }
    List<string> ComboListA { get; set; }
    List<string> ComboListB { get; set; }
}

Hope that makes sense, if not please comment and I'll expand on it.

Answer 2

I’m currently building out a new reporting section for one of our products at work and am dealing with this same issue. The solution I’ve come up with so far, though it hasn’t been implemented yet so this is still a work in progress, is along the lines of this.

There will be a class that will represent a report filter which will contain some basic info such as the label text and a list of option values.

public enum DisplayStyle
{
    DropDown,
    ListBox,
    RadioList,
    CheckList,
    TextBox
}

public class FilterOption
{
    public string Name { get; set; }
    public string Value { get; set; }
    public bool Selected { get; set; }
}

public class ReportFilter
{
    public string Title { get; set; }
    public DisplayStyle Style { get; set; }
    public List<FilterOption> Options { get; set; }
}

And then my model will contain a list of these option classes that will be generated based on each report’s needs. I also have a base report class that each report will inherit from so that way I can handle building out the option lists on a per report basis and use one view to handle them all.

public class ReportModel
{
    public string Name { get; set; }
    public List<ReportFilter> Filters { get; set; }
    public DateTime StartDate { get; set; }
    public DateTime EndDate { get; set; }
}

Then inside my view(s) I’ll have some helper methods that will take in those option classes and build out the actual controls for me.

public static string ReportFilter(this HtmlHelper htmlHelper, DisplayStyle displayStyle, FilterOption filterOption)
{
    switch (displayStyle)
    {
        case DisplayStyle.TextBox:
            return string.Format("<input type=\"text\"{0}>", filterOption.Selected ? (" value=\"" + filterOption.Value + "\"") : string.Empty);
            break;
        ...
    }
}

My route would look like this

Reports/{reportID}/start/{startDate}/end/{endDate}/{*pathInfo}

All reports have a start and end date and then optional filters. The catchall parameter will have lists of filter values in the form of “Customer/1,4,7/Program/45,783”. So it’ll be like a key/value pair in list form. Then when the controller loads it’ll parse out those values into something more meaningful.

public static Dictionary<string, string> RouteParams(string pathInfo)
{
    if (string.IsNullOrEmpty(pathInfo))
    {
        return new Dictionary<string, string>();
    }

    var values = new Dictionary<string, string>();

    // split out params and add to the dictionary object

    return values;
}

Then it will pass them off to the report class and validate them to make sure they’re correct for that report. Then when the options are loaded for that report anything that’s been set in the URL will be set to Selected in the ReportOption class so their state can be maintained. Then the filter list and other report data will be added to the model.

For my setup some filters will change when another filters selection changes so there will be some AJAX in here to post the data and get the updated filter options. The drilldown will work sort of like the search options at amazon or newegg when you narrow your search criteria.

I hope that all makes sense to someone beside me. And if anyone has some input on improving it I’d be happy to hear it.