CODEWITHSUNDEEP
sql-serverreporting-servicesssrs-tablix
Whistler
Whistler

Reputation: 1977

SSRS. How to group in a group?

I have SSRS report like below with Boolean parameter to show 12h view or 24h view. To fit report into single screen the 24h report need to group by every 2hr.

          07:00 08:00 09:00 10:00 11:00 12:00 13:00 14:00 ...
Line 1     25    30    24    26    25    25    30    30   ...

          08:00 10:00 12:00 14:00 ...
Line 1     55    50    50    60   ...

The query for the dataset is:

SELECT LineID
  ,Hour
  ,HourValue
  ,Target
FROM vwData
ORDER BY LineID, CASE WHEN [Hour] > 6 THEN - 1 ELSE [Hour] END

How can I achieve this?

Upvotes: 0

Views: 72

Answers (2)

High Plains Grifter
High Plains Grifter

Reputation: 1571

You could add a calculated field with a value given by something like this: `Fields!Hour.Value + Fields!Hour.Value Mod 2' and then group on that field, using a parameter to choose the Group By field in the report (Your new field or the actual hour value).

Upvotes: 0

Stan Shaw
Stan Shaw

Reputation: 3034

This declares your bit variable (which should be true when they want the 24 hour view - false when 12 hour)

DECLARE @24Hour bit = 0

SELECT CASE WHEN @24Hour = 0
    THEN Hour
    ELSE Hour + (Hour % 2)
    END AS [HourGroup]
  ,SUM(Target) AS [TargetTotal]
FROM vwData
GROUP BY CASE WHEN @24Hour = 0
    THEN Hour
    ELSE Hour + (Hour % 2)
    END

If they want the 24 hour view, we make hour = hour + hour % 2. (7 = 8, 8=8, 9=10, etc., etc.). If you had a more complex query, I would suggest reading up on cross apply, but this is so simple I think this will suffice. The grouping by makes sure to aggregate the REAL 7 and REAL 8 hour records (which will both be returned as "8", if using the 24 hour view). If you don't group your results, you will get two 8 oclock records - one with the REAL 7 hour total and one with the REAL 8 hour total.

EDIT:

Since you didn't include the schema of your DB, I'm guessing that 'Target' is the value being summated, but it could just as easily be 'HourValue'. Furthermore, I have no idea why you would need LineID, so I omitted it from my answer. But you can easily modify that if it's inaccurate. In the future, you should provide some sample data and your database schema so that others aren't forced to make assumptions or guess.

Upvotes: 1

Related Questions