How to dynamically filter a member in a hierarchy according to fact value

[mederik]

Hi,

I work with cognos 8.4 plugged onto Essbase.
I want to get the members of my client axis whose margin is < -10000

I have somethin that does quite well the job...

Code Select Expand

filter(filter(
        members ( [TMS_CLT].[Tiers].[Tiers] );
        count(1 within set children( currentMember( [TMS_CLT].[Tiers].[Tiers] )  )  ) = 0 );
        [TMS_CLT].[Indicateur].[Indicateur]->:[DO].[IND].[RNE].[RBE].[PNB].[MRG] < 10000)

...but i want it to be dynamic. I want my users to be able to set their own margin value in a prompt :

So when I do this =>

Code Select Expand

filter(filter(
        members ( [TMS_CLT].[Tiers].[Tiers] );
        count(1 within set children( currentMember( [TMS_CLT].[Tiers].[Tiers] )  )  ) = 0 );
        [TMS_CLT].[Indicateur].[Indicateur]->:[DO].[IND].[RNE].[RBE].[PNB].[MRG] < ?P_SEUIL_MNI?)

I've got an error message (I translated it from french so it might not be very well done) : Impossible to convert the operands of '[TMS_CLT].[Indicateur].[Indicateur]->:[DO].[IND].[RNE].[RBE].[PNB].[MRG] < ?P_SEUIL_MNI?' into coherent dimensionnal objects.

Is there anyone here who might help me ?

Thank in advance for your time

[RKMI]

Hi,

Can you try this,

Create a new data item and add a value of 10000 to ensure static value works. Then add it to detail filter try with [Data item1] = ?Prompt Para?

Then make the following change to your previous data item as follows,

filter(filter(
        members ( [TMS_CLT].[Tiers].[Tiers] );
        count(1 within set children( currentMember( [TMS_CLT].[Tiers].[Tiers] )  )  ) = 0 );
        [TMS_CLT].[Indicateur].[Indicateur]->:[DO].[IND].[RNE].[RBE].[PNB].[MRG] < [Data Item1])

If that doesn't work cast as a numeric.

Thanks,
RK

[CognosPaul]

You're trying to compare a value, 1000, against a member. That won't work as the datatypes are different. Casting won't help here it's meaningless to cast a member into a value or vice versa. Is margin a measure? If it needs to be tupled with that member, then do tuple(measure, member)> 1000.

EDIT: My phone doesn't know the difference between databases and datatypes.