Just to confirm for people:
MDX indeed DOES NOT support the datetime format in the manner you may be expecting. To understand that, you'll notice that there are MDX fuinctions like StrToMember(@parm) and StrToValue(@parm) but no DateTimetoMember(@parm). (Get it??)
(BTW, there's no NumToMember(@parm) for similiar reasons.)
It's helpful to remember that MSRS was perhaps designed first with relatgional concepts in mind. That's evident from a number of perspectives:
- MSRS deals with flattened result sets. MSAS data has to be flattened before being consumed by MSRS.
- When using measures, the best reference to use with MSAS is Fields!myField.FormattedValue, as opposed to Fields!myField.Value which is the default.
- You generally have to override the default behaviors to force RS to use the values returned from the cube (i.e. aggregate values).
- The MDX designer really isn't up to the task. It really needs to be replaced to implement proper OLAP concepts. (I always hand write the MDX for MSRS.)
Having said that, MSRS is a great tool for operational style reports. I use it all the time.