Hi all,
I don't know how to achieve what i want.

As it seems, every measure has an aggregation function, but only
one, despite of how many dimension are involved in my cube. What i need
is to aggregate the measures by dimension with different functions,
which is more appropiate.

For example,
--------------------------------
Measures:Clients
Dimension: Zones, Time
--------------------------------


Inicial instance:
Q1-------------- Q2---------------
Jan Feb Mar Apr May Jun
State 1
*City a 1000 1030 1055 1045 1060 1062
*City b 503 480 460 450 435 415
*City c 890 885 895 880 896 896

State 2
*City d 1652 1655 1598 1615 1658 1692
*City e 895 892 905 945 956 948
*City f 645 700 715 745 739 840



Step1: Aggregated Dimension Time to Quarter Level

Q1---------------------- Q2---------------------
State 1
*City 1 avg(1000;1030;1055)=1028 avg(1045;1060;1062)=1056
*City 2 avg(503;480;460)=481 avg(450;435;415)=433
*City 3 avg(890;885;895)=890 avg(880;896;896)=891

State 2
*City 4 avg(1652;1655;1598)=1635 avg(1615;1658;1692)=1655
*City 5 avg(895;892;905)=897 avg(945;956;948)=950
*City 6 avg(645;700;715)=687 avg(745;739;840)=775



Step2: Aggregated Dimension Zone to State Level

Q1-------------------- Q2---------------------
State 1 sum(1028;481;890)=2399 sum(1056;433;891)=2380
State 2 sum(1635;897;687)=3219 sum(1655;950;775)=3380


I thought about using Clients Measure as a Dimension property of Zones.
By doing that i wouldn't be able to know how many clients i had at a
specific moment in time, thus losing important information.

I would really appreciate any help.
Emiliano

Me again!!

I've tried using Custom Rollup Formulas for mi problem. This is
what i've been trying to use(MDX):

Notes:
* [Test] <--- Cube (Dim: [Net],[Date] + some measures)
* [KPI] <--- Virtual Cube based on Test. I've used some
measures from Test (Visible=False) and i've create some Calculated
Members (Visible=True). One of the Calculate Member is used in the
example(VAP).


* [Date]
[Year] <--- Custom Rollup Formula. MDX below
[Quarter] <--- Custom Rollup Formula. MDX below
[Month]

* [Net] <--- is a parent-child dimension

* VAP is a Calculated Member

MDX> iif( [Measures].CurrentMember.Name="VAP", Avg(
{[Date].CurrentMember.Children}
,[Measures].CurrentMember),[Measures].CurrentMember)

My MDX level is not something i'm proud of.

I need to aggregate calculated member using different functions. In
some cases i need to use Avg(), and in some other cases i need to use
Sum().

Is my approach correct, or i should use something else?

I'd realy appreciate any help.
Emiliano

Hi Emiliano,

If you use AS 2005 Enterprise Edition, there are some semi-additive
aggregation functions that may meet you needs:

http://msdn2.microsoft.com/en-us/library/ms189734.aspx
Semiadditive Function
Select the aggregation function for the selected measure. The following
table lists the aggregation functions that are available.


Value Description
AverageOfChildren
Aggregated by returning the average of the measure's children.

ByAccount
Aggregated by the aggregation function associated with the specified
account type of an attribute in an account dimension.

Count
Aggregated using the Count function.

DistinctCount
Aggregated using the DistinctCount function.

FirstChild
Aggregated by returning the measure's first child member.

FirstNonEmpty
Aggregated by returning the measure's first nonempty member.

LastChild
Aggregated by returning the measure's last child member.

LastNonEmpty
Aggregated by returning the measure's last nonempty member.
...

- Deepak

Deepak Puri
Microsoft MVP - SQL Server

*** Sent via Developersdex http://www.developersdex.com ***