AggregateFunction] property to [None]

• zabistian

  SSC Veteran

  Points: 250

  More actions

  August 16, 2010 at 10:32 pm

  #387591

  I have two dimensions and one fact.

  DimClass

  - ClassID

  - ClassName

  - ClassYear (YYYY)

  DimStudent

  - StudentID

  - StudentName

  FactResult

  - ResultID

  - ClassID

  - StudentID

  - PercentageAchieved

  I don’t want to sum [PercentageAchieved]. As I understand I should set its [AggregateFunction] property to [None] to avoid summing up of values.

  If I set to [None] it return blank but it's fine with [Sum]. I did try to set the [FormatString] property to [#,#.##] and [Percentage].

  Please note the value for [PercentageAchieved] is pre-calculated so I can't write calculated measure in this case...

  am I right? :unsure:

  Thanks in advance

  Z

• stevefromOZ

  SSC-Forever

  Points: 43646

  More actions

  August 16, 2010 at 11:22 pm

  #1207660

  Zabastian,

  What exactly do you expect to see at the aggregated/higher/highest level of the cube? That is, if you don't want to sum (which I agree would be wrong) or Average the Percent Achieved value, when you are *not* filtered to a single student for a single class, what value do you believe should be shown?

  Steve.

• zabistian

  SSC Veteran

  Points: 250

  More actions

  August 17, 2010 at 12:21 am

  #1207672

  Steve,

  Thanks for your reply. No aggregated value but actual value of [PercentageAchieved].

  a matrix like....

  -------| 2008 | 2009 | 2010

  -----------------------------

  John | 89.3 | 90.2 | 78.9

  Beck | 89.3 | 90.2 | 78.9

  Smith | 99.3 | 50.2 | 98.9

  Please let me know if it make sense

  Thank again

  Z

• zabistian

  SSC Veteran

  Points: 250

  More actions

  August 17, 2010 at 4:39 am

  #1207731

  I found a solution on SQLDev - [Not exactly what I was looking for but similar]

  http://www.sqldev.org/sql-server-analysis-services/aggregate-function-none-11910.shtml

  --------------------------------------------------------------------

  Hi Sandrine,

  I think you need to set the IsAggretable property for the parent-child hierarchy, not setting the aggregate function for the measure.

  Double click the Countries dimension, right-click Parent/Child hierarchy in the Attributes pane of the Dimension Structure tab, and then click Properties. Scroll to the IsAggregatable property, and then click False in the property’s drop-down list. After that, the subtotal/total will not appear.

  Aggregate function for a measure determines how to display the value along with the dimensions, it will change the fact value aggregate behavior, has no relationship with subtotal/total of a dimension. And if you select None for the aggregation, no aggregation is performed, and all values for leaf and nonleaf members in a dimension are supplied directly from the fact table for the measure group that contains the measure. If there is no value, it will show NULL. More information, see:

  http://msdn.microsoft.com/en-us/library/ms175623.aspx#AggFunction

  Hope this helps.

  Raymond