Calculate expression - error

The field name in my column 'Num' (1, 2, 3 and 4),
the data i am using is a 'price' field, so under column name 1 my data is
'10', under 2 is '20', under 3 is '60' and under 4 is '90'.
On my next table i need to create data expression. Some thing like this;
'Price' where 'Num' = 1 devide by 'Price' where 'Num' > 1. So in this case
my data should look like this;
under column 1 nothing
under column 2 would be '2'
under column 3 would be '6'
under column 4 would be '9'
=IIF(Fields!Num.Value = 1, Sum(Fields!Price.Value)/Sum(Fields!Price.Value),
Fields!Num.Value > 1)Although I am not truly sure I understand your question, I'll give a stab..
IIF(Fields!Num.Value = 1
,Fields!Price.Value
, Sum(Fields!Num.Value)/Sum(Fields!Price.Value)
)
If Num = 1 Return the Price, Otherwise return the Num/Price...
--
Wayne Snyder MCDBA, SQL Server MVP
Mariner, Charlotte, NC
I support the Professional Association for SQL Server ( PASS) and it''''s
community of SQL Professionals.
"JT" wrote:
> The field name in my column 'Num' (1, 2, 3 and 4),
> the data i am using is a 'price' field, so under column name 1 my data is
> '10', under 2 is '20', under 3 is '60' and under 4 is '90'.
> On my next table i need to create data expression. Some thing like this;
> 'Price' where 'Num' = 1 devide by 'Price' where 'Num' > 1. So in this case
> my data should look like this;
> under column 1 nothing
> under column 2 would be '2'
> under column 3 would be '6'
> under column 4 would be '9'
> =IIF(Fields!Num.Value = 1, Sum(Fields!Price.Value)/Sum(Fields!Price.Value),
> Fields!Num.Value > 1)|||On Feb 12, 5:40=A0pm, JT <J...@.discussions.microsoft.com> wrote:
> The field name in my column 'Num' (1, 2, 3 and 4),
> the data i am using is a 'price' field, so under column name 1 my data is
> '10', under 2 is =A0'20', under 3 is '60' and under 4 is '90'.
> On my next table i need to create data expression. Some thing like this;
> 'Price' where 'Num' =3D 1 devide by 'Price' where 'Num' > 1. So in this ca=se
> my data should look like this;
> under column 1 nothing
> under column 2 would be '2'
> under column 3 would be '6'
> under column 4 would be '9'
> =3DIIF(Fields!Num.Value =3D 1, Sum(Fields!Price.Value)/Sum(Fields!Price.Va=lue),
> Fields!Num.Value > 1)
The problem is that when you group by the Num (by using it in a
Column), then the values that are in scope in the Details area of the
Matrix are only the rows that are available in that group. To get
around this, you have to provide a value for the Scope Parameter in
the Sum function to to a Sum over all rows in the Dataset.
Assuming your dataset is named "DataSet1", your column is grouping on
Fields!Num.Value, then you can get the desired output with the
following expression in the Details area:
=3DIIF( Fields!Num.Value =3D 1, Nothing,
Sum( Fields!Price.Value ) / Sum( IIF(Fields!Num.Value =3D 1, Fields!
Price.Value, Nothing ), "DataSet1" ) )
-- Scott