How to filter an OData feed before the data gets imported

Question

How is it possible to filter an OData feed before the actual data gets imported?
I have a huge list of data which I wanted to filter for year. OData has some nice capabilities to filter data via querystring.
For example: .../listdata.svc/Bookings?$filter=year(ReceivedDate) eq 2012
But I see no way to use this filter with PowerPivot.

Is there a way to get only the filtered data? If I retrieve all the data the size of  the Excel file gets extremely large, containing data that I'm not interested in.
I'm using the SharePoint OData feed so I don't have much control over the returned results.
I've never used "SQL Server PowerPivot for SharePoint", but if there exists a solution I'm willing to looking into it.

Answer 1

Hi Tobias,

It seems you can use the workaround to this requirement, you can first load all the data into PowerPivot, then leverage filter to filter some records. We cannot use "Preview&Filter" option currently,there are still other communities who can reproduce this issue, It seems this is a known issue.

We have submit this feedback 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.

---

Challen Fu

TechNet Community Support

Answer 2

The problem with this solution is, that all the data gets saved into the Excel file and the file gets very huge. We have a list of 7000 items and the documents is more than 20 MB big. Saving this onto SharePoint is not a great idea.

In the meantime I have written a workaround, by writing my own OData service which itself calls the SharePoint OData service, but provides service operations that filter the data by year.

By not providing a query string PowerPivot can call this Service. It's a ugly workaround, but it works!

[WebGet]
public IQueryable<BookingItem> BookingsInYear2012
{
    return from b in this.CurrentDataSource.Bookings
           where
                b.ReceivedDate.Value.Year == 2012
           select b;
}

Answer 3

Thanks for sharing your workaround, it must be helpful to other communities.

---

Challen Fu

TechNet Community Support