Matrix showing Negative Values when using InScope

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

SQL Server 2005 Forums

Analysis Server and Reporting Services (2005)

Matrix showing Negative Values when using InScope

Author

Topic

mashedtaz1
Starting Member

9 Posts

Posted - 2011-07-05 : 07:55:29

Hi,

I am using the Inscope function in a matrix to create a custom subtotal to show the variance between two values in a column group. However, when i use the following formula, the "Previous DD Collection Amount" value shows as a negative value? The variace in the subtotal colum calculates correctly though.

=sum(IIF(InScope("matrix1_Product") ,Fields!Value.Value,IIF(Fields!Val ueType.Value="Current DD Collection Value",Fields!Value.Value,0)-IIF(Fields!ValueType.Value="Previous DD Collection Amount",Fields!Value.Value,0)))

Any help would be appreciated as this is driving me slightly mad!

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2011-07-05 : 14:43:01

try like

IIF(InScope("matrix1_Product") ,SUM(Fields!Value.Value),SUM(IIF(Fields!Val ueType.Value="Current DD Collection Value",Fields!Value.Value,0)-IIF(Fields!ValueType.Value="Previous DD Collection Amount",Fields!Value.Value,0)))

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/

mashedtaz1
Starting Member

9 Posts

Posted - 2011-07-06 : 04:19:34

I did try that, which does prevent a negative value from appearing, but then the subtotal shows the sum of the two columns rather than the variance between the two.

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2011-07-06 : 14:02:21

if thats the case why not use like

IIF(InScope("matrix1_Product") ,SUM(Fields!Value.Value),SUM(IIF(Fields!Val ueType.Value="Current DD Collection Value",Fields!Value.Value,0)+ IIF(Fields!ValueType.Value="Previous DD Collection Amount",Fields!Value.Value,0)))

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources