Help with Macros

[godawgs85]

Is it possible to use dimensional functions within query macros (such as prompt or substitute)?  I've gotten error messages that functions aren't recognized when trying to use within a query macro.  Is there a workaround to this or any suggestions?

Thanks!

[CognosPaul]

It is absolutely possible. The macros are simply used to replace expression components before the SQL/MDX is generated.

For example:

#prompt('topOrBottom','token','top')#Count([Cube].[Dim].[Hierarchy].[Level],#prompt('Amount','integer','10')#,[Measure])

Will allow the user to choose if he wants to the top or bottom amount of a specific level against a measure. The measure an level could also be prompts. Why not post what you're doing so we can see exactly what the problem is.

[godawgs85]

Thanks Paul.

I was getting stumped trying to plast a LastChild function into a substitute macro (see below):

#substitute('All Weeks','All Dates',substitute('All Weeks','All Dates',lastChild (prompt('Selected Week','MUN'))))#

which resulted in error message

QE-DEF-0406  Unknown function 'lastChild' in macro expression.

Looking at your example below, do I need to change placement and/or include more "#" symbols?  Normally I don't have to do this with the prompt macro within the substitute macro but maybe dimensional functions are different?

Thanks!

[CognosPaul]

Macros are a separate language - not SQL and not MDX. You can embed macros into your expressions, but you can't put regular functions in macros.

The hash delineates the start and end of the macro. So you need to insert the macro in the correct place in your expression.

Code Select Expand

lastChild(#<MACRO STUFF GOES HERE>#)