Filter he row value based on static prompt value dimensional reporting

[cognos05]

I have a static prompt for measures with 4 values top20 qty and top 20 revenue,top 20 growth and bottom 20 growth etc

so on the rows i have a data item which has a filter that ranks the products based on the measure selection.

I have an expression like

(Order(
filter(Descendants(#prompt('PrmProduct','mun','[10 - Knee & Hip]')#,[APAC SALES].[Items].[Items].[Item]),
rank([TupRev] within set (Descendants(#prompt('PrmProduct','mun','[10 - Knee & Hip]')#,[APAC SALES].[Items].[Items].[Item])))<=20)
,tuple([TupRev]),BDESC))


All i need to is change the [tuprev] to 3 other values.

so my data item will be like
if(prmmeasure=top20 )
filter,(product ,rev)
Else
if(prmmeasue=top20growth)
filter(product,growth)

etcc.



but the issue is I cannot filter with if expressions , so how to handle this
.
I cannot also directly replace the measure value of prompt as i am filtering on growth for one of the measure.

[CognosPaul]

The token prompt is your friend here. When the user selects a measure, make sure the use value is the entire measure mun.

Then in your filter function, you can use

Code Select Expand


filter(
    descendants(...)
  , rank(#prompt('Measure','token','[Cube].[Measures].[DefaultMeasureGoesHere]')# within set (...) )
)


The macro expression will resolve before the mdx is processed, so when the user selects a measure it the filter function will appear like:

Code Select Expand


filter(
    descendants(...)
  , rank([Cube].[Measures].[SelectedMeasure] within set (...) )
)


[cognos05]

Paul ,

Thanks for the response.so my prompt say has 2 measures one is top 20 sales and other is top 20 growth
for sales, i can have the use value as the mun of sales measure  but for top 20 growth , there is no growth measure , I am calculating it by getting the the timeperiod and its prior timeperiod with sales value. so what will i have as a use value.

so my calculate data item for tupgrpwth is

if([TupRevLastYear]=0)
Then(0)
Else
([TupRev]-[TupRevLastYear]/[TupRevLastYear])

TupRev - (aggregate([USD Sales] within set ([Month])))

TupRevLastYear - (aggregate([USD Sales] within set (lag(#prompt('PrmMonth','mun','[2019/Dec]')#,13))))

Then i rank on tupgrowth data item, how can i get this achievable , its easy if its same measure if i have to manipulate that measure ?

can i give the tupgrowth as a use value for top 20 growth  static measure value. I am confused on how this would work for the growth .
I saw your example for topcount and bottomcount ,but here the growth is not the actual measure.

Thanks for all your help .

[CognosPaul]

So the prompt controls the edge set, not the measure?

Easiest way would be to create four data items Top 20 Sales, Top 20 Growth, etc. each one defined exactly the way you need. Then make a fifth data item that's simply

#    sb (pro mpt('Measure', 'token')) #

That data item should be the one in the report.

The use value of the prompt should be the names of the data items. When the report runs, the prompt control on the page will send Top 20 Growth to the parameter. In the query, the data item will get Top 20 Growth, and wrap that in brackets (the sb function), pointing to the desired item.

[cognos05]

Let me try this ,so is this the use of using token , where prompt can take a dataitem and replace it .