x

Sum IIF Problem

I have a tablix report and I am trying to add up values in column a only when values in column b are not null.

So Something like this

 Item      Price A               Price B
 1234       100                   0
 6789       100                   10
 4567       200                   20
 TTL        300                   30

I have tried this syntax in the rexpression editor =Sum(IIF(Fields!ColimnA.Value > 0 And Fields!ColimnB.Value >0 , 1, 0) "Scope")

this results in an error like "The Value expression for the textbox ‘textbox21’ has a scope parameter that is not valid for an aggregate function. The scope parameter must be set to a string constant that is equal to either the name of a containing group, the name of a containing data region, or the name of a data set."

more ▼

asked May 09, 2011 at 10:49 AM in Default

avatar image

siera_gld
1k 82 88 93

Perhaps it should be ColumnA rather than ColimnA...? or is that just a typo?

May 09, 2011 at 01:27 PM ThomasRushton ♦♦
(comments are locked)
10|1200 characters needed characters left

1 answer: sort voted first

As the error says, if you include the Scope parameter, it must be equal to tne name of the group, data region or data set name.

If you have the formula in the Group footer/header, instead of "Scope" put there the name of the Group. If you have it in the footer of the whole table, you can ommit the Scope parameter, or put there the name of the Data Set.

If you include the Scope parameter, the aggregation will restart with each change in the scope. If you would like cummulative sums even in group footer/hear, you should ommit the Scope parameter.

more ▼

answered May 09, 2011 at 11:34 AM

avatar image

Pavel Pawlowski
22.7k 10 15 26

(comments are locked)
10|1200 characters needed characters left
Your answer
toggle preview:

Up to 2 attachments (including images) can be used with a maximum of 524.3 kB each and 1.0 MB total.

Follow this question

By Email:

Once you sign in you will be able to subscribe for any updates here

By RSS:

Answers

Answers and Comments

SQL Server Central

Need long-form SQL discussion? SQLserverCentral.com is the place.

Topics:

x650
x70
x43
x2

asked: May 09, 2011 at 10:49 AM

Seen: 1723 times

Last Updated: May 09, 2011 at 10:49 AM

Copyright 2016 Redgate Software. Privacy Policy