Filtering Data from a SharePoint List in SSRS Reports

December 22, 2011

I have created a few SSRS reports which pull data from SharePoint lists and while its relatively easy I have come across the same problem a few times. Every time I try and filter my results using dates it never seems to work as I expect.

In this latest scenario I had a custom SharePoint List which had a column for the item start date. All I wanted to do was present the user with 2 date pickers which allow them to set a start and an end date. I then wanted to execute a query using these to return all items where the start date was in between the selected dates.

I generated my CAML query using U2U CAML Query Builder and it worked as expected with some hardcoded dates.

Next I opened Visual Studio and created a new Report Project. I then created a new report using the wizard to help setup a new DataSource of type ‘Microsoft SharePoint List’. I entered the query using the one generated in U2U leaving the values as hard coded to test it was working.

After formatting the report I ran it and all was well. Next I decided to create two new parameters one for the start date and one for the end date. I set the default values to be the first day of the month for the start date and the last day of the month for the end date, see formula for default values below.

Start date default value: =CDate(DateSerial(Year(Now), Month(Now), 1))
End date default value: =DateSerial(Year(Now), Month(Now)+1, 0)

I checked and these displayed on the report preview with the correct default values. At this point I was thinking everything was going too smoothly and I was right. My final step was to update the query to add in the values from the date pickers. I started by adding parameters to the dataset properties and mapped these to my report parameters, see figure 1.

Figure 1
DSProps

Next I went to my query and removed the hardcoded, see figure 2, values and added what I think is the correct way to include parameters, see figure 3.

Figure 2
DSHardCodedQuery

Figure 3
DSParamQuery

As soon as I make this change my report stops returning any results. I have spent time looking around and playing with passing different formats of the dates but no matter what I do it simply doesn’t return any values.

For now as a workaround I have managed to get my report working by not including any filters in my query but by adding a filter on the dataset, see figure 4. While this works I am aware this will probably return all items from the list and then show or hide the rows as needed. If anyone can help with a better solution I would appreciate it.

Figure 4
DSRowFilters


%d bloggers like this: