Showing posts with label calculation. Show all posts
Showing posts with label calculation. Show all posts

Wednesday, March 21, 2012

How to address cells in a calculation

I have a calculation where I need to get the value of a certain cell in my cube. It looks like this:

([Measures].[Amount],

[Table].[Table].&[{327365F8-F148-4C34-A749-D3F56FD3B8F6}],

[Line].[Line].&[{2155F898-47EA-4269-B167-BE940C79E9F6}],

[Column].[Column].&[{83435F85-93AB-48D1-B2F3-B5E0BAF62642}],

[Currency].[Currency].&[{2FA606E1-0A20-4A21-B360-643A530C297B}],

[Maturity].[Maturity].&[{188601ED-2E02-4883-9336-45BC831C5EE8}],

[Region].[Region].&[{0712BAAF-FEE4-479E-8287-E75DAD405B40}],

[Sector].[Sector].&[{E52C493D-6528-490E-BD63-4A0857F8B53F}],

[PastDue].[PastDue].&[{524942A0-A1ED-4BF5-86D2-F8EF393F0004}],

[Custom].[Custom].&[{7FCAB9D1-4D7B-48CD-8EEC-1AA47F5D9CD1}],

[Situation].[Situation].&[{7187DCEE-11C9-4EC2-A2CB-BC2534716D8B}])

The problem here are the uniqueidentifiers or as you like the ID's of the members. Is there another way to select a certain cell in my cube by using other properties of the members ? I would prefer something like

(

[Measures].[Amount],

[Table].[Table].&["First Table"],

[Line].[Line].&["First Line"],

[Column].[Column].&["First Column"],

[Currency].[Currency].&["EUR"],

[Maturity].[Maturity].&["TotalMaturity"],

[Region].[Region].&["TotalRegion"],

[Sector].[Sector].&["TotalSector"],

[PastDue].[PastDue].&["TotalPastDue"],

[Custom].[Custom].&["TotalCustom"],

[Situation].[Situation].&["TotalSituation"]

)

When referencing a member you can use the "Key" value or the "Name" value. In your example:

This referes to a "Key" value

[Currency].[Currency].&[{2FA606E1-0A20-4A21-B360-643A530C297B}],

You can refer to the "Name" value using:

[Currency].[Currency].[EUR],

Notice that the "&" symbol is removed when referencing a member name.

HTH,

- Steve

|||

Absolutely.

You can use names instead of the keys.
In the simpliest case you just to drop the "&" character and make sure you provide a full path to your member.

In form of :

[Dimension].[Hierarchy].[Level].[Member]

For instance ; [Product].[Products].[Product Family].[Drink]

Edward.
--
This posting is provided "AS IS" with no warranties, and confers no rights.

|||

Thank you both for your answer.

I have a dimension where multiple members have the same name ... how can I address these ?

thanx in advance

|||

Answer is that it depends.

If you have two members with the same parent then:

[Dimension].[Hierarchy].[Parent Level Name].[Parent Member Name].[Member Name]

will return the value for the first member matching the "Member Name"

If the two members have different parents then:

[Dimension].[Hierarchy].[Parent Level Name].[Parent Member Name 1].[Member Name]

[Dimension].[Hierarchy].[Parent Level Name].[Parent Member Name 2].[Member Name]

can be used to reference each member individually.

HTH,

Steve

How to add YTD (Calculation) to Time Dimension?

Is there any you can add YTD to Time Dimension as attribute? Or it has to be Calculation? Then how do we do this? Is this need to base on Dimension or Measure? I would prefer this to be base on dimension and show in Time dimension hierarchy.

Any inputs on this are highly appreciated.

If you are using Analysis Services 2005 you can open the cube editor and then click on the "Add Business Intelligence" button you will get a wizard that will guide you through the process of adding a "Period Calculations" attribute hierarchy to your time dimension which can be used to support YTD, QTD, MTD and other period calculations.

The following is a link to an excellent article on the "Time Intelligence" wizard that was published in SQLServer magazine:

http://www.windowsitpro.com/Article/ArticleID/46157/46157.html

|||

Although you need to be aware that some of the calculations produced by the Time Intelligence Wizard, including the YTD calculation, don't actually work. See:

http://spaces.msn.com/members/cwebbbi/Blog/cns!1pi7ETChsJ1un_2s41jm9Iyg!379.entry

|||

There are several ways to accomplish this. If your users want a way to select Monthly values vs, QTD, YTD from a selector/slicer you will need to create a Time Utility Dim or a Time Ref Dim.

This Dim will only contain one member for the lowest level of detail. ie. Month or Daily. You would then create calculated members to compute YTD and anyother values that are based on what the user has selected in the Time Dim.

|||Can this be done in AS 2005?

How to add YTD (Calculation) to Time Dimension?

Is there any you can add YTD to Time Dimension as attribute? Or it has to be Calculation? Then how do we do this? Is this need to base on Dimension or Measure? I would prefer this to be base on dimension and show in Time dimension hierarchy.

Any inputs on this are highly appreciated.

If you are using Analysis Services 2005 you can open the cube editor and then click on the "Add Business Intelligence" button you will get a wizard that will guide you through the process of adding a "Period Calculations" attribute hierarchy to your time dimension which can be used to support YTD, QTD, MTD and other period calculations.

The following is a link to an excellent article on the "Time Intelligence" wizard that was published in SQLServer magazine:

http://www.windowsitpro.com/Article/ArticleID/46157/46157.html

|||

Although you need to be aware that some of the calculations produced by the Time Intelligence Wizard, including the YTD calculation, don't actually work. See:

http://spaces.msn.com/members/cwebbbi/Blog/cns!1pi7ETChsJ1un_2s41jm9Iyg!379.entry

|||

There are several ways to accomplish this. If your users want a way to select Monthly values vs, QTD, YTD from a selector/slicer you will need to create a Time Utility Dim or a Time Ref Dim.

This Dim will only contain one member for the lowest level of detail. ie. Month or Daily. You would then create calculated members to compute YTD and anyother values that are based on what the user has selected in the Time Dim.

|||Can this be done in AS 2005?