none
Adding Date as a Measure to SSAS Dataset in Mobile Reports causes incorrect results RRS feed

  • Question

  • I'm taking another stab at this issue and hope someone can help.

    To use a timeline filter in Mobile reports with an SSAS based dataset, you have to add a date as a calculated measure as specified here (https://docs.microsoft.com/en-us/sql/reporting-services/mobile-reports/retain-date-formatting-for-analysis-services-in-mobile-reports)

    Unfortunately, after doing this, my dataset returns unexpected results and prevents the mobile report from showing accurate totals.

    The data is based on Invoice records and the Date column is the invoice date. 

    Here is a screen shot of the dataset without the Invoice Date Measure

    And here is a screen shot with the Invoice Date Measure included:

    As you can see, adding the Invoice Date as a Measure returns extra records with null values.  Here is a screenshot of the Invoice Date Measure

    Does anybody know why this is happening and how it can be resolved?  It seems silly to me that the Mobile report builder requires a date exposed as a measure in order to use the timeline feature.  I understand dimensions returned from a model are returned as strings, but, it's not like that was unknown to Microsoft.  The Mobile Report Builder should be able to accept a date as a Dimension instead of requiring a measure.  If anything, setting up dates as measures goes against the KIMBALL principles for designing data cubes and it's not required in other products.

    Wednesday, April 5, 2017 1:42 PM

All replies

  • Hi Phill D,

    According to your description, the dataset generate extra unexpected rows with (null) after adding date as a calculated member, right?

    Based on my research, you can exclude the null value for measure via IIF condifition. For more details, please refer to this similar thread: MDX Help SSRS Query Designer MDX Operator Filter Expression

    If you still have any question, please feel free to ask.

    Best regards,
    Yuliana Gu


    MSDN Community Support
    Please remember to click "Mark as Answer" the responses that resolved your issue, and to click "Unmark as Answer" if not. This can be beneficial to other community members reading this thread. If you have any compliments or complaints to MSDN Support, feel free to contact MSDNFSF@microsoft.com.

    Friday, April 7, 2017 3:03 AM
  • Can you explain why the extra records are created.  It doesn't make sense that calling an MemberValue for an existing row would result in the creation of additional records with null values.  Also, are there any plans to remedy this in the near future.  You shouldn't have to include the date as a measure in the query.

    Thanks

    Friday, April 7, 2017 3:52 PM
  • Also, adding the date as a measure causes the load time for the dashboard to be considerably higher and in some cases, fail to load if there are many columns of data.  It is a huge limitation that shouldn't exist.
    Friday, April 7, 2017 3:57 PM