Descendants on Measures (Cube source)

[iBoy]

Hello Cognos World,

I'm fairly new, and would like to know if when using a cube source in Report Studio I can use a function to bring back a set of measures and their childrens and their childrens children.

For example:

Dim Time
Dim Company
Dim Product
Measure
Measure 1
Measure 2
  Measure 2.1
  Measure 2.2
   Measure 2.2.1
  Measure 2.3
Measure 3

Problem
When I try to use "Descendants ([Measure],4,before)" I get an error saying:

"The expression for dataItem="Measure" contains a measure set expression that cannot be evaluated by the OLAP Planner

However
If I use "Descendants ([Measure],1)" I have no problems.


So the initial conclusionn is that; "if I want to create a set of items from the measures, I can only go down 1 level.  If I want to capture more data items at lower levels this can't be done"

...Anyone want to challenge that last statement?

Thanks,
iBoy

[Lynn]

Can you provide an example of what you mean by Measure 2 versus Measure 2.1 etc?

As far as I know measures don't have levels....Dimensions have levels. You can use the descendants function for a dimension in order to see one or more measures associated with each member in the set that is returned from the function.

I find the attached document a handy one for understanding dimension functions. It is v8 but still applicable in 10.

[iBoy]

Hi,

The numbering was to suggest the levels (/granularity) of the measures.

I've added some info to clarify the previous example:

Measure 2 (Profit)
  Measure 2.1 (Income)
  Measure 2.2 (Expenses)
   Measure 2.2.1 (Wages)
Measure 3 (Shareholder Funds)

The source I am using is structured like this, the measures are not using a simple collective term such as "Revenue".  I'm not looking for ways to change the source, I'd like to know if descendants should be able to work in this instance normally?

I do appreciate people could have a mighty opinion on this, but I'm only looking for the workaround solution, which in this case is about using the descendants function :)

[Lynn]

The descendants function is expecting a set expression, not a measure. Does your cube have a dimension that identifies that hierarchy?

It is hard to specifically suggest things without understanding the structure of your cube.

[iBoy]

Attached is an example structure - let me know if this makes better sense.

If I want a report to show me the measures down to the 5th digit level (i.e. 1.1.1.1.1) then the logic would be to apply a function along the line of:

"descendants ([1],5,before)" - this would return all the measures before any 5th level that would exist from Measure [1]

Unfortunately I get an error saying "The expression for dataItem="Data Item1" contains a measure set expression that cannot be evaluated by the OLAP Planner." - Data Item1 is the name of the item doing the descendants function

The descendants function will work if I apply a descendant function that goes down 1 level from the member specified.  It seems any more than 1 level it does not tolerate.  Is this because I'm using measures?  I assumed the logic would be similar to using that on a dimension?

Is there a viable workaround to achieving this?

Thanks!

[iBoy]

With said attachment

[Lynn]

What type of cube source do you have?

[iBoy]

TM1 - I'm being introduced gently to Cognos right? :)

[RKMI]

Hi,

I think those measure are suppose to be attributes to dimension i.e book of business dimension where those account will rollup the way you mention, and load a separte unit in a measure dimension. I don't think your TM1 expert know what structure is most feaisible for reporting in BI. Anyways TM1 experts might disagree but that's my 2 cents...

Thanks,
RK

[iBoy]

Thanks for the input RKMI,

That sounds like a change to the structure you're suggesting, and while desireable, is not acceptable solution in the context here.

I'm only after knowing if anyone has any (successful) experience or solution to allowin me to capture measures on many levels in just one data item.

iBoy

[MFGF]

Hi,

In the BI paradigm, measures are simply numbers identifying values, usually associated with the lowest level members in each dimension. They do not have a hierarchical structure in and of themselves. They do, however, relate to hierarchically organised members of a dimension.

The dimensional functions in Cognos 10 BI do not support levels of measures. Your options are either to change the structure of your TM1 cube to make it appropriate for reporting, or to replace it with metadata modelled (perhaps) from the original data sources feeding the cube.

Regards,

MF.

[iBoy]

In conclusion:

• You can perform a Children () function on a measure, but you cannot use the Descendants () function any lower than 1 level from what I've found.
• If you want to have greater control on the navigation of your measures, make them a dimension, and not a measure, as MFGF explains above.

Thanks everyone for your time and help!