CODEWITHSUNDEEP
reporting-servicesparametersssrs-2012
happygostacie
happygostacie

Reputation: 173

SSRS - how to use string value in date range parameter

I'm having some trouble with the following question and I'm hoping there is an easy solution that I'm just not considering.

I have a table which looks like this:

Person    Action_Taken  Year_Taken  Month_Taken Week_Taken  Composite_Date
John Doe      1            2017        1              3       Mar-17
Jane Doe      1            2017        2              6       Jun-17

I'm trying to write a report that will let the user select a Start Composite_Date and End Composite_Date, but pass the Year and Month in as a hidden parameter. I am able to successfully get the Composite_Date to populate as an available list of values in the report itself and pass in a value to the hidden parameters so that the report runs, but I think because it's a string, it isn't filtering by year and month properly (the data that's being returned is just all the data available in the set).

I've been through the MSDN documentation and searched around the internet but can't find a solution that addresses my specific problem. If it's not possible, then I'll just use Year and Month parameters and make them visible. I was just hoping to see if it was possible to use Composite_Date instead. Any ideas? Thanks!

Upvotes: 1

Views: 224

Answers (1)

StevenWhite
StevenWhite

Reputation: 6024

As you alluded to, the problem is that the composite date is a string and is being compared alphabetically. I would suggest adding a calculated field to your dataset that converts it to a date. In the parameter properties, you can use the existing string as the Label, but use the new calculated date as the Value.

Then in the query you would need to have similar logic to compare the dates of the records with your selected range. The expression will depend on the flavor of SQL you are using.

Upvotes: 1

Related Questions