Generic calculated measure

SQL Server Analysis Services

SSAS_STUDENT


Hi

I have to create a calculated measure which is applicable for 16 measures.

How can i specify it in a generic manner

for eg:

create member currentcube.ytd

as

sum(ytd(),[measures].[sales units];

here my scenario requires the calculation of YTD for many other measures also other than sales units

so can i have something instead of [measures].[units], so that it will be generic.

Thanks in advance.

Top

Re: Generic calculated measure

Deepak Puri


The standard SSAS Time Intelligence enhancements provide this type of capability:

http://msdn2.microsoft.com/en-us/library/ms175440.aspx

>>

SQL Server 2005 Books Online
Defining Time Intelligence Calculations using the Business Intelligence Wizard

The time intelligence enhancement is a cube enhancement that adds time calculations (or time views) to a selected hierarchy. This enhancement supports the following categories of calculations:

  • Period to date.
  • Period over period growth.
  • Moving averages.
  • Parallel period comparisons.

...

Apply Time Views to Members

On the Define Scope of Calculations page, you specify the members to which the new time views apply. You can apply the new time views to one of the following objects:

  • Measures Instead of specifying an account dimension, you can specify the measures to which the time views apply. In this case, select the views to which selected time calculations apply. For example, assets and liabilities are year-to-date data; therefore, you do not apply a Year-to-Date calculation to assets or liabilities measures.
    • >>





    Top

    Re: Generic calculated measure

    Gert-Jan van der Kamp

    You can also create is as a calculated member in a seperate dimension called ValueType or something.

     I can't give you tested precise answer because i'm installing SP2 at hte moment, but for your example it would look something like

    create calculated member CurrentCube.ValueType.Ytd as

    sum(ytd();

     

    It will display the Ytd of whatever measure is in context. If it should only apply to certain measures you could scope on those.

    Regards GJ

     






    Top