Excel 12, SSRS - More intuitive dates

When a user wants to specify a date, a date range or even select a period relative to today their options have been a bit limited (from a pure MS perspective). Often is will be up to the report developer to design specific date functionality into what the end user will interact with.

One of the commonly-lamented shortcomings of RS2000 was that there was no date-picker available to wrap a date parameter. Sure, if you built your own custom front-end to RS this could be done but not with the out-of-the-box Report Manager. We all know that the date-picker functionality is now in SSRS, and a very welcome addition it is.

So, what about when our user is creating a report in Report Builder. They come across a date field. When developers build models for Report Builder a field with a datetime data type can be manipulated in number of ways and then grouped in the model. For example, the Order Date field; the user is presented with a range of different meta data options pertaining to that specific date field. When initially creating the model the model designer wizard automatically creates a number of different date derivatives when building the initial model (day, month, quarter, year etc.). However if your users have a specific need for a certain format of date meta data you can easily create these with the formula editor in model designer. The left-hand pane of the below image shows an example of pre-defined date derivative fields.

















There are even more options available if the user wants to add some form of date filtering to their Report Builder report. The above screenshot shows the options a user is presented with when they choose to filter on a date field. Not a bad set of choices, and we don't have to code anything to give them that level of functionality, all we do is provide the date field.

So what about dates in Excel 12? A user's ability to sort and filter data in Excel 12 is going to take a major step up. So, if your user is working with data in an Excel table or PivotTable and would like to do some date-based analysis they will be presented with options like Next Year, Last Week, Yesterday etc (screenshot from Excel 12 Blog) Excel 12 will check the system clock and thereby determine what relative time period actually is.

These types of date manipulation options have certainly been available in various forms in different BI products for some time. This is nothing new or revolutionary from that perspective. However, it shows that MS is really concentrating on giving the BI end user much more power right off the mark without any (or at least a lot less) developer intervention. Great stuff!