Adding Parameter toggle for Different date fields

Question

I have a requirement to implement a toggle in an SSRS report where the user of the report can select to filter on either dates 1) Termination Date, 2) Activation Date or 3) Billing Date

I've created the parameter in SSRS as @toggle with the values of T, A, B @Date_From is the field below that we want to start the date range filter @Date_To is the field below that we want to end the date range filter

In my TSQL query I have added this in the where clause:

AND ((@Toggle = 'A' AND BS.ActivatedDatePK BETWEEN (@Date_From) AND (@Date_To)) OR (@Toggle = 'T' AND BS.TerminationDatePK BETWEEN (@Date_From) AND (@Date_To)) OR (@Toggle = 'B' AND BS.BillingStartDatePK BETWEEN (@Date_From) AND (@Date_To)))

And I'm not getting any results for either of the parameters. Visual Studio isn't throwing any errors so somewhere my logic is just plan bad.

Does anyone have any ideas?

Answer 1

There are a few things to try here...

The logic actually looks OK to me (although being old an tired I might have missed something!)

1. Run a trace on the server and see what is actually getting passed to the query parameters.
2. Try removing everything except one of the date parts of the where clause - this will make sure the dates are not the issue
3. Dates are always a pain, check that the datatypes are correct and you are not passing in dates in the wrong format
4. Test you login in SSMS or whatever query editor you use with some manual parameter values and makes sure you get the results you expect.
5. Hit the refresh button on the report toolbar to make sure you are not seeing outdated data (or lack of)

All that said and done, it 'might' be easier to write your dataset query as an expression.

So, hit the function button next to the query text field in the dataset editor and then set it to an expression something like

="SELECT * FROM myTable WHERE BS." & 
    SWITCH(
           Parameters!Toggle.Value = "A", "ActivatedDatePK",
           Parameters!Toggle.Value = "T", "TerminationDatePK",
           Parameters!Toggle.Value = "B", "BillingStartDatePK") &
    " BETWEEN " & Parameters!Date_From.Value " AND " & Parameters!Date_To.Value 

you might need to convert the dates to the correctly formatted strings...