Toggling Date Field Format in Events Reports (Auto Show Time of Day)
The default field for event date for dockets is SQL datetime which appends midnight onto the day for any docket that is set as an all day appointment, 10/22/2018 00:00:00:00, which isn't intuitive to the eyes. The report format default date, masks the time in all instances, and only shows the date, 10/22/2018. This method works until you have a deposition or in person docket that you need the specific time. How do you change the field view type on the fly between date and date time as appropriate? Expressions.
The expression translates to; if the hour of the data in the eventdate field is 0, meaning midnight, then use the format d, else use the format g. SQL formatting codes are here: Link to Microsoft. Many options using MS functions.
- Edit the report
- Go to the properties of the eventdate expression.
- Choose Number from the left pane
- Choose Custom as the Category
- Click the expression button fx
- Enter: =iif(Hour(Fields!EventDate.Value)=0,"d","g")
- Click all the oks and save.
The default prolaw listing report uses the the custom format MM/dd/yyyy h:mm tt
If the default doesn't work though, try the above method.
Every time you save, SSRS recompiles the report. Saving after every change will ensure your changes are at least programmatically correct. Always run your report before applying the changes so you can see how the report works prior to committing changes.
If you break something, just don't apply your changes. If it's a really sticky report, export it out (manual backup) prior to making changes.