CODEWITHSUNDEEP
sql-serverreporting-servicesssrs-2008
Stephanie
Stephanie

Reputation: 516

How can I filter on different dates based on the value of parameters

In SSRS, is it possible to display different date field results based on a parameter value? Here is the requirement: There is a parameter called status (values: open, closed, pending) which filters result based on a status field. if open is chosen then all the open loans will display if pending then all the pending loan records will be displayed. Each status has its own date column. In report, user will first select a status (either open, closed or pending), then the date parameter will filter on date field depending on what is selected. So if user selects open status then date field will filter on open date field. If closed is selected then date filter will filter on closed date and so on. I know cascading parameter functionality will be used but I am really confused on how to allow date filter to filter on a specific status date (based on what status value is chosen).

Upvotes: 0

Views: 1792

Answers (1)

Mike D.
Mike D.

Reputation: 4104

You can do this in your SQL with a case statement in your WHERE clause.

Something like this:

WHERE CASE @statusParam
       WHEN 'open' THEN tbl.open_date
       WHEN 'closed' THEN tbl.close_date
       WHEN 'pending' THEN tbl.pending_date
    END > @startDateParam

OR you can do it at the report layer with a SWITCH() in your filter expression

=SWITCH(
    Parameters!statusParam.Value="open", Fields!open_date.Value,
    Parameters!statusParam.Value="closed", Fields!close_date.Value,
    Parameters!statusParam.Value="pending", Fields!pending_date.Value
) > Parameters!startDateParam.Value

Doing it in the SQL would be preferred, since it would cut down on the amount of rows returned, unless you need the rest of the dataset for some other part of your report.

Upvotes: 1

Related Questions