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
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')')#
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
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
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