locked
PowerPivot and Odata feed with filter RRS feed

  • Question

  • When I use PowerPivot against database , in the PowerPivot filter options , I'm able to add my filters. When I use PowerPivot against Odata feed , filter options are not enabled. Do I have to do anything on the Odata feed service to enable this options ? Or How do I add Filter parameters in the Odata feed url in the PowePivot data configuration wizard ?

    Thanks

    Thursday, January 5, 2012 1:09 PM

Answers

All replies

  • Hi WCFDataServices,

    Here you mentioned Filter, could you please be more specific about this? Is it the filter when import the data from external datasource into PowerPivot? Or the filter on the PivotTable or the Report Filter pane?

    Thanks,
    Challen Fu


    Please remember to mark the replies as answers if they help and unmark them if they provide no help.

    Friday, January 6, 2012 11:28 AM
  • Hi Challen,

           I'm refereing to the filter when you import data . When I use SQL server data source, during the import time I can click the dropdown arrow next to each column and set my filters.

     When I use Odata feed as a data source , there is no drop down list next to each column for me to filter the data. Also I'm not able to use the Odata url with filter on the url for eg.

     http://odata.netflix.com/v2/Catalog/Titles()?$filter=((AverageRating ge 4.0) and (Instant/Available eq true)) and (Type eq 'Movie')&$orderby=AverageRating&$select=Type,Name,AverageRating

    Friday, January 6, 2012 6:02 PM
  • Hi WCFDataService,

    Based on my test, it works very well in local environment, you can see below image, I can click the filter drop-down list.  

    My computer have installed the SP1 for Office 2010, so I would recommend you install this services package at this link http://www.microsoft.com/download/en/details.aspx?id=26622 

    Besides, I still recommend you reinstall the add-in for Excel, if it still does not work, please feel free to let us know.

    Thanks,
    Challen Fu

     


    Please remember to mark the replies as answers if they help and unmark them if they provide no help.

    Tuesday, January 10, 2012 6:15 AM
  • Challen,

     

        Thanks for your response. I have SP1 as well I reinstall the power pivot, still I'm not able to filter Odata feed data service tables. If I connect to any database ,I can filter as  expected.

     

    Thank you

     

    Wednesday, January 11, 2012 9:04 PM
  • I'm encountering the same issue.

    I'm connecting to a WCF data service and cannot filter in the PowerPivot wizard.

    As the service can return large quantities of records a filter is a real necessity.

    Timo

    Friday, January 13, 2012 7:44 AM
  • Hi WCFDataservice,

    I cannot reproduce this issue in my local environment, could you please share your file with me, I want to further troubleshoot the issue to find the root cause. If possible, you can upload your file at https://skydrive.live.com/ then give the file's URL is ok.

    Thanks,
    Challen Fu


    Please remember to mark the replies as answers if they help and unmark them if they provide no help.

    Wednesday, January 18, 2012 6:17 AM
  • Challen ,

     

    I think you are talking about the filter , after the odata feed is download to the excel spreed sheet and then applying filter. I'm talking about applying the filter before the data is downloaded from the odata feed using "Preview&Filter" option.

      http://odata.netflix.com/Catalog/ in power pivot data feed window(used titles entity). In the data import wizard I want to apply some filters using "Preview & Filter" options button ,before the data is downloaded.

    In the wizard there is no filter down arrow button next to each column to define the filters. If I use the direct sql server connection, I can see the data filter drop down arrows next to each and every columns.

    Eventhough the power pivot  is cabable enough to download lots of data , I want to apply the filter even before the data import begins.

    I took the screen shot of my wizard , here is the link for the image

     

    http://db.tt/uR1OIxJQ

     

    Thanks

     

     

     

     








    Friday, January 20, 2012 9:47 PM
  • Hi,

    I have the same problem here. I think the filter functionality is not implemented for the OData feed import.

    Jürgen

     

    Monday, January 23, 2012 7:31 AM
  • Hi WCFDataService,

    Thanks for your detail information, yes I thought about the filter , after the odata feed is download to the excel spreed sheet and then applying filter. you are talking about applying the filter before the data is downloaded from the odata feed using "Preview&Filter" option. Sorry for misunderstanding your meaning, now I can reproduce this issue, it seems that this is a known issue, here I would recommend you submit a feedback to Microsoft Connect at this link at this link https://connect.microsoft.com/SQLServer/Feedback This connect site is a connection point between you and Microsoft. Your feedback enables Microsoft to make software and services the best that they can be, Microsoft might consider to add some fix to solve this issue

    Regards,
    Challen Fu


    Please remember to mark the replies as answers if they help and unmark them if they provide no help.

    Tuesday, January 24, 2012 5:16 AM
  • Challen,

      After all the detailed discussion about this issue , now we have to open this issue in Connect ???. I thought WCF data Service team  uses this forum to monitor all the activities.

    Really surprised with all the duplicate effort you have to make to get an answer.

      

    Monday, February 13, 2012 2:42 PM
  • Hi WCFWebservice,

    Actually this is SQL Server PowerPivot for Excel forum not WCF data services forum.

    After your detail information, I did reproduce this issue, there are still other communities who can reproduce this issue, so It appear to me this is a known issue, I have submit this feedback for you to the product connect group site

    https://connect.microsoft.com/SQLServer/feedback/details/724823/import-odata-feed-datasource-into-powerpivot-without-filter

    you can give it more weight by voting on it. Thanks for your understanding.

    Regards,


    Challen Fu

    TechNet Community Support

    • Marked as answer by Challen Fu Tuesday, February 28, 2012 8:20 AM
    Tuesday, February 14, 2012 2:06 AM
  •   

        Challen Fu,

                Thank you so much for your effort to help the community. I will followup this in the connect.

        Regards

     

    Tuesday, February 14, 2012 12:53 PM