Prompt Macro on dimensional data

[mickyo73]

Hi All,

I have a scenario where a we have cognos connected to an SSAS cube.  The cube consolidated a number of databases that can be sliced by a company dimension.

We have a report that we are working on that has a number of value prompts - one of which is a company prompt based on the company dimension. I'm attempting to create a filter in all the other prompts eg Customer Bill to, Territory that will cross filter these prompts based on the selection(s)  made in the company prompt (note - all prompts are multi select).

In the query for the Bill To prompt I've created a detail filter that contains the following:

[Sales Cube].[Customer].[Company Code] in (#csv(split(';', promptmany('pComp','token')))#)

where the pComp parameter is the Company prompt.

When running the report it's returning the following message:
  Invalid coercion from 'hierarchy' to 'string' for '[Sales Cube].[Customer].[Company Code]' in '[Sales Cube].[Customer].[Company Code] in ('20','30','40')'.

I understand it's because the company codes selected are string. My question is how can I get square brackets around the result ie so it returns the following instead:

  [Sales Cube].[Customer].[Company Code]' in '[Sales Cube].[Customer].[Company Code] in ([20],[30],[40])

I've attempted to use the #sb()# macro in conjunction with my detail filter with no success.

Any assistance would be very much appreciated.

Thanks in advance
Mick

[cognostechie]

You are trying to create a detailed filter instead of slicer. Remove the filter and create a slicer instead.

#promptmany('pComp','mun')#

The 3rd parameter is the default so if you want to select company 20,30,40 by default then you can try this:

#promptmany('pComp','mun','set('20','30','40')')#

[mickyo73]

Hi Cognostechie,

thanks for the reply but I thought the slicers only worked on the measures.

The default will be all members so I think I can use rootmembers( [Sales Cube].[Customer].[Company Code]) to get all of them as the default.

I'll give your suggestion a go and see how I go.

Thanks again
Mick

[adik]

a slicer does not exclude members from hierachies but instead filters the measures accordingly. if you want to display a set of selected id's than create a new data item with the prompt macro formula

[cognostechie]

Yes, that's what I meant too. just that I did not specify it clearly. Instead of a slicer for the query, create a data item and put the macro there and remove the detailed filter