Percentage Difference Calculation!

I have a table as follows:
Year Month Value %Diff
2004 Jul 200
Aug 100
etc etc
2003 Jul 300
Aug 400
etc etc
I need to be able to calculate the % difference for each month compared to
the same month for the previous year. The Previous() function is close to
what I need but not close enough. Any help would be much appreciated?If you have control over the source query, a LEFT OUTER JOIN ... ON
a.Month=b.Month and a.Year=b.Year-1 would bring back the data you need on
each row to calculate the diff. As you can see from this post and others,
my philosophy is not to jump through major coding hoops in the report if you
can easily solve it in the SQL query.
--
'(' Jeff A. Stucker
\
Business Intelligence
www.criadvantage.com
---
"St Matthew" <StMatthew@.discussions.microsoft.com> wrote in message
news:676C6EB0-0383-4BE6-86E5-58DFF89AA30B@.microsoft.com...
>I have a table as follows:
> Year Month Value %Diff
> 2004 Jul 200
> Aug 100
> etc etc
> 2003 Jul 300
> Aug 400
> etc etc
> I need to be able to calculate the % difference for each month compared to
> the same month for the previous year. The Previous() function is close to
> what I need but not close enough. Any help would be much appreciated?|||I should have mentioned in my SQL examples below that a and b are aliases
for the same table.
Cheers,
--
'(' Jeff A. Stucker
\
Business Intelligence
www.criadvantage.com
---
"Jeff A. Stucker" <jeff@.mobilize.net> wrote in message
news:OZdTrAb0EHA.3584@.TK2MSFTNGP11.phx.gbl...
> If you have control over the source query, a LEFT OUTER JOIN ... ON
> a.Month=b.Month and a.Year=b.Year-1 would bring back the data you need on
> each row to calculate the diff. As you can see from this post and others,
> my philosophy is not to jump through major coding hoops in the report if
> you can easily solve it in the SQL query.
> --
> '(' Jeff A. Stucker
> \
> Business Intelligence
> www.criadvantage.com
> ---
> "St Matthew" <StMatthew@.discussions.microsoft.com> wrote in message
> news:676C6EB0-0383-4BE6-86E5-58DFF89AA30B@.microsoft.com...
>>I have a table as follows:
>> Year Month Value %Diff
>> 2004 Jul 200
>> Aug 100
>> etc etc
>> 2003 Jul 300
>> Aug 400
>> etc etc
>> I need to be able to calculate the % difference for each month compared
>> to
>> the same month for the previous year. The Previous() function is close
>> to
>> what I need but not close enough. Any help would be much appreciated?
>

Percentage Column AND Row

Hello,

I have a matrix that looks as follows:

Name Jan Feb Total

John 5 6 11

Mary 3 4 7

Total 8 10 18

I want to add a percent column to the RIGHT of the total, and also on the bottom row. I can't find any clear examples of how to do this. If I had a new column, it adds additional headers beneath my top row. Or, my columns appear to the LEFT of the data, not the right. Can some please post some simple instructions that will make my simple matrix look like this:

Name Jan Feb Total %

John 5 6 11 60%

Mary 3 4 7 40%

Total 8 10 18 100%

% 40% 60% 100% 100%

I am so stuck on this I can pull my hair out.

Thanks!

Michael

p.s. I really hope the next version of SSRS has a simple "Sub-total %" option that you can enable just like the sub-total column.

I know the feeling...I am having the same issues

The only answer I came up with is do ALL calculations in a Stored Procedure or in SQL and some kind of way arrange the data in a way that will display your info right in your report.

For example, I had to do a Variance (orginally it was a percent like yours, but they settled on a difference between the last two columns) and instead of doing it in Reporting Services, I created a cursor in SQL and then just did a select top (1) * SQL as dataset. Cheesy I know what what else to do with so little built in stuff within Reporting Services?

Hope that helps...

|||

My simple example on this page is only a small portion of the types of matrices I need to generate. Most are dynamic, have dynamic columns, and various sub-groupings. The code to generate all the sub-totals and the primary totals would be miserable. I've done this before, and one of the reasons I moved to SSRS was because it made it so easy to create pivot tables out of my data and not have to write SQL to handle every possible situation. Your suggestion is probably reasonable, but I hate the idea of doing it manually, and manually adding the various columns and rows to display the data, rather than letting the matrix do all the work (which was my initial goal). I am still hopeful I can figure this out. If anyone else has a suggestion, please respond. I just can't believe that this is so difficult with SSRS.

Mike

|||

If you find a better way using SSRS please post...I am sure all would like to know!

Thanks...