Re: Creating a measure using member properties

Tech-Archive recommends: Fix windows errors by optimizing your registry

If you don't want to see AS to aggregate the values, just define a calucated
member or customer roll-up formula to override the values.

"fsanchez" wrote:

Hi Yongli,
I want to expand on my respond from Yesterday. When I am looking at the
Min, Max, and Avg measures for All Period and Office, I don't want the
Analysis Services to aggregate the values. I want to see the Min, Max, and
Avg for all the periods.

Regards,
Fernando

"yongli" wrote:

It looks like that there are something wrong with the field "Base Rate".
What's the origianl data type for the field? if this is non-numerical, you
have to make sure to get rid of invalid characters before converting to
numerical type.

"fsanchez" wrote:

Hi Yongli,
I created the following measure to see what I am getting back...

Commission Rate = IIF(IsEmpty(CDbl([DEAL].CurrentMember.Properties("Base
Rate"))),NULL, CDbl([DEAL].CurrentMember.Properties("Base Rate")))


All of the values are -2,147,467,259 and not sure why I am getting this.

When I create a measure as followed, I get back null values.

Avg Commission Rate = IIF(IsEmpty(CDbl([DEAL].CurrentMember.Properties("Base
Rate"))),NULL,IIF(CDbl([DEAL].CurrentMember.Properties("Base Rate")) <=
0,NULL,CDbl([DEAL].CurrentMember.Properties("Base Rate")) / [Measures].[Deal
Count]))

"yongli" wrote:

divide by 0 error? If so, use conditional statement to avoid this.

"fsanchez" wrote:

Hi Akshai,
I will try that but the error I am getting just say ERR when browsing the
data..

"Akshai Mirchandani [MS]" wrote:

What is the error?

I believe you may need to cast the returned value into a typed value either
by using:
Avg Commission Rate = [DEAL].Properties("Base Rate", TYPED) /
[Measures].[Deal Count]
or
Avg Commission Rate = CDBL( [DEAL].Properties("Base Rate") ) /
[Measures].[Deal Count]

The Properties method returns strings and you would need to either obtain
the raw data type or cast the string to a numeric data type...

Thanks,
Akshai
--
Try out the MSDN Forums for Analysis Services at:
http://forums.microsoft.com/MSDN/ShowForum.aspx?ForumID=83&SiteID=1

This posting is provided "AS IS" with no warranties, and confers no rights
Please do not send email directly to this alias. This alias is for newsgroup
purposes only.

"fsanchez" <fsanchez@xxxxxxxxxxxxxxxxxxxxxxxxx> wrote in message
news:778B61BC-C50B-432D-AC35-C6EA6097CF0E@xxxxxxxxxxxxxxxx
Hi,
I have a DEAL dimension that has several member proproties (Base Rate,
Spread Rate, etc.). I want to calculate the min, max, and avg for each of
the member properties.

I have created a Deal Count and the following calculated measure but I get
an error back.

Avg Commission Rate = [DEAL].Properties("Base Rate") / [Measures].[Deal
Count]



.

Relevant Pages

  • Re: Creating a measure using member properties
    ... Can you provide me an example in how to formulate this calculated member? ... "yongli" wrote: ... "fsanchez" wrote: ... the raw data type or cast the string to a numeric data type... ...
    (microsoft.public.sqlserver.olap)
  • Re: How to fix this structure?
    ... > figure out how to fix this structure that seems to make wrong output. ... array of data type WORD as one member and a counter to keep a count ... on the number of words in the list as the other member. ... typedef struct WORD ...
    (comp.lang.c)
  • Re: persist an array of data
    ... and tell me how can I specify the data type of each array's element (and, ... the data type of each arrayList element)? ... > ArrayList which contains some certain class's instance's arraies? ... > them has a member, the member is a certain class's object array. ...
    (microsoft.public.dotnet.framework.aspnet.webservices)
  • Re: Application speed against data type
    ... I want to change the data type of member from "unsigned short" ... "int". ... it depends on so much issues that benchmarking is the only way to know the ...
    (microsoft.public.vc.language)
  • Re: Formatting a Date Dimension
    ... the text of the member name and member key properties into the column ... DATENAMEfunction which returns a string data type and would ... Darren Gosbell [MCSD] ... "Andrew Oliner" wrote: ...
    (microsoft.public.sqlserver.olap)