Dynamically removing parameters from an ObjectDataSource and LinqDataSource within a ReportViewer LocalReport

If you've been using the ReportViewer in your web application, you've probably encountered having to deal with dynamically adding and removing parameters from a datasource to dynamically change your query statement in your report.  This is useful if you provide your users a filter for ad hoc reporting.  You could have radio buttons or drop downs on your report page to allow your users to filter search results.

If you are using Linq in your application, there's a LinqDataSource control that you can use to make calls using your DataContext object.  In the example below, my LinqDataSource object has a Where condition parameter:  StudentStatusID == @StudentStatusID

 <asp:LinqDataSource ID="ldsExamCompleted" runat="server" 

        ContextTypeName="MyDataContext" EntityTypeName="" 

        Select="new (StudentID, FirstName, LastName, ExamDate)" 

        TableName="Students" 

        

    

        Where="StudentStatusID == @StudentStatusID" 

        OrderBy="LastName">

        <WhereParameters>

            <asp:QueryStringParameter Name="StudentStatusID" QueryStringField="status" 

                Type="Int16" />

        </WhereParameters>

    </asp:LinqDataSource>

 For example on the aspx page we have an html dropdown named Status with 3 possible values:  Active (value=1), Inactive (value=2) and Both (value=0).  Each time we change the value on the dropdown, we pass the value as a querystring parameter.  Let's say we choose Both on the dropdown.  Our code behind would look like this.

            if (!IsPostBack)

            {

                ReportViewer1.LocalReport.DataSources.Clear();

                ParameterCollection examParams = ldsExamCompleted.WhereParameters;

                //if user chooses 'Both' on status dropdown

                if (Request.QueryString["status"] == "0") 

                {

                    Parameter p = examParams["StudentStatusID"];

                    ldsExamCompleted.WhereParameters.Remove(p);

                }

            }

Here, we removed the where condition parameter "StudentStatus == @StudentStatusID" from our Linq select query and our DataContext object returns all results.  That simple.

So when should you use an ObjectDataSource in your ReportViewer?  An ObjectDataSource is ideal if you use  TableAdapters.  TableAdapters provide communication between your application and the database.  TableAdapters are automatically created when you add a DataSet to your project.  You pass parameters from your ObjectDataSource to your TableAdapter if the Select method on your TableAdapter expects parameters.   Below is a sample of an ObjectDataSource on the aspx page:

   <asp:ObjectDataSource ID="odsExamCompleted" runat="server" 

        OldValuesParameterFormatString="original_{0}" SelectMethod="GetDataByStatusID" 

        

        TypeName="dsStudentTableAdapters.vwExamResultsTableAdapter">

        <SelectParameters>

            <asp:QueryStringParameter Name="StudentStatusID" QueryStringField="status" 

                Type="Int16" />

        </SelectParameters>

    </asp:ObjectDataSource>

Unlike a LinqDataSource where you can remove Where Condition parameters such as "StudentStatusID == @StudentStatusID", an ObjectDataSource can only remove the Select parameter StudentStatusID.  So if you try to remove this parameter from your ObjectDataSource, it will error out since your TableAdapter expects this parameter on its where clause.  You will need to add another Select method to your TableAdapter if you need to have a parameterless query or one with additional parameters.  So on our TableAdapter, I defined 2 Select methods.  One named GetDataByStatusID which expects a @StudentStatusID parameter and one named GetData which doesn't expect any parameter. 

Let's say we want to achieve the same results as our LinqDataSource  example above, this is what we should do on our code behind:

           

               if (!IsPostBack)

            {

                ReportViewer1.LocalReport.DataSources.Clear();

                ParameterCollection examCompletedParams = odsExamCompleted.SelectParameters;

                //if user chooses 'Both' on status dropdown

                if (Request.QueryString["status"] == "0") 

                {

                    Parameter p = examCompletedParams["StudentStatusID"];

                    odsExamCompleted.SelectParameters.Remove(p);

                    odsExamCompleted.SelectMethod = "GetData";

                }

            }

Hope this helps anyone implementing ad hoc reporting on their web applications.