How to implement Optional Parameters filtering using SSRS report against Sharepoint List

Question

I wonder if I can create conditional Caml filtering within the report itself.. to modify the Caml query based on the filter parameters provided.

If not, then I'm seeking advices on the best method to create SSRS reports within SP2010 that can do that.

Thanks

Answer 1

This is the way to do such thing

([Parameter1] = "" or Field1 contains [Parameter1])
and
([Parameter2] = "" or Field2 contains [Parameter2])

thanks to Junlasak

Answer 2

Old one but I lost couple of hours on it, so here it goes:

(Just to clarify, the problem as I understand it is that report parameters used on SharePoint CAML queries cannot be optional; AFAIK you cannot implement the pattern "@parameter IS NULL OR column = @parameter" we use in SQL.)

1. Create your dataset retrieving all records from SharePoint.

   • You have to be carefull on retrieving more records that the limit SharePoint imposes on queries (default is 5K items on SharePoint 2013+). Did not test it to see how it behaves. Ideal would be to add non-optional parameter to minimize or eliminate this possibility.

2. Create a report parameter that accepts blanks, and has a blank (empty string in SSRS) as the default value.

3. Add a filter to the (Details) row group (tab View > Grouping to show the Row and Column Groups panes, Row Group > (Details) context menu > Group Properties > Filters > Add):

   • For "Expression" click on "fx" button and add something like
     =Iif(Parameters!Client.Value = "", 1, Fields!Client.Value)
   • For "Value" use something like
     =Iif(Parameters!Client.Value = "", 1, Parameters!Client.Value)

So as the parameter accepts blank and has blank as the default value, when the report is first loaded all items will be shown, since on the filter both Expression and Value will evaluate to "1". If the user provides a value on the "Client" parameter textbox, Expression will evalute to Fields!Client.Value, Value will evaluate to Parameters!Client.Value and the items will be filtered to clients corresponding to the value the user provided. To return to full listing you just clear the value on the Client parameter textbox. The example is assuming "=" as the filter operator, but it can be used with "Like"; just add "*" on the Value expression as you need.