Prev: Report Builder
Next: show XML schema and values
From: Andre on 21 Apr 2010 01:38
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

From: Kiwi_smith on 26 Apr 2010 16:34
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
>
> .
>
 | 
Pages: 1
Prev: Report Builder
Next: show XML schema and values