none
Does functionality in Mobile Reports Publisher negate the need for a Tabular Model or Multi-Dimensional Cube RRS feed

  • Question

  • Hi,

    I'm building some reports in the Mobile Report Publisher that's connected to a 2016 Tabular Model.  In order to connect the Timeline navigator in the mobile report to your dataset, you have to create a calculated measure based on one of the date columns in the Dimensional tables (as stated here https://msdn.microsoft.com/en-us/library/mt732283.aspx).

    When I did this, I had to include both the source column and the calculated measure otherwise, the calculated measure would show null.  It also causes duplicated data to appear when I include a column from my date table.  E.G. Every 1 record in my main dimension table would be duplicated for every 1 record that existed in my date table as below.

    Results without Year Name column from date table.

    Results without Year Name

    Results with Year Name Column

    As You can see, the result from the 1st line in the top query is now duplicated for every year that exists in the dates table.

    If I remove the calculated column ReportDate, the problem does not occur, but, ReportDate is needed for the timeline navigator in the Mobile Report Publisher

    As the Mobile report Publisher/Timeline navigator (and many other controls) are capable of producing their own groupings based on year, quarter, month etc. and that most of the controls can also sum/avg/min/max etc, why/should you use a tabular model with an SSRS Mobile Report, or, should you just feed it a flat table?

    Hope this question isn;t an insult to anyone or the community, it's just from the functionality I have seen so far, SSRS Mobile has no need for a tabular model or multi-dimensional cube as it's source.

    Thanks 





    • Edited by Phill D Friday, July 15, 2016 4:45 PM
    Friday, July 15, 2016 4:41 PM

All replies

  • Hi Phill D,

    Based on your screenshots, the duplicated issue can be caused by the way you specified the date table. As we can see in the second screenshot, there exists a row with Total Ticket Cost value and it is corresponding to the Year_Name of Calendar 2000 which is right for the Pull Date 7/7/2000. But in other Calendar years, there also exists 7/7. That’s why the first line will be duplicated. So to resolve this issue, I would suggest you re-specify the date table and make it corresponding to the year in the value of Pull Date.

    Then it is not necessary that we should always use a tabular model or SSAS cube as a data source for mobile reports. Generally, there’re two kinds of data source you can use to create a mobile report. Local Excel files and SQL Server 2016 Reporting Services (SSRS) shared datasets. In SSRS shared datasets, we can specify all the type of data sources which SSRS supported and use them in mobiles reports.

    Reference:

    Data for Reporting Services mobile reports

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

    Monday, July 18, 2016 5:59 AM
    Moderator
  • The date table contains a unique record for every day between Jan 1st 2000 and 31st Dec 2039 and was created for SQL 2014 Report builder because any date passed in as a parameter must exist in the results set.  I'm sure you understand a lot more about it than I do, but it's because SQL 2014 Report Builder uses STRTOMEMBER as part of the parameter processing if the date doesn't exist, the report errors out.  So with that said, I can't re-specify the date table with just the pull dates that exist in the main table because it would break other reports where date parameters are entered by the user.
    I don't understand your suggestion that the data is being duplicated because of "7/7"  It's a date, so there is obviously going to be other dates with "7/7/" as part of the date string.  I don't understand how SQL would be confusing or combining 7/7/2000 with any other date.  That just doesn't make sense to me.

    Here is a screen shot of the date table for clarification.

    and here is a screen shot of the relationship

    If there's anything I've missed out, please let me know, but the problems I'm running into seem to be a result of the way models have to be designed in order to make them function with SSRS.

    Monday, July 18, 2016 2:05 PM
  • Hi Phill D,

    I have found a work around that we can retain date formatting for SSAS tabular model date type data. So that you don't need to combine another date table with your main dataset.

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

    Thanks,

    Xi Jin.

    Friday, July 29, 2016 9:10 AM
    Moderator
  • Hi Phill

    Could you solve the problem? I have the same issue

    Tks

    Patricio

    Thursday, August 4, 2016 8:55 PM
  • Unfortunately not. I really believe it's a fundamental issue with trying to connect mobile reports to an SSAS Model/Cube.

    The only way I know to get around the problem is to base the model on 1 table.  In doing so, it almost defeats the purpose of having a model in the first place.  Also, if you do that, you're probably going to end up maintaining 2 versions of the same model, one for Mobile Reports and another for functionality like Excel pivot or for paginated reports.

    I was hoping to get Xi Jin's attention on the problem as he seems to be associated with the Mobile Reports product.

    At some point, they will get enough complaints that they have to address it, but it's a huge problem right now and they are having other issues to with parameters.

    Friday, August 5, 2016 8:26 PM