Submit My Question - Recent Questions - Recent Answer
Msdn Tech >> SQL Server >> SQL Server Analysis Services

MeasureExpression Property - How to use

I have a cube with several MeasureGroups.

I want to provide a calculated measure "Average Sale Price" which is calculated as "Gross Revenue" / "Product Count".

Both the "Gross Revenue" and "Product Count" measures belonging to the same measure group ... "Royalty Statement". Ideally I would like the "Average Sale Price" measure also to belong to the "Royalty Statement" Measure Group also.

I am therefore trying to use a MeasureExpression calculation on a regular measure, as opposed to a Calculated Member to achieve the calculation. (or can someone advise how to make a Calculated Member appear within a specific Measure Group ?)


On the measure properties
- "AggregateFunction" is set to "Sum"
- "MeasureExpression" is set to "[Measures].[Gross Revenue] / [Measures].[Product Count]"

On deploying the cube I get ... "Error 1 Errors in the metadata manager. The 'Product Count' right operand of the measure expression of the 'Average Sale Price' measure cannot belong to the same measure group. "

Can I really not have a MeasureExpression with the numerator and denominator in the same Measure Group ?

I have futzed with things a bit including getting the cube to process by using a denominator from a different MeasureGroup, and playing with the Source object binding.

What am I missing ?

Thanks in Advance

Marcus

[1541 byte] By [MarcusWinn] at [2007-12-24]
«« How to CreateProcess NOT as administrator
»» Using dataview to search through a datagrid
# 1

No. I do not think that you can use the same measure group in measure expressions. This is the new version of Lookup cube(MDX) in AS2005 with limited support for calculations except (*, /)

You can achive the same with a calculated member.

Regards

Thomas Ivarsson

ThomasIvarsson at 2007-10-8 > top of Msdn Tech,SQL Server,SQL Server Analysis Services...
# 2

Marcus,

You can easily associate your calculated measure with any existing measure group. To do this, please follow these steps:

- Open your project in SQL Server Business Intelligence Development Studio & double click your cube

- In the Cube editor, go to the Calculations tab and double click your calculated member "Average Sale Price"

- In the toolbar, just right from the "Form View" and "Script View" toolbar buttons, click on the "Calculation Properties" toolbar button

- In the Calculation Properties dialog that comes up, select your calculated member "Average Sale Price" from the dropdown and set the associated measure group for it to be "Royalty Statement"

- Deploy the project and you are ready to go

Hope this helps,

Artur

ArturPop at 2007-10-8 > top of Msdn Tech,SQL Server,SQL Server Analysis Services...
# 3

Thanks Artur, that helps.

If I do this, can I also assume that the Dimension Usage characteristics for that Measure Group now apply to the Calcuated Member ?

Marcus

MarcusWinn at 2007-10-8 > top of Msdn Tech,SQL Server,SQL Server Analysis Services...
# 4

Unfortunately the answer is no. This is a common request from customers and is planned to be implemented in our next release. For Yukon, you could use the following work around: if you want to associate the new calculated member with specific dimensions, you could set the non-empty behavior for the calculated member to a measure from a measure group that intersects with those dimensions.

Hope this helps,

Artur

ArturPop at 2007-10-8 > top of Msdn Tech,SQL Server,SQL Server Analysis Services...
SQL Server
SQL Server Analysis Services

SQL Server Hot Topic

SQL Server New Topic

SQL Server

Java / MSDN / Linux Tech / PHP Tech / MSDN DotNet / MSDN TECH / Health Library / Programmers Tech / visual basic Tech / Developer Tech / Ipod faqs / Msdn China Tech

Site Classified