Descendant function QE-DEF-0030 when level does not exist due to Cube security

[gagnonp]

In report studio I have a Dimensional query with one query item. See code below. Because of security, the Level 3 is not always availble in the Hierarchy. Report studio generates the error: QE-DEF-0030 Expression parsing error.

Is there a way around this?  i.e. Macro substitution, build a string...  I cannot find a function that returns level names.

Code Select Expand

union(
descendants(
[Cube].[Dimension].[Hierarchy],
[Cube].[Dimension].[Hierarchy].[Level 2]
),

union(
descendants(
[Cube].[Dimension].[Hierarchy],
[Cube].[Dimension].[Hierarchy].[Level 3]
),
union(
descendants(
[Cube].[Dimension].[Hierarchy],
[Cube].[Dimension].[Hierarchy].[Level 4]
),
descendants(
[Cube].[Dimension].[Hierarchy],
[Cube].[Dimension].[Hierarchy].[Level 7]
)
)
)
)


[CognosPaul]

With security issues like that you'll need to find a different way. First a few questions:

Is this DMR or a real cube?
Will only Level 3 disappear, or will other levels also?
It looks like you're unioning the members into a single set ordered by level. Is that correct? Does it need to be in that specific order, or do you just need all available members starting in level 2?

Have you tried:

descendants(
    allMember
  , cube.dim.hier.level7
  , self beforewithmember
)

or:

order(
  descendants(
    allMember
    , cube.dim.hier.level7
    , self beforewithmember
  )
  , roleValue('_levelNumber',currentMember(cube.dim.hier))
)

[gagnonp]

Paul, thanks for your reply.  It is a real cube . All levels except Level 1, 2 and 3 can dissapear.

Yes I am unioning the members from the specified levels 3,4 and 7 into a single set. I do not want members from levels 1, 2, 5 and 6. Do not need specific order.

Using descendants of level 7 beforewithmember will give me members from levels 5 and 6 which I don't want.

[CognosPaul]

How about something like:

filter(cube.dim.hier,roleValue('_levelNumber',currentMember(cube.dim.hier)) not in (1,2,5,6))