| T O P I C R E V I E W |
| dbooth |
Posted - 12/19/2012 : 08:01:59
A calculated measure with MDX "datediff ("d",[Collection Date].[Date],now())" is returning a huge number - 734855 - for all records.
The Date dimension has a surrogate key with the natural key 'Date' identified in SSAS as the Name column for the surrogate. Incorporating 'Date' as a row in a report correctly shows the date.
Is there an error in the MDX above or is it due to using a surrogate key on the Date dimension? |
| 3 L A T E S T R E P L I E S (Newest First) |
| admin |
Posted - 12/19/2012 : 20:11:59
Fast Track to MDX
http://www.amazon.com/Fast-Track-MDX-Mark-Whitehorn/dp/1846281741
|
| dbooth |
Posted - 12/19/2012 : 16:00:28
That worked perfectly, thank you. Is there a book or other resource you can recommend for this level of MDX? |
| admin |
Posted - 12/19/2012 : 13:43:44
When you don’t specify the [Collection Date].[Date] on filters, rows or columns it will be set to the default member (All Collection Dates). The DateDiff will net be able to convert “All Collection Dates” to a date.
Your MDX expression should check if the text is a date like:
Iif(IsDate([Collection Date].[Date].CurrentMember.Name),
datediff ("d",[Collection Date].[Date].CurrentMember.Name,now()), 0)
|
Report Portal
