Click here to monitor SSC
SQLServerCentral is supported by Red Gate Software Ltd.
 
Log in  ::  Register  ::  Not logged in
 
 
 
        
Home       Members    Calendar    Who's On


Add to briefcase

AggregateFunction] property to [None] Expand / Collapse
Author
Message
Posted Monday, August 16, 2010 10:32 PM
Forum Newbie

Forum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum Newbie

Group: General Forum Members
Last Login: Thursday, March 17, 2011 11:09 PM
Points: 6, Visits: 1,367
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?


Thanks in advance
Z
Post #970134
Posted Monday, August 16, 2010 11:22 PM


SSCommitted

SSCommittedSSCommittedSSCommittedSSCommittedSSCommittedSSCommittedSSCommittedSSCommitted

Group: Moderators
Last Login: Today @ 10:07 AM
Points: 1,782, Visits: 3,346
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.
Post #970148
Posted Tuesday, August 17, 2010 12:21 AM
Forum Newbie

Forum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum Newbie

Group: General Forum Members
Last Login: Thursday, March 17, 2011 11:09 PM
Points: 6, Visits: 1,367
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
Post #970162
Posted Tuesday, August 17, 2010 4:39 AM
Forum Newbie

Forum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum Newbie

Group: General Forum Members
Last Login: Thursday, March 17, 2011 11:09 PM
Points: 6, Visits: 1,367
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
Post #970239
« Prev Topic | Next Topic »

Add to briefcase

Permissions Expand / Collapse