INTELLIGENT WORK FORUMS
FOR COMPUTER PROFESSIONALS

Log In

Come Join Us!

Are you a
Computer / IT professional?
Join Tek-Tips Forums!
  • Talk With Other Members
  • Be Notified Of Responses
    To Your Posts
  • Keyword Search
  • One-Click Access To Your
    Favorite Forums
  • Automated Signatures
    On Your Posts
  • Best Of All, It's Free!

*Tek-Tips's functionality depends on members receiving e-mail. By joining you are opting in to receive e-mail.

Posting Guidelines

Promoting, selling, recruiting, coursework and thesis posting is forbidden.

Jobs

Aggregation type - NONE; but setting aggregation through MDX - how to?

Aggregation type - NONE; but setting aggregation through MDX - how to?

(OP)
Hi,

I have a measure I want to shown ONLY at a certain scope. But cant seem to get this to work. The measure aggregation type is set to None, as to show nulls when analysed normally.
But in MDX the measure is set to aggregation using a scope (which specifies the ONLY intersection where the user shoudl see something.

1) Aggregation for the measure [ed02_d_mh_flag_lhntarget] set to None

2) A new calculated measure created

CODE

CREATE MEMBER CURRENTCUBE.[Measures].[ed02_d_mh_flag_target]
AS
NULL,
VISIBLE = 1 ,  DISPLAY_FOLDER = 'Targets' ,  ASSOCIATED_MEASURE_GROUP = 'FactCombined';

3) The new calculated measure defined at the relevant scope with an aggregation function

CODE

SCOPE ([Gen_Hospital].[Hierarchy],[Gen_Date].[Hierarchy]);
[Measures].[ed02_d_mh_flag_target]
  = sum({[Gen_Hospital].[Hierarchy].members,[Gen_Date].[Hierarchy].members},[Measures].[ed02_d_mh_flag_lhntarget]);
END SCOPE;

But I get NULLS even when intersecting the new calculated measure with the hierarchies defined in the scope.

EO
Hertfordshire, England

RE: Aggregation type - NONE; but setting aggregation through MDX - how to?

Hi eo,

I have a couple of suggestions. If your base physical measure is not visible to end users, then you don't have to set its aggregation to NONE. Set it to SUM and let the engine do its work. Then you only use its value when you want for your calculated measure. Since this is all users see, you don't have to worry about the rest. I am guessing that you have more than the two dimensions you have used in the SCOPE statement. If that is the case, you are still referreing to an intersection of the cube that is not at the grain of the fact table. Since you have set the aggreagtion to NONE, there are no values there since you are not at the bottom, hence sum of nothing returns noting.
Try the above approach and let me know if it works:

1- Change the aggregation of the base physical measure to SUM.
2- Change the scope statement to this:


SCOPE ([Gen_Hospital].[Hierarchy],[Gen_Date].[Hierarchy]); [Measures].[ed02_d_mh_flag_target] = [Measures].[ed02_d_mh_flag_lhntarget]); END SCOPE;

Hope this helps.

S.Watson

Red Flag This Post

Please let us know here why this post is inappropriate. Reasons such as off-topic, duplicates, flames, illegal, vulgar, or students posting their homework.

Red Flag Submitted

Thank you for helping keep Tek-Tips Forums free from inappropriate posts.
The Tek-Tips staff will check this out and take appropriate action.

Reply To This Thread

Posting in the Tek-Tips forums is a member-only feature.

Click Here to join Tek-Tips and talk with other members!

Resources

Close Box

Join Tek-Tips® Today!

Join your peers on the Internet's largest technical computer professional community.
It's easy to join and it's free.

Here's Why Members Love Tek-Tips Forums:

Register now while it's still free!

Already a member? Close this window and log in.

Join Us             Close