Apply filter on parameter in SSRS report

Question

I am creating SSRS reports using SharePoint list in BIDS. I have two fields viz. Name, Time&Date, both are string parameters. I am able to get SSRS report however I want to let user to get reports using filter on Time&Date parameter. User should be able to select start and end date to get the results. Do I need to edit Caml query for this or this can be achieved using UI? Please suggest.

Updated-------------------

I have used below Caml Query

<RSSharePointList xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance" xmlns:xsd="http://www.w3.org/2001/XMLSchema">
   <ListName>Test_FAMM</ListName>
   <ViewFields>
     <FieldRef Name="Document_x0020_Name" />
     <FieldRef Name="Time_x0020_of_x0020_Upload_x005c" />
   </ViewFields>
<Query><Where><And><Gt><FieldRef Name="Time_x0020_of_x0020_Upload_x005c"/><Value Type="Text" IncludeTimeValue="FALSE">
'+'@StartDate'+'</Value></Gt><Lt><FieldRef Name="Time_x0020_of_x0020_Upload_x005c"/><Value Type="Text" IncludeTimeValue="FALSE">'+'@EndDate'+'</Value></Lt></And></Where></Query>
</RSSharePointList>

However, the query doesn't fetch me any data. Also, Time_x0020_of_x0020_Upload_x005c is of text type....

Hannover Fist Hannover Fist · Accepted Answer · 2015-03-30T20:41:17

You can either use the parameter in the query as Samuel mentioned or you can add it to the Filter section of the Dataset or table.

It's best to use it in the query when possible because it reduces the amount of information that has to be transferred from the server the data is on to the Report Server and cuts down on the time needed to render the report since the Report Server doesn't have to spend the time doing the filtering. Add the parameters to the query, then in the Filter tab of your dataset add your parameter and select the matching parameter in the query with the drop down menu.

You could just use your parameters in the Filter tab of the dataset. Open your dataset and click on the Filters tab. If your field is a date field, select your date field in the Expression, change the Type from Text to Date/Time and the Operator to Between. For the first Value, choose your Start Date Parameter you created in the Report Parameters and your End Date as the second Value.

Apply filter on parameter in SSRS report

1 Answers