SSRS REPORT using hour ,day and month selection , I have hourly data

Question

I have SQL table having hourly data for some Temperature & pressure , wanted to show same data by hourly , daily & monthly selection from SSRS REPORT

Answer 1

you need to create a parameterized SQL query for the dataset and design the report layout

I will give you an example query just create yours like this one

DECLARE @TimeAggregation NVARCHAR(10) = @AggregationParameter;

WITH CTE AS (
    SELECT
        DATEADD(HOUR, DATEDIFF(HOUR, 0, [DateTime]), 0) AS Hour,
        DATEADD(DAY, DATEDIFF(DAY, 0, [DateTime]), 0) AS Day,
        DATEADD(MONTH, DATEDIFF(MONTH, 0, [DateTime]), 0) AS Month,
        [Temperature],
        [Pressure]
    FROM
        YourTableName
)
SELECT
    CASE
        WHEN @TimeAggregation = 'Hourly' THEN Hour
        WHEN @TimeAggregation = 'Daily' THEN Day
        ELSE Month
    END AS DateTime,
    AVG([Temperature]) AS AvgTemperature,
    AVG([Pressure]) AS AvgPressure
FROM
    CTE
GROUP BY
    CASE
        WHEN @TimeAggregation = 'Hourly' THEN Hour
        WHEN @TimeAggregation = 'Daily' THEN Day
        ELSE Month
    END
ORDER BY
    DateTime;

1. Open SQL Server Data Tools (SSDT) and create a new report project.
2. Add a new report to the project.
3. Set up the data source to connect to your SQL Server.
4. Create a new dataset for the report
5. Create a report parameter named AggregationParameter with three available values: "Hourly", "Daily", and "Monthly". Set the default value as per your requirement
6. Bind the AggregationParameter to the dataset parameter @AggregationParameter.
7. Design the report layout

Answer 2

Hi @Rahul Patil

What you mean by showing the same data by hourly, daily and monthly options, I don't particularly understand. Do you want to set the parameters in SSRS? You can refer to this link: https://learn.microsoft.com/en-us/sql/reporting-services/report-design/report-parameters-report-builder-and-report-designer?view=sql-server-ver16.

You can provide more detailed instructions and information so that I can test it for you.

Best regards,

Aniya