I have used SSRS 2005 to create a matrix and need to add a % of variance column. Is this possible? I hope I'm missing something simple. Basically the report should look like this:
Item CurVol PriorVol % of Var CurSales PriorSales % of Var
abc 100 90 11% 1250 990 26%
cde 96 128 -25% 192 243 -21%
Dataset looks this this:
Select item, vol, sales, 'Current' AS Per
FROM Table
WHERE (invoice_date >= @.curStartDate) AND (invoice_date < @.curEndDate + 1)
UNION
Select item, vol, sales, 'Prior' AS Per
FROM Table
WHERE (invoice_date >= @.priorStartDate) AND (invoice_date < @.priorEndDate + 1)
The column group on the matrix is using the Per column because the accounting periods cross over calendar months.
gusina
Item CurVol PriorVol % of Var CurSales PriorSales % of Var
abc 100 90 11% 1250 990 26%
cde 96 128 -25% 192 243 -21%
OK so I am assuming you have the columns for current and prior with the data correct in the matrix. To get your % of Var column, just add a new column to the matrix. For the expression of that column use something like the following:
= (Fields!CurrentVol.Value - Fields!PriorVol.Value) / Fields!Prior.Value
Do similar to the above for Sales.
|||The matrix for this project is structured with two data fields (Vol and Sales), one row group (item), and one column group (per) with values of Current or Prior. The available fields are Fields!Vol.Value and Fields!Sales.Value. How can the recommended expression be created?
No comments:
Post a Comment