Because of the nature of my report, we need to be able to sort on the % growth on orders between 2 periods. This is done by placing a dynamic column in the scope of the Period column header, then putting the following expression into the column. (Expression is shown at the end of this post)
What this expression does is:
If p1 value is 0, then growth is 100%, if p2 value is 0, the growth is -100%, otherwise the growth
is (P2 - P1) / ABS(P1).
HERE IS THE PROBLEM. When I try to sort by this column's expression I get an error which basically
says "If you use an aggregate function in the column, you cannot sort on the column".
This is such a basic report requirement - show % growth and sort by highest to lowest (or vice versa), I cannot believe Microsoft did not envision of a way to do this. This is the most obvious way, but this is also not the first time the SSRS functionality and obvious solution have been a cross purposes. Frankly Microsoft's PM team on SSRS really needs to get out into the field more and see how people are trying to use this tool. I'm getting tired of fighting it.
Anyone know how this could be done? If not, can anyone suggest an alternative tool?
HERE IS THE Expression
= Iif( (Sum(Fields!Period_Dollars.Value)= 0 ), -1,
IsNothing(Previous( Sum(Fields!Period_Dollars.Value),"Group1" )), 1,
(iif(IsNothing( Code.GetGrowth( Sum(Fields!Period_Dollars.Value), Previous( Sum(Fields!Period_Dollars.Value),"Group1" ) ) ), 0,
Code.GetGrowth( Sum(Fields!Period_Dollars.Value), Previous( Sum(Fields!Period_Dollars.Value),"Group1" ) ) ))