Checking olap prompt value on a realtional filter to filter data

[cognos05]

Hi ,

I posted this under FM , just reposting here since its appropriate here .

Have a fm model with a olap cube and a realtional table . Report has mulitple pages from data from 2 sources seperately . Report has a olap prompt and the similar column is  present on realtional table . so based on the olap prompt chosen from prompt page i have to filter the realtional table with some case value filter .

prmAgency is a olap prompt with query - Desendats(set(1,2,3),agencies)

[Agency] is a query item in realtional table and has similar value as agency

so my detailed filter on realtional table is belwo ,but it throws error .
Rolevalue function is called with invalid syntax.I will have multiple cases for 20 agencies.The below 1 case throws the error .

[Agency]=
case when (caption(?prmAgency?) ='agency1')
Then ('Aagency1')
Else ('Others')
END

I think i need totake the olap prompt value and convert it as a string , but not sure how to do other than caption and not sure why caption will error.

Any help is appreciated.

Thanks

[CognosPaul]

Instead of using a prompt alias, you should use a prompt macro. This will give you more opportunities to work with the MUN that's being passed.

In your filter you're using the caption function, which is only available in the cube. The relational database will have no idea what you're asking.

Are you filtering only on either Agency1 or Others, or is it more complex? Can you paste an example of what the agency MUN looks like?

Consider the following statement:

Code Select Expand

[Agency] = #
sq(
  case prompt('prmAgency','mun')
    when '[Cube].[Dim].[Hier].[Level]->[123]' then 'Aagency1'
    else 'Others'
  end
)
#

It uses a case statement, like yours, but because the case statement is in the macro, it is being processed as the SQL is being generated, meaning the database will have a simple [Agency]='Aagency' filter. In my example it's a little difficult because the member key is 123, but if the MUN contained the phrase 'Aagency1' we could strip out the excess text and use that. That's why I asked you for an example mun.

[cognos05]

Thanks for the suggestions and help

This is the mun

[Agency Sales Cube].[ShipTo Customer].[ShipTo Customer].[ShipToAgency]->:[PC].[@MEMBER].[ABCDNAME]

The issue is ABDNAME is mapped to ABNAME1 in realtional database column ,

so i will have a case for each agencyor each prompt value
I could add a coulmn in relation table which will have the same values as MUN 's Key ABDNAME if thats more easier

[cognos05]

[Agency] = #sq(
  case prompt('prmAgency','mun')
    when '[Agency Sales].[ShipTo Customer].[ShipTo Customer].[ShipToAgency]->:[PC].[@MEMBER].[CROSSROADS]' then 'Aagency1'
    else 'Others'
  end
)
#

Getting validation error on the above code.

I am not sure where the parsing error happens

[cognos05]

Thanks Paul for your solution .

Looks like case with macro doesnot work with DQM .

I created a Parameter map in M Model and used that in my filter by haing the key as MUN of the olap prompt and value as the data to be filtered on relational table .

ShipToAgencyLookup
default 'Others'
key: [Agency Sales].[ShipTo Customer].[ShipTo Customer].[ShipToAgency]->:[PC].[@MEMBER].[CROSSROADS] value: Aagency1

Then in the query you would do [Agency] = #sq($ShipToAgencyLookup{prompt('prmAgency','mun'})#


Thanks,