SSRS Report Between Two Dates

ONLINECAST

2 min read

·

07-10-2024

5

0

Share

How to Filter Your SSRS Reports by Date Range

Ever needed to extract data from your SQL Server Reporting Services (SSRS) reports within a specific date range? Filtering reports by dates is a common requirement for analyzing trends, understanding performance, or simply focusing on specific periods. This article will guide you through the process of implementing this feature in your SSRS reports.

The Scenario: A Sales Report

Imagine you have a sales report that displays daily sales figures. You want to be able to view the sales data for a specific week, month, or even a custom date range.

Here's how your existing report might look, without date filtering:

SELECT
    OrderDate,
    ProductName,
    QuantitySold,
    PricePerUnit
FROM
    SalesData

Implementing Date Filtering in Your Report

Let's add the functionality to filter this report by a date range. We'll do this by using parameters in SSRS.

1. Create Parameters:

   • Go to the Report Data pane in SSRS.
   • Right-click and choose "Add Parameter."
   • Name your parameters: StartDate and EndDate.
   • Set the data type for both parameters to "Date/Time."

2. Modify Your Query:

   • Adjust your SQL query to include the parameters within a WHERE clause:

   SELECT
       OrderDate,
       ProductName,
       QuantitySold,
       PricePerUnit
   FROM
       SalesData
   WHERE
       OrderDate BETWEEN @StartDate AND @EndDate
   

3. Add Parameter Controls:

   • Go to the Report Design view.
   • Drag and drop two "Text Box" controls onto your report.
   • Set the properties of each textbox to:
     • Value: =Parameters!StartDate.Value for the first textbox.
     • Value: =Parameters!EndDate.Value for the second textbox.
   • Add labels for each textbox (e.g., "Start Date:" and "End Date:").

4. Set Parameter Values:

   • Go to the Report Properties window.
   • Click on "Parameters."
   • In the "Available Values" section for each parameter, choose "Specify values."
   • You can add default values or allow users to enter their own dates.

Additional Considerations:

• Date Formats: Ensure consistent date formats for your parameters, your data, and your report display.
• Default Values: Set default parameter values for a more user-friendly experience.
• Validation: Implement validation to prevent users from entering incorrect dates.
• Time Component: If your data includes time stamps, consider using the >= and < operators for more accurate filtering.

Conclusion

By implementing parameters and adjusting your SQL query, you can easily add date filtering to your SSRS reports. This allows you to analyze your data within specific timeframes and gain valuable insights. Remember, date filtering is a powerful tool that can significantly enhance your SSRS report's functionality.