Calculate percentage from subtotals in SSRS 2005

Question

Calculate percentage from subtotals in SSRS 2005

Puja Shah

SSCommitted

Points: 1724
More actions
July 17, 2009 at 6:17 am

#207091

Hello Everybody,
I want to generate financial year report. I’m generating report in matrix form having following groups
Row groups:
1st row group: Income_expense
2nd row group: GroupName
3rd row group: ParameterName
Column groups:
1st column group: FiscalYear
2nd column group: Quarter
3rd column group: Month
Please look at the attachment.
I want to calculate percentage for each subtotal with respect to the groups. I have used following expression to calculate percent:
=switch(
inscope("ParameterName") and inscope("month"),
FormatPercent(sum(fields!Data.Value)/sum(fields!Data.Value,"matrix1"),2),
inscope("GroupName") and inscope("quarter"),
FormatPercent(sum(fields!Data.Value)/sum(fields!data.Value,"matrix1"),2)
)
It gives me following error:
“The Value expression for the textbox ‘textbox12’ refers to the field ‘data’. Report item expressions can only refer to fields within the current data set scope or, if inside an aggregate, the specified data set scope. “
I think the textbox12 is not in the data set scope. How shall I include this textbox in the data set scope so that percentage can be calculated from the subtotals of each group?
I've added Percent column as I want to calculate percentage from data using functions provided by SSRS 2005.
Please reply soon.
Thanks in Advance.

Viewing 6 posts - 1 through 5 (of 5 total)

You must be logged in to reply to this topic. Login to reply

JWIDM76 Mr or Mrs. 500 Points: 588 More actions · Answer 1

Maybe this will help shed some light for you. I'm doing something similar in one of my reports based on what I learned from this article:

http://72.32.35.89/sql/sql-server-2005/advanced-matrix-reporting-techniques/

Puja Shah SSCommitted Points: 1724 More actions · Answer 2

Thanks for the help.

The link is having the formula that i want, but i'm getting same error while calculating percentage.

I'm using this formula:

=iif(inscope("matrix1_FiscalYear") and inscope("matrix1_Expense_Income"),formatpercent(fields!data.Value/Sum(iif(inscope("matrix1_Expense_Income"),Fields!Data.Value,nothing)),2),"")

Please correct me if I'm wrong.

Thanks in advance.

JWIDM76 Mr or Mrs. 500 Points: 588 More actions · Answer 3

It's hard to say what is wrong exactly without seeing your layout and where exactly "textbox12" is located. I personally utilized the formula to replace the normal behavior of the subtotal functionality on a group (a simple sum).

I assume "data" is a name of an actual column in your dataset.

Puja Shah SSCommitted Points: 1724 More actions · Answer 4

Hello JWIDM,

your assumption is correct about "data", it is a field in table. I've attached excel file.

Hope it is understandable.

Net Sales,Total Payroll, Advertising and Operating Expenses are groups and Sales1, Sales2, Sales3, Wages....and so on are Parameters.

I want to calculate the percentage for each group based on Net Sales group.

Total I've 3 row groups as below in order:

Income/Expense

GroupName

ParameterName

3 column groups in order are:

Fiscal Year

Quarter

Month

I hope I'm clear now.

Puja Shah SSCommitted Points: 1724 More actions · Answer 5

Thanks for the help.

I got the solution. I've used a different dataset to calculate the percentage with respect to group.

I've using two datasets:

FiscalData and NetSales

FiscalData contains the fields that i want in my report & NetSales for summing the value for a particular parameter group (here Net Sales). I'm calculating the percentage of other groups based on the subtotal of Net Sales.

=iif(fields!GroupName.Value="Net Sales",formatpercent(sum(fields!Data.Value)/sum(fields!Data.Value,"matrix1_GroupName")),formatpercent(sum(fields!Data.Value)/sum(fields!Data.Value,"NetSales")))

Thanks again.