October 21, 2013 at 1:29 pm
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 *
FROM
(SELECT acct, sum(hours) AS TotalHours, year, month,
row_number()
OVER (ORDER BY Year DESC, month DESC) AS rn
FROM TestTable
WHERE Acct = 100
GROUP BY acct, year, month) AS rst1
WHERE rn <= 4
Sample Data returned going backwards from last billing:
AcctHoursYearMthrn
1004201321
10020201312
10032012123
100332012114
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-2.
Ideas?
Viewing 0 posts
You must be logged in to reply to this topic. Login to reply