Errpr in :Calculated member builder

#1

Hi All,

This ones a newbie Q, I am creating a calculated member on a virtual cube
under the Measures,

with the below simple code,
Iif(([Item Code].[Item Code]) = "0004-92-031-I" ,
([Measures].[Quantity]*100)/5000,

Iif([Item Code].[Item Code] = "0015-92-003-J",
(([Measures].[Quantity]*(50*100))/5000),

Iif([Item Code].[Item Code] = "0004-92-053-J",
(([Measures].[Quantity]*10)/5000),

Iif([Item Code].[Item Code] = "0004-92-030-K",
(([Measures].[Quantity]*25)/5000) , 0)

) ) )

but this one giuves me error when i check it

I will apprecdiate if any one can guide me .
The error is UNABLE TO UPDATE THE CALUCLATED MEMBER : FORMULA ERROR : SYNTAX
ERROR : TOKEN IS NOT VALID....

Its a sort of new bie q, but i have already spent 4-5 hrs in figuring thois
out... and cannnot..

Thanks in advance..

#2

I'm not sure I understand.
Your Calculated member works along the [Item Code] dimension, so if you
don't place members of that dimension in rows or columns and you don't filter
by the dimension, you'll get the [All] member.As I said on an earlier post,
unless you create an special IIf to handle it the [All] member will be zero.
HTH,
Brian

"Hitesh" wrote:

Quote:

hI ...

Actually after further researching.. i find that the calculated member does
displays some amount.. ONLY when we filter by any of the item codes . But
when the specific item codes are not filtered.. i.e its a group by some other
dimension, then the value for the cell is 0...

Any clues as to what should be done inorder to achieve the above... (values
to be displayed irrespectively of filtering by item code?

~Thanks

"Hitesh" wrote:

correction... Calculated member and not calculated measure....

Thanks Brian, this one worked....But does not do anything. i.e produces 0 in
the output.

Am i doing something wrong basically? i.e is this correct to do this in the
way of a calculated member or something else should be done?

The below is my calculated measure which works syntactically... but when i
see the data it is all 0 i.e looks like it has not satisfied any of the
conditions...

Iif ( ancestor([Item Code].currentmember,[Item Code].[Item Code]) is
[0004-92-031-I],([Measures].[Quantity]*100)/5000,
IiF ( ancestor([Item Code].currentmember,[Item Code].[Item Code]) is
[0015-92-003-J],(([Measures].[Quantity]*(50*100))/5000),
IiF ( ancestor([Item Code].currentmember,[Item Code].[Item Code]) is
[0004-92-053-J],(([Measures].[Quantity]*10)/5000),
IiF ( ancestor([Item Code].currentmember,[Item Code].[Item Code]) is
[0004-92-030-K],(([Measures].[Quantity]*25)/5000) ,0 )
)
)
)

Thanks in advance...

"Hitesh" wrote:

Thanks Brian, this one worked....But does not do anything. i.e produces 0 in
the output.

Am i doing something wrong basically? i.e is this correct to do this in the
way of a calculated member or something else should be done?

The below is my calculated measure which works syntactically... but when i
see the data it is all 0 i.e looks like it has not satisfied any of the
conditions...

Iif ( ancestor([Item Code].currentmember,[Item Code].[Item Code]) is
[0004-92-031-I],([Measures].[Quantity]*100)/5000,
IiF ( ancestor([Item Code].currentmember,[Item Code].[Item Code]) is
[0015-92-003-J],(([Measures].[Quantity]*(50*100))/5000),
IiF ( ancestor([Item Code].currentmember,[Item Code].[Item Code]) is
[0004-92-053-J],(([Measures].[Quantity]*10)/5000),
IiF ( ancestor([Item Code].currentmember,[Item Code].[Item Code]) is
[0004-92-030-K],(([Measures].[Quantity]*25)/5000) ,0 )
)
)
)

Thanks in advance...

"Brian Altmann" wrote:

Do you mean something like this:

iif ( ancestor([item code].currentmember,[item code].[item code]) is
[qwer1-234-12], ...

You should consider the [All] member, which would be a special case.
HTH,
Brian

"Hitesh" wrote:

Thanks Brian,
That does not work as the first [Item Code] is the shared dimension
and the second one is a level in the dimension.

The dimension data will look like
Dimension Item COde
All Items
Item Code('qwer1-234-12')
Item Name('My Item')

So i want to perform a calculation based on the value of the Item Code
But i am unable to access the Item Code value ... though now i am close...

I can reach upto the level...
IIf( [Item Code].CurrentMember.Level. ??? ='asdsa-asdsa-q23', ....

Any clues??

Thanks

"Brian Altmann" wrote:

You are trying to compare member objects with strings.
You can either use the member name, as in:

[Item Code].[Item Code].currentmember.name = "0004-92-031-I"

or compare objects (this is faster):

[Item Code].[Item Code].currentmember is [0004-92-031-I]

HTH,
Brian
www.geocities.com/brianaltmann/olap.html

"Hitesh" wrote:

Hi All,

This ones a newbie Q, I am creating a calculated member on a virtual cube
under the Measures,

with the below simple code,
Iif(([Item Code].[Item Code]) = "0004-92-031-I" ,
([Measures].[Quantity]*100)/5000,

Iif([Item Code].[Item Code] = "0015-92-003-J",
(([Measures].[Quantity]*(50*100))/5000),

Iif([Item Code].[Item Code] = "0004-92-053-J",
(([Measures].[Quantity]*10)/5000),

Iif([Item Code].[Item Code] = "0004-92-030-K",
(([Measures].[Quantity]*25)/5000) , 0)

) ) )

but this one giuves me error when i check it

I will apprecdiate if any one can guide me .
The error is UNABLE TO UPDATE THE CALUCLATED MEMBER : FORMULA ERROR : SYNTAX
ERROR : TOKEN IS NOT VALID....

Its a sort of new bie q, but i have already spent 4-5 hrs in figuring thois
out... and cannnot..

Thanks in advance..

#3

Looks like you want the calculated measure to work at
higher levels than [Item Code].[Item Code], whereas the
MDX was geared to that level and lower. Will this work?

Quote:

Sum(Descendants([Item Code].currentmember,

[Item Code].[Item Code]),
Iif ([Item Code].currentmember is
[0004-92-031-I],([Measures].[Quantity]*100)/5000,
Iif ([Item Code].currentmember is
[0015-92-003-J],(([Measures].[Quantity]*(50*100))/5000),
Iif ([Item Code].currentmember is
[0004-92-053-J],(([Measures].[Quantity]*10)/5000),
Iif ([Item Code].currentmember is
[0004-92-030-K],(([Measures].[Quantity]*25)/5000), 0)
)
)
))

Quote:

- Deepak

Quote:

-----Original Message-----
hI ...

Actually after further researching.. i find that the
calculated member does
displays some amount.. ONLY when we filter by any of the
item codes . But
when the specific item codes are not filtered.. i.e its a
group by some other
dimension, then the value for the cell is 0...

Any clues as to what should be done inorder to achieve
the above... (values
to be displayed irrespectively of filtering by item code?

~Thanks

"Hitesh" wrote:

correction... Calculated member and not calculated
measure....

Thanks Brian, this one worked....But does not do
anything. i.e produces 0 in
the output.

Am i doing something wrong basically? i.e is this
correct to do this in the
way of a calculated member or something else should be
done?

The below is my calculated measure which works
syntactically... but when i
see the data it is all 0 i.e looks like it has not
satisfied any of the
conditions...

Iif ( ancestor([Item Code].currentmember,[Item Code].
[Item Code]) is
[0004-92-031-I],([Measures].[Quantity]*100)/5000,
IiF ( ancestor([Item Code].currentmember,[Item
Code].[Item Code]) is
[0015-92-003-J],(([Measures].[Quantity]*
(50*100))/5000),
IiF ( ancestor([Item Code].currentmember,[Item
Code].[Item Code]) is
[0004-92-053-J],(([Measures].[Quantity]*10)/5000),
IiF ( ancestor([Item Code].currentmember,[Item
Code].[Item Code]) is
[0004-92-030-K],(([Measures].[Quantity]*25)/5000) ,0 )
)
)
)

Thanks in advance...

"Hitesh" wrote:

Thanks Brian, this one worked....But does not do
anything. i.e produces 0 in
the output.

Am i doing something wrong basically? i.e is this
correct to do this in the
way of a calculated member or something else should
be done?

The below is my calculated measure which works
syntactically... but when i
see the data it is all 0 i.e looks like it has not
satisfied any of the
conditions...

Iif ( ancestor([Item Code].currentmember,[Item Code].
[Item Code]) is
[0004-92-031-I],([Measures].[Quantity]*100)/5000,
IiF ( ancestor([Item Code].currentmember,[Item
Code].[Item Code]) is
[0015-92-003-J],(([Measures].[Quantity]*
(50*100))/5000),
IiF ( ancestor([Item Code].currentmember,[Item
Code].[Item Code]) is
[0004-92-053-J],(([Measures].[Quantity]*10)/5000),
IiF ( ancestor([Item Code].currentmember,[Item
Code].[Item Code]) is
[0004-92-030-K],(([Measures].[Quantity]
*25)/5000) ,0 )
)
)
)

Thanks in advance...

"Brian Altmann" wrote:

Do you mean something like this:

iif ( ancestor([item code].currentmember,[item
code].[item code]) is
[qwer1-234-12], ...

You should consider the [All] member, which would
be a special case.
HTH,
Brian

"Hitesh" wrote:

Thanks Brian,
That does not work as the first [Item Code] is
the shared dimension
and the second one is a level in the dimension.

The dimension data will look like
Dimension Item COde
All Items
Item Code('qwer1-234-12')
Item Name('My Item')

So i want to perform a calculation based on the
value of the Item Code
But i am unable to access the Item Code value ...
though now i am close...

I can reach upto the level...
IIf( [Item Code].CurrentMember.Level. ??? ='asdsa-
asdsa-q23', ....

Any clues??

Thanks

"Brian Altmann" wrote:

You are trying to compare member objects with
strings.
You can either use the member name, as in:

[Item Code].[Item Code].currentmember.name
= "0004-92-031-I"

or compare objects (this is faster):

[Item Code].[Item Code].currentmember is [0004-
92-031-I]

HTH,
Brian
www.geocities.com/brianaltmann/olap.html

"Hitesh" wrote:

Hi All,

This ones a newbie Q, I am creating a
calculated member on a virtual cube
under the Measures,

with the below simple code,
Iif(([Item Code].[Item Code]) = "0004-92-031-
I" ,
([Measures].[Quantity]*100)/5000,

Iif([Item Code].[Item Code] = "0015-92-003-
J",
(([Measures].[Quantity]*(50*100))/5000),

Iif([Item Code].[Item Code] = "0004-92-053-
J",
(([Measures].[Quantity]*10)/5000),

Iif([Item Code].[Item Code] = "0004-92-030-
K",
(([Measures].[Quantity]*25)/5000) , 0)

) ) )

but this one giuves me error when i check it

I will apprecdiate if any one can guide me .
The error is UNABLE TO UPDATE THE CALUCLATED
MEMBER : FORMULA ERROR : SYNTAX
ERROR : TOKEN IS NOT VALID....

Its a sort of new bie q, but i have already
spent 4-5 hrs in figuring thois
out... and cannnot..

Thanks in advance..
.

#4

Thanks Deepak,

This seems to work in Analysis manager...
But in Pivot table in ie, it still displays all the items and not only the 4
itesm below (unless i filter out all the 4 items from the item list)

Also this time the figures in PVT table is also 0 and not the one that is
displayed in the AM.,

Thanks,
Hitesh

"Deepak" wrote:

Quote:

Looks like you want the calculated measure to work at
higher levels than [Item Code].[Item Code], whereas the
MDX was geared to that level and lower. Will this work?

Sum(Descendants([Item Code].currentmember,
[Item Code].[Item Code]),
Iif ([Item Code].currentmember is
[0004-92-031-I],([Measures].[Quantity]*100)/5000,
Iif ([Item Code].currentmember is
[0015-92-003-J],(([Measures].[Quantity]*(50*100))/5000),
Iif ([Item Code].currentmember is
[0004-92-053-J],(([Measures].[Quantity]*10)/5000),
Iif ([Item Code].currentmember is
[0004-92-030-K],(([Measures].[Quantity]*25)/5000), 0)
)
)
))

- Deepak

-----Original Message-----
hI ...

Actually after further researching.. i find that the
calculated member does
displays some amount.. ONLY when we filter by any of the
item codes . But
when the specific item codes are not filtered.. i.e its a
group by some other
dimension, then the value for the cell is 0...

Any clues as to what should be done inorder to achieve
the above... (values
to be displayed irrespectively of filtering by item code?

~Thanks

"Hitesh" wrote:

correction... Calculated member and not calculated
measure....

Thanks Brian, this one worked....But does not do
anything. i.e produces 0 in
the output.

Am i doing something wrong basically? i.e is this
correct to do this in the
way of a calculated member or something else should be
done?

The below is my calculated measure which works
syntactically... but when i
see the data it is all 0 i.e looks like it has not
satisfied any of the
conditions...

Iif ( ancestor([Item Code].currentmember,[Item Code].
[Item Code]) is
[0004-92-031-I],([Measures].[Quantity]*100)/5000,
IiF ( ancestor([Item Code].currentmember,[Item
Code].[Item Code]) is
[0015-92-003-J],(([Measures].[Quantity]*
(50*100))/5000),
IiF ( ancestor([Item Code].currentmember,[Item
Code].[Item Code]) is
[0004-92-053-J],(([Measures].[Quantity]*10)/5000),
IiF ( ancestor([Item Code].currentmember,[Item
Code].[Item Code]) is
[0004-92-030-K],(([Measures].[Quantity]*25)/5000) ,0 )
)
)
)

Thanks in advance...

"Hitesh" wrote:

Thanks Brian, this one worked....But does not do
anything. i.e produces 0 in
the output.

Am i doing something wrong basically? i.e is this
correct to do this in the
way of a calculated member or something else should
be done?

The below is my calculated measure which works
syntactically... but when i
see the data it is all 0 i.e looks like it has not
satisfied any of the
conditions...

Iif ( ancestor([Item Code].currentmember,[Item Code].
[Item Code]) is
[0004-92-031-I],([Measures].[Quantity]*100)/5000,
IiF ( ancestor([Item Code].currentmember,[Item
Code].[Item Code]) is
[0015-92-003-J],(([Measures].[Quantity]*
(50*100))/5000),
IiF ( ancestor([Item Code].currentmember,[Item
Code].[Item Code]) is
[0004-92-053-J],(([Measures].[Quantity]*10)/5000),
IiF ( ancestor([Item Code].currentmember,[Item
Code].[Item Code]) is
[0004-92-030-K],(([Measures].[Quantity]
*25)/5000) ,0 )
)
)
)

Thanks in advance...

"Brian Altmann" wrote:

Do you mean something like this:

iif ( ancestor([item code].currentmember,[item
code].[item code]) is
[qwer1-234-12], ...

You should consider the [All] member, which would
be a special case.
HTH,
Brian

"Hitesh" wrote:

Thanks Brian,
That does not work as the first [Item Code] is
the shared dimension
and the second one is a level in the dimension.

The dimension data will look like
Dimension Item COde
All Items
Item Code('qwer1-234-12')
Item Name('My Item')

So i want to perform a calculation based on the
value of the Item Code
But i am unable to access the Item Code value ...
though now i am close...

I can reach upto the level...
IIf( [Item Code].CurrentMember.Level. ??? ='asdsa-
asdsa-q23', ....

Any clues??

Thanks

"Brian Altmann" wrote:

You are trying to compare member objects with
strings.
You can either use the member name, as in:

[Item Code].[Item Code].currentmember.name
= "0004-92-031-I"

or compare objects (this is faster):

[Item Code].[Item Code].currentmember is [0004-
92-031-I]

HTH,
Brian
www.geocities.com/brianaltmann/olap.html

"Hitesh" wrote:

Hi All,

This ones a newbie Q, I am creating a
calculated member on a virtual cube
under the Measures,

with the below simple code,
Iif(([Item Code].[Item Code]) = "0004-92-031-
I" ,
([Measures].[Quantity]*100)/5000,

Iif([Item Code].[Item Code] = "0015-92-003-
J",
(([Measures].[Quantity]*(50*100))/5000),

Iif([Item Code].[Item Code] = "0004-92-053-
J",
(([Measures].[Quantity]*10)/5000),

Iif([Item Code].[Item Code] = "0004-92-030-
K",
(([Measures].[Quantity]*25)/5000) , 0)

) ) )

but this one giuves me error when i check it

I will apprecdiate if any one can guide me .
The error is UNABLE TO UPDATE THE CALUCLATED
MEMBER : FORMULA ERROR : SYNTAX
ERROR : TOKEN IS NOT VALID....

Its a sort of new bie q, but i have already
spent 4-5 hrs in figuring thois
out... and cannnot..

Thanks in advance..
.

Thread Tools
Show Printable Version Email this Page
Display Modes
Linear Mode Switch to Hybrid Mode Switch to Threaded Mode

Errpr in :Calculated member builder

microsoft.public.sqlserver.olap microsoft.public.sqlserver.olap

Errpr in :Calculated member builder - 09-12-2004 , 01:25 PM

RE: Errpr in :Calculated member builder - 09-13-2004 , 11:08 AM

RE: Errpr in :Calculated member builder - 09-13-2004 , 01:59 PM

RE: Errpr in :Calculated member builder - 09-13-2004 , 10:45 PM