How to generate an expression that uses the value from a previous column in a matrix?
I have a RS Matrix element where I am attempting to genrate an expression that uses a value from a previous column. I have had no luck so far...
Here's a brief example of the matrix that I am using:
=Fields!MonthName (my one and only Column Group)
=Fields!ProductName =Sum(Fields!Count) =DifferenceExpressionInQuestion
I gotta use a Matrix since the number of months is dynamic.
In my test, I have 12 month columns. Under each Month column is a subordinate Count and a Difference column. In the Difference column, I want to calculate the difference in the Count columns relative to the previous month (if the Count for Jan is 15 and the Count for Feb is 27, then Feb's Difference column should be 12).
So, I am stuck trying to generate an expression for the Difference column. This expression needs to refer to the Count of the previous month.
RS's Previous() function cannot be used in a Matrix (because that's the compiler error that I get when I try to use it).
Does anybody know how to do this?
Do I have to use custom code to do this?
If I can't do this calculation in a matrix, then what good is it?
TIA!!!
[1196 byte] By [
DLdfrd] at [2007-12-24]
Hey Carl,
Thanks for the response...
I tried adding a textbox as a static column in my matrix but when I generate an expression that uses textbox12.Value, I get a compilation error (saying textbox12 is undeclared).
Do you have any other ideas, as I am stumped on this...
I am going to try slinging some custom code to save the counts and months in a dynamic VB.NET matrix and then use this array for performing this calculation...
Thanks alot!!!
OK
Using the "reportitems!textbox12.value", I can now compile (RS 2003 doesn't have intellisense).
So, if I added a static column (textbox12) to my matrix and give it an expression of =Sum(Fields!Count), then the static column on the generated report record's each month's summation of the Count fields.
In my Difference column, I used an expression of "=ReportItems!textbox12.Value". When I execute the report, the Difference show the summation for the month's Count fields.
Within the Difference column's expression, I am trying to grab the previous month's Count total. Using textbox12 hasn't helped.
Did I miss something?
Thanks again for your help...
Dan
Hey Sam,
Sorry for the slow response but I just got back into the office and have found some spare time.
I never found a fix for creating an expression that would reference the contents of another cell in a RS 2003 matrix.
For a work around, I modified the stored procedure, that is used by the report. I added a new column to the returned recordset where the new column contains the matrix cell contents that I needed in the expression. So, in the expression, I used the new column from the recordset for my calculations.
As you can probably see, some of the report's calculations are done in the stored procedure, not in the rdl.
Hope this helps...
Dan
