I often use direct date fields in my reports, in the following SQL the start and end dates are SSRS parameters which feed the report. the parameters are given values in ISM and when the report runs the user can update the date values and refresh the report.
Here is an example which works:
- SQL Select statement
Select Service as Service, RecId as GroupCount from incident where Createddatetime >= @StartDate and createddatetime < @EndDate
- Dataset Paramter Properties
- @StartDate and @EndDate are the parameters, they are configured in SSRS and pulled in to my report as needed.
- When report is uploaded to ISM the configure the parameters:
- Report runs
There is a report template in ISM you can download which has some date functions already configured in it, these also take in to account the browser language and region settings so corrects date off sets for localisation. to get the template follow the instructions below:
- Open ISM
- Access the Report Manager Role
- Select the Templates workspace
- New Report
OOTB parameters, to figure these out download an existing report and take it a part to see how it is pulled together:
One thing to note about SSRS reports is that the SQL version for the report server must match the SQL Report Builder 3 or BIDS version of the platform in use, it is not backwards compatible so SSRS 2012 reports may not run on an SSRS 2008R2 server. As far as I can tell they are forward compatible.
I hope that helps.