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-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. |
 |
|
|
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? |
|
|
|
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)) |
|
|
|
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. |
|
|
|
|
|
|
|
|
Expression not calculating as expected