ReportViewer and filtering

Question

0.00/5 (No votes)

See more:

Hello,

I am try to manually; no wizard, populate a the ReportViewer control. I have been successful in doing so, by using a SQL Data Access utility class I've created. But I wanted to be able to filter result of the report as well, and as such, I explored passing in parameters and filtering based on the paramater passed in. This also works, but by placing a parameter on a field in report, if do not set; in others words leave it empty, the report itself is empty. I result are there but they are not being shown, because the filter is being applied on load; I can tell because I have other textboxes on the page that make use of the values of the report via some expressions I've written.

Is there a way to by default ignoring the filter, initially on load? I am not the best at using the ReportViewer but I am learning so please be patient with me if I ask some seemingly dumb questions. Also, is there a way to remove a filter once you no longer have use for it during runtime?
.

Posted 11-Jun-13 8:48am

noobville

Add a Solution

Comments

PrissySC 11-Jun-13 16:31pm

I do not use the filters much at all. I leave selections for the user via dropdown and/or radio buttons. I do the filtering before I ever fill the dataset. When it arrives at the report it is in the format that I want already. Not a direct answer, but a possible alternative. For the same reasons that you mentioned, parameter-based or fields and boxes on the report itself that are unwanted, I perform the filter on the datasource myself. I use a bindingsource to populate the reports dataset.

iCanDivideByZero 27-Jun-13 7:30am

I actually ended implementing it they way you mentioned, as I was not having much luck otherwise.

1 solution

Add a Solution

Add your solution here

Treat my content as plain text, not as HTML

Preview 0

…

Existing Members

Sign in to your account

...or Join us

Download, Vote, Comment, Publish.

Your Email
Password
Forgot your password?

Your Email
This email is in use. Do you need your password?
Optional Password

I have read and agree to the Terms of Service and Privacy Policy
Please subscribe me to the CodeProject newsletters

When answering a question please:

Read the question carefully.
Understand that English isn't everyone's first language so be lenient of bad spelling and grammar.
If a question is poorly phrased then either ask for clarification, ignore it, or edit the question and fix the problem. Insults are not welcome.
Don't tell someone to read the manual. Chances are they have and don't get it. Provide an answer or move on to the next question.

Let's work to help developers, not make them feel stupid.

This content, along with any associated source code and files, is licensed under The Code Project Open License (CPOL)

code_madi · Answer 1 · 2013-06-11T17:25:00

Solution 1

First of all,it is not a dumb question at all :)
Based on your question, let me paint a scenario. Say you have data belonging to 2 different systems in a single sql table. If you pass the system id 1 as a report parameter, you successfully filter the data belonging to system 1. If you pass system id 2, you filter to get data for system 2. You want a default filter when the report loads (in case you haven't actually passed the report parameter i.e. System ID 1 or 2...let's say you have passed an uninitialised parameter 0). Is this correct?

If so, change your filter criteria expression from something like
=Parameters!SystemIDParm.Value
to
=Parameters!SystemIDParm.Value OR Fields!SystemID.Value

The second expression looks for the passed report parameter to filter the data on...if it can't find it, it will just return data for both system 1 and system 2.

Hope that makes sense. Good luck!

Posted 11-Jun-13 17:25pm

code_madi

Updated 11-Jun-13 17:28pm

v2

Comments

iCanDivideByZero 12-Jun-13 7:52am

I tried your solution, but I received the following error on the page at runtime:

"An error has occurred during report processing.
Failed to evaluate the FilterValue of the Tablix ‘Tablix1’."

Here is the filter I used. Perhaps you can see something, that I am not.

"=Parameters!DateFilterParam.Value Or Fields!BillDate.Value"

Please let me know what you and thanks for any help you can give.

code_madi 12-Jun-13 9:34am

I have actually used this logic successfully in one of my projects. I am reasonably confident that the complication in your case is due to the fact that it is a datetime parameter that is being passed here. Can you post the code that is passing the parameter to the report? Could it be that a null value instead of the .net default datetime value of 01/01/0001 could be causing this issue? Try assigning it a default datetime value instead of leaving it as a null. You could change the filter to just be =Fields!BillDate.Value. If this works, you can be sure that it is the first part of the filter that is the problem.

iCanDivideByZero 13-Jun-13 7:33am

Hello,
Sorry for this late response. I tried doing what you mentioned and came up with the same result. Below is the method, perhaps that will shed more light on the situation.

private void BindData()
{
// Bind the report viewer control.
string queryStr = @"SELECT * FROM Invoice_Table";
SqlQueryParameter queryParams = new SqlQueryParameter();
DataSet ds = SqlDataAccessUtility.SQLSelect("tblBilling", queryStr, queryParams,
CommandType.Text);

ReportParameter[] param = new ReportParameter[1];
if (!string.IsNullOrEmpty(DateTextBox.Text))
{
param[0] = new ReportParameter("DateFilterParam", DateTextBox.Text);
}
else
{
param[0] = new ReportParameter("DateFilterParam",
new DateTime().ToShortDateString());
}
rvInvoices.LocalReport.SetParameters(param);

ReportDataSource rds = new ReportDataSource("InvoiceDataSet", ds.Tables[0]);
rvInvoices.LocalReport.DataSources.Clear();
rvInvoices.LocalReport.DataSources.Add(rds);
rvInvoices.LocalReport.Refresh();
rvInvoices.Visible = true;
}

code_madi 13-Jun-13 8:17am

So when you pass a value through the datetextbox, does your report work? If so, what is the format of the date string in your text box.Use the same format and hard code 01/01/0001 in your else condition (instead of new DateTime().ToShortDateString(). See if that works.

iCanDivideByZero 13-Jun-13 10:52am

I did the following: string dateStr = "01/01/0001";//new DateTime().ToShortDateString(); param[0] = new ReportParameter("DateFilterParam", dateStr); I received the same result an error was displayed. So I went ahead and changed my filter to just "=Parameters!DateFilterParam.Value", and the report simply came back empty; no error. Perhaps I am not doing something incorrectly with the setup of the filter maybe?

iCanDivideByZero 13-Jun-13 12:29pm

I tried writing another expression as another avenue, but the results were the same:

CDate(IIF(IsDate(Parameters!DateFilterParam.Value), Parameters!DateFilterParam.Value, Fields!BillDate.Value))

code_madi 13-Jun-13 20:59pm

Let us try and isolate the problem.

1)The filter expression I suggested was "=Parameters!DateFilterParam.Value Or Fields!BillDate.Value". This resulted in an error -> "An error has occurred during report processing. Failed to evaluate the FilterValue of the Tablix ‘Tablix1’.".

2)You then tried one half of the filter expression ("=Parameters!DateFilterParam.Value") and that resulted in an empty report. This is expected because you were passing "01/01/0001" and no records from select query would match that.

3)Now, try the second half of the expression ("=Fields!BillDate.Value"). This should theoretically give you back all the rows from your select command because you are applying a filter against the "BillDate" column and selecting all those records whose BillDate.Value = BillDate.Value. Is this what is happening?

This way, we would have sliced the filter expression into 3 parts and would have isolated the problem (hopefully). Let us take stock after this.

iCanDivideByZero 18-Jun-13 10:00am

Hello,
Sorry for the late response. But I was asked not to spend anymore time on this, so I decided to write a round about solution. I basically have a textbox where a date is entered, and once the user presses filter I essentially rebind the reportviewer based on the parameter. This essentially works, even when going through the pages.

I wish I had more time to come to a more eloquent solution, but unfortunately time was not on my side. Thanks for all your help!