Filter expression - Cube source

[kado]

Hello,

I am trying to leverage the FILTER expression and can't seem to get the syntax to work. I have a hierarchy with only 1 level. I would like to look for specific members in this hierarchy and display them based on their name (as opposed to just dropping the members onto my report).

hierarchy name = [cube].[vs].[vs]
member = 'Actual'

I thought I could create a data item and say:

filter ([cube].[vs].[vs], [cube].[vs].[vs]='Actual')

Any advice on what this syntax should look like or any other approaches?

thanks,
kado

[CognosPaul]

Try filter ([cube].[vs].[vs], caption([cube].[vs].[vs])='Actual')

It's worth noting that filter will always return a set, even if it's just one member. If you need to work with functions that expect a member, you'll need to wrap it with item(<filter>,0) (this will return the first member from the filter), or convert it into a member of its own with member(total(currentMeasure within set <filter>),'identifier','caption',<hierarchy>) (you can use this in tuples or slicers, for instance).

[kado]

Your suggestion worked and helped me out big time, THANKS!!!!

;D

[kado]

Hey PaulM,

Your suggestion above:

[Actual Label] = filter ([cube].[vs].[vs], [cube].[vs].[vs]='Actual')

Worked perfect for some crosstab headers I am using but when I integrate this exact same logic into a chart and then scale it back with some math:

[Actual Label]/1000000

Because the 'Style' --> 'Data Format...' for the base version of 8.4 doesn't work (whereas I want to display the chart labels scaled down a million without decimals). I know this is fixed in 8.4.1 (post link pasted below) but we aren't planning to upgrade anytime soon. Unfortunately when I try to scale it back I get this error:

Invalid coercion from 'memberSet' to 'value' for '[Actual Header]' in '[Actual Header] / 1000000'.

Any thoughts/ideas as to how I could dance around this?

THANKS,
kado

http://www.cognoise.com/community/index.php/topic,9390.msg30917.html#msg30917

[kado]

 ;D

PLEASE DISREGARD the above question!!!! It doesn't make sense!!