|
Home > Archive > MS SQL Server OLAP > September 2005 > Error Designing Caluclated Member
You are viewing an archived Text-only version of the thread.
To view this thread in it's original format and/or if you want to reply to
this thread please [click here]
| Author |
Error Designing Caluclated Member
|
|
|
| I am trying this expression in the Calculated Member Builder
[Measures].[Cost]/[Building].[Head Count]
And I get the following error:
Unable to update the calculated member.
Formula error - cannot bind: unknown dimension or member: "[Building].[Head
Count]"
This seems simple unless I cannot use a dimension member in the calculation.
Thanks in advance
GH
| |
|
| I have figured out the binding issue ... is really telling me i have a poorly
formed MDX expression.
I am not trying this test expression as my Calculated Member:
(Calendar.CurrentMember).[Location].[Head Count]
This is returning an #err in all cells returned.
My challenge is finding Cost\Person
I have the following dimensions:
Calendar with Date/Year/QTR/Month/MonthDay/WeekDay
Location with Area/Region/District/Branch/Building/HeadCount
The fact table fctCosts with the measures FixedCost,VriblCost
I want to be able to add the Calculated Member Cost/person where (FixedCost
+ VrblCost) / HeadCount = Cost\Person
But I cannot get the value of (Calendar.CurrentMember).[Location].[Head
Count] to print.
--
Thanks in advance
GH
"GH" wrote:
> I am trying this expression in the Calculated Member Builder
>
> [Measures].[Cost]/[Building].[Head Count]
>
> And I get the following error:
>
> Unable to update the calculated member.
> Formula error - cannot bind: unknown dimension or member: "[Building].[Head
> Count]"
>
> This seems simple unless I cannot use a dimension member in the calculation.
>
> Thanks in advance
> GH
| |
| Peter Yang [MSFT] 2005-09-09, 7:24 am |
| Hello,
To understand the issue better, I'd like to know the detailed meaning of
HeadCount. Is it a member property of building member? Or they are staffs
in the building?
Best Regards,
Peter Yang
MCSE2000/2003, MCSA, MCDBA
Microsoft Online Partner Support
When responding to posts, please "Reply to Group" via your newsreader so
that others may learn and benefit from your issue.
====================
====================
=============
This posting is provided "AS IS" with no warranties, and confers no rights.
--------------------
| Thread-Topic: Error Designing Caluclated Member
| thread-index: AcW0xgKC3VR1obzsTCa1
QBFjxi7b1Q==
| X-WBNR-Posting-Host: 131.107.0.79
| From: "=?Utf-8?B?R0g=?=" <vakar@community.nospam>
| References: <9E1D8EA7-A670-4794-AD79- 8141A5D4B19E@microso
ft.com>
| Subject: RE: Error Designing Caluclated Member
| Date: Thu, 8 Sep 2005 15:38:18 -0700
| Lines: 42
| Message-ID: <3BB9D044-DB7F-448F-B836- CE26EF4B3A19@microso
ft.com>
| MIME-Version: 1.0
| Content-Type: text/plain;
| charset="Utf-8"
| Content-Transfer-Encoding: 7bit
| X-Newsreader: Microsoft CDO for Windows 2000
| Content-Class: urn:content-classes:message
| Importance: normal
| Priority: normal
| X-MimeOLE: Produced By Microsoft MimeOLE V6.00.3790.0
| Newsgroups: microsoft.public.sqlserver.olap
| NNTP-Posting-Host: TK2MSFTNGXA03.phx.gbl 10.40.2.250
| Path: TK2MSFTNGXA02.phx.gbl!TK2MSFTNGXA03.phx.gbl
| Xref: TK2MSFTNGXA02.phx.gbl microsoft.public.sqlserver.olap:62587
| X-Tomcat-NG: microsoft.public.sqlserver.olap
|
| I have figured out the binding issue ... is really telling me i have a
poorly
| formed MDX expression.
|
| I am not trying this test expression as my Calculated Member:
| (Calendar.CurrentMember).[Location].[Head Count]
|
| This is returning an #err in all cells returned.
|
| My challenge is finding Cost\Person
|
| I have the following dimensions:
| Calendar with Date/Year/QTR/Month/MonthDay/WeekDay
| Location with Area/Region/District/Branch/Building/HeadCount
|
| The fact table fctCosts with the measures FixedCost,VriblCost
|
| I want to be able to add the Calculated Member Cost/person where
(FixedCost
| + VrblCost) / HeadCount = Cost\Person
|
| But I cannot get the value of (Calendar.CurrentMember).[Location].[Head
| Count] to print.
| --
| Thanks in advance
| GH
|
|
| "GH" wrote:
|
| > I am trying this expression in the Calculated Member Builder
| >
| > [Measures].[Cost]/[Building].[Head Count]
| >
| > And I get the following error:
| >
| > Unable to update the calculated member.
| > Formula error - cannot bind: unknown dimension or member:
"[Building].[Head
| > Count]"
| >
| > This seems simple unless I cannot use a dimension member in the
calculation.
| >
| > Thanks in advance
| > GH
|
| |
|
| Headcount is the number of staff permanetly assigned to that building.
So if the annual total costs of the building is $100,000 and the Headcount
is 10 then the Annual CostPerPerson will be $10,000.
--
Thanks in advance
GH
"Peter Yang [MSFT]" wrote:
> Hello,
>
> To understand the issue better, I'd like to know the detailed meaning of
> HeadCount. Is it a member property of building member? Or they are staffs
> in the building?
>
> Best Regards,
>
> Peter Yang
> MCSE2000/2003, MCSA, MCDBA
> Microsoft Online Partner Support
>
> When responding to posts, please "Reply to Group" via your newsreader so
> that others may learn and benefit from your issue.
>
> ====================
====================
=============
>
>
>
> This posting is provided "AS IS" with no warranties, and confers no rights.
>
>
> --------------------
> | Thread-Topic: Error Designing Caluclated Member
> | thread-index: AcW0xgKC3VR1obzsTCa1
QBFjxi7b1Q==
> | X-WBNR-Posting-Host: 131.107.0.79
> | From: "=?Utf-8?B?R0g=?=" <vakar@community.nospam>
> | References: <9E1D8EA7-A670-4794-AD79- 8141A5D4B19E@microso
ft.com>
> | Subject: RE: Error Designing Caluclated Member
> | Date: Thu, 8 Sep 2005 15:38:18 -0700
> | Lines: 42
> | Message-ID: <3BB9D044-DB7F-448F-B836- CE26EF4B3A19@microso
ft.com>
> | MIME-Version: 1.0
> | Content-Type: text/plain;
> | charset="Utf-8"
> | Content-Transfer-Encoding: 7bit
> | X-Newsreader: Microsoft CDO for Windows 2000
> | Content-Class: urn:content-classes:message
> | Importance: normal
> | Priority: normal
> | X-MimeOLE: Produced By Microsoft MimeOLE V6.00.3790.0
> | Newsgroups: microsoft.public.sqlserver.olap
> | NNTP-Posting-Host: TK2MSFTNGXA03.phx.gbl 10.40.2.250
> | Path: TK2MSFTNGXA02.phx.gbl!TK2MSFTNGXA03.phx.gbl
> | Xref: TK2MSFTNGXA02.phx.gbl microsoft.public.sqlserver.olap:62587
> | X-Tomcat-NG: microsoft.public.sqlserver.olap
> |
> | I have figured out the binding issue ... is really telling me i have a
> poorly
> | formed MDX expression.
> |
> | I am not trying this test expression as my Calculated Member:
> | (Calendar.CurrentMember).[Location].[Head Count]
> |
> | This is returning an #err in all cells returned.
> |
> | My challenge is finding Cost\Person
> |
> | I have the following dimensions:
> | Calendar with Date/Year/QTR/Month/MonthDay/WeekDay
> | Location with Area/Region/District/Branch/Building/HeadCount
> |
> | The fact table fctCosts with the measures FixedCost,VriblCost
> |
> | I want to be able to add the Calculated Member Cost/person where
> (FixedCost
> | + VrblCost) / HeadCount = Cost\Person
> |
> | But I cannot get the value of (Calendar.CurrentMember).[Location].[Head
> | Count] to print.
> | --
> | Thanks in advance
> | GH
> |
> |
> | "GH" wrote:
> |
> | > I am trying this expression in the Calculated Member Builder
> | >
> | > [Measures].[Cost]/[Building].[Head Count]
> | >
> | > And I get the following error:
> | >
> | > Unable to update the calculated member.
> | > Formula error - cannot bind: unknown dimension or member:
> "[Building].[Head
> | > Count]"
> | >
> | > This seems simple unless I cannot use a dimension member in the
> calculation.
> | >
> | > Thanks in advance
> | > GH
> |
>
>
| |
|
| HI Peter,
My latest approach is to make the Headcount a Member Property of the
BuildingName.
Then when using the CalculatedMemberBuil
der to create a calculated measure
or Member using MDX expression
[Location_HC].CurrentMember.Properties("HeadCount") returns a #ERR in the
grid.
--
Thanks in advance
GH
"GH" wrote:
[color=darkred]
> Headcount is the number of staff permanetly assigned to that building.
>
> So if the annual total costs of the building is $100,000 and the Headcount
> is 10 then the Annual CostPerPerson will be $10,000.
> --
> Thanks in advance
> GH
>
>
> "Peter Yang [MSFT]" wrote:
>
| |
| Peter Yang [MSFT] 2005-09-12, 7:23 am |
| Hello,
Because the headcount is only the member properties of the leaf level of
location dimension, there will be error if you use them directly in
caculated member. You may want to try the following MDX:
With
Member Measures.[headcount] As
' IIf
(
IsLeaf ( location.CurrentMember ),
val( location.CurrentMember.Properties("headcount") ),
Sum
(
Descendants( location. CurrentMember,,Leave
s ),
Measures.[store sqft]
)
) '
Member Measures.[Cost Per headcount] As
' Measures.[store sales] / Measures.[headcount] '
select
{ Measures.[headcount], Measures.[Cost Per headcount] } on columns,
NON EMPTY [location].[building].members on rows
from costcube
Hope this is helpful.
Regards,
Peter Yang
MCSE2000/2003, MCSA, MCDBA
Microsoft Online Partner Support
When responding to posts, please "Reply to Group" via your newsreader so
that others may learn and benefit from your issue.
====================
====================
=============
This posting is provided "AS IS" with no warranties, and confers no rights.
--------------------
| Thread-Topic: Error Designing Caluclated Member
| thread-index: AcW1mpfCZep34eQLR3Wo
xR7d3sDR2Q==
| X-WBNR-Posting-Host: 131.107.0.79
| From: "=?Utf-8?B?R0g=?=" <vakar@community.nospam>
| References: <9E1D8EA7-A670-4794-AD79- 8141A5D4B19E@microso
ft.com>
<3BB9D044-DB7F-448F-B836- CE26EF4B3A19@microso
ft.com>
<udRVilStFHA.780@TK2MSFTNGXA01.phx.gbl>
<C8FA01CE-9763-4977-8344- 5EE0197F96C2@microso
ft.com>
| Subject: RE: Error Designing Caluclated Member
| Date: Fri, 9 Sep 2005 17:00:02 -0700
| Lines: 123
| Message-ID: <AF5113B7-374B-4F2D-8391- B7B74BB6A7C7@microso
ft.com>
| MIME-Version: 1.0
| Content-Type: text/plain;
| charset="Utf-8"
| Content-Transfer-Encoding: 7bit
| X-Newsreader: Microsoft CDO for Windows 2000
| Content-Class: urn:content-classes:message
| Importance: normal
| Priority: normal
| X-MimeOLE: Produced By Microsoft MimeOLE V6.00.3790.0
| Newsgroups: microsoft.public.sqlserver.olap
| NNTP-Posting-Host: TK2MSFTNGXA03.phx.gbl 10.40.2.250
| Path: TK2MSFTNGXA01.phx.gbl!TK2MSFTNGXA03.phx.gbl
| Xref: TK2MSFTNGXA01.phx.gbl microsoft.public.sqlserver.olap:14373
| X-Tomcat-NG: microsoft.public.sqlserver.olap
|
| HI Peter,
|
| My latest approach is to make the Headcount a Member Property of the
| BuildingName.
|
| Then when using the CalculatedMemberBuil
der to create a calculated
measure
| or Member using MDX expression
| [Location_HC].CurrentMember.Properties("HeadCount") returns a #ERR in the
| grid.
| --
| Thanks in advance
| GH
|
|
| "GH" wrote:
|
| > Headcount is the number of staff permanetly assigned to that building.
| >
| > So if the annual total costs of the building is $100,000 and the
Headcount
| > is 10 then the Annual CostPerPerson will be $10,000.
| > --
| > Thanks in advance
| > GH
| >
| >
| > "Peter Yang [MSFT]" wrote:
| >
| > > Hello,
| > >
| > > To understand the issue better, I'd like to know the detailed meaning
of
| > > HeadCount. Is it a member property of building member? Or they are
staffs
| > > in the building?
| > >
| > > Best Regards,
| > >
| > > Peter Yang
| > > MCSE2000/2003, MCSA, MCDBA
| > > Microsoft Online Partner Support
| > >
| > > When responding to posts, please "Reply to Group" via your newsreader
so
| > > that others may learn and benefit from your issue.
| > >
| > > ====================
====================
=============
| > >
| > >
| > >
| > > This posting is provided "AS IS" with no warranties, and confers no
rights.
| > >
| > >
| > > --------------------
| > > | Thread-Topic: Error Designing Caluclated Member
| > > | thread-index: AcW0xgKC3VR1obzsTCa1
QBFjxi7b1Q==
| > > | X-WBNR-Posting-Host: 131.107.0.79
| > > | From: "=?Utf-8?B?R0g=?=" <vakar@community.nospam>
| > > | References: <9E1D8EA7-A670-4794-AD79- 8141A5D4B19E@microso
ft.com>
| > > | Subject: RE: Error Designing Caluclated Member
| > > | Date: Thu, 8 Sep 2005 15:38:18 -0700
| > > | Lines: 42
| > > | Message-ID: <3BB9D044-DB7F-448F-B836- CE26EF4B3A19@microso
ft.com>
| > > | MIME-Version: 1.0
| > > | Content-Type: text/plain;
| > > | charset="Utf-8"
| > > | Content-Transfer-Encoding: 7bit
| > > | X-Newsreader: Microsoft CDO for Windows 2000
| > > | Content-Class: urn:content-classes:message
| > > | Importance: normal
| > > | Priority: normal
| > > | X-MimeOLE: Produced By Microsoft MimeOLE V6.00.3790.0
| > > | Newsgroups: microsoft.public.sqlserver.olap
| > > | NNTP-Posting-Host: TK2MSFTNGXA03.phx.gbl 10.40.2.250
| > > | Path: TK2MSFTNGXA02.phx.gbl!TK2MSFTNGXA03.phx.gbl
| > > | Xref: TK2MSFTNGXA02.phx.gbl microsoft.public.sqlserver.olap:62587
| > > | X-Tomcat-NG: microsoft.public.sqlserver.olap
| > > |
| > > | I have figured out the binding issue ... is really telling me i
have a
| > > poorly
| > > | formed MDX expression.
| > > |
| > > | I am not trying this test expression as my Calculated Member:
| > > | (Calendar.CurrentMember).[Location].[Head Count]
| > > |
| > > | This is returning an #err in all cells returned.
| > > |
| > > | My challenge is finding Cost\Person
| > > |
| > > | I have the following dimensions:
| > > | Calendar with Date/Year/QTR/Month/MonthDay/WeekDay
| > > | Location with Area/Region/District/Branch/Building/HeadCount
| > > |
| > > | The fact table fctCosts with the measures FixedCost,VriblCost
| > > |
| > > | I want to be able to add the Calculated Member Cost/person where
| > > (FixedCost
| > > | + VrblCost) / HeadCount = Cost\Person
| > > |
| > > | But I cannot get the value of
(Calendar.CurrentMember).[Location].[Head
| > > | Count] to print.
| > > | --
| > > | Thanks in advance
| > > | GH
| > > |
| > > |
| > > | "GH" wrote:
| > > |
| > > | > I am trying this expression in the Calculated Member Builder
| > > | >
| > > | > [Measures].[Cost]/[Building].[Head Count]
| > > | >
| > > | > And I get the following error:
| > > | >
| > > | > Unable to update the calculated member.
| > > | > Formula error - cannot bind: unknown dimension or member:
| > > "[Building].[Head
| > > | > Count]"
| > > | >
| > > | > This seems simple unless I cannot use a dimension member in the
| > > calculation.
| > > | >
| > > | > Thanks in advance
| > > | > GH
| > > |
| > >
| > >
|
| |
|
| HI Peter,
Your post was helpful and I have made progress.
Currently I am using one dimension Location_HC with the following levels
Region, Country,City, Building Name.
Building Name has the Property "Head Count"
There is one measure "Cost".
I am trying to create a CalculateMeasure CostPerPerson ...
Step one is to get the headcount correctly calculated across the tree.
The following MDX expression works great and the calculations are correct at
all levels:
IIf(IsLeaf(location_
hc.CurrentMember),
val(location_hc.CurrentMember.Properties("head count")),
SUM(Descendants(loca
tion_hc.CurrentMember,[building Name])))
Step two is to divide the Cost by the headcount so I tried this expression:
IIf(IsLeaf(Location_
hc.CurrentMember),
(Measures.Cost)/(val(Location_hc.CurrentMember.Properties("head
count"))),(Measures.Cost)/ (SUM(Descendants(loc
ation_hc.CurrentMember,[Building Name]))))
Only the leaf level calculates correctly, none of the other levels calculate
correctly.
The only difference is the division .... I don't understand!!
--
Thanks in advance
GH
"Peter Yang [MSFT]" wrote:
> Hello,
>
> Because the headcount is only the member properties of the leaf level of
> location dimension, there will be error if you use them directly in
> caculated member. You may want to try the following MDX:
>
> With
> Member Measures.[headcount] As
> ' IIf
> (
> IsLeaf ( location.CurrentMember ),
> val( location.CurrentMember.Properties("headcount") ),
> Sum
> (
> Descendants( location. CurrentMember,,Leave
s ),
> Measures.[store sqft]
> )
> ) '
>
> Member Measures.[Cost Per headcount] As
> ' Measures.[store sales] / Measures.[headcount] '
>
> select
> { Measures.[headcount], Measures.[Cost Per headcount] } on columns,
> NON EMPTY [location].[building].members on rows
> from costcube
>
> Hope this is helpful.
>
> Regards,
>
> Peter Yang
> MCSE2000/2003, MCSA, MCDBA
> Microsoft Online Partner Support
>
> When responding to posts, please "Reply to Group" via your newsreader so
> that others may learn and benefit from your issue.
>
> ====================
====================
=============
>
> This posting is provided "AS IS" with no warranties, and confers no rights.
>
>
> --------------------
> | Thread-Topic: Error Designing Caluclated Member
> | thread-index: AcW1mpfCZep34eQLR3Wo
xR7d3sDR2Q==
> | X-WBNR-Posting-Host: 131.107.0.79
> | From: "=?Utf-8?B?R0g=?=" <vakar@community.nospam>
> | References: <9E1D8EA7-A670-4794-AD79- 8141A5D4B19E@microso
ft.com>
> <3BB9D044-DB7F-448F-B836- CE26EF4B3A19@microso
ft.com>
> <udRVilStFHA.780@TK2MSFTNGXA01.phx.gbl>
> <C8FA01CE-9763-4977-8344- 5EE0197F96C2@microso
ft.com>
> | Subject: RE: Error Designing Caluclated Member
> | Date: Fri, 9 Sep 2005 17:00:02 -0700
> | Lines: 123
> | Message-ID: <AF5113B7-374B-4F2D-8391- B7B74BB6A7C7@microso
ft.com>
> | MIME-Version: 1.0
> | Content-Type: text/plain;
> | charset="Utf-8"
> | Content-Transfer-Encoding: 7bit
> | X-Newsreader: Microsoft CDO for Windows 2000
> | Content-Class: urn:content-classes:message
> | Importance: normal
> | Priority: normal
> | X-MimeOLE: Produced By Microsoft MimeOLE V6.00.3790.0
> | Newsgroups: microsoft.public.sqlserver.olap
> | NNTP-Posting-Host: TK2MSFTNGXA03.phx.gbl 10.40.2.250
> | Path: TK2MSFTNGXA01.phx.gbl!TK2MSFTNGXA03.phx.gbl
> | Xref: TK2MSFTNGXA01.phx.gbl microsoft.public.sqlserver.olap:14373
> | X-Tomcat-NG: microsoft.public.sqlserver.olap
> |
> | HI Peter,
> |
> | My latest approach is to make the Headcount a Member Property of the
> | BuildingName.
> |
> | Then when using the CalculatedMemberBuil
der to create a calculated
> measure
> | or Member using MDX expression
> | [Location_HC].CurrentMember.Properties("HeadCount") returns a #ERR in the
> | grid.
> | --
> | Thanks in advance
> | GH
> |
> |
> | "GH" wrote:
> |
> | > Headcount is the number of staff permanetly assigned to that building.
> | >
> | > So if the annual total costs of the building is $100,000 and the
> Headcount
> | > is 10 then the Annual CostPerPerson will be $10,000.
> | > --
> | > Thanks in advance
> | > GH
> | >
> | >
> | > "Peter Yang [MSFT]" wrote:
> | >
> | > > Hello,
> | > >
> | > > To understand the issue better, I'd like to know the detailed meaning
> of
> | > > HeadCount. Is it a member property of building member? Or they are
> staffs
> | > > in the building?
> | > >
> | > > Best Regards,
> | > >
> | > > Peter Yang
> | > > MCSE2000/2003, MCSA, MCDBA
> | > > Microsoft Online Partner Support
> | > >
> | > > When responding to posts, please "Reply to Group" via your newsreader
> so
> | > > that others may learn and benefit from your issue.
> | > >
> | > > ====================
====================
=============
> | > >
> | > >
> | > >
> | > > This posting is provided "AS IS" with no warranties, and confers no
> rights.
> | > >
> | > >
> | > > --------------------
> | > > | Thread-Topic: Error Designing Caluclated Member
> | > > | thread-index: AcW0xgKC3VR1obzsTCa1
QBFjxi7b1Q==
> | > > | X-WBNR-Posting-Host: 131.107.0.79
> | > > | From: "=?Utf-8?B?R0g=?=" <vakar@community.nospam>
> | > > | References: <9E1D8EA7-A670-4794-AD79- 8141A5D4B19E@microso
ft.com>
> | > > | Subject: RE: Error Designing Caluclated Member
> | > > | Date: Thu, 8 Sep 2005 15:38:18 -0700
> | > > | Lines: 42
> | > > | Message-ID: <3BB9D044-DB7F-448F-B836- CE26EF4B3A19@microso
ft.com>
> | > > | MIME-Version: 1.0
> | > > | Content-Type: text/plain;
> | > > | charset="Utf-8"
> | > > | Content-Transfer-Encoding: 7bit
> | > > | X-Newsreader: Microsoft CDO for Windows 2000
> | > > | Content-Class: urn:content-classes:message
> | > > | Importance: normal
> | > > | Priority: normal
> | > > | X-MimeOLE: Produced By Microsoft MimeOLE V6.00.3790.0
> | > > | Newsgroups: microsoft.public.sqlserver.olap
> | > > | NNTP-Posting-Host: TK2MSFTNGXA03.phx.gbl 10.40.2.250
> | > > | Path: TK2MSFTNGXA02.phx.gbl!TK2MSFTNGXA03.phx.gbl
> | > > | Xref: TK2MSFTNGXA02.phx.gbl microsoft.public.sqlserver.olap:62587
> | > > | X-Tomcat-NG: microsoft.public.sqlserver.olap
> | > > |
> | > > | I have figured out the binding issue ... is really telling me i
> have a
> | > > poorly
> | > > | formed MDX expression.
> | > > |
> | > > | I am not trying this test expression as my Calculated Member:
> | > > | (Calendar.CurrentMember).[Location].[Head Count]
> | > > |
> | > > | This is returning an #err in all cells returned.
> | > > |
> | > > | My challenge is finding Cost\Person
> | > > |
> | > > | I have the following dimensions:
> | > > | Calendar with Date/Year/QTR/Month/MonthDay/WeekDay
> | > > | Location with Area/Region/District/Branch/Building/HeadCount
> | > > |
> | > > | The fact table fctCosts with the measures FixedCost,VriblCost
> | > > |
> | > > | I want to be able to add the Calculated Member Cost/person where
> | > > (FixedCost
> | > > | + VrblCost) / HeadCount = Cost\Person
> | > > |
> | > > | But I cannot get the value of
> (Calendar.CurrentMember).[Location].[Head
> | > > | Count] to print.
> | > > | --
> | > > | Thanks in advance
> | > > | GH
> | > > |
> | > > |
> | > > | "GH" wrote:
> | > > |
> | > > | > I am trying this expression in the Calculated Member Builder
> | > > | >
> | > > | > [Measures].[Cost]/[Building].[Head Count]
> | > > | >
> | > > | > And I get the following error:
> | > > | >
> | > > | > Unable to update the calculated member.
> | > > | > Formula error - cannot bind: unknown dimension or member:
> | > > "[Building].[Head
> | > > | > Count]"
> | > > | >
> | > > | > This seems simple unless I cannot use a dimension member in the
> | > > calculation.
> | > > | >
> | > > | > Thanks in advance
> | > > | > GH
> | > > |
> | > >
> | > >
> |
>
>
| |
| Peter Yang [MSFT] 2005-09-16, 7:23 am |
|
Hello,
I think the reason is that cost is already a measure and you does not need
to manually sum it again:
You may want to use another caculated member to do this as I mententioned:
Member Measures.[Cost Per headcount] As ' Measures.cost /
Measures.[headcount]
Regards,
Peter Yang
MCSE2000/2003, MCSA, MCDBA
Microsoft Online Partner Support
When responding to posts, please "Reply to Group" via your newsreader so
that others may learn and benefit from your issue.
====================
====================
=============
This posting is provided "AS IS" with no warranties, and confers no rights.
--------------------
| Thread-Topic: Error Designing Caluclated Member
| thread-index: AcW6RShs4dqmPpKHRW6v
CbMqqN1wcg==
| X-WBNR-Posting-Host: 131.107.0.79
| From: "=?Utf-8?B?R0g=?=" <vakar@community.nospam>
| References: <9E1D8EA7-A670-4794-AD79- 8141A5D4B19E@microso
ft.com>
<3BB9D044-DB7F-448F-B836- CE26EF4B3A19@microso
ft.com>
<udRVilStFHA.780@TK2MSFTNGXA01.phx.gbl>
<C8FA01CE-9763-4977-8344- 5EE0197F96C2@microso
ft.com>
<AF5113B7-374B-4F2D-8391- B7B74BB6A7C7@microso
ft.com>
<7h$#Hf3tFHA.768@TK2MSFTNGXA01.phx.gbl>
| Subject: RE: Error Designing Caluclated Member
| Date: Thu, 15 Sep 2005 15:31:04 -0700
| Lines: 244
| Message-ID: <9B081A15-60A0-4E0C-857D- 84414F88DDF4@microso
ft.com>
| MIME-Version: 1.0
| Content-Type: text/plain;
| charset="Utf-8"
| Content-Transfer-Encoding: 7bit
| X-Newsreader: Microsoft CDO for Windows 2000
| Content-Class: urn:content-classes:message
| Importance: normal
| Priority: normal
| X-MimeOLE: Produced By Microsoft MimeOLE V6.00.3790.0
| Newsgroups: microsoft.public.sqlserver.olap
| NNTP-Posting-Host: TK2MSFTNGXA03.phx.gbl 10.40.2.250
| Path: TK2MSFTNGXA01.phx.gbl!TK2MSFTNGXA03.phx.gbl
| Xref: TK2MSFTNGXA01.phx.gbl microsoft.public.sqlserver.olap:14543
| X-Tomcat-NG: microsoft.public.sqlserver.olap
|
| HI Peter,
|
| Your post was helpful and I have made progress.
|
| Currently I am using one dimension Location_HC with the following levels
| Region, Country,City, Building Name.
|
| Building Name has the Property "Head Count"
|
| There is one measure "Cost".
|
| I am trying to create a CalculateMeasure CostPerPerson ...
|
| Step one is to get the headcount correctly calculated across the tree.
|
| The following MDX expression works great and the calculations are correct
at
| all levels:
| IIf(IsLeaf(location_
hc.CurrentMember),
| val(location_hc.CurrentMember.Properties("head count")),
| SUM(Descendants(loca
tion_hc.CurrentMember,[building Name])))
|
| Step two is to divide the Cost by the headcount so I tried this
expression:
| IIf(IsLeaf(Location_
hc.CurrentMember),
| (Measures.Cost)/(val(Location_hc.CurrentMember.Properties("head
|
count"))),(Measures.Cost)/ (SUM(Descendants(loc
ation_hc.CurrentMember,[Buildi
ng Name]))))
|
| Only the leaf level calculates correctly, none of the other levels
calculate
| correctly.
|
| The only difference is the division .... I don't understand!!
|
|
| --
| Thanks in advance
| GH
|
|
| "Peter Yang [MSFT]" wrote:
|
| > Hello,
| >
| > Because the headcount is only the member properties of the leaf level
of
| > location dimension, there will be error if you use them directly in
| > caculated member. You may want to try the following MDX:
| >
| > With
| > Member Measures.[headcount] As
| > ' IIf
| > (
| > IsLeaf ( location.CurrentMember ),
| > val( location.CurrentMember.Properties("headcount") ),
| > Sum
| > (
| > Descendants( location. CurrentMember,,Leave
s ),
| > Measures.[store sqft]
| > )
| > ) '
| >
| > Member Measures.[Cost Per headcount] As
| > ' Measures.[store sales] / Measures.[headcount] '
| >
| > select
| > { Measures.[headcount], Measures.[Cost Per headcount] } on columns,
| > NON EMPTY [location].[building].members on rows
| > from costcube
| >
| > Hope this is helpful.
| >
| > Regards,
| >
| > Peter Yang
| > MCSE2000/2003, MCSA, MCDBA
| > Microsoft Online Partner Support
| >
| > When responding to posts, please "Reply to Group" via your newsreader
so
| > that others may learn and benefit from your issue.
| >
| > ====================
====================
=============
| >
| > This posting is provided "AS IS" with no warranties, and confers no
rights.
| >
| >
| > --------------------
| > | Thread-Topic: Error Designing Caluclated Member
| > | thread-index: AcW1mpfCZep34eQLR3Wo
xR7d3sDR2Q==
| > | X-WBNR-Posting-Host: 131.107.0.79
| > | From: "=?Utf-8?B?R0g=?=" <vakar@community.nospam>
| > | References: <9E1D8EA7-A670-4794-AD79- 8141A5D4B19E@microso
ft.com>
| > <3BB9D044-DB7F-448F-B836- CE26EF4B3A19@microso
ft.com>
| > <udRVilStFHA.780@TK2MSFTNGXA01.phx.gbl>
| > <C8FA01CE-9763-4977-8344- 5EE0197F96C2@microso
ft.com>
| > | Subject: RE: Error Designing Caluclated Member
| > | Date: Fri, 9 Sep 2005 17:00:02 -0700
| > | Lines: 123
| > | Message-ID: <AF5113B7-374B-4F2D-8391- B7B74BB6A7C7@microso
ft.com>
| > | MIME-Version: 1.0
| > | Content-Type: text/plain;
| > | charset="Utf-8"
| > | Content-Transfer-Encoding: 7bit
| > | X-Newsreader: Microsoft CDO for Windows 2000
| > | Content-Class: urn:content-classes:message
| > | Importance: normal
| > | Priority: normal
| > | X-MimeOLE: Produced By Microsoft MimeOLE V6.00.3790.0
| > | Newsgroups: microsoft.public.sqlserver.olap
| > | NNTP-Posting-Host: TK2MSFTNGXA03.phx.gbl 10.40.2.250
| > | Path: TK2MSFTNGXA01.phx.gbl!TK2MSFTNGXA03.phx.gbl
| > | Xref: TK2MSFTNGXA01.phx.gbl microsoft.public.sqlserver.olap:14373
| > | X-Tomcat-NG: microsoft.public.sqlserver.olap
| > |
| > | HI Peter,
| > |
| > | My latest approach is to make the Headcount a Member Property of the
| > | BuildingName.
| > |
| > | Then when using the CalculatedMemberBuil
der to create a calculated
| > measure
| > | or Member using MDX expression
| > | [Location_HC].CurrentMember.Properties("HeadCount") returns a #ERR in
the
| > | grid.
| > | --
| > | Thanks in advance
| > | GH
| > |
| > |
| > | "GH" wrote:
| > |
| > | > Headcount is the number of staff permanetly assigned to that
building.
| > | >
| > | > So if the annual total costs of the building is $100,000 and the
| > Headcount
| > | > is 10 then the Annual CostPerPerson will be $10,000.
| > | > --
| > | > Thanks in advance
| > | > GH
| > | >
| > | >
| > | > "Peter Yang [MSFT]" wrote:
| > | >
| > | > > Hello,
| > | > >
| > | > > To understand the issue better, I'd like to know the detailed
meaning
| > of
| > | > > HeadCount. Is it a member property of building member? Or they
are
| > staffs
| > | > > in the building?
| > | > >
| > | > > Best Regards,
| > | > >
| > | > > Peter Yang
| > | > > MCSE2000/2003, MCSA, MCDBA
| > | > > Microsoft Online Partner Support
| > | > >
| > | > > When responding to posts, please "Reply to Group" via your
newsreader
| > so
| > | > > that others may learn and benefit from your issue.
| > | > >
| > | > > ====================
====================
=============
| > | > >
| > | > >
| > | > >
| > | > > This posting is provided "AS IS" with no warranties, and confers
no
| > rights.
| > | > >
| > | > >
| > | > > --------------------
| > | > > | Thread-Topic: Error Designing Caluclated Member
| > | > > | thread-index: AcW0xgKC3VR1obzsTCa1
QBFjxi7b1Q==
| > | > > | X-WBNR-Posting-Host: 131.107.0.79
| > | > > | From: "=?Utf-8?B?R0g=?=" <vakar@community.nospam>
| > | > > | References:
<9E1D8EA7-A670-4794-AD79- 8141A5D4B19E@microso
ft.com>
| > | > > | Subject: RE: Error Designing Caluclated Member
| > | > > | Date: Thu, 8 Sep 2005 15:38:18 -0700
| > | > > | Lines: 42
| > | > > | Message-ID: <3BB9D044-DB7F-448F-B836- CE26EF4B3A19@microso
ft.com>
| > | > > | MIME-Version: 1.0
| > | > > | Content-Type: text/plain;
| > | > > | charset="Utf-8"
| > | > > | Content-Transfer-Encoding: 7bit
| > | > > | X-Newsreader: Microsoft CDO for Windows 2000
| > | > > | Content-Class: urn:content-classes:message
| > | > > | Importance: normal
| > | > > | Priority: normal
| > | > > | X-MimeOLE: Produced By Microsoft MimeOLE V6.00.3790.0
| > | > > | Newsgroups: microsoft.public.sqlserver.olap
| > | > > | NNTP-Posting-Host: TK2MSFTNGXA03.phx.gbl 10.40.2.250
| > | > > | Path: TK2MSFTNGXA02.phx.gbl!TK2MSFTNGXA03.phx.gbl
| > | > > | Xref: TK2MSFTNGXA02.phx.gbl
microsoft.public.sqlserver.olap:62587
| > | > > | X-Tomcat-NG: microsoft.public.sqlserver.olap
| > | > > |
| > | > > | I have figured out the binding issue ... is really telling me i
| > have a
| > | > > poorly
| > | > > | formed MDX expression.
| > | > > |
| > | > > | I am not trying this test expression as my Calculated Member:
| > | > > | (Calendar.CurrentMember).[Location].[Head Count]
| > | > > |
| > | > > | This is returning an #err in all cells returned.
| > | > > |
| > | > > | My challenge is finding Cost\Person
| > | > > |
| > | > > | I have the following dimensions:
| > | > > | Calendar with Date/Year/QTR/Month/MonthDay/WeekDay
| > | > > | Location with Area/Region/District/Branch/Building/HeadCount
| > | > > |
| > | > > | The fact table fctCosts with the measures FixedCost,VriblCost
| > | > > |
| > | > > | I want to be able to add the Calculated Member Cost/person
where
| > | > > (FixedCost
| > | > > | + VrblCost) / HeadCount = Cost\Person
| > | > > |
| > | > > | But I cannot get the value of
| > (Calendar.CurrentMember).[Location].[Head
| > | > > | Count] to print.
| > | > > | --
| > | > > | Thanks in advance
| > | > > | GH
| > | > > |
| > | > > |
| > | > > | "GH" wrote:
| > | > > |
| > | > > | > I am trying this expression in the Calculated Member Builder
| > | > > | >
| > | > > | > [Measures].[Cost]/[Building].[Head Count]
| > | > > | >
| > | > > | > And I get the following error:
| > | > > | >
| > | > > | > Unable to update the calculated member.
| > | > > | > Formula error - cannot bind: unknown dimension or member:
| > | > > "[Building].[Head
| > | > > | > Count]"
| > | > > | >
| > | > > | > This seems simple unless I cannot use a dimension member in
the
| > | > > calculation.
| > | > > | >
| > | > > | > Thanks in advance
| > | > > | > GH
| > | > > |
| > | > >
| > | > >
| > |
| >
| >
|
| |
|
| Thanks Peter,
That point went over my head.
I have it working now ...
Now I am going to add in additional dimensions ... hopefully without issue.
Thanks for your patience.
--
Thanks in advance
GH
"Peter Yang [MSFT]" wrote:
>
> Hello,
>
> I think the reason is that cost is already a measure and you does not need
> to manually sum it again:
>
> You may want to use another caculated member to do this as I mententioned:
>
> Member Measures.[Cost Per headcount] As ' Measures.cost /
> Measures.[headcount]
>
> Regards,
>
> Peter Yang
> MCSE2000/2003, MCSA, MCDBA
> Microsoft Online Partner Support
>
> When responding to posts, please "Reply to Group" via your newsreader so
> that others may learn and benefit from your issue.
>
> ====================
====================
=============
>
>
> This posting is provided "AS IS" with no warranties, and confers no rights.
>
> --------------------
> | Thread-Topic: Error Designing Caluclated Member
> | thread-index: AcW6RShs4dqmPpKHRW6v
CbMqqN1wcg==
> | X-WBNR-Posting-Host: 131.107.0.79
> | From: "=?Utf-8?B?R0g=?=" <vakar@community.nospam>
> | References: <9E1D8EA7-A670-4794-AD79- 8141A5D4B19E@microso
ft.com>
> <3BB9D044-DB7F-448F-B836- CE26EF4B3A19@microso
ft.com>
> <udRVilStFHA.780@TK2MSFTNGXA01.phx.gbl>
> <C8FA01CE-9763-4977-8344- 5EE0197F96C2@microso
ft.com>
> <AF5113B7-374B-4F2D-8391- B7B74BB6A7C7@microso
ft.com>
> <7h$#Hf3tFHA.768@TK2MSFTNGXA01.phx.gbl>
> | Subject: RE: Error Designing Caluclated Member
> | Date: Thu, 15 Sep 2005 15:31:04 -0700
> | Lines: 244
> | Message-ID: <9B081A15-60A0-4E0C-857D- 84414F88DDF4@microso
ft.com>
> | MIME-Version: 1.0
> | Content-Type: text/plain;
> | charset="Utf-8"
> | Content-Transfer-Encoding: 7bit
> | X-Newsreader: Microsoft CDO for Windows 2000
> | Content-Class: urn:content-classes:message
> | Importance: normal
> | Priority: normal
> | X-MimeOLE: Produced By Microsoft MimeOLE V6.00.3790.0
> | Newsgroups: microsoft.public.sqlserver.olap
> | NNTP-Posting-Host: TK2MSFTNGXA03.phx.gbl 10.40.2.250
> | Path: TK2MSFTNGXA01.phx.gbl!TK2MSFTNGXA03.phx.gbl
> | Xref: TK2MSFTNGXA01.phx.gbl microsoft.public.sqlserver.olap:14543
> | X-Tomcat-NG: microsoft.public.sqlserver.olap
> |
> | HI Peter,
> |
> | Your post was helpful and I have made progress.
> |
> | Currently I am using one dimension Location_HC with the following levels
> | Region, Country,City, Building Name.
> |
> | Building Name has the Property "Head Count"
> |
> | There is one measure "Cost".
> |
> | I am trying to create a CalculateMeasure CostPerPerson ...
> |
> | Step one is to get the headcount correctly calculated across the tree.
> |
> | The following MDX expression works great and the calculations are correct
> at
> | all levels:
> | IIf(IsLeaf(location_
hc.CurrentMember),
> | val(location_hc.CurrentMember.Properties("head count")),
> | SUM(Descendants(loca
tion_hc.CurrentMember,[building Name])))
> |
> | Step two is to divide the Cost by the headcount so I tried this
> expression:
> | IIf(IsLeaf(Location_
hc.CurrentMember),
> | (Measures.Cost)/(val(Location_hc.CurrentMember.Properties("head
> |
> count"))),(Measures.Cost)/ (SUM(Descendants(loc
ation_hc.CurrentMember,[Buildi
> ng Name]))))
> |
> | Only the leaf level calculates correctly, none of the other levels
> calculate
> | correctly.
> |
> | The only difference is the division .... I don't understand!!
> |
> |
> | --
> | Thanks in advance
> | GH
> |
> |
> | "Peter Yang [MSFT]" wrote:
> |
> | > Hello,
> | >
> | > Because the headcount is only the member properties of the leaf level
> of
> | > location dimension, there will be error if you use them directly in
> | > caculated member. You may want to try the following MDX:
> | >
> | > With
> | > Member Measures.[headcount] As
> | > ' IIf
> | > (
> | > IsLeaf ( location.CurrentMember ),
> | > val( location.CurrentMember.Properties("headcount") ),
> | > Sum
> | > (
> | > Descendants( location. CurrentMember,,Leave
s ),
> | > Measures.[store sqft]
> | > )
> | > ) '
> | >
> | > Member Measures.[Cost Per headcount] As
> | > ' Measures.[store sales] / Measures.[headcount] '
> | >
> | > select
> | > { Measures.[headcount], Measures.[Cost Per headcount] } on columns,
> | > NON EMPTY [location].[building].members on rows
> | > from costcube
> | >
> | > Hope this is helpful.
> | >
> | > Regards,
> | >
> | > Peter Yang
> | > MCSE2000/2003, MCSA, MCDBA
> | > Microsoft Online Partner Support
> | >
> | > When responding to posts, please "Reply to Group" via your newsreader
> so
> | > that others may learn and benefit from your issue.
> | >
> | > ====================
====================
=============
> | >
> | > This posting is provided "AS IS" with no warranties, and confers no
> rights.
> | >
> | >
> | > --------------------
> | > | Thread-Topic: Error Designing Caluclated Member
> | > | thread-index: AcW1mpfCZep34eQLR3Wo
xR7d3sDR2Q==
> | > | X-WBNR-Posting-Host: 131.107.0.79
> | > | From: "=?Utf-8?B?R0g=?=" <vakar@community.nospam>
> | > | References: <9E1D8EA7-A670-4794-AD79- 8141A5D4B19E@microso
ft.com>
> | > <3BB9D044-DB7F-448F-B836- CE26EF4B3A19@microso
ft.com>
> | > <udRVilStFHA.780@TK2MSFTNGXA01.phx.gbl>
> | > <C8FA01CE-9763-4977-8344- 5EE0197F96C2@microso
ft.com>
> | > | Subject: RE: Error Designing Caluclated Member
> | > | Date: Fri, 9 Sep 2005 17:00:02 -0700
> | > | Lines: 123
> | > | Message-ID: <AF5113B7-374B-4F2D-8391- B7B74BB6A7C7@microso
ft.com>
> | > | MIME-Version: 1.0
> | > | Content-Type: text/plain;
> | > | charset="Utf-8"
> | > | Content-Transfer-Encoding: 7bit
> | > | X-Newsreader: Microsoft CDO for Windows 2000
> | > | Content-Class: urn:content-classes:message
> | > | Importance: normal
> | > | Priority: normal
> | > | X-MimeOLE: Produced By Microsoft MimeOLE V6.00.3790.0
> | > | Newsgroups: microsoft.public.sqlserver.olap
> | > | NNTP-Posting-Host: TK2MSFTNGXA03.phx.gbl 10.40.2.250
> | > | Path: TK2MSFTNGXA01.phx.gbl!TK2MSFTNGXA03.phx.gbl
> | > | Xref: TK2MSFTNGXA01.phx.gbl microsoft.public.sqlserver.olap:14373
> | > | X-Tomcat-NG: microsoft.public.sqlserver.olap
> | > |
> | > | HI Peter,
> | > |
> | > | My latest approach is to make the Headcount a Member Property of the
> | > | BuildingName.
> | > |
> | > | Then when using the CalculatedMemberBuil
der to create a calculated
> | > measure
> | > | or Member using MDX expression
> | > | [Location_HC].CurrentMember.Properties("HeadCount") returns a #ERR in
> the
> | > | grid.
> | > | --
> | > | Thanks in advance
> | > | GH
> | > |
> | > |
> | > | "GH" wrote:
> | > |
> | > | > Headcount is the number of staff permanetly assigned to that
> building.
> | > | >
> | > | > So if the annual total costs of the building is $100,000 and the
> | > Headcount
> | > | > is 10 then the Annual CostPerPerson will be $10,000.
> | > | > --
> | > | > Thanks in advance
> | > | > GH
> | > | >
> | > | >
> | > | > "Peter Yang [MSFT]" wrote:
> | > | >
> | > | > > Hello,
> | > | > >
> | > | > > To understand the issue better, I'd like to know the detailed
> meaning
> | > of
> | > | > > HeadCount. Is it a member property of building member? Or they
> are
> | > staffs
> | > | > > in the building?
> | > | > >
> | > | > > Best Regards,
> | > | > >
> | > | > > Peter Yang
> | > | > > MCSE2000/2003, MCSA, MCDBA
> | > | > > Microsoft Online Partner Support
> | > | > >
> | > | > > When responding to posts, please "Reply to Group" via your
> newsreader
> | > so
> | > | > > that others may learn and benefit from your issue.
> | > | > >
> | > | > > ====================
====================
=============
> | > | > >
> | > | > >
> | > | > >
> | > | > > This posting is provided "AS IS" with no warranties, and confers
> no
> | > rights.
> | > | > >
> | > | > >
> | > | > > --------------------
> | > | > > | Thread-Topic: Error Designing Caluclated Member
> | > | > > | thread-index: AcW0xgKC3VR1obzsTCa1
QBFjxi7b1Q==
> | > | > > | X-WBNR-Posting-Host: 131.107.0.79
> | > | > > | From: "=?Utf-8?B?R0g=?=" <vakar@community.nospam>
> | > | > > | References:
> <9E1D8EA7-A670-4794-AD79- 8141A5D4B19E@microso
ft.com>
> | > | > > | Subject: RE: Error Designing Caluclated Member
> | > | > > | Date: Thu, 8 Sep 2005 15:38:18 -0700
> | > | > > | Lines: 42
> | > | > > | Message-ID: <3BB9D044-DB7F-448F-B836- CE26EF4B3A19@microso
ft.com>
> | > | > > | MIME-Version: 1.0
> | > | > > | Content-Type: text/plain;
> | > | > > | charset="Utf-8"
> | > | > > | Content-Transfer-Encoding: 7bit
> | > | > > | X-Newsreader: Microsoft CDO for Windows 2000
> | > | > > | Content-Class: urn:content-classes:message
> | > | > > | Importance: normal
> | > | > > | Priority: normal
> | > | > > | X-MimeOLE: Produced By Microsoft MimeOLE V6.00.3790.0
> | > | > > | Newsgroups: microsoft.public.sqlserver.olap
> | > | > > | NNTP-Posting-Host: TK2MSFTNGXA03.phx.gbl 10.40.2.250
> | > | > > | Path: TK2MSFTNGXA02.phx.gbl!TK2MSFTNGXA03.phx.gbl
> | > | > > | Xref: TK2MSFTNGXA02.phx.gbl
> microsoft.public.sqlserver.olap:62587
> | > | > > | X-Tomcat-NG: microsoft.public.sqlserver.olap
> | > | > > |
> | > | > > | I have figured out the binding issue ... is really telling me i
> | > have a
> | > | > > poorly
> | > | > > | formed MDX expression.
> | > | > > |
> | > | > > | I am not trying this test expression as my Calculated Member:
> | > | > > | (Calendar.CurrentMember).[Location].[Head Count]
> | > | > > |
> | > | > > | This is returning an #err in all cells returned.
> | > | > > |
> | > | > > | My challenge is finding Cost\Person
> | > | > > |
> | > | > > | I have the following dimensions:
> | > | > > | Calendar with Date/Year/QTR/Month/MonthDay/WeekDay
> | > | > > | Location with Area/Region/District/Branch/Building/HeadCount
> | > | > > |
> | > | > > | The fact table fctCosts with the measures FixedCost,VriblCost
> | > | > > |
> | > | > > | I want to be able to add the Calculated Member Cost/person
> where
> | > | > > (FixedCost
> | > | > > | + VrblCost) / HeadCount = Cost\Person
> | > | > > |
> | > | > > | But I cannot get the value of
> | > (Calendar.CurrentMember).[Location].[Head
> | > | > > | Count] to print.
> | > | > > | --
> | > | > > | Thanks in advance
> | > | > > | GH
> | > | > > |
> | > | > > |
> | > | > > | "GH" wrote:
> | > | > > |
> | > | > > | > I am trying this expression in the Calculated Member Builder
> | > | > > | >
> | > | > > | > [Measures].[Cost]/[Building].[Head Count]
> | > | > > | >
> | > | > > | > And I get the following error:
> | > | > > | >
> | > | > > | > Unable to update the calculated member.
| |
|
| HI Peter,
I am back on this issue ...I have added the dimension Calendar
(Year,Qtr,Month) to the cube ... now I have the Location ( Area, Region,
District, Branch, Building (and the building property Headcount)).
Now all levels are calculating the total sum of the headcount .... I cannot
find any documentation with a calander dim being used.
Any suggestions would be appreciated.
--
Thanks in advance
GH
"GH" wrote:
[color=darkred]
> Thanks Peter,
>
> That point went over my head.
>
> I have it working now ...
>
> Now I am going to add in additional dimensions ... hopefully without issue.
>
> Thanks for your patience.
>
> --
> Thanks in advance
> GH
>
>
> "Peter Yang [MSFT]" wrote:
>
| |
| Peter Yang [MSFT] 2005-09-21, 3:24 am |
| Hello,
I think Calendar dimension does not have relatation to
Measures.[headcount]. However, since Measures.cost is related to calender
dimesnion, the measure measure.[cost]/measure.[headcount] shall aggregate
properly.
Thanks & Regards,
Peter Yang
MCSE2000/2003, MCSA, MCDBA
Microsoft Online Partner Support
When responding to posts, please "Reply to Group" via your newsreader so
that others may learn and benefit from your issue.
====================
====================
=============
This posting is provided "AS IS" with no warranties, and confers no rights.
--------------------
| Thread-Topic: Error Designing Caluclated Member
| thread-index: AcW+M9FijufOnvFiQDOa
0xDRnB1cjQ==
| X-WBNR-Posting-Host: 131.107.0.79
| From: "=?Utf-8?B?R0g=?=" <vakar@community.nospam>
| References: <9E1D8EA7-A670-4794-AD79- 8141A5D4B19E@microso
ft.com>
<3BB9D044-DB7F-448F-B836- CE26EF4B3A19@microso
ft.com>
<udRVilStFHA.780@TK2MSFTNGXA01.phx.gbl>
<C8FA01CE-9763-4977-8344- 5EE0197F96C2@microso
ft.com>
<AF5113B7-374B-4F2D-8391- B7B74BB6A7C7@microso
ft.com>
<7h$#Hf3tFHA.768@TK2MSFTNGXA01.phx.gbl>
<9B081A15-60A0-4E0C-857D- 84414F88DDF4@microso
ft.com>
<1yqrjsouFHA.3020@TK2MSFTNGXA01.phx.gbl>
<7D004F38-A0A2-4D21-8D4B- A42807167A75@microso
ft.com>
| Subject: RE: Error Designing Caluclated Member
| Date: Tue, 20 Sep 2005 15:37:01 -0700
| Lines: 317
| Message-ID: <3580AAD1-8817-4F7E-9848- 87424BCD4427@microso
ft.com>
| MIME-Version: 1.0
| Content-Type: text/plain;
| charset="Utf-8"
| Content-Transfer-Encoding: 7bit
| X-Newsreader: Microsoft CDO for Windows 2000
| Content-Class: urn:content-classes:message
| Importance: normal
| Priority: normal
| X-MimeOLE: Produced By Microsoft MimeOLE V6.00.3790.0
| Newsgroups: microsoft.public.sqlserver.olap
| NNTP-Posting-Host: TK2MSFTNGXA03.phx.gbl 10.40.2.250
| Path: TK2MSFTNGXA01.phx.gbl!TK2MSFTNGXA03.phx.gbl
| Xref: TK2MSFTNGXA01.phx.gbl microsoft.public.sqlserver.olap:14659
| X-Tomcat-NG: microsoft.public.sqlserver.olap
|
| HI Peter,
|
| I am back on this issue ...I have added the dimension Calendar
| (Year,Qtr,Month) to the cube ... now I have the Location ( Area, Region,
| District, Branch, Building (and the building property Headcount)).
|
| Now all levels are calculating the total sum of the headcount .... I
cannot
| find any documentation with a calander dim being used.
|
| Any suggestions would be appreciated.
| --
| Thanks in advance
| GH
|
|
| "GH" wrote:
|
| > Thanks Peter,
| >
| > That point went over my head.
| >
| > I have it working now ...
| >
| > Now I am going to add in additional dimensions ... hopefully without
issue.
| >
| > Thanks for your patience.
| >
| > --
| > Thanks in advance
| > GH
| >
| >
| > "Peter Yang [MSFT]" wrote:
| >
| > >
| > > Hello,
| > >
| > > I think the reason is that cost is already a measure and you does not
need
| > > to manually sum it again:
| > >
| > > You may want to use another caculated member to do this as I
mententioned:
| > >
| > > Member Measures.[Cost Per headcount] As ' Measures.cost /
| > > Measures.[headcount]
| > >
| > > Regards,
| > >
| > > Peter Yang
| > > MCSE2000/2003, MCSA, MCDBA
| > > Microsoft Online Partner Support
| > >
| > > When responding to posts, please "Reply to Group" via your newsreader
so
| > > that others may learn and benefit from your issue.
| > >
| > > ====================
====================
=============
| > >
| > >
| > > This posting is provided "AS IS" with no warranties, and confers no
rights.
| > >
| > > --------------------
| > > | Thread-Topic: Error Designing Caluclated Member
| > > | thread-index: AcW6RShs4dqmPpKHRW6v
CbMqqN1wcg==
| > > | X-WBNR-Posting-Host: 131.107.0.79
| > > | From: "=?Utf-8?B?R0g=?=" <vakar@community.nospam>
| > > | References: <9E1D8EA7-A670-4794-AD79- 8141A5D4B19E@microso
ft.com>
| > > <3BB9D044-DB7F-448F-B836- CE26EF4B3A19@microso
ft.com>
| > > <udRVilStFHA.780@TK2MSFTNGXA01.phx.gbl>
| > > <C8FA01CE-9763-4977-8344- 5EE0197F96C2@microso
ft.com>
| > > <AF5113B7-374B-4F2D-8391- B7B74BB6A7C7@microso
ft.com>
| > > <7h$#Hf3tFHA.768@TK2MSFTNGXA01.phx.gbl>
| > > | Subject: RE: Error Designing Caluclated Member
| > > | Date: Thu, 15 Sep 2005 15:31:04 -0700
| > > | Lines: 244
| > > | Message-ID: <9B081A15-60A0-4E0C-857D- 84414F88DDF4@microso
ft.com>
| > > | MIME-Version: 1.0
| > > | Content-Type: text/plain;
| > > | charset="Utf-8"
| > > | Content-Transfer-Encoding: 7bit
| > > | X-Newsreader: Microsoft CDO for Windows 2000
| > > | Content-Class: urn:content-classes:message
| > > | Importance: normal
| > > | Priority: normal
| > > | X-MimeOLE: Produced By Microsoft MimeOLE V6.00.3790.0
| > > | Newsgroups: microsoft.public.sqlserver.olap
| > > | NNTP-Posting-Host: TK2MSFTNGXA03.phx.gbl 10.40.2.250
| > > | Path: TK2MSFTNGXA01.phx.gbl!TK2MSFTNGXA03.phx.gbl
| > > | Xref: TK2MSFTNGXA01.phx.gbl microsoft.public.sqlserver.olap:14543
| > > | X-Tomcat-NG: microsoft.public.sqlserver.olap
| > > |
| > > | HI Peter,
| > > |
| > > | Your post was helpful and I have made progress.
| > > |
| > > | Currently I am using one dimension Location_HC with the following
levels
| > > | Region, Country,City, Building Name.
| > > |
| > > | Building Name has the Property "Head Count"
| > > |
| > > | There is one measure "Cost".
| > > |
| > > | I am trying to create a CalculateMeasure CostPerPerson ...
| > > |
| > > | Step one is to get the headcount correctly calculated across the
tree.
| > > |
| > > | The following MDX expression works great and the calculations are
correct
| > > at
| > > | all levels:
| > > | IIf(IsLeaf(location_
hc.CurrentMember),
| > > | val(location_hc.CurrentMember.Properties("head count")),
| > > | SUM(Descendants(loca
tion_hc.CurrentMember,[building Name])))
| > > |
| > > | Step two is to divide the Cost by the headcount so I tried this
| > > expression:
| > > | IIf(IsLeaf(Location_
hc.CurrentMember),
| > > | (Measures.Cost)/(val(Location_hc.CurrentMember.Properties("head
| > > |
| > >
count"))),(Measures.Cost)/ (SUM(Descendants(loc
ation_hc.CurrentMember,[Buildi
| > > ng Name]))))
| > > |
| > > | Only the leaf level calculates correctly, none of the other levels
| > > calculate
| > > | correctly.
| > > |
| > > | The only difference is the division .... I don't understand!!
| > > |
| > > |
| > > | --
| > > | Thanks in advance
| > > | GH
| > > |
| > > |
| > > | "Peter Yang [MSFT]" wrote:
| > > |
| > > | > Hello,
| > > | >
| > > | > Because the headcount is only the member properties of the leaf
level
| > > of
| > > | > location dimension, there will be error if you use them directly
in
| > > | > caculated member. You may want to try the following MDX:
| > > | >
| > > | > With
| > > | > Member Measures.[headcount] As
| > > | > ' IIf
| > > | > (
| > > | > IsLeaf ( location.CurrentMember ),
| > > | > val( location.CurrentMember.Properties("headcount") ),
| > > | > Sum
| > > | > (
| > > | > Descendants( location. CurrentMember,,Leave
s ),
| > > | > Measures.[store sqft]
| > > | > )
| > > | > ) '
| > > | >
| > > | > Member Measures.[Cost Per headcount] As
| > > | > ' Measures.[store sales] / Measures.[headcount] '
| > > | >
| > > | > select
| > > | > { Measures.[headcount], Measures.[Cost Per headcount] } on
columns,
| > > | > NON EMPTY [location].[building].members on rows
| > > | > from costcube
| > > | >
| > > | > Hope this is helpful.
| > > | >
| > > | > Regards,
| > > | >
| > > | > Peter Yang
| > > | > MCSE2000/2003, MCSA, MCDBA
| > > | > Microsoft Online Partner Support
| > > | >
| > > | > When responding to posts, please "Reply to Group" via your
newsreader
| > > so
| > > | > that others may learn and benefit from your issue.
| > > | >
| > > | > ====================
====================
=============
| > > | >
| > > | > This posting is provided "AS IS" with no warranties, and confers
no
| > > rights.
| > > | >
| > > | >
| > > | > --------------------
| > > | > | Thread-Topic: Error Designing Caluclated Member
| > > | > | thread-index: AcW1mpfCZep34eQLR3Wo
xR7d3sDR2Q==
| > > | > | X-WBNR-Posting-Host: 131.107.0.79
| > > | > | From: "=?Utf-8?B?R0g=?=" <vakar@community.nospam>
| > > | > | References:
<9E1D8EA7-A670-4794-AD79- 8141A5D4B19E@microso
ft.com>
| > > | > <3BB9D044-DB7F-448F-B836- CE26EF4B3A19@microso
ft.com>
| > > | > <udRVilStFHA.780@TK2MSFTNGXA01.phx.gbl>
| > > | > <C8FA01CE-9763-4977-8344- 5EE0197F96C2@microso
ft.com>
| > > | > | Subject: RE: Error Designing Caluclated Member
| > > | > | Date: Fri, 9 Sep 2005 17:00:02 -0700
| > > | > | Lines: 123
| > > | > | Message-ID: <AF5113B7-374B-4F2D-8391- B7B74BB6A7C7@microso
ft.com>
| > > | > | MIME-Version: 1.0
| > > | > | Content-Type: text/plain;
| > > | > | charset="Utf-8"
| > > | > | Content-Transfer-Encoding: 7bit
| > > | > | X-Newsreader: Microsoft CDO for Windows 2000
| > > | > | Content-Class: urn:content-classes:message
| > > | > | Importance: normal
| > > | > | Priority: normal
| > > | > | X-MimeOLE: Produced By Microsoft MimeOLE V6.00.3790.0
| > > | > | Newsgroups: microsoft.public.sqlserver.olap
| > > | > | NNTP-Posting-Host: TK2MSFTNGXA03.phx.gbl 10.40.2.250
| > > | > | Path: TK2MSFTNGXA01.phx.gbl!TK2MSFTNGXA03.phx.gbl
| > > | > | Xref: TK2MSFTNGXA01.phx.gbl
microsoft.public.sqlserver.olap:14373
| > > | > | X-Tomcat-NG: microsoft.public.sqlserver.olap
| > > | > |
| > > | > | HI Peter,
| > > | > |
| > > | > | My latest approach is to make the Headcount a Member Property
of the
| > > | > | BuildingName.
| > > | > |
| > > | > | Then when using the CalculatedMemberBuil
der to create a
calculated
| > > | > measure
| > > | > | or Member using MDX expression
| > > | > | [Location_HC].CurrentMember.Properties("HeadCount") returns a
#ERR in
| > > the
| > > | > | grid.
| > > | > | --
| > > | > | Thanks in advance
| > > | > | GH
| > > | > |
| > > | > |
| > > | > | "GH" wrote:
| > > | > |
| > > | > | > Headcount is the number of staff permanetly assigned to that
| > > building.
| > > | > | >
| > > | > | > So if the annual total costs of the building is $100,000 and
the
| > > | > Headcount
| > > | > | > is 10 then the Annual CostPerPerson will be $10,000.
| > > | > | > --
| > > | > | > Thanks in advance
| > > | > | > GH
| > > | > | >
| > > | > | >
| > > | > | > "Peter Yang [MSFT]" wrote:
| > > | > | >
| > > | > | > > Hello,
| > > | > | > >
| > > | > | > > To understand the issue better, I'd like to know the
detailed
| > > meaning
| > > | > of
| > > | > | > > HeadCount. Is it a member property of building member? Or
they
| > > are
| > > | > staffs
| > > | > | > > in the building?
| > > | > | > >
| > > | > | > > Best Regards,
| > > | > | > >
| > > | > | > > Peter Yang
| > > | > | > > MCSE2000/2003, MCSA, MCDBA
| > > | > | > > Microsoft Online Partner Support
| > > | > | > >
| > > | > | > > When responding to posts, please "Reply to Group" via your
| > > newsreader
| > > | > so
| > > | > | > > that others may learn and benefit from your issue.
| > > | > | > >
| > > | > | > > ====================
====================
=============
| > > | > | > >
| > > | > | > >
| > > | > | > >
| > > | > | > > This posting is provided "AS IS" with no warranties, and
confers
| > > no
| > > | > rights.
| > > | > | > >
| > > | > | > >
| > > | > | > > --------------------
| > > | > | > > | Thread-Topic: Error Designing Caluclated Member
| > > | > | > > | thread-index: AcW0xgKC3VR1obzsTCa1
QBFjxi7b1Q==
| > > | > | > > | X-WBNR-Posting-Host: 131.107.0.79
| > > | > | > > | From: "=?Utf-8?B?R0g=?=" <vakar@community.nospam>
| > > | > | > > | References:
| > > <9E1D8EA7-A670-4794-AD79- 8141A5D4B19E@microso
ft.com>
| > > | > | > > | Subject: RE: Error Designing Caluclated Member
| > > | > | > > | Date: Thu, 8 Sep 2005 15:38:18 -0700
| > > | > | > > | Lines: 42
| > > | > | > > | Message-ID:
<3BB9D044-DB7F-448F-B836- CE26EF4B3A19@microso
ft.com>
| > > | > | > > | MIME-Version: 1.0
| > > | > | > > | Content-Type: text/plain;
| > > | > | > > | charset="Utf-8"
| > > | > | > > | Content-Transfer-Encoding: 7bit
| > > | > | > > | X-Newsreader: Microsoft CDO for Windows 2000
| > > | > | > > | Content-Class: urn:content-classes:message
| > > | > | > > | Importance: normal
| > > | > | > > | Priority: normal
| > > | > | > > | X-MimeOLE: Produced By Microsoft MimeOLE V6.00.3790.0
| > > | > | > > | Newsgroups: microsoft.public.sqlserver.olap
| > > | > | > > | NNTP-Posting-Host: TK2MSFTNGXA03.phx.gbl 10.40.2.250
| > > | > | > > | Path: TK2MSFTNGXA02.phx.gbl!TK2MSFTNGXA03.phx.gbl
| > > | > | > > | Xref: TK2MSFTNGXA02.phx.gbl
| > > microsoft.public.sqlserver.olap:62587
| > > | > | > > | X-Tomcat-NG: microsoft.public.sqlserver.olap
| > > | > | > > |
| > > | > | > > | I have figured out the binding issue ... is really
telling me i
| > > | > have a
| > > | > | > > poorly
| > > | > | > > | formed MDX expression.
| > > | > | > > |
| > > | > | > > | I am not trying this test expression as my Calculated
Member:
| > > | > | > > | (Calendar.CurrentMember).[Location].[Head Count]
| > > | > | > > |
| > > | > | > > | This is returning an #err in all cells returned.
| > > | > | > > |
| > > | > | > > | My challenge is finding Cost\Person
| > > | > | > > |
| > > | > | > > | I have the following dimensions:
| > > | > | > > | Calendar with Date/Year/QTR/Month/MonthDay/WeekDay
| > > | > | > > | Location with
Area/Region/District/Branch/Building/HeadCount
| > > | > | > > |
| > > | > | > > | The fact table fctCosts with the measures
FixedCost,VriblCost
| > > | > | > > |
| > > | > | > > | I want to be able to add the Calculated Member
Cost/person
| > > where
| > > | > | > > (FixedCost
| > > | > | > > | + VrblCost) / HeadCount = Cost\Person
|
| |
|
| Hi Peter,
I maybe missing something here ... Measures.Headcount is calculated from the
HeadCount Property of the BuildingLevel of the Location dim table.
The headcount changes monthly ...
The time dimension is Calendar (Year,QtrNbr,MonthNb
rYear)
I have checked the fact table ... there was an error caused by reprocessing
some data which was providing incorrect numbers. That issue is resolved ..
I am now trying to generate the correct syntax for calculating the Monthly
headcount with the Calendar dimension added to the schema.
I am working with the script:
IIf(IsLeaf(location_
hc.CurrentMember),
val(location_hc.CurrentMember.Properties("head count")),
SUM(Descendants(loca
tion_hc.CurrentMember,[building Name])))
Adding various Calendar.[(All)] variations to the above but have not
discovered the magic approach.
--
Thanks in advance
GH
"Peter Yang [MSFT]" wrote:
> Hello,
>
> I think Calendar dimension does not have relatation to
> Measures.[headcount]. However, since Measures.cost is related to calender
> dimesnion, the measure measure.[cost]/measure.[headcount] shall aggregate
> properly.
>
> Thanks & Regards,
>
> Peter Yang
> MCSE2000/2003, MCSA, MCDBA
> Microsoft Online Partner Support
>
> When responding to posts, please "Reply to Group" via your newsreader so
> that others may learn and benefit from your issue.
>
> ====================
====================
=============
>
>
>
> This posting is provided "AS IS" with no warranties, and confers no rights.
>
> --------------------
> | Thread-Topic: Error Designing Caluclated Member
> | thread-index: AcW+M9FijufOnvFiQDOa
0xDRnB1cjQ==
> | X-WBNR-Posting-Host: 131.107.0.79
> | From: "=?Utf-8?B?R0g=?=" <vakar@community.nospam>
> | References: <9E1D8EA7-A670-4794-AD79- 8141A5D4B19E@microso
ft.com>
> <3BB9D044-DB7F-448F-B836- CE26EF4B3A19@microso
ft.com>
> <udRVilStFHA.780@TK2MSFTNGXA01.phx.gbl>
> <C8FA01CE-9763-4977-8344- 5EE0197F96C2@microso
ft.com>
> <AF5113B7-374B-4F2D-8391- B7B74BB6A7C7@microso
ft.com>
> <7h$#Hf3tFHA.768@TK2MSFTNGXA01.phx.gbl>
> <9B081A15-60A0-4E0C-857D- 84414F88DDF4@microso
ft.com>
> <1yqrjsouFHA.3020@TK2MSFTNGXA01.phx.gbl>
> <7D004F38-A0A2-4D21-8D4B- A42807167A75@microso
ft.com>
> | Subject: RE: Error Designing Caluclated Member
> | Date: Tue, 20 Sep 2005 15:37:01 -0700
> | Lines: 317
> | Message-ID: <3580AAD1-8817-4F7E-9848- 87424BCD4427@microso
ft.com>
> | MIME-Version: 1.0
> | Content-Type: text/plain;
> | charset="Utf-8"
> | Content-Transfer-Encoding: 7bit
> | X-Newsreader: Microsoft CDO for Windows 2000
> | Content-Class: urn:content-classes:message
> | Importance: normal
> | Priority: normal
> | X-MimeOLE: Produced By Microsoft MimeOLE V6.00.3790.0
> | Newsgroups: microsoft.public.sqlserver.olap
> | NNTP-Posting-Host: TK2MSFTNGXA03.phx.gbl 10.40.2.250
> | Path: TK2MSFTNGXA01.phx.gbl!TK2MSFTNGXA03.phx.gbl
> | Xref: TK2MSFTNGXA01.phx.gbl microsoft.public.sqlserver.olap:14659
> | X-Tomcat-NG: microsoft.public.sqlserver.olap
> |
> | HI Peter,
> |
> | I am back on this issue ...I have added the dimension Calendar
> | (Year,Qtr,Month) to the cube ... now I have the Location ( Area, Region,
> | District, Branch, Building (and the building property Headcount)).
> |
> | Now all levels are calculating the total sum of the headcount .... I
> cannot
> | find any documentation with a calander dim being used.
> |
> | Any suggestions would be appreciated.
> | --
> | Thanks in advance
> | GH
> |
> |
> | "GH" wrote:
> |
> | > Thanks Peter,
> | >
> | > That point went over my head.
> | >
> | > I have it working now ...
> | >
> | > Now I am going to add in additional dimensions ... hopefully without
> issue.
> | >
> | > Thanks for your patience.
> | >
> | > --
> | > Thanks in advance
> | > GH
> | >
> | >
> | > "Peter Yang [MSFT]" wrote:
> | >
> | > >
> | > > Hello,
> | > >
> | > > I think the reason is that cost is already a measure and you does not
> need
> | > > to manually sum it again:
> | > >
> | > > You may want to use another caculated member to do this as I
> mententioned:
> | > >
> | > > Member Measures.[Cost Per headcount] As ' Measures.cost /
> | > > Measures.[headcount]
> | > >
> | > > Regards,
> | > >
> | > > Peter Yang
> | > > MCSE2000/2003, MCSA, MCDBA
> | > > Microsoft Online Partner Support
> | > >
> | > > When responding to posts, please "Reply to Group" via your newsreader
> so
> | > > that others may learn and benefit from your issue.
> | > >
> | > > ====================
====================
=============
> | > >
> | > >
> | > > This posting is provided "AS IS" with no warranties, and confers no
> rights.
> | > >
> | > > --------------------
> | > > | Thread-Topic: Error Designing Caluclated Member
> | > > | thread-index: AcW6RShs4dqmPpKHRW6v
CbMqqN1wcg==
> | > > | X-WBNR-Posting-Host: 131.107.0.79
> | > > | From: "=?Utf-8?B?R0g=?=" <vakar@community.nospam>
> | > > | References: <9E1D8EA7-A670-4794-AD79- 8141A5D4B19E@microso
ft.com>
> | > > <3BB9D044-DB7F-448F-B836- CE26EF4B3A19@microso
ft.com>
> | > > <udRVilStFHA.780@TK2MSFTNGXA01.phx.gbl>
> | > > <C8FA01CE-9763-4977-8344- 5EE0197F96C2@microso
ft.com>
> | > > <AF5113B7-374B-4F2D-8391- B7B74BB6A7C7@microso
ft.com>
> | > > <7h$#Hf3tFHA.768@TK2MSFTNGXA01.phx.gbl>
> | > > | Subject: RE: Error Designing Caluclated Member
> | > > | Date: Thu, 15 Sep 2005 15:31:04 -0700
> | > > | Lines: 244
> | > > | Message-ID: <9B081A15-60A0-4E0C-857D- 84414F88DDF4@microso
ft.com>
> | > > | MIME-Version: 1.0
> | > > | Content-Type: text/plain;
> | > > | charset="Utf-8"
> | > > | Content-Transfer-Encoding: 7bit
> | > > | X-Newsreader: Microsoft CDO for Windows 2000
> | > > | Content-Class: urn:content-classes:message
> | > > | Importance: normal
> | > > | Priority: normal
> | > > | X-MimeOLE: Produced By Microsoft MimeOLE V6.00.3790.0
> | > > | Newsgroups: microsoft.public.sqlserver.olap
> | > > | NNTP-Posting-Host: TK2MSFTNGXA03.phx.gbl 10.40.2.250
> | > > | Path: TK2MSFTNGXA01.phx.gbl!TK2MSFTNGXA03.phx.gbl
> | > > | Xref: TK2MSFTNGXA01.phx.gbl microsoft.public.sqlserver.olap:14543
> | > > | X-Tomcat-NG: microsoft.public.sqlserver.olap
> | > > |
> | > > | HI Peter,
> | > > |
> | > > | Your post was helpful and I have made progress.
> | > > |
> | > > | Currently I am using one dimension Location_HC with the following
> levels
> | > > | Region, Country,City, Building Name.
> | > > |
> | > > | Building Name has the Property "Head Count"
> | > > |
> | > > | There is one measure "Cost".
> | > > |
> | > > | I am trying to create a CalculateMeasure CostPerPerson ...
> | > > |
> | > > | Step one is to get the headcount correctly calculated across the
> tree.
> | > > |
> | > > | The following MDX expression works great and the calculations are
> correct
> | > > at
> | > > | all levels:
> | > > | IIf(IsLeaf(location_
hc.CurrentMember),
> | > > | val(location_hc.CurrentMember.Properties("head count")),
> | > > | SUM(Descendants(loca
tion_hc.CurrentMember,[building Name])))
> | > > |
> | > > | Step two is to divide the Cost by the headcount so I tried this
> | > > expression:
> | > > | IIf(IsLeaf(Location_
hc.CurrentMember),
> | > > | (Measures.Cost)/(val(Location_hc.CurrentMember.Properties("head
> | > > |
> | > >
> count"))),(Measures.Cost)/ (SUM(Descendants(loc
ation_hc.CurrentMember,[Buildi
> | > > ng Name]))))
> | > > |
> | > > | Only the leaf level calculates correctly, none of the other levels
> | > > calculate
> | > > | correctly.
> | > > |
> | > > | The only difference is the division .... I don't understand!!
> | > > |
> | > > |
> | > > | --
> | > > | Thanks in advance
> | > > | GH
> | > > |
> | > > |
> | > > | "Peter Yang [MSFT]" wrote:
> | > > |
> | > > | > Hello,
> | > > | >
> | > > | > Because the headcount is only the member properties of the leaf
> level
> | > > of
> | > > | > location dimension, there will be error if you use them directly
> in
> | > > | > caculated member. You may want to try the following MDX:
> | > > | >
> | > > | > With
> | > > | > Member Measures.[headcount] As
> | > > | > ' IIf
> | > > | > (
> | > > | > IsLeaf ( location.CurrentMember ),
> | > > | > val( location.CurrentMember.Properties("headcount") ),
> | > > | > Sum
> | > > | > (
> | > > | > Descendants( location. CurrentMember,,Leave
s ),
> | > > | > Measures.[store sqft]
> | > > | > )
> | > > | > ) '
> | > > | >
> | > > | > Member Measures.[Cost Per headcount] As
> | > > | > ' Measures.[store sales] / Measures.[headcount] '
> | > > | >
> | > > | > select
> | > > | > { Measures.[headcount], Measures.[Cost Per headcount] } on
> columns,
> | > > | > NON EMPTY [location].[building].members on rows
> | > > | > from costcube
> | > > | >
> | > > | > Hope this is helpful.
> | > > | >
> | > > | > Regards,
> | > > | >
> | > > | > Peter Yang
> | > > | > MCSE2000/2003, MCSA, MCDBA
> | > > | > Microsoft Online Partner Support
> | > > | >
> | > > | > When responding to posts, please "Reply to Group" via your
> newsreader
> | > > so
> | > > | > that others may learn and benefit from your issue.
> | > > | >
> | > > | > ====================
====================
=============
> | > > | >
> | > > | > This posting is provided "AS IS" with no warranties, and confers
> no
> | > > rights.
> | > > | >
> | > > | >
> | > > | > --------------------
> | > > | > | Thread-Topic: Error Designing Caluclated Member
> | > > | > | thread-index: AcW1mpfCZep34eQLR3Wo
xR7d3sDR2Q==
> | > > | > | X-WBNR-Posting-Host: 131.107.0.79
> | > > | > | From: "=?Utf-8?B?R0g=?=" <vakar@community.nospam>
> | > > | > | References:
> <9E1D8EA7-A670-4794-AD79- 8141A5D4B19E@microso
ft.com>
> | > > | > <3BB9D044-DB7F-448F-B836- CE26EF4B3A19@microso
ft.com>
> | > > | > <udRVilStFHA.780@TK2MSFTNGXA01.phx.gbl>
> | > > | > <C8FA01CE-9763-4977-8344- 5EE0197F96C2@microso
ft.com>
> | > > | > | Subject: RE: Error Designing Caluclated Member
> | > > | > | Date: Fri, 9 Sep 2005 17:00:02 -0700
> | > > | > | Lines: 123
> | > > | > | Message-ID: <AF5113B7-374B-4F2D-8391- B7B74BB6A7C7@microso
ft.com>
> | > > | > | MIME-Version: 1.0
> | > > | > | Content-Type: text/plain;
> | > > | > | charset="Utf-8"
> | > > | > | Content-Transfer-Encoding: 7bit
> | > > | > | X-Newsreader: Microsoft CDO for Windows 2000
> | > > | > | Content-Class: urn:content-classes:message
> | > > | > | Importance: normal
> | > > | > | Priority: normal
> | > > | > | X-MimeOLE: Produced By Microsoft MimeOLE V6.00.3790.0
> | > > | > | Newsgroups: microsoft.public.sqlserver.olap
> | > > | > | NNTP-Posting-Host: TK2MSFTNGXA03.phx.gbl 10.40.2.250
> | > > | > | Path: TK2MSFTNGXA01.phx.gbl!TK2MSFTNGXA03.phx.gbl
> | > > | > | Xref: TK2MSFTNGXA01.phx.gbl
> microsoft.public.sqlserver.olap:14373
> | > > | > | X-Tomcat-NG: microsoft.public.sqlserver.olap
> | > > | > |
> | > > | > | HI Peter,
> | > > | > |
> | > > | > | My latest approach is to make the Headcount a Member Property
> of the
> | > > | > | BuildingName.
> | > > | > |
> | > > | > | Then when using the CalculatedMemberBuil
der to create a
> calculated
> | > > | > measure
> | > > | > | or Member using MDX expression
> | > > | > | [Location_HC].CurrentMember.Properties("HeadCount") returns a
> #ERR in
> | > > the
> | > > | > | grid.
> | > > | > | --
> | > > | > | Thanks in advance
> | > > | > | GH
> | > > | > |
> | > > | > |
> | > > | > | "GH" wrote:
> | > > | > |
| |
| Darren Gosbell 2005-09-26, 8:24 pm |
| I know I'm jumping in here a half way through a conversation, but it
looks like your headcount is being stored in the wrong spot.
If your headcount changes over time (which makes sense) then it should
be stored as a fact and not as a member property.
You should have a fact table that looks something like the following
LocationId TimeId Cost HeadCount
========== ====== ==== =========
Storing the cost and headcount as measures, for each location, for each
month. Then you can use the dimension hierarchies to give you higher
level aggregations so that you can do things like - look at cost per
headcount at a regional level for a quarter.
This will also make your calculated measure easier to write as it would
simply be "[Measures].[Cost]/[Measures].[HeadCount]"
--
Regards
Darren Gosbell [MCSD]
< dgosbell_at_yahoo_do
t_com>
Blog: http://www.geekswithblogs.net/darrengosbell
In article <FC6ACC5B-4841-40A7-91BD- 604B4FFC7CA5@microso
ft.com>,
vakar@community.nospam says...
> Hi Peter,
>
> I maybe missing something here ... Measures.Headcount is calculated from the
> HeadCount Property of the BuildingLevel of the Location dim table.
>
> The headcount changes monthly ...
>
> The time dimension is Calendar (Year,QtrNbr,MonthNb
rYear)
>
> I have checked the fact table ... there was an error caused by reprocessing
> some data which was providing incorrect numbers. That issue is resolved ..
>
> I am now trying to generate the correct syntax for calculating the Monthly
> headcount with the Calendar dimension added to the schema.
>
> I am working with the script:
> IIf(IsLeaf(location_
hc.CurrentMember),
> val(location_hc.CurrentMember.Properties("head count")),
> SUM(Descendants(loca
tion_hc.CurrentMember,[building Name])))
>
> Adding various Calendar.[(All)] variations to the above but have not
> discovered the magic approach.
>
>
>
| |
|
| Hi Darren,
Thanks for the response.
Your suggestion may end up being my backup.
Currently I am seeking a way to have a 4 coordinated cube with a Time
dimension with a weekly level where the headcount rollsup correctly for each
month. Because the headcount is only updated monthly I cannot aggregate from
the week level .. that is why I am trying the Property approach.
I started with a single dimension, the dimLocation_hc, and have the MDX
script almost totalling correctly " IIf(IsLeaf(location_
hc.CurrentMember),
val(location_hc.CurrentMember.Properties("head count")),
SUM(Descendants(loca
tion_hc.CurrentMember,[building Name])))"
Now I am trying to add the second coordinate, the time dimension dimCalendar
with only on level, Month. I have been unsuccessful so far in determining
the proper syntax ...
--
Thanks in advance
GH
"Darren Gosbell" wrote:
> I know I'm jumping in here a half way through a conversation, but it
> looks like your headcount is being stored in the wrong spot.
>
> If your headcount changes over time (which makes sense) then it should
> be stored as a fact and not as a member property.
>
> You should have a fact table that looks something like the following
>
> LocationId TimeId Cost HeadCount
> ========== ====== ==== =========
>
> Storing the cost and headcount as measures, for each location, for each
> month. Then you can use the dimension hierarchies to give you higher
> level aggregations so that you can do things like - look at cost per
> headcount at a regional level for a quarter.
>
> This will also make your calculated measure easier to write as it would
> simply be "[Measures].[Cost]/[Measures].[HeadCount]"
>
> --
> Regards
> Darren Gosbell [MCSD]
> < dgosbell_at_yahoo_do
t_com>
> Blog: http://www.geekswithblogs.net/darrengosbell
>
> In article <FC6ACC5B-4841-40A7-91BD- 604B4FFC7CA5@microso
ft.com>,
> vakar@community.nospam says...
>
>
| |
| Darren Gosbell 2005-09-27, 8:24 pm |
| In that case I have another suggestion...
Try breaking the calculated measure into 2 calculated measures (at least
temporarily), using the old divide and conquer technique :)
The first one gets the value from the member property at the leaf level
(you would hide this from the end users). The second one adds up all the
values of the first member from the descendants of the current member.
Measures.[HeadCountBase] (set this one to visible = false)
====================
====
IIf(IsLeaf(location_
hc.CurrentMember), val
(location_hc.CurrentMember.Properties("head count")),null)
Measures.[HeadCount]
====================
SUM(Descendants(loca
tion_hc. CurrentMember,,LEAVE
S),Measures.
[HeadCountBase])"
--
Regards
Darren Gosbell [MCSD]
< dgosbell_at_yahoo_do
t_com>
Blog: http://www.geekswithblogs.net/darrengosbell
In article <0B855D60-FB20-4D4C-8BDC- 80A7B29BF43C@microso
ft.com>,
vakar@community.nospam says...
> Hi Darren,
>
> Thanks for the response.
>
> Your suggestion may end up being my backup.
>
> Currently I am seeking a way to have a 4 coordinated cube with a Time
> dimension with a weekly level where the headcount rollsup correctly for each
> month. Because the headcount is only updated monthly I cannot aggregate from
> the week level .. that is why I am trying the Property approach.
>
> I started with a single dimension, the dimLocation_hc, and have the MDX
> script almost totalling correctly " IIf(IsLeaf(location_
hc.CurrentMember),
> val(location_hc.CurrentMember.Properties("head count")),
> SUM(Descendants(loca
tion_hc.CurrentMember,[building Name])))"
>
> Now I am trying to add the second coordinate, the time dimension dimCalendar
> with only on level, Month. I have been unsuccessful so far in determining
> the proper syntax ...
>
| |
|
| This works partially ... It sums the HeadCount based upon the MAX value of
the HeadCount for any given Location.BuildingID ... I have three monnths of
data
The Calendar dim with the single Month level needs to be added so the sum of
the HeadCount reflects each month.
The
--
Thanks in advance
GH
"Darren Gosbell" wrote:
> In that case I have another suggestion...
>
> Try breaking the calculated measure into 2 calculated measures (at least
> temporarily), using the old divide and conquer technique :)
>
> The first one gets the value from the member property at the leaf level
> (you would hide this from the end users). The second one adds up all the
> values of the first member from the descendants of the current member.
>
> Measures.[HeadCountBase] (set this one to visible = false)
> ====================
====
> IIf(IsLeaf(location_
hc.CurrentMember), val
> (location_hc.CurrentMember.Properties("head count")),null)
>
> Measures.[HeadCount]
> ====================
> SUM(Descendants(loca
tion_hc. CurrentMember,,LEAVE
S),Measures.
> [HeadCountBase])"
>
>
>
> --
> Regards
> Darren Gosbell [MCSD]
> < dgosbell_at_yahoo_do
t_com>
> Blog: http://www.geekswithblogs.net/darrengosbell
>
> In article <0B855D60-FB20-4D4C-8BDC- 80A7B29BF43C@microso
ft.com>,
> vakar@community.nospam says...
>
| |
|
| HI Darren,
Lets do a sanity check on my Location dim and how changes to the headcount
are handled.
Each month when the headcount number for a building changes we add a new
Surrogate key with the same BuildingID, update the HeadCount, and use a
BeginDate and EndDate to identify the current record.
Could this be an issue why the Calendar is not being picked up??
--
Thanks in advance
GH
"Darren Gosbell" wrote:
> In that case I have another suggestion...
>
> Try breaking the calculated measure into 2 calculated measures (at least
> temporarily), using the old divide and conquer technique :)
>
> The first one gets the value from the member property at the leaf level
> (you would hide this from the end users). The second one adds up all the
> values of the first member from the descendants of the current member.
>
> Measures.[HeadCountBase] (set this one to visible = false)
> ====================
====
> IIf(IsLeaf(location_
hc.CurrentMember), val
> (location_hc.CurrentMember.Properties("head count")),null)
>
> Measures.[HeadCount]
> ====================
> SUM(Descendants(loca
tion_hc. CurrentMember,,LEAVE
S),Measures.
> [HeadCountBase])"
>
>
>
> --
> Regards
> Darren Gosbell [MCSD]
> < dgosbell_at_yahoo_do
t_com>
> Blog: http://www.geekswithblogs.net/darrengosbell
>
> In article <0B855D60-FB20-4D4C-8BDC- 80A7B29BF43C@microso
ft.com>,
> vakar@community.nospam says...
>
| |
| Darren Gosbell 2005-09-28, 8:24 pm |
| Yes, this is most likely what is causing your issue. This comes back to
my original suggestion to make the headcount a measure in the fact table
in it's own right.
If you can only see one building in your dimension then AS2k is
consolidating the records. It basically does a "SELECT DISTINCT..." on
the Key and Name columns on each level in your dimension table and I
think from memory it does a second pass to attach the member properties
to the dimension (probably doing a GROUP BY getting the MAX of each
property)
The basic rule of thumb is that if a particular value only varies by a
single dimensions it is a candidate for member properties.
(eg. Number of Floors in the Build - you would only have one value of
this for each building and it would not alter over time. Other similar
examples might
If a value varies by more that one dimension then it is probably a
measure. In your case Head Count varies by building and by time.
--
Regards
Darren Gosbell [MCSD]
< dgosbell_at_yahoo_do
t_com>
Blog: http://www.geekswithblogs.net/darrengosbell
In article <B9B4E38C-5CA8-4C05-82DC- 91D6F8B7F062@microso
ft.com>,
vakar@community.nospam says...
> HI Darren,
>
> Lets do a sanity check on my Location dim and how changes to the headcount
> are handled.
>
> Each month when the headcount number for a building changes we add a new
> Surrogate key with the same BuildingID, update the HeadCount, and use a
> BeginDate and EndDate to identify the current record.
>
> Could this be an issue why the Calendar is not being picked up??
>
| |
|
| HI Darren,
This is great info ... I am having a difficult time finding info on what is
happening under the hood so to speak.
I will go to a model with headcount as a measure ...
Thank you for your patience.
You rock!!
--
Thanks in advance
GH
"Darren Gosbell" wrote:
> In that case I have another suggestion...
>
> Try breaking the calculated measure into 2 calculated measures (at least
> temporarily), using the old divide and conquer technique :)
>
> The first one gets the value from the member property at the leaf level
> (you would hide this from the end users). The second one adds up all the
> values of the first member from the descendants of the current member.
>
> Measures.[HeadCountBase] (set this one to visible = false)
> ====================
====
> IIf(IsLeaf(location_
hc.CurrentMember), val
> (location_hc.CurrentMember.Properties("head count")),null)
>
> Measures.[HeadCount]
> ====================
> SUM(Descendants(loca
tion_hc. CurrentMember,,LEAVE
S),Measures.
> [HeadCountBase])"
>
>
>
> --
> Regards
> Darren Gosbell [MCSD]
> < dgosbell_at_yahoo_do
t_com>
> Blog: http://www.geekswithblogs.net/darrengosbell
>
> In article <0B855D60-FB20-4D4C-8BDC- 80A7B29BF43C@microso
ft.com>,
> vakar@community.nospam says...
>
|
|
|
|
|