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 2012 Forums
 SSIS and Import/Export (2012)
 Expression not calculating as expected

Author  Topic 

jackkaye
Starting Member

7 Posts

Posted - 2013-10-21 : 15:25:00
I have a need to create an expression on my SQL Report to calculate the accuracy of how well an individual's opportunities compare to the actual revenue.

I created a simplified version of the expression to test the functionality and this isn't working. Here is the expression:

=IIF(Fields!NavRevenue.Value=0,0,IIF(Fields!NavRevenue.Value>Fields!OppRevenue.Value,1,-1))

What I'm noticing is that on aggregated values, it isn't calculating correctly. Does the expression only calculate at the detail level? Is it possible to get the expression to calculate on the aggregate totals?

James K
Master Smack Fu Yak Hacker

3873 Posts

Posted - 2013-10-21 : 15:38:03
Are you using corresponding aggregates rather than field values themselves in the IIF construct? If so it should work.
Go to Top of Page

jackkaye
Starting Member

7 Posts

Posted - 2013-10-21 : 15:44:34
quote:
Originally posted by James K

Are you using corresponding aggregates rather than field values themselves in the IIF construct? If so it should work.


Not sure what you are referencing, on the SQL Report, there is grouping which sums the values of the Revenue fields. The calculated field appears at each of the groupings and has the formula listed above (i.e. it is using the Field Values to perform the IIF). I'm not aware of a different way to have it perform the IIF on the aggregate value?
Go to Top of Page

James K
Master Smack Fu Yak Hacker

3873 Posts

Posted - 2013-10-21 : 16:50:10
What I meant is using an expression such as shown below on the aggregate row
=IIF(SUM(Fields!NavRevenue.VALUE)=0,0,IIF(SUM(Fields!NavRevenue.VALUE)>SUM(Fields!OppRevenue.VALUE),1,-1))
Go to Top of Page

jackkaye
Starting Member

7 Posts

Posted - 2013-10-21 : 17:17:58
quote:
Originally posted by James K

What I meant is using an expression such as shown below on the aggregate row
=IIF(SUM(Fields!NavRevenue.VALUE)=0,0,IIF(SUM(Fields!NavRevenue.VALUE)>SUM(Fields!OppRevenue.VALUE),1,-1))



It returns an error, "The expression used for the calculated field '=IIF(Sum(Fields!NavRevenue.Value)=0,0,IIF(Sum(Fields!NavRevenue.Value)>Sum(Fields!OppRevenue.Value),1,-1))' includes an aggregate, RowNumber, RunningValue, Previous or lookup function. Aggregate, RowNumber, RunningValue, Previous and lookup functions cannot be used in calculated field expressions."

I believe you cannot include an aggregate function on a running value in the expression.
Go to Top of Page
   

- Advertisement -