dbTalk Databases Forums  

Calculated Member Issue

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


Discuss Calculated Member Issue in the microsoft.public.sqlserver.olap forum.



Reply
 
Thread Tools Display Modes
  #1  
Old   
Mark Dengler
 
Posts: n/a

Default Calculated Member Issue - 07-07-2003 , 03:53 PM






The following is the expression that I use to create a calculates
member:

IIF ([Measures].[Revenue Gross] <> 0,
IIF (Descendants([Pricing Type].CurrentMember,,Leaves),[Pricing
Type].CurrentMember.Properties("Unit Type") = "impressions" and
[Measures].[Impressions] <> 0,
Val([Measures].[Revenue Gross] / [Measures].[Impressions]) *
Val([Pricing Type].CurrentMember.Properties("Divisor")),
IIF ([Pricing Type].CurrentMember.Properties("Unit Type") =
"clicks" and [Measures].[Clicks] <> 0,
Val([Measures].[Revenue Gross] / [Measures].[Clicks]) *
Val([Pricing Type].CurrentMember.Properties("Divisor")),
0)
),
0)


It works fine, but only if I use the [Pricing Type] dimension as a
filter, row, or column selection. I would like to have this
calculated member correctly available even if the [Pricing Type]
dimension is not in the picture. Is there a way?

Reply With Quote
  #2  
Old   
Mark Dengler
 
Posts: n/a

Default Re: Calculated Member Issue - 07-08-2003 , 10:41 AM






More information...

The problem looks to be related to the "All Level" of the Pricing Type
dimension. As long as the measures selected are based off of the
Pricing Type dimension "all" level, then what I experience is that the
CostPerUnit calculated member cell generates an #ERR as its value.
When I click on the cell, it says that the property "Unit Type" is not
valid. If I set the "All Level" to "no" in the Pricing Type dimension
properties, then I get the desired result meaning the CostPerUnit
calculated member is being calculated properly for any variations of
filter, row, or column combinations with one exception. The problem
is that the measures represented are only those of the first Pricing
Type member. When I include the Pricing Type dimension as a filter,
row, or column selection then all Pricing Type members are included.

It looks like I need the "All Level" of the Pricing type dimension, so
how do I get around this issue?

By the way, I thing I accidently posted an experimental mdx statement
in my initial description. Please review the statement below.

IIF ([Measures].[Revenue Gross] <> 0,
IIF ([Pricing Type].CurrentMember.Properties("Unit Type") =
"impressions" and [Measures].[Impressions] <> 0,
Val([Measures].[Revenue Gross] / [Measures].[Impressions]) *
Val([Pricing Type].CurrentMember.Properties("Divisor")),
IIF ([Pricing Type].CurrentMember.Properties("Unit Type") =
"clicks" and [Measures].[Clicks] <> 0,
Val([Measures].[Revenue Gross] / [Measures].[Clicks]) *
Val([Pricing Type].CurrentMember.Properties("Divisor")),
0)
),
0)

Thanks again to all of those who respond.

Reply With Quote
Reply




Thread Tools
Display Modes

Posting Rules
You may not post new threads
You may not post replies
You may not post attachments
You may not edit your posts

vB code is On
Smilies are On
[IMG] code is On
HTML code is Off



Powered by vBulletin Version 3.5.3
Copyright ©2000 - 2012, Jelsoft Enterprises Ltd.