SSRS Report Between Two Dates

Question

I'm using SSRS reports in SharePoint 2010. I need to get my report to generate information in between the StartDate and StopDate. Currently it only gives me what was entered on the exact stop date. Where do I put the >= and how? I've tried putting it in the filter, but I get an error that says I can't mix fields and parameters. I have parameters so the user can enter the dates and I have Start_Date and Stop_Date fields.

I apologize in advance. I'm a newbie to SSRS Reporting.

<FieldRef Name="Drug_x0020_Name" />
<FieldRef Name="Inmate_x0020_Last_x0020_Name" />
<FieldRef Name="Start_x0020_Date" />
<FieldRef Name="Stop_x0020_Date" />
<FieldRef Name="ID" />
<FieldRef Name="DiscontinuedDate" />
<FieldRef Name="Prescription_x0020_Type" />
<FieldRef Name="Created" />
<FieldRef Name="InmateID" />
<FieldRef Name="Inmate_x0020_First_x0020_Name" />
<FieldRef Name="Ordered_x0020_Date" />
<FieldRef Name="DrugClassification" />

Thanks in advance! Tara

If you could edit your question to include the SQL for you report, that would allow us to help you more quickly. — R. Richards
Not quite. What I am looking for is the SQL code, or the query, for the main dataset in the report. You can see it by right-clicking on the dataset in the report designer and choosing Query... The code usually starts with the clause SELECT ... . I would need to see all of that. — R. Richards
I don't see the SQL code. All I see is the query that I posted. If I open it in Visual Studio I can pull this out. <Field Name="Start_Date"> <DataField>Start_x0020_Date</DataField> <rd:TypeName>System.DateTime</rd:TypeName> </Field> <Field Name="Prescription_Type"> <DataField>Prescription_x0020_Type</DataField> <rd:TypeName>System.String</rd:TypeName> </Field> <Field Name="Stop_Date"> <DataField>Stop_x0020_Date</DataField> <rd:TypeName>System.DateTime</rd:TypeName> </Field> — TYOzzy
These are the parameters I have, I just need the start date to be less than or equal to and the stop date to be greater than or equal to. <FilterExpression>=Fields!Start_Date.Value</FilterExpression> <Operator>Between</Operator> <FilterValues> <FilterValue>=Parameters!StartDate.Value</FilterValue> <FilterValue>=Parameters!StopDate.Value</FilterValue> </FilterValues> </Filter> — TYOzzy

R. Richards R. Richards · Accepted Answer · 2016-07-05T17:44:46

Here is what a filter should look like in the designer. I don't use Report Builder, but it should have something similar as far as a tablix properties dialog. Of course, the expression field and parameter names will be different for you. Usually a Between filter is inclusive of the beginning and end date, so this may give similar results to what you are getting now. You can always remove the second part of the filter if you want to get everything after the start date.

If you want to overwrite the Filter right in the RDL, here is what that looks like based on the image above. I have changed the names to match what you have posted in the comments.

<Filters>
    <Filter>
    <FilterExpression>=Fields!Start_Date.Value</FilterExpression>
    <Operator>GreaterThanOrEqual</Operator>
    <FilterValues>
        <FilterValue>=Parameters!StartDate.Value</FilterValue>
    </FilterValues>
    </Filter>
    <Filter>
    <FilterExpression>=Fields!Start_Date.Value</FilterExpression>
    <Operator>LessThanOrEqual</Operator>
    <FilterValues>
        <FilterValue>=Parameters!StopDate.Value</FilterValue>
    </FilterValues>
    </Filter>
</Filters>

Editing RDL directly is not the best way to go about this, but you can do it. Just be careful, is all.

Good luck!

SSRS Report Between Two Dates

1 Answers