Using between clause on a VARCHAR date field
20 November, 2014
In a time dimension table within SQL Server, we have �Date� field with DATETIME datatype and �Month_Year� field with VARCHAR datatype. From the Yellowfin report, user wants �Month_Year� as filter so that they can use between clause during report generation by entering (or selecting) values like Jan-2014 or Mar-2014. Example: Net Sales between Jan-2014 to Mar-2014 where in Sum(Net Sales) for the three months (Jan, Feb & Mar 2014) have to be reported.
Client doesn�t want a calendar to pop-up for entering user inputs or end up mentioning the entire date. They instead want to enter the filter as MMM-YYYY format alone. Please share your inputs on how this can be achieved?
We tried options with Date Hierarchy default functions and through datatype formatting at field level during view creation. It however didn't meet the intended requirement.
Client doesn�t want a calendar to pop-up for entering user inputs or end up mentioning the entire date. They instead want to enter the filter as MMM-YYYY format alone. Please share your inputs on how this can be achieved?
We tried options with Date Hierarchy default functions and through datatype formatting at field level during view creation. It however didn't meet the intended requirement.
Hi Kishore,
in this situation I would just use the Date field (DATETIME) and apply the Date Hierarchy calculated field called Date to it in the view and then set the appropriate format:
then in the report, when you use the above calculated field as a filter, be sure to set its format as a Value List Selection with the Cached Values option. Then choose the Between filter operator and it should all work as you have described:
Please let us know if you have any questions about the above process.
regards,
Dave
in this situation I would just use the Date field (DATETIME) and apply the Date Hierarchy calculated field called Date to it in the view and then set the appropriate format:
then in the report, when you use the above calculated field as a filter, be sure to set its format as a Value List Selection with the Cached Values option. Then choose the Between filter operator and it should all work as you have described:
Please let us know if you have any questions about the above process.
regards,
Dave