AggregateFunction] property to [None]

Question

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

Viewing 4 posts - 1 through 3 (of 3 total)

You must be logged in to reply to this topic. Login to reply

stevefromOZ SSC-Forever Points: 43646 More actions · Answer 1

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 · Answer 2

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 · Answer 3

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