none
ATOM feed and SSRS parameters

    Question

  • Is there anything I can do in either the .atomsvc service document or in the Microsoft Data Feed connection string to exclude SSRS 2008 R2 parameters from being added to available columns for mapping (Excel 2010 Power Pivot)?

    I noticed there are some obscure ways to customize the Microsoft Data Feed a little bit (eg. "Include Atom Elements", "Included Data Services Content", etc) but I can't find anything specifically about SSRS parameters.

    The parameters are shown very prominently at the front of the available columns and many are not relevant to most of my Excel users and I don't want them to have to manually delete or hide them.  Also, they aren't easily distinguishable from actual report data (content) which is a problem. 

    NOTE:  The parameters should otherwise remain visible for other SSRS-specific reasons.  (I've observed that I can set parameter visibility to "Internal" in the RDL and this fixes the data feed, but it causes other problems.)  Ideally there'd be something (msdn-documented or otherwise) that I could do in the .atomsvc to exclude report parameters.  I haven't found it yet.

    Monday, September 09, 2013 3:55 PM

Answers

  • Hi David,

    I have tested it on my local environment (SQL Server Reporting Services 2008 R2 on Window 8), I got the same results with yours. First I export the report to Data Feed as an .atomsvc service document, and then open it on Power Pivot for Excel. Then the report parameters were added as extra columns on the Power Pivot table. Base on my research, the report parameters will be added as extra columns on the Power Pivot table from .atomsvc service document. We cannot restrict the report parameters to be added as columns. Currently, the only workaround for this issue is that delete the parameters columns manually on the Power Pivot table.

    Thank you for your understanding.

    Regards,
    Charlie Liao

    If you have any feedback on our support, please click here.


    Charlie Liao
    TechNet Community Support

    Wednesday, September 11, 2013 2:39 AM

All replies

  • Hi David,

    I have tested it on my local environment (SQL Server Reporting Services 2008 R2 on Window 8), I got the same results with yours. First I export the report to Data Feed as an .atomsvc service document, and then open it on Power Pivot for Excel. Then the report parameters were added as extra columns on the Power Pivot table. Base on my research, the report parameters will be added as extra columns on the Power Pivot table from .atomsvc service document. We cannot restrict the report parameters to be added as columns. Currently, the only workaround for this issue is that delete the parameters columns manually on the Power Pivot table.

    Thank you for your understanding.

    Regards,
    Charlie Liao

    If you have any feedback on our support, please click here.


    Charlie Liao
    TechNet Community Support

    Wednesday, September 11, 2013 2:39 AM
  • Thanks for the response.  If you discover anything new on this please let us know.

    (I find it hard to understand why someone took it upon themselves to determine that the SSRS parameters always needed to be placed right at the front of the data feed, and they didn't allow for an alternative opinion about that.)

    In my experience, most well-written RDL reports will always display the most important parameters in the body of the report anyway (sometimes after transforming them to make them a bit more esthetically pleasing and/or user-friendly, like transforming a business key into a key-and-name pair).  It is redundant for the atomsvc data feed to automatically show parameters when the developer of the report is also picking and choosing what parameters to display in the report body.

    Thanks again for your help.  At this time my work-around will probably be a prefix in the names of my parameters (eg. "DONOTUSEME_UglyParameter1") so that they are ugly enough to be left alone when they appear in the PowerPivot Window.

    Thanks,

    David


    David Beavon

    Thursday, September 12, 2013 6:42 PM
  • Hi David,

    Thank you for your posting again.

    It seem that all workarounds are not good enough. Personally, I recommend you that submit this suggestion at
    https://connect.microsoft.com/SQLServer/. If the suggestion mentioned by customers for many times, the product team may consider to add the feature in the next SQL Server version. Your feedback is valuable for us to improve our products and increase the level of service provided.

    Thank you for your understanding.

    Regards,


    Charlie Liao
    TechNet Community Support

    Friday, September 13, 2013 1:07 AM