dd/mm/yyyy hh:mm
dd/MM/yyyy HH:mm
So next time I’ll be thinking about formatting a field in Report Builder – I’ll think about .Net format.
Pingback from DateTime Format in Report Builder???s Report Model « A Blog for SQL Server Reporting Services (SSRS) Programmers
I've found HH:mm format useful for displaying just time of datetime value. But one customer wanted to filter patient appointment times so (eg) they could see out-of-hours activity between 18:00 and 08:00.
Even if a field in the Report Model is the HH:mm format it does not allow a filter on time of day as the field behaves like a datetime value in the Report Builder filer dialogue (and assume todays date!).
I've got round this by adding a calculated field to the Report Model “HM” & RIGHT(TEXT(100+HOUR(DateTime)),2) & “:” & RIGHT(TEXT(100+MINUTE(DateTime)),2)
Though a little convoluted it returns values that can be used as a time of day filter e.g. between HM07:00 and HM08:59
The "HM" makes the calculated value behave like a string in Report Builder filtering. Without alpha characters in the calculated value attempts to filter between 07:00 and 08:59 return vaues filtered between 7 and 8 - not what the user requires.
I hope there's no easier way to do this.
First of all Stephen, thank you for visiting the blog and leaving your insightful comment.
Secondly, I wanted to ask, did you try to create an attribute of “hour” which would be the following expression: Hour(Relevant DateTime) and then apply your filter on that?
Thank you again for your input,
Ella
Ella,
Yes, Hour(datetime) works ok but the user wanted to filter on times of day such as 17:30 to 20:30 (times outside of their 'usual' clinic slots). Adding a second attribute based on minutes made the filering options very clumsy.
BTW - agree with your observations regarding Report Builder versions 2 and 3 and the relative usability in relation to version 1.0. My users get along fine with version 1.0 and are very productive (often more so than their colleagues who develop reports based on SQL!) but the interface is such that they almost need to go on a course for the BIDS Report Designer. Until MS sort out a friendy version (4.0?) of Report Builder I'm staying with version 1.0 but have recently has excellent results with those same users using XL Pivot Tables linked to SSAS data.
Stephen
Hi Stephen,
Maybe try something else? Do you have SQL Server 2008 onwards? If you do, then try the following:
CONVERT(TIME, datetimefield) as tm_fulltime
You can use this field in Report Builder 1.0 as an attribute of data type “time” and set valueselection for the attribute to “none”.
From what I’ve tested on my model, that works as a filter you can write freely into things like 7:02 and so on and it works.
It should work, depending on if you have SQL Server 2008 onwards or not…
As for a replacement for Report Builder 1.0, I totally agree that Excel Pivot tables are a great way to query ad – hoc your data. If you have Excel 2007 onwards, then you can also use their tables (which are also quite smart), the visual aspects and the data mining (see previous posts in this blog on the subject). I understood that the release of Project Crescent in SQL Server 11 should be a sort of replacement, but for now it only works with DAX (which is only in Office 2010 at the moment in PowerPivot). Let’s hope for the best…
Thanks for trying TIME on SQL 2008 - I'll certainly use that when clients move up.