SharePoint List Connection Type (SSRS) with no Default Sharepoint View

Question

Hi,

I am getting my data via the new Reporting Services 2008 R2 Sharepoint List Data Connector - this works. However, the data being returned is subject to any Filters defined on the Sharepoint Current Default View.

This is really a big problem as often you want to report on the entire dataset but the default view seen by Sharepoint Site Users often does not have to include certain items.

An example is Reporting on a "Service Request" List.. Once a service request has been marked as closed the site users really don't have to see these service request anymore but it should still be possible to report on the closed service requests.

In my opinion Connector should pass all list data to the Reporting Server who should then do the processing.

Please tell me there is a workaround or is this a bug?

Regards,

Albert

Answer 1

Hi Albert,

We can use the Filter the filter the un-used items.

For more information, please see:
SharePoint List Query Designer (Report Builder 3.0): http://msdn.microsoft.com/en-us/library/ff519559.aspx

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

Thanks,
Jin Chen

---

Jin Chen - MSFT

Answer 2

That does not answer Albert's Question in anyway. He did not ask how to filter the items - his question was quite the contrary... how to unfilter items that have been filtered by the list's default view setting.

I am in a similar conundrum, where the default view only shows me tasks assigned to me; but the report needs to be able to view historical data that is not necessarliy present in the default view.

Albert, did you figure this out?

UPDATE: It would seem that Jinchun was correct after all. If no parameters are specified, the data is returned from the default view. If the underlying SharePoint data is required, then one must specify atleast one query parameter. In my case, I wanted to return all items in the list, so I had to create a dummy query filter: Created > 1900-01-01

Strange behaviour, but works.

Answer 3

How can I do this in SSRS 2008?

Answer 4

First, Yusuf, thanks for posting this and your update.

This is so counter-intuitive and poorly documented that it's no wonder it's so hard to find this info. I'm replying to include some terms that will hopefully be picked up by a search engine.

It's counter-intuitive because

1) for decades filtering has connoted selection of rows at the client (not in the where clause on the server) and because

2) It's hard to believe that specifying a filter expression causes NO view to be used - so you can get more data than the default view itself returned

but it works and it works well. I wish it was documented better!

For search:

How to specify a view when selecting data from a SharePoint list in SSRS 2008 R2 SQL 2012