Macros allow you to alter SQL/MDX before it’s send to the datasource. Using the macro functions timestampmask, you can easily select specific members, or query certain tables.
Prompt macros give you an extremely fine grain of control. You can, for instance, allow a user to which measure he wants to see in his graph, or create dynamic default values.
To begin with, the structure of the prompt macro:
#prompt/promptmany(‘parameterName’,’dataType’,’defaultValue’,’leadingText’,’source’,’trailingText’)#
parameterName is self explanatory. Note that it is case sensitive. If you want to pass a value to that parameter via the URL or POST you need to preface it with a p_ so Par_Year becomes p_Par_Year.
The dataType will reject anything not covered by the datatype. The string ‘Hello World!’ would be rejected if the datatype was set to integer. The datatype also determines the type of prompt generated. ‘Integer’, for example, will always create a text box prompt even if the source field (see below) is filled.
The defaultValue will be returned if no parameter is selected. This does not need to obey the dataType. For example, you could have a filter set up: [Year] = #prompt(‘parYear’,’integer’,’year(getDate())’)# If 2010 was selected the filter would be [Year] = 2010, if no value was returned to the prompt the filter would be [Year] = year(getDate()).
The leadingText is text that is automatically entered before the selected parameter. For example a filter: #prompt(‘parYear’,’integer’,’1=1′,'[Year] = ‘)# In that example if 2010 was selected [Year] = 2010 would be returned to the filter. If nothing was selected, the filter would be 1=1.
The source will a list or tree prompt based on a referenced field. #prompt(‘timeMember’,’memberuniquename’,”,”,'[NS].[TimeDimension].[TimeHierarchy]’)# will create a tree prompt that will allow the user to select any member from the TimeHierarchy. #prompt(‘timeMember’,’memberuniquename’,”,”,'[NS].[TimeDimension].[TimeHierarchy].[MonthLevel]’)# will create a list prompt that will allow the user to select a single member from the MonthLevel. You can also include functions in the source: #prompt(‘timeMember’,’memberuniquename’,”,”,’filter([NS].[TimeDimension].[TimeHierarchy].[MonthLevel],[Measures].[Sales]>1000)’)# will create a list prompt of all months that had more than 1000 sales.
The trailingText, much like the leadingText, placing text after any values selected. #promptmany(‘Years’,’memberuniquename’,’children([NS].[TimeDimension].[TimeHierarchy]->[All Member])’,’set(‘,'[NS].[TimeDimension].[TimeHierarchy].[YearLevel]’,’)’)# will either return the children of the All member (if no values are selected), or will return a set of members that the user selected.
Congrats! Very neat and useful blog. If you meant for it to become a thorough Cognos KB, than you may want to copy-paste or add a reference to other articles / tricks / solutions other post all over, unless it is your intention to make it home brew only.
If you’ll need, I can send you those references from time to time as I find them, should you be interested.
Another idea, is to create a dictionary for all those annoying error messages that RS generates. I have personally started to gather them, so … let me know if you’ll be interested in those.
Cheers, and ttul8r (talk to you later).
I’m aiming for this to be more home-brew; a repository for tricks that I develop that I think other people would appreciate.
There’s another site http://cognoswiki.wikispaces.com/ that would be more appropriate as a repository for all things Cognos. It’s a Wiki, so anyone can edit it.
Hi Paul!
First ive got to say, GREAT blog!! Keep it up.
Ive run into an issue with one of my prompt macros. What i want to do is to dynamic set the default value within the prompt macro. Sort of:
#'[Dimensional view].[Week].[Week].[Week]->[all].[‘ + prompt(‘WeekTo’, ‘token’, ‘_week_of_year(current_timestamp)’) +’]’#
I am using a DMR (dont hate me :D) and im on 10.2. The problem is that the function _week_of_year(current_timestamp) is a function and not in the “macro functions tab” and im getting http://www-01.ibm.com/support/docview.wss?uid=swg1PI14056. The code works seperate though.
I was just wondering if its worth upgrading, as this post sort of says, or am i missing something?
br
/Peter
The problem here is trying to mix query and macro functions. This is actually one of the problems I have with DMR, it’s incredibly difficult to get non-standard date parts with macro functions. Fortunately there are a few cheats we can use.
The easiest solution I have requires a modification of the Framework. Create a new query with two data items. Key: 1 – Value: _week_of_year(current_timestamp)
Create a parameter map (currentWeekNumber) based on that query with key being key and value being value. Now you can reference that in the macro:
#'[Dimensional view].[Week].[Week].[Week]->[all].[‘ + prompt(‘WeekTo’, ‘token’, $currentWeekNumber{1}) +’]’#
Great Paul! Worked like a charm