March 18, 2008 at 7:04 am
Hi, maybe I'm just not thinking creatively -- I want to create a report that shows, say, five areas of information, but comparing this year with last year.
So for example (using three areas):
# employees # servers # cell phones [etc...]
2007 2008 2007 2008 2007 2008
JAN 100 120 7 12 34 47
FEB 102 124 7 13 35 46
[etc...]
Is there a clever way to do this (through formatting the result set a certain way or tweaking the column expressions) so I can use a matrix or regular table to achieve this kind of report?
March 18, 2008 at 8:03 am
Hi Todd,
I do it pretty much through SQL can't say that that's the best method though but it works for me. I create a couple of temp tables in my stored procedure, #current_year_data and #previous_year_data. I then insert data into both these tables like so:
insert into #current_year_data(col1, col2, ...)
select col1, col2,...
from my_table
where some_date between @start_date and @end_date
insert into #previous_year_data(previous_col1, previous_col2,...)
select col1, col2, ...
from my_table
where some_date between dateadd(y, -1, @start_date) and dateadd(y, -1, @end_date)
In my final select I then do a full outer join between both tables.
select col1, col2, previous_col1, previous_col2,...
from #current_year_data c
full outer join #previous_year_data p on p.col1 = p.col2 -- naturally you must join on the column(s) that relate the tables to each other.
When building your report you then have access to twice the normal number of columns.... all of your current year's columns and all of the previous year's columns.
You could carry out the T-SQL with one statement by using derived tables but I found it slightly easier to read if I used temp tables.
Like I said, I'm not sure if this is the best way of doing it but it works for me and makes the report building easy. It's easy to then compare previous year columns with the corresponding current year and if the values for this year are lower than last year you can change the row color to red so it stands out more (management love that sort of thing :)).
Hope that helps.
March 18, 2008 at 9:08 am
Oh my gosh, that's embarassing. I didn't even think about just pre-processing the columns before hitting the report. I really appreciate your response--it will help tremendously.
March 18, 2008 at 9:12 am
Todd Biggins (3/18/2008)
Oh my gosh, that's embarassing. I didn't even think about just pre-processing the columns before hitting the report. I really appreciate your response--it will help tremendously.
No worries. I've been there before. You spend so long looking at a problem that you end up convincing yourself that the solution is more complicated than it actually is.
March 18, 2008 at 9:15 am
I agree with SQLZ. I always try to do as much work as possible in my stored procedure, then display it on the report. At that point all you have to do is subtotals, grouping, etc.
March 18, 2008 at 9:28 am
select
[MonthName] = datename(month,a.[Month]),
NumEmp_Curr = sum(case a.CurrentYear when 1 then a.NumEmp else 0 end),
NumEmp_Prior = sum(case a.PriorYear when 1 then a.NumEmp else 0 end),
Servers_Curr = sum(case a.CurrentYear when 1 then a.Servers else 0 end),
Servers_Prior = sum(case a.PriorYear when 1 then a.Servers else 0 end),
CellPhones_Curr = sum(case a.CurrentYear when 1 then a.CellPhones else 0 end),
CellPhones_Prior = sum(case a.PriorYear when 1 then a.CellPhones else 0 end)
from
(
select
-- Get first day of Month for grouping
[Month] = dateadd(month,datediff(month,0,aa.Mydate),0),
CurrentYear = case year(aa.MyDate) when 2008 then 1 else 0 end,
PriorYear = case year(aa.MyDate) when 2007 then 1 else 0 end,
aa.*
from
Mytable aa
where
aa.Date >= @StartDateand aa.Date < @EndDate
) a
group by
a.[Month]
order by
a.[Month]
March 19, 2008 at 3:53 am
I agree with the posts posted above to put the most of work\logic in the query or the stored procedure before building your report.
The general problem of this post ís solved in SQL Server 2008 where this kind of reports can easily be made with the Tablix component.
If you are interested in the improvements of SQL server 2008 and of course Reporting Services 2008 just visit the link below:
http://sqlserver2008jumpstart.microsofttraining.com
Tho follow this training you can go to the following link:
https://training.partner.microsoft.com/plc/details.aspx?publisher=12&delivery=242521
Greetings,
Niels Naglé
March 19, 2008 at 4:14 am
Niels Naglé (3/19/2008)
I agree with the posts posted above to put the most of work\logic in the query or the stored procedure before building your report.The general problem of this post ís solved in SQL Server 2008 where this kind of reports can easily be made with the Tablix component.
If you are interested in the improvements of SQL server 2008 and of course Reporting Services 2008 just visit the link below:
http://sqlserver2008jumpstart.microsofttraining.com
Tho follow this training you can go to the following link:
https://training.partner.microsoft.com/plc/details.aspx?publisher=12&delivery=242521
Greetings,
Niels Naglé
Thanks Niels. Looks really interesting.
March 19, 2008 at 4:19 am
Niels, I'm thrilled to hear RS 2008 features can do this; we'll be rolling that out in a few months. Your links will help me hit the ground running.
Since we're currently on RS 2005, I also appreciate and have learned new tricks from Michael Valentine Jones' method that should tie me over. Thanks so much, folks. This is a great community!
March 20, 2008 at 9:34 am
I'd be interested in clarification on exactly what the difficulty was. This type of a report is handled simply and directly by the matrix control. All you need is a result set with month, year, type and the value.
Am I misisng an obvious point?
March 20, 2008 at 9:41 am
No you're not missing a point but this solution has already been given by the posts above and that is to put te logic in the query so you can put it in the matrix.
March 20, 2008 at 9:45 am
I answered this post but something went wrong.
So here is my answer again.....
No you're not missing a point but the clue was that the question was how he could put the current datain the report. So the answer was to put more logic in the query so you could put it in the Matrix component.
And then i said that you don't need to put all of the logic in the query anymore by using the Tablix component in 2008.
Greetings
March 20, 2008 at 3:39 pm
As Neils said, I'm working off existing data which is a culmination of some hefty stored procedures.
But most importantly the format required is more like this:
AVG #employees net income ($k) AVG #cell phones [etc...]
2007 2008 2007 2008 2007 2008
JAN 100 120 20 32 34 47
FEB 102 124 18 31 35 46
MAR 102 124 18 31 35 46
------------------------------------------------------
Q1 101 123 56 94 35 46
------------------------------------------------------
[etc...]
-------------------------------------------------------
YTD
[Where both quarterly subtotals and a YTD subtotal are required.
Some columns' subtotals are averages (like avg # employees), while others are sums (average net income). ]
To make sure we're on the same page, I'm assuming what you're suggesting is working off rows of data in this format:
QuarterMonthYearTypeValue
112007AvgEmps100
122007AvgEmps102
132007AvgEmps102
112007NetIncome20
122007NetIncome18
132007NetIncome18
112007AvgCellPhones34
122007AvgCellPhones35
132007AvgCellPhones35
112008AvgEmps120
122008AvgEmps124
132008AvgEmps124
112008NetIncome32
122008NetIncome31
132008NetIncome31
112008AvgCellPhones47
122008AvgCellPhones46
132008AvgCellPhones46
---------------------
I can see that I can make the rows quarter, then month
and columns type, then year.
That gives me a great layout.
I get stuck on how to conditionally format the calculations of subtotals. With table format, I can pick and choose on each column.
If you can tell me how to make subtotals calculate as averages off Fields!Type.Value= 'Avg(whatevers)', and sums off everything else, you've sold me on Matrix format and I'll just generate stored procedures to get to the row format above.
Thanks again for your interest-- this is a great discussion.
March 21, 2008 at 3:35 am
Todd Biggins (3/20/2008)
I get stuck on how to conditionally format the calculations of subtotals. With table format, I can pick and choose on each column.If you can tell me how to make subtotals calculate as averages off Fields!Type.Value= 'Avg(whatevers)', and sums off everything else, you've sold me on Matrix format and I'll just generate stored procedures to get to the row format above.
Thanks again for your interest-- this is a great discussion.
Todd, I'm pretty sure I managed to get sub-totals in one of my matrix reports but I can't remember for the life of me how I did it and I'm not at work till Tuesday so I won't be able to come back to you on that.
Knowing me though I probably did it in SQL somehow. A couple of things you could try (or have a play with) is using the T-SQL rollup operator or maybe using the pivot functionality to get the data in a matrix format before you send it to reporting services.
I'd be interested to know if there's a way to do it in reporting services though.
March 21, 2008 at 6:44 am
Yes, one thought that had crossed my mind was (using my previous table result example) to include rows with precalculated (through SQL) results and hand pick what gets averaged vs summed before hitting the report.
I think the SSRS 2005 Matrix is great, by all means, but it would be great if it displayed column holders for each pivot item through the design tab (and a subtotal field for each item), vs the one column to rule them all. When you factor in all the pre-processing on the more intensive reports, you end up doing as much work as you would with a table with less to see up front when you go back in a few months.
Viewing 15 posts - 1 through 15 (of 15 total)
You must be logged in to reply to this topic. Login to reply