Hi,
I am using the below code in my slicer
#prompt('prmDistrib','mun','[AllRegion]')#
And my [AllRegion] data item has the following expression.
set(#prompt('prmRegion','mun','[001 - MID WEST],[002 - MID ATLANTIC],[003 - NORTH EAST]')#)
Which implies when there is no value selected for Distributor Prompt then auto select the values in Region prompt.
I read that using dataitem that is not in layout would decrease the performance of the query.
so If I want all in one statement in slicer removing the additional [All Region] DataItem, how will my slicer expression be.
I am getting error will changing my slicer expression to below
#prompt('prmDistrib','mun','set(#prompt('prmRegion','mun','[001 - MID WEST],[002 - MID ATLANTIC],[003 - NORTH EAST]')#)')
Please Advise.
Thanks,
Nithya
Where did you read that? That doesn't make sense to me at all.
The reason that you're getting an error here is that the default value is contains nested quotes and hashes. Remember, a string starts and ends with a quote, so Cognos is interpreting:
'set(#prompt('prmRegion','mun','[001 - MID WEST],[002 - MID ATLANTIC],[003 - NORTH EAST]')#)'
as
'set(#prompt(' prmRegion','
where blue is a string and red is a function. Obviously incorrect syntax.
Instead, the set( should be a string, with the prompt values concatenated inside it. You only need one hash to delineate the start and end of a macro, so you can get rid of those.
#prompt('prmDistrib','mun','set('+ prompt('prmRegion','mun','[001 - MID WEST],[002 - MID ATLANTIC],[003 - NORTH EAST]')+')')
Or, because prmRegion will only return one value if selected, do this:
#prompt('prmDistrib','mun',prompt('prmRegion','mun','set([001 - MID WEST],[002 - MID ATLANTIC],[003 - NORTH EAST])'))
There will be no improvement in performance over doing that putting that macro in a separate data item and calling that in the default. There may even be a slight decrease in performance if you have to use prmRegion in other data items in the query.