Trying to expand on a report that reports the last N months of electric usage for an account.
So far I have this:
CREATE TABLE [dbo].[TestTable](
[id] [int] IDENTITY(1,1) NOT NULL,
[Acct] [int] NULL,
[Month] [int] NULL,
[Year] [int] NULL,
[Hours] [int] NULL
) ON [PRIMARY]
(SELECT acct, sum(hours) AS TotalHours, year, month,
OVER (ORDER BY Year DESC, month DESC) AS rn
WHERE Acct = 100
GROUP BY acct, year, month) AS rst1
WHERE rn <= 4
Sample Data returned going backwards from last billing:
Acct Hours Year Mth rn
100 4 2013 2 1
100 20 2013 1 2
100 3 2012 12 3
100 33 2012 11 4
I can then create a bar chart in SSRS based on the TotalHours; Hours for the Y Axis and YearMonth for the X Axis.
I get the electric usage for each month in a bar.
Now I'm trying to duplicate what is on the electric bill. Once multiple years of data are added, how would I get another series matching on the previous year/month?
So for reporting month 2, I would have two bars side by side, one for 2014
-2, and another one for 2013