x

How to find Previous Column values in Matrix

I have a matrix with columns which are dynamically created as the report is run. I need to be able to include for each column after the first one a percentage of the difference between each column and the previous column value.

Something Like

2009    2010    2011    Total

Students    Remaining
Students    Total
Students    Remaining
Students    Total
Students    Remaining
Students
100    NA    80(80%)    20(25%)

How do I pick up the value of the previous column for Total Students in order to calculate the remaining percentage.

I've tried Previous but get an error telling me this cannot be used with matrix.

Can anyone help with this.
Thanks
more ▼

asked Sep 24, 2012 at 11:55 PM in Default

gaylereihana gravatar image

gaylereihana
0 1 1 1

Sorry table for this did not publish very well.
Sep 24, 2012 at 11:56 PM gaylereihana
(comments are locked)
10|1200 characters needed characters left

2 answers: sort voted first

a similar question in the below link.hope you find it useful.

link text

more ▼

answered Sep 25, 2012 at 05:55 AM

aRookieBIdev gravatar image

aRookieBIdev
2.3k 50 56 61

(comments are locked)
10|1200 characters needed characters left
I found this answer previously. I've never used code before in a report but I did try and use it and kept getting an error to say that there was no value or similar. I think possibly because not all columns have data in them. I was hoping there might be something simpler. I'll try again with the code. Thanks
more ▼

answered Sep 25, 2012 at 09:32 AM

gaylereihana gravatar image

gaylereihana
0 1 1 1

(comments are locked)
10|1200 characters needed characters left
Your answer
toggle preview:

Up to 2 attachments (including images) can be used with a maximum of 524.3 kB each and 1.0 MB total.

New code box

There's a new way to format code on the site - the red speech bubble logo will automatically format T-SQL for you. The original code box is still there for XML, etc. More details here.

Follow this question

By Email:

Once you sign in you will be able to subscribe for any updates here

By RSS:

Answers

Answers and Comments

SQL Server Central

Need long-form SQL discussion? SQLserverCentral.com is the place.

Topics:

x554

asked: Sep 24, 2012 at 11:55 PM

Seen: 938 times

Last Updated: Sep 25, 2012 at 10:17 AM