group totals w/criteria?

SSRS 2005

I'm relatively new to RS and have a challenge I don't know how to overcome. 
I have a report where I need to subtract one group total from another.  I 
read that this isn't possible in 2005, but that 2008 will support 
aggregations on aggregations.  I'm sure someone has figured out how to do 
this in 2005 though, so any words of wisdom?

This is an Accounting report, where I need to get the value for the 
Contribution Margin total.  The aggregation I'm looking for is this:

Contribution Margin  = sum(Revenue) - sum(Expenses)

The challenge is the the sum of Revenue and Expenses are the same group on 
the report, just on different pages if you will.  What I really need is the 
ability to do a sum with the ability to add criteria, such as:
sum(myValue, where SomeField="Revenue")  minus sum(myValue, where 
SomeField="Expenses")

I'd appreciate any advice I can get.

Thanks, Andre 

0
Andre
4/21/2010 5:38:38 AM
sqlserver.reportingsvcs 542 articles. 0 followers. Follow

1 Replies
1089 Views

Similar Articles

[PageSpeed] 40

Andre, if you ahve not yet found a solution, try this expression in the Group 
footer or table footer below your income/expenses group. 

=Sum(IIF(Fields!Type.Value = "Revenue", Fields!Amount.Value,nothing)) - 
Sum(IIF(Fields!Type.Value = "Expense", Fields!Amount.Value,nothing))

"Andre" wrote:

> SSRS 2005
> 
> I'm relatively new to RS and have a challenge I don't know how to overcome. 
> I have a report where I need to subtract one group total from another.  I 
> read that this isn't possible in 2005, but that 2008 will support 
> aggregations on aggregations.  I'm sure someone has figured out how to do 
> this in 2005 though, so any words of wisdom?
> 
> This is an Accounting report, where I need to get the value for the 
> Contribution Margin total.  The aggregation I'm looking for is this:
> 
> Contribution Margin  = sum(Revenue) - sum(Expenses)
> 
> The challenge is the the sum of Revenue and Expenses are the same group on 
> the report, just on different pages if you will.  What I really need is the 
> ability to do a sum with the ability to add criteria, such as:
> sum(myValue, where SomeField="Revenue")  minus sum(myValue, where 
> SomeField="Expenses")
> 
> I'd appreciate any advice I can get.
> 
> Thanks, Andre 
> 
> .
> 
0
Utf
4/26/2010 8:34:01 PM
Reply:

Similar Artilces: