Best way to refine report result inside the Report, not using the SQL query

[fuhrj]

I have a report where the user can refine their report data based on three options.

The problem with this is that my SQL query is HUGE. It looks something like this:

if (@locationID = 0) BEGIN

if (@userID = 0) BEGIN

if (@deptID = 0) BEGIN
'query here
END ELSE BEGIN
'query here
END

END ELSE BEGIN
'query here
END

END ELSE BEGIN
'query here
END

So basically, the query is selected based on the options the user selects on the Form.

What I want to do is create a general query and then filter it within the Report; like the way you can filter a DataView by using the RowFilter property.

Thanks!

[Brendan]

You could use the Filters on a Databand to filter your output at runtime. You could combine with a Form within your report using the Form Designer and ask the users for whatever filters you need. or the filters could be passed into the report from your application.

[fuhrj]

You could use the Filters on a Databand to filter your output at runtime. You could combine with a Form within your report using the Form Designer and ask the users for whatever filters you need. or the filters could be passed into the report from your application.

Thanks!

What I've come up with so far was to create three variables that are set in the Form, after the user clicks Run Report.

The filters are predefined on the report to match the database column with the variable property.

However, what I need to do now is being able to turn filters on or off at the Form level.

Is this possible?

[Edward]

Hi

There is only one way to do this is to add a fourth variable of boolean type and to set it when is possible to switch off the filter. So this variable will be just a part of condition.

Thank you.