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 |

4/21/2010 5:38:38 AM

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 |

4/26/2010 8:34:01 PM