Hi,

I know to set up time based sets and calculated members (YTD, previous
Year, ...) and calulated measures which give you i.e. the difference
of sales of the current year and the previous year.

We have quite a lot of measures and I don't like to set up lots of
calulated measures, for each measure one for the last year's values,
the absolute and procentual change. This makes me to have minimum 3
extra measures for each measure... We have actuals and budgets in the
cube so we really have lots of measures...

So what I'm looking for is a way to have a "flexible" calculated
member, I guess it has to be in the time dimension. It should give me
a way to select i.e.
- a month
- 3 months
- 2 quarters
and I need the values of
- the total of that selection
- the total of the selection but for the previous year
- the total of the year from the selected values
- the total of the previous year of the selected values

Example: I have some measures (sales, costs, ...), a products
dimension and a standard time dimension (year, quarter, month, day). I
select March and April 2003 and sales as measure. What I want to see
is the sales of 2002, 2003, sum of March and April 2002, sum of March
and April 2003, the difference between sales of March and April
2002/2003 absolute and in percent in columns, and products in rows. I
want then to change to costs by only changing the measure or select
some other months and see the same schema of the report.

This doesn't work with measures like "sales" and "sales previous year"
...., becaus that would mean that we have to have a bunch of measures
for all combinations.

This also doesnt work with standard calculated time members because
you would need an element for each combination you can imagine (i.e.
sum of March and April 2003, sum of March and April 2002, ....).

What can I do?

Thanks,


Thomas

The usual solution is to set up an additional dimension,
with one regular member to store the values for individual
periods and calculated members for YTD, rolling totals,
variances and so on.

BTW, if you have actual and budgets for all your measures
it is a good idea to create a Scenario dimension.

If you create both aggregations and percentages you should
take solve order into account.

HTH,
Brian
www.geocities.com/brianaltmann/olap.html

Hi Thomas,

Thanks for your feedback. Based on my research, it seems that your
requirements are complex and extensive. Do you mean that you want to build
all of them in the cube and let them be created automatically? If so, it
means that you need to create more objects in cube such as calculated
member, virtual dimension. Also, it is a hard and heavy work. It is
commended that you use MDX statements to retrieve the data.

In addition, if you insist on retrieving the data automatically from the
cube, due to the complexity of this issue, it would be best to contact
Microsoft Product Support Services via telephone so that a dedicated
Support Professional can assist with your request. To obtain the phone
numbers for specific technology request please take a look at the web site
listed below.
http://support.microsoft.com/default...S;PHONENUMBERS

Thanks for using MSDN newsgroup.

Regards,

Michael Shao
Microsoft Online Partner Support
Get Secure! - www.microsoft.com/security
This posting is provided "as is" with no warranties and confers no rights.