none
SSRS 2016 Mobile Reports / SSAS 2016 Tabular Model Calculated Measures do not Show Correct Value on a Guage RRS feed

  • Question

  • We created a tabular model on SSAS 2016 that includes a calculated ratio measure: SUM(X)/SUM(Y). We tested the model by connecting it from Excel PivotTable, PowerView, PowerPIVOT and SSRS Paginated report. In all these tools we were able to get the correct value calculated for this measure on all grouping levels.

    Now we created a dataset that includes this calculated measure with other dimensions and measures. We created an SSRS 2016 mobile report and we connected the report to this dataset.

    We added a Guage to the report layout, and we associated the Guage with this ratio (calculated measure). Unfortunately, this yields always incorrect results. After few investigations, we found that the problem is that mobile reports gauges force you to choose an aggregate function for the measure: SUM, AVG, LAST... etc and there is no NONE option available as in other BI tools to handle calculated measures!!!

    This means that we will never be able to display this calculated measure value correctly on Mobile reports!!!

    A KPI will not solve the issue either.

    For us this is a real problem as we have so many of those calculated measures in our model.

    Guys, is there a problem in this area??!!

    What is the solution??


    Luai7


    • Edited by luai7 Monday, August 29, 2016 8:20 AM
    Monday, August 29, 2016 8:19 AM

Answers

  • Hi luai7,

    1. In current Reporting Service 2016, gauge is used to display one aggregated value for their main value and one aggregated value for their comparison value. And its default aggregation is a sum. This is by design.

    If you still want to use gauge to display a value without aggregation. You could provide Microsoft a feature request at https://connect.microsoft.com/SQLServer so that we can try to modify and expand the product features based on your needs.

    Reference: https://msdn.microsoft.com/en-us/library/mt732285.aspx

    2. Then in current SQL Server Analysis Service tabular model, the default return type of data is a string. And in Reporting Service 2016 mobile reports, the Time slider object only supports the date type data with timestamp.

    So if you want to use this tabular model, you should retain a date field data type. You can try following methods: When you create the shared dataset for mobile report. Right click calculated members -> create a calculated member by using the expression like [Data].[Date].CurrentMember.MemberValue. Then add this calculated member into your dataset column grid.

    Reference: https://msdn.microsoft.com/en-us/library/mt732283.aspx

    If you still have any questions, please feel free to ask.
    Thanks,
    Xi Jin.

    • Marked as answer by luai7 Tuesday, August 30, 2016 10:25 AM
    Tuesday, August 30, 2016 5:28 AM
    Moderator

All replies

  • We created an tabular model on SSAS 2016. One of the dimensions in this model is a date type.

    We deployed the model to SSAS 2016.

    We connected our Excel PowerPivot, PivotTable and PowerView to this model and we were able to see this Date type perfectly all right. In PowerView for example it was recogonized perfectly all right for performing play-animation on a scatter chart which means that it is recognized as a date type all right.

    Now we created a dataset that includes this date type with other dimensions and measures from the tabular model.

    Then we created an SSRS 2016 mobile report, we selected the time slider object, however when we added this data set to the mobile report, it seems that it does not recognize this date type and thus we are not able to use the time slider!!!

    We returned back to the model and made 10s of changes but with no luck, the mobile report time slider is not able to see any date type.

    Guys, is there a problem in this area??!!

    What is the solution??


    Luai7

    Monday, August 29, 2016 7:59 AM
  • Hi luai7,

    1. In current Reporting Service 2016, gauge is used to display one aggregated value for their main value and one aggregated value for their comparison value. And its default aggregation is a sum. This is by design.

    If you still want to use gauge to display a value without aggregation. You could provide Microsoft a feature request at https://connect.microsoft.com/SQLServer so that we can try to modify and expand the product features based on your needs.

    Reference: https://msdn.microsoft.com/en-us/library/mt732285.aspx

    2. Then in current SQL Server Analysis Service tabular model, the default return type of data is a string. And in Reporting Service 2016 mobile reports, the Time slider object only supports the date type data with timestamp.

    So if you want to use this tabular model, you should retain a date field data type. You can try following methods: When you create the shared dataset for mobile report. Right click calculated members -> create a calculated member by using the expression like [Data].[Date].CurrentMember.MemberValue. Then add this calculated member into your dataset column grid.

    Reference: https://msdn.microsoft.com/en-us/library/mt732283.aspx

    If you still have any questions, please feel free to ask.
    Thanks,
    Xi Jin.

    • Marked as answer by luai7 Tuesday, August 30, 2016 10:25 AM
    Tuesday, August 30, 2016 5:28 AM
    Moderator
  • Thanks Xi Jin.

    Is there a workaround for the calculated measure thing ??


    Luai7

    Tuesday, August 30, 2016 10:26 AM
  • Luai 7,

    I think a lot of people are going to find that Mobile reports does not meet anything past basic reporting and basic math.  We have discovered some huge bugs in the product and a lot of people like yourselves are going to be running into them sooner or later.  The sooner MS realizes they have a problem, the better.  I'm extremely upset that they released a product in this bad of shape and that we paid $30K to license it.  I wish I'd have gone with some other tried and tested (& working) technology.  MS have really let the BI community down right now.


    • Edited by Phill D Tuesday, August 30, 2016 12:18 PM
    Tuesday, August 30, 2016 12:18 PM
  • I was able to make some headway on the problem with SSAS Models and parameters but it's just more bugs on top of bugs.  Here is the post if your interested.

    https://social.msdn.microsoft.com/Forums/en-US/9b61aa5e-7554-4612-86fb-361049b8667a/huge-mobile-report-publisher-bug-dont-think-about-connecting-to-ssas-model-with-any-parameters?forum=sqlreportingservices

    Friday, September 2, 2016 8:20 PM
  • Hi Guys,

    Is there a solution for this issue??!!!.

    Is there any way that we can use SSAS 2016 Tabular calculated measures in SSRS 2016 Mobile Reports and KPIs because as is now it forces you to select an aggregation and this is not compatible with calculated measures???!!!!

    Luai 


    Luai7


    • Edited by luai7 Tuesday, July 11, 2017 3:56 PM
    Tuesday, July 11, 2017 1:35 PM