|
Home > Archive > MS SQL Server OLAP > November 2005 > AS2005 Different aggregate functions depending on dimension
You are viewing an archived Text-only version of the thread.
To view this thread in it's original format and/or if you want to reply to
this thread please [click here]
| Author |
AS2005 Different aggregate functions depending on dimension
|
|
|
| In AS2005 is it possible to set up different aggregate functions depending on
dimension?
E.g. with a balance measure.
For all dimensions except time use Sum as the aggregate function.
For Time use LastChild as the Aggregate function.
e.g. Sum across customers, divisions, etc.
For Time: Months take the last fortnight in the month, Years take the last
month in the year.
Thanks
| |
| Darren Gosbell 2005-11-25, 3:23 am |
| AS2005 has a number of these semi-additive aggregates built-in. When you
select a measure you can specify a number of extra aggregations, two
examples are LastChild and LastNonemptyChild.
If you look up "Measure and Measure Group Properties" in BOL it has more
details. I believe all the semi-additive measures do not aggregate over
time dimensions. (Note: It looks like there is an error in BOL as Count
is marked as semi-additive, when I it should be marked as Additive)
--
Regards
Darren Gosbell [MCSD]
Blog: http://www.geekswithblogs.net/darrengosbell
In article <37AFA32F-717A-41CB-A0E2- F58663FD2644@microso
ft.com>,
acius@nospam.nospam says...
> In AS2005 is it possible to set up different aggregate functions depending on
> dimension?
>
> E.g. with a balance measure.
>
> For all dimensions except time use Sum as the aggregate function.
> For Time use LastChild as the Aggregate function.
>
> e.g. Sum across customers, divisions, etc.
> For Time: Months take the last fortnight in the month, Years take the last
> month in the year.
>
> Thanks
>
| |
|
| Thanks Darren,
Didin't click that LastChild and LastNonemptyChild only applied to time.
Much simpler than using CustomRollupColumn in the time dimension with MDX to
test for the appropriate measure
Cheers
"Darren Gosbell" wrote:
> AS2005 has a number of these semi-additive aggregates built-in. When you
> select a measure you can specify a number of extra aggregations, two
> examples are LastChild and LastNonemptyChild.
>
> If you look up "Measure and Measure Group Properties" in BOL it has more
> details. I believe all the semi-additive measures do not aggregate over
> time dimensions. (Note: It looks like there is an error in BOL as Count
> is marked as semi-additive, when I it should be marked as Additive)
>
> --
> Regards
> Darren Gosbell [MCSD]
> Blog: http://www.geekswithblogs.net/darrengosbell
>
> In article <37AFA32F-717A-41CB-A0E2- F58663FD2644@microso
ft.com>,
> acius@nospam.nospam says...
>
>
|
|
|
|
|