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.
| Author |
Topic |
|
jackkaye
Starting Member
7 Posts |
 Posted - 2013-11-12 : 11:48:57
|
| I have an Excel Pivot table that has a Calculated Expression to determine how accurate a person is with their Opportunity Revenue vs. te Actual Revenue. I would like to create the same Calculated Expression in the Matrix but I'm not having much success.Here is the Calculated Expression from the Excel Pivot Table:=IF(AND(OppRevenue=0,NavRevenue=0),0,IF(OppRevenue>ABS(NavRevenue),ABS(NavRevenue)/OppRevenue,OppRevenue/ABS(NavRevenue)))Here is what I created in SQL Reports for the Expression field:=IIF(((Fields!NavRevenue.Value=0) And (Fields!OppRevenue.Value=0)),0,IIF(Fields!OppRevenue.Value>ABS(Fields!NavRevenue.Value),ABS(Fields!NavRevenue.Value)/Fields!OppRevenue.Value,Fields!OppRevenue.Value/ABS(Fields!NavRevenue.Value)))I keep getting a #Error back in the field results. Any ideas? |
|
|
visakh16
Very Important crosS Applying yaK Herder
52326 Posts |
Posted - 2013-11-12 : 12:47:02
|
| =IIF(((Fields!NavRevenue.Value=0) Or (Fields!OppRevenue.Value=0)),0,IIF(Fields!NavRevenue.Value=0) Or (Fields!OppRevenue.Value=0),0,IIF(Fields!OppRevenue.Value>ABS(Fields!NavRevenue.Value),ABS(Fields!NavRevenue.Value)/Fields!OppRevenue.Value,Fields!OppRevenue.Value/ABS(Fields!NavRevenue.Value))))------------------------------------------------------------------------------------------------------SQL Server MVPhttp://visakhm.blogspot.com/https://www.facebook.com/VmBlogs |
 |
|
|
jackkaye
Starting Member
7 Posts |
Posted - 2013-11-12 : 13:07:59
|
| You were missing a comma but I added that and the report ran. However, I'm still receiving a #error message. |
|
|
|
visakh16
Very Important crosS Applying yaK Herder
52326 Posts |
Posted - 2013-11-12 : 13:09:57
|
| whats the datatype of NavRevenue and OppRevenue?------------------------------------------------------------------------------------------------------SQL Server MVPhttp://visakhm.blogspot.com/https://www.facebook.com/VmBlogs |
|
|
|
jackkaye
Starting Member
7 Posts |
Posted - 2013-11-12 : 13:13:04
|
| In the Dataset SQL query they both are defined as decimal. |
|
|
|
jackkaye
Starting Member
7 Posts |
Posted - 2013-11-13 : 09:27:47
|
| Any other thoughts on how to resolve the #error message for this expression field? |
|
|
|
|
|
|
|
|
Trying to setup Matrix like Excel Pivot Table