Filtering Data from a SharePoint List in SSRS Reports

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

Advertisements

5 Responses to Filtering Data from a SharePoint List in SSRS Reports

  1. sc says:

    Did you get this working? I am looking to do the same thing.

    • raymondlittle1000 says:

      I was able to get my report to show the correct data using the dataset filtering method but I wasn’t able to find the correct way to filter the results via the Query.

  2. NigelH says:

    Might be a little late, but instead of:
    @StartDate etc., try:

    “StartDate” should then appear in the Parameters tab to be linked to your report parameter and it will then filter as required.

    Took some puzzling over, but I eventually figured it out by putting a valid value into the Applied filters pane of the Query Designer dialog *and* ticked the parameter checkbox. That creates the required XML and puts the dummy value into the report parameter definition as the default. You can then go and delete the default from the report parameter.

    • NigelH says:

      Sorry, lost the XML – not unreasonably, I guess. Pretend the following #s are angle braces:

      Instead of:
      #Value Type=`DateTime`#@StartDate#/Value#

      Use:
      #Value Type=`DateTime`#
      #Parameter Name=`StartDate`/#
      #/Value#

      • raymondlittle1000 says:

        Hi Nigel,

        That project was completed a while ago but thanks for the response at least I can try it in the future.

        Cheers,

        Ray

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out /  Change )

Google+ photo

You are commenting using your Google+ account. Log Out /  Change )

Twitter picture

You are commenting using your Twitter account. Log Out /  Change )

Facebook photo

You are commenting using your Facebook account. Log Out /  Change )

w

Connecting to %s

%d bloggers like this: