Monday, March 19, 2012

How to create a percent of variance column in a matrix

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


What is the calculation that you use to get % of variance?|||(Current - Prior) / Prior|||

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