none
HUGE Mobile Report Publisher BUG. Don't think about connecting to SSAS Model with any parameters!! RRS feed

  • Question

  • Please read this entire post before commenting.  I have been trying to get some kind of acknowledgement from Microsoft on this problem in other posts but finally just needed to make this known.  Mobile Report Publisher cannot connect to parameterized datasets if it's connected to an SSAS Tabular Model.

    I keep getting half responses like "update to latest mobile reports publisher" or "specify a default parameter" but I'm telling you, the functionality is broken!

    I have a dataset that is connected to an SSAS Tabular model.  The model works fine in MS Excel or in SQL Report Builder 2014.
    I have created a dataset using Report Builder 2016 and added a parameter to it.  We need the parameter so the report can be filtered before it is rendered to the client.  For now, I am leaving the parmeter blank to fully demonstate the problem

    The Operator Name is just a customer name.

    When I try and connect the Mobile Report to this dataset, I get the following error:

    I then add a default value for the parameter.

    I run the query and data is returned in the designer.

    When I try to connect Mobile Report Publisher to the Dataset, I now get this error.

    MS, can you please explain why this doesn't work.  This has to be a bug and a huge one at that!

    Monday, August 29, 2016 3:33 PM

Answers

  • Hi Phill,

    I'm on the Reporting Services product team, and appreciate your feedback here.  We've been aware of some of the issues you point out in Report Builder for some time, and they should be addressed in the next update of Report Builder that is going out here in the next few weeks.  This includes the fixes for being able to open a shared dataset in Mobile Report Publisher that has multi-value parameters enabled, plus having the shared dataset re-open in Report Builder in design mode when you've saved it to the server in design mode. 

    That being said, the way the Mobile Report Publisher works is based heavily on the existing Datazen technology that was integrated, so the MDX statement that needs to be passed for a multi-value parameter will require some manual editing of the underlying query to make sure it works as expected for your reports.  I agree this isn't ideal, and we're looking at ways to address this in a future update.  For now, the team is going to be making sure we update the MSDN documentation to be more specific about the steps involved in this scenario, in addition to doing a similar TechNet article like we did for Datazen that walks through the entire scenario in detail.  We are looking to have that article published in the next 1-2 weeks.

    I know this isn't the exact answer you'd probably like to hear, but we do monitor these forums and provide feedback/answers and are committed to addressing issues as the arise.  So keep the feedback coming, good and bad.

    Thanks,

    Chris Finlan

    • Marked as answer by Phill D Thursday, September 8, 2016 4:33 PM
    Tuesday, September 6, 2016 9:27 PM

All replies

  • I can't believe that no one can verify this issue given that a lot of reports must be powered by cubes in this day and age.

    What this problem means is that you cannot pre filter a report before it's displayed on screen.  So, imagine if you were trying to add a mobile report to something like extranet or customer portal and you wanted to allow customers to utilize the dashboard technology.  Well due to this bug, you would have to let customers see each others data because you can filter it out.  That's obviously not an acceptable solution so it means dashboard reports cannot be used in any solution where the initial data is sensitive or must be pre-filtered.

    • Edited by Phill D Tuesday, August 30, 2016 5:09 PM
    Tuesday, August 30, 2016 5:07 PM
  • Hi Phill,

    First I have made some test in my local environment (Reporting Service 2016 version 13.0.2149.0). I can pass values for the parameter in the tabular model DataSet. Please refer:

    To troubleshoot your issue, first please try to Preview the dataset Data at Web Portal side by clicking the Data Preview option. Ensure that it can return records. If not, please choose edit in Report Builder again, see if the default parameter values setting persists. 

    Also, I have to say that I once encountered your issue, as specified the parameter's default value for the dataset. However, when deployed it to Web Portal, it lost the settings automatically. After installing the CU1, I can create the dataset with parameters successfully. So if your SSRS 2016 version is older, you can try to upgrade it to CU1  

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

    Thanks,

    Xi Jin.

    Wednesday, August 31, 2016 8:24 AM
    Moderator
  • Hi,

    Can you share the text representation of the query, please?


    Thanks, Andrew
    My blog...

    Wednesday, August 31, 2016 8:59 AM
  • I am checking on the update right now and will see if that fixes the problem.
    Wednesday, August 31, 2016 1:36 PM
  • The SQL server is already updated to the latest version so the CU update does not have any applicable updates.

    I also downloaded the latest version of the report builder and Mobile Report Publisher but they are the same version I already have installed.

    I attempted to perform the dataset preview you suggested but it errors out on the report server like so.

    Here is a screenshot of the dataset in the Report Builder Query designer with the default parameter supplied. I also tried replacing the default parameter All with an actual Operator name but it resulted in the same error as above.

    Here is the text of the query, it is generated by the query designer.  It's just a simple query I'm using to demonstrate the problem.

     SELECT NON EMPTY { [Measures].[Pull Count] } ON COLUMNS, NON EMPTY { ([Well Pulls with Service].[Operator Name].[Operator Name].ALLMEMBERS ) } DIMENSION PROPERTIES MEMBER_CAPTION, MEMBER_UNIQUE_NAME ON ROWS FROM ( SELECT ( STRTOSET(@WellPullswithServiceOperatorName, CONSTRAINED) ) ON COLUMNS FROM [Model]) CELL PROPERTIES VALUE, BACK_COLOR, FORE_COLOR, FORMATTED_VALUE, FORMAT_STRING, FONT_NAME, FONT_SIZE, FONT_FLAGS

     

    Wednesday, August 31, 2016 2:14 PM
  • Hi,

    It looks like this to me:

    https://social.msdn.microsoft.com/Forums/sqlserver/en-US/c30bf64b-c7e3-4bfc-a955-779afa4bd8bc/query-perparation-failed-report-designer-trying-to-order-by-time-dimension?forum=sqlanalysisservices



    Thanks, Andrew
    My blog...

    Wednesday, August 31, 2016 3:56 PM
  • I created the query using Report Builder 2016 query designer. I just dragged and dropped the columns from my model to the designer grid and saved the query. Due to yet another bug in Report Builder, when you re-open the query in report builder, you are no longer able to edit the query in the designer because Report Builder breaks the syntax. We've been waiting on a fix for this for about 2 months. Not sure why it is taken MS so long to get round to it. It's extremely frustrating when you have multiple problems preventing you from using something you paid $30K for. 
    Wednesday, August 31, 2016 8:05 PM
  • I read the post but I don't see how it relates to my problem.  Can you be more specific on what you think the problem is?
    Wednesday, August 31, 2016 8:06 PM
  • I have been able to get an SSAS Tabular Model with Parameters to connect to a Mobile Report but it's a ridiculous process and is plagued with bugs. At this point, it's not the Mobile Report Publisher that is broken, it's the Dataset designer in the Report Builder.

    Thanks to the screenshots from Xi Jin which led me to discover the problem.

    1st issue, when in query designer mode, the parameters option is greyed out so it makes you believe the query designer has handled them but it hasn't.

    When I switch to text mode, the parameters option becomes enabled.  After opening them, the values aren't fully populated.

    The next problem is the multiple values option that isn't shown on XI Jin's screenshot but was enabled in my parameters.  After I disabled the option and saved the dataset, the Report site was able to load data from the Dataset.

    The next bug is if you close the dataset in the Report Builder and try to re-open it, you can't access the parameter options.  Also, I don't think the MDX statement that is shown is correct.

    So this just reinforces my original statement, this product is unbelievably buggy and there is still no recognition or known plans to fix this stuff.  Other users have started to comment on my other posts and are also unhappy with the amount of bugs.  What looks like a great product on the surface is just broken under the hood.  There is another major bug where creating the date measure for the date navigator to work result in incorrect data being shown in the reports when the Model has multiple tables.

    To get around the date problem, we have to create a model specifically for the mobile report where the content is based on 1 table.  It completely defeats the purpose of using a model or being able to share the same model between mobile report publisher, Paginated Reports & Power Pivot.  It means you do not have a single point of true because the data can be different between the different models and information that is available.

    Friday, September 2, 2016 8:02 PM
  • Hi Phill,

    I'm on the Reporting Services product team, and appreciate your feedback here.  We've been aware of some of the issues you point out in Report Builder for some time, and they should be addressed in the next update of Report Builder that is going out here in the next few weeks.  This includes the fixes for being able to open a shared dataset in Mobile Report Publisher that has multi-value parameters enabled, plus having the shared dataset re-open in Report Builder in design mode when you've saved it to the server in design mode. 

    That being said, the way the Mobile Report Publisher works is based heavily on the existing Datazen technology that was integrated, so the MDX statement that needs to be passed for a multi-value parameter will require some manual editing of the underlying query to make sure it works as expected for your reports.  I agree this isn't ideal, and we're looking at ways to address this in a future update.  For now, the team is going to be making sure we update the MSDN documentation to be more specific about the steps involved in this scenario, in addition to doing a similar TechNet article like we did for Datazen that walks through the entire scenario in detail.  We are looking to have that article published in the next 1-2 weeks.

    I know this isn't the exact answer you'd probably like to hear, but we do monitor these forums and provide feedback/answers and are committed to addressing issues as the arise.  So keep the feedback coming, good and bad.

    Thanks,

    Chris Finlan

    • Marked as answer by Phill D Thursday, September 8, 2016 4:33 PM
    Tuesday, September 6, 2016 9:27 PM
  • Chris,

    It's nice to finally have someone verify that these are known issues and that they are being worked on.  The issue with multi valued parameter will definitely affect us based on what we are trying to build but I don't understand the problem.  I know the mobile report writer is a Datazen product, but the underlying dataset is still an MDX query and the 2014 Report Builder is able to handle the syntax just fine without manual editing.  If the mobile report is just reading the data from the initial query, I don't understand why the 2016 Report Builder can't build or handle the Syntax Correctly when Report Builder 2014 can.  You would assume the 2014 & 2016 versions would handle the Syntax the same and the Mobile Report Builder would be adapted to work with the query?

    I checked the Microsoft connect site but I swear when I checked it last week SQL 2016 was not open for bug submission.  I also now see a post relating to bugs with SSAS parameters too.

    I do like what Microsoft is attempting to do with the Mobile Report Product, but the goal of self-service BI is still a very long ways off given the current issues & limitations. 

    Wednesday, September 7, 2016 3:08 PM
  • Hi Phill,

    You're right, I don't think it's unrealistic to assume Mobile Report Publisher would work with that as it is designed in the query designer.  :)

    Like I said, it isn't an issue with Report Builder, but rather how mobile reports interprets that MDX that is generated by the query designer.  We're looking to add the property that needs to be exposed to both Report Builder and SQL Server Data Tools to mitigate this issue without having to edit the MDX by hand - this will also allow us to address the current limitation with how date fields are passed and interpreted by the mobile report publisher.  We'll be providing additional details when this is complete on the Reporting Services blog and what the updates to Report Builder are as we have more regular releases.

    Thanks,

    Chris

    Thursday, September 8, 2016 11:49 PM
  • Hi Chris,

    Thank you for admitting having these issues and that they will be addressed by development team very soon.

    Kindly, please forward also the following issues so as to be addressed by the SSRS Mobile Reports team:

    * Inability to use SSAS Tabular Model Calculated Measures in Mobile Reports, specially in Gauges!!

    * Inability to set Top(N)/Bottom(N) based on a specific measure or calculated measure on category charts!!

    * Inability to use the SSAS Tabular Model date measure for Slider Control!!

    The above are core BI features that should be incorporated in any BI tool..

    Thank you


    Luai7


    • Edited by luai7 Saturday, November 12, 2016 2:41 PM
    Wednesday, October 26, 2016 8:58 AM
  • * Inability to set Top(N)/Bottom(N) based on a specific measure or calculated measure on category charts!

    +1 on this.  My "evaluation" of Mobile Reports ended after about 5 minutes because of this.  The tool is unfinished without it.

    Friday, November 11, 2016 5:18 PM
  • Hi Chris,

    Has the issue been fixed yet? I ran a trace to see what SQL Server Mobile Report Publisher is actually executing. The parameter that gets plugged into STRTOSET is causing the issue. It is sending the parameter as just the value, while my Shared Dataset is expecting it to be formatted as MDX. See my **bold** example below:

    <Parameters xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance" xmlns:xsd="http://www.w3.org/2001/XMLSchema" xmlns="urn:schemas-microsoft-com:xml-analysis">
            <Parameter>
              <Name>DimDateCalendarYear</Name>
              <Value xsi:type="xsd:string">2017</Value>  **Need to format like: [DimDate].[Calendar Year].[2017]**
            </Parameter>
            <Parameter>
              <Name>DimDateMonthName</Name>
              <Value xsi:type="xsd:string">September</Value>  **Need to format like: [DimDate].[Month Name].[September]**
            </Parameter>
            <Parameter>
              <Name>DimHearingTypeHearingTypeName</Name>
              <Value xsi:type="xsd:string">[DimHearingType].[Hearing Type Name].[All]</Value>
            </Parameter>
            <Parameter>
              <Name>DimHearingResultHearingResultName</Name>
              <Value xsi:type="xsd:string">[DimHearingResult].[Hearing Result Name].[All]</Value>
            </Parameter>
          </Parameters>


    Thanks,
    Mike Albers


    • Edited by MikeAlbers23 Friday, September 22, 2017 6:28 PM not sure if BOLD worked, so wrapped **what i want to bold**
    Friday, September 22, 2017 6:25 PM
  • One more thing I am seeing. The MDX query executes fine with just the value, ONLY when the value is an actual String.

    For example September works great, but 2017 breaks the query causing an error: "Query (18, 12) The restrictions imposed by the CONSTRAINED flag in the STRTOSET function were violated."

    So to test this, I changed my raw data in the DimDate table to be W017 instead of 2017, then processed the dimension, and now the Mobile Report works. Also tested the query directly in SSMS and here is the relevant code:

    ==========================================================

    ...    ( SELECT ( STRTOSET('September', CONSTRAINED) ) ON COLUMNS FROM
    ( SELECT ( STRTOSET('2017', CONSTRAINED) ) ON COLUMNS FROM

    causes the above error.

    ==========================================================

    ...    ( SELECT ( STRTOSET('September', CONSTRAINED) ) ON COLUMNS FROM
    ( SELECT ( STRTOSET('W017', CONSTRAINED) ) ON COLUMNS FROM

    works fine.

    ==========================================================

    Thanks and hope to hear back soon,
    Mike Albers

    Friday, September 22, 2017 6:45 PM
  • I gave up on the product because of the bugs I kept running into so I guess they didn't fix it.
    Friday, September 22, 2017 7:36 PM
  • Everything about Report Builder has always been astoundingly buggy. The entire product is incredibly badly designed and needs to be thrown out and rewritten from scratch.
    Monday, March 4, 2019 9:39 PM