Hi folks,



I have the following problem creating averages using MDX:



We created an application that enables our customers to compose a table
using dimensions. For example:



They select two dimensions, Product (Prod1, Prod2 and Prod3) and RATING
(1-5)

The result can be as follows:



Current result:



All X Prod1 Prod2 Prod3

All RATING 21 6 8 6

1 5 1 3 1

2 3 1 0 2

3 7 2 3 1

4 2 0 1 1

5 4 2 1 1





Perfect, however they also wants to be able to show rating averages as in
the following example:



Required result:





All X Prod1 Prod2 Prod3

All RESP 2,86 3,17 2,63 2,83

...

...

...

...

...





I am able to create measures to calculate averages. The problem is that we
don't know on what dimensions they want to create averages. So I want to be
able to calculate the averages run-time and not when composing the cube. An
alternative is to calculate an average for all dimensions when building the
cube, but that's an ugly solution.



Please advise!

Is this challenge not possible?

"Free like a bird" <otalens (AT) hotmail (DOT) com> wrote

In the example, the Rating dimension members have numerical values
(1,2,3..). Since these values are being averaged, will any dimension on
the rows always have numeric values?


- Deepak

*** Sent via Developersdex http://www.developersdex.com ***
Don't just participate in USENET...get rewarded for it!

It will be numeric values, but with string labels

Is this a problem?

"Deepak Puri" <deepak_puri (AT) progressive (DOT) com> wrote

Doesn's anybody have a clue? It it very important for us.

Thanks in advance,
Oliver

"Free like a bird" <otalens (AT) hotmail (DOT) com> wrote

It can be done
You could create a member property, say 'Value', to hold the value of each rating, i.e 1,2,3..
If you substitute the correct names of your AS objects this query should work

with member Ratings.AvgRating as 'sum(ratings.[Base Level].members, [Your Measure] * cint(ratings.currentmember.properties("value") )/ratings.[All Ratings] )' , format_string='#.00
select
products.members on columns
{ratings.members,ratings.avgrating} on row
from rating

You can create this query dynamically from your application for any combination of dimensions as long as you have the member property
HTH
Bria
www.geocities.com/brianaltmann/olap.htm

Thanks Brian,

This looks like the solution to me, but I can't get it to work:
I substituted it with:

with member Q10_4.AvgRating as 'sum([Q10_4].[(All)].members, [Count1] *
cint(Q10_4.currentmember.properties("value") )/Q10_4.[All Q10_4])' ,
format_string='#.00'
select
[Group].members on columns,
{Q10_4.members,Q10_4.avgrating} on rows
from [Survey 12345]

Do I need to fill the values first?
Is my Base Level set ok?

Thanks,
Oliver


"Brian Altmann" <anonymous (AT) discussions (DOT) microsoft.com> wrote

Ratings] )' , format_string='#.00'

Sorry I wasn't clear.
By Base Level I meant the leaf level of the dimension, not the (All) level.
HTH,
Brian

Thanks,

It works!

Oliver

"Brian Altmann" <anonymous (AT) discussions (DOT) microsoft.com> wrote