Tek-Tips is the largest IT community on the Internet today!

Members share and learn making Tek-Tips Forums the best source of peer-reviewed technical information on the Internet!

  • Congratulations strongm on being selected by the Tek-Tips community for having the most helpful posts in the forums last week. Way to Go!

Matrix with Variable Columns and Rows

Status
Not open for further replies.

jymm

Programmer
Apr 11, 2002
707
US
I have a matrix with variable rows & columns (user can select what variable they want for row and what they want for columns in addition to ranges of dates...).

What I want to do is when they select 'Sales Year' or 'Sales Month' as a column that I could DYNAMICALLY put in another column that would be the percent increase over the previous value - what I am not sure how to do is identify what value is in the row
ex - this is how the matrix comes out if the user selects Month & Year (pretty graph at the top - then the matrix underneath)
2006 2007 2008
01 - January $1,000 $1,500 $3,000
...

what I would LOVE to output is similar to:
2006 2007 % 2008 %
01 - January $1,000 $1,500 +50% $3,000 +100%

I could even put up with another % column in between 2006 & 2007, but only want to turn the column on if it makes sense to compare.

So the questions are
1) how do I look backwards in a Matrix to do the math
2) how do I dynamically 'shut off' a column?

Years ago I did something similar in Crystal - and am trying to do same in SSRS 2005. Even a general starting direction would get me going

Thanks in advance!
 
Hi :)

I was wondering if you could use a formula field in the cell that converts the number and percentage into character (fixed width for uniform look) with spaces in between so that it actually looks like two fields placed side by side.

If you were not using a matrix, you can select a column and goto the Visibility option in the properties and choose and expression that would set visibility - hidden to true or false based on a condition.

Hope this helps.
 
sorry - gotta use a matrix so that I can use the variable columns & rows - PLUS I am allowing the user to select what the row value is & what the column value is (so row might be Month and Column would be Year by default - but they can select SalesPerson & Year and the matrix automagically regenerates) - so I really need a way to look at column x & column x+1 and do the math ---

thanks so much for the idea of putting it in one cell - I came close on trying that over the weekend, but again - was not sure how to store the total (like in my example) and do the math since I am letting the matrix do the totaling.
 
Status
Not open for further replies.

Part and Inventory Search

Sponsor

Back
Top