Cognos | The cognos #prompt()# macro

Here’s another bitch. The Cognos #prompt()# macro.

In case you’re wondering about giving a default value to the prompt, here’s how you do it :

#prompt(‘prompt_name’, ‘prompt_type’, ‘default_value’)#

However, there is a catch. The prompt won’t accept just anything as its default value – the third argument can only be a column name in case of strings. However for numbers, it accepts numeric constants. For example :

#prompt(‘id’, ‘INTEGER’,’-1′)# is correct,

but

#prompt(‘name’, ‘STRING’, ‘HELLO’)# is wrong. It will have to be :

#prompt(‘name’, ‘STRING’, column_name)#

Weird. I’m not very sure about this, but After a lot of hit and trials on it, where there is no documentation available on this particular topic, I think this is correct.

Please let me know if this is correct/wrong, if you, like me are fedup with the lack of documentation too.

Advertisements
This entry was posted in Uncategorized. Bookmark the permalink.

20 Responses to Cognos | The cognos #prompt()# macro

  1. Pingback: superdrupermegapuper54321

  2. Paul Mendelson says:

    That’s not entirely accurate. The following will work:

    #prompt(‘name’,’string’,”’hello”’)#

    The hashes are resolved before anything else in the query, and are returned as literal strings. So the following prompt:
    #prompt(‘test’,’string’,’hello’)#
    will return: hello
    Cognos will try to resolve the hello as if it was also a function.

    By adding two additional single quotes, you are escaping a quote, and the macro will return ‘hello’, which Cognos will correctly interpret as a string.

    Also, the full prompt macro is:
    #prompt(‘name’,’datatype’,’default’,’pretext’,’source’,’posttext’)#

    The following prompt will work with a cube:

    #prompt(‘example’,’memberuniquename’,'[cube].[dimension].[hierarchy].[level].[ALL]’,’children(‘,'[cube].[dimension].[hierarchy].[level]’,’)’)#

    This is an optional macro that will return the children of the selected member, or will return the ALL member if nothing is selected. Because the source parameter is filled, it will create a value prompt based on that level when that query item is called.

    • anonymouscowherd says:

      Coolness. I didn’t have the time then to look up things in this detail, and stopped after I found out about the ‘default’ parameter, and I haven’t had to work on cognos reports ever since, so didn’t really follow up on this small hit and trial effort of mine.

      Now that you mention about the single quotes, it makes sense to me :) Thanks !

      :( Thanks a lot for this – I’ll make the required changes to the post.

  3. Vivek says:

    Absolutely helpful! I am surprised that cognos has not bothered to put this information in their KB!!! Thank you.

  4. Michelle says:

    Hi,

    I am new to Cognos and I need some help. I’m trying to connect the value prompts from several tabs in my dashboard. Different tabs have different value prompts and I have multiple cubes as well. Is it possible for me to use global filtering and “communication between portlets” to do it? Cos I’m currently encountering some errors.

    Can you please help me?
    Many thanks!!

    • Paul Mendelson says:

      Writing prompts for multiple cubes is difficult but not impossible. You simply need to make the value of the prompt something all the cubes can recognise.

      Most often when I need to do this I use the something like the following:

      item(filter([Cube].[TimeDim].[Hier].[Years].[CatCode],currentMember([Cube].[TimeDim].[Hier])=#prompt(‘Year’,’integer’)#),0)

      Instead of directly setting the member, like you should be doing with cubes, I’m filtering a set of members (filter always returns a set, even if it’s a set of 1 or 0 members) and taking the first member. Use a similar expression in all of your reports and they should all be able to accept the parameter value from the same prompt.

      Now I’m going to be extremely rude (apologies to atruesowordsdman) and suggest that you send any further questions to a forum dedicated to Cognos (Developer Works or Cognoise Communities for instance). More people there would be able to help you, and the answers could help others as well.

  5. Anonymous says:

    Is it possible to use the prompt value in multiple places in an sql query (with other prompt values) and inside a to_date function?

  6. Cognos says:

    Hi All,
    The following macro is failing could you please help me in resolving the single quotes in pretext.

    #promptmany(‘QuoteMethod’,
    ‘varchar(500)’,
    ‘ ‘,
    ‘ AND (Case When Quote_Assess.QUOTE_COUNT=1 Then ‘Single Quote’ Else ‘Competitive Quote’ End) IN (‘, ”, ‘)’
    )#

    Thanks

    • Jiping Wang says:

      First, change “varchar(500)” to “string”.
      “varchar” is not a valid datatype here.
      Could be {boolean,date,datetime,decimal,double,float,int,integer, interval,long,memberuniquename, numeric,real,short,string,time,timeinterval,timestamp,token,… }

  7. Anonymous says:

    Hi,

    I’ve been using cognos express for a couple of months now and i would like to know the following:
    Is it possible to make one page that uses value prompts and shows the output (for example a crosstab) in the same page? So you can see the selected prompt values after selecting them and clicking on the finish button.

  8. luhar says:

    how to get model name in report studio…can anybody plz let me know

  9. Anonymous says:

    none of the above is working properly on cognos 10 , will you all post the correct method to resolve prompt macro problems .

  10. Nick says:

    I am trying this since 2 days.in Report Studio 8.4 It.doesn’t work

    [gosales_goretailers].[Orders].[Order date] BETWEEN cast(#prompt(‘pBEGINDATE’)#;date) AND _last_of_month(cast(#prompt(‘pBEGINDATE’)#;date))

    Can someone tell me where I am wrong?

  11. Swetab says:

    first thing that stands out as wrong is the use of ; instead of , in the cast functions.
    Also, please provide a data type in the prompt macro, example: #prompt(‘pBEGINDATE’,’String’)# (since you are going to cast it to date any ways, use string)

  12. Anonymous says:

    The # is in the wrong place. Should be #prompt(‘pBeginDate’,’date’)#

  13. BE says:

    Also you’re first post is incorrect, it is in the KB at IBM – see 1373120
    You need to put sq around the text you want to use as a default

  14. pinterest.com says:

    great post, very informative. I wonder why the
    other specialists of this sector do not notice this. You should proceed
    your writing. I am sure, you have a huge readers’ base already!

  15. Raj says:

    Hi Paul

    Pls let me know how can I pass ALL or the prompt value in macro for relational .

  16. Felicia says:

    I have a problem when I’m calculating the totals for other two columns based on grouping of this dataitem containg case:

    CASE WHEN [A/C]=’WTD’
    THEN ‘WITHDRAWALS’
    WHEN [A/C] IN (‘@PA’,’PUR’) AND [CODE] in (‘A’,’D’)
    THEN ‘PURCHASES’
    WHEN [A/C] IN (‘DEP’,’DE3′,’DE4′)
    THEN ‘DEPOSITS’
    WHEN [A/C] IN (‘ABI’,’ALI’)
    THEN ‘BALANCE INQUIRY’
    WHEN
    [DENIALCODE]=’XX’ AND [AMTREQUESTED]=0
    THEN ‘SFEE’
    [DENIALCODE]’XX’ AND [RESPONSECODE]=’02’ THEN
    ‘DENIALS’
    END

    for SFEE and Denials totals are populated wrong, becoz we are not checking A/C condition it should calculate for all A/C along with other two conditions.

    is there any way I can mention [A/C] =ALL values in [A/C] ??

    I tried creating a hidden prompt with use value and display value as [A/C] and I gave
    WHEN ( ?p_msg? IN (‘ALL’) AND (1=1)) or ([MSGTYPEQUAL] in (?p_msg?)) [DENIALCODE]=’XX’ AND [AMTREQUESTED]=0
    THEN ‘SFEE’

    WHEN ( ?p_msg? IN (‘ALL’) AND (1=1)) or ([MSGTYPEQUAL] in (?p_msg?)) AND [DENIALCODE]’XX’ AND [RESPONSECODE]=’02’ THEN
    ‘DENIALS’
    but it gives me SFEE value along with other values but does not return DENIALS and the total calculated for SFEE alone is wrong whereas for others its correct.

    Problem to be fixed
    1.I should get both SFEE and DENIALS
    2.The totals should be also correct

    Thanks In Advance

  17. sreevp says:

    Hi,
    I am trying to pass optional where clause in sql
    SELECT * FROM table WHERE (DATETIME >= #sq(prompt(‘Reportstartdate’,’date’))#)
    # prompt( ‘Mgr1’ , ‘string’ ,” ,’ AND MGR_NAME LIKE ‘
    ) #

    Here now if user enter ‘Mgr1’ parameter then where clause should be
    SELECT * FROM table WHERE (DATETIME >= #sq(prompt(‘Reportstartdate’,’date’))#)
    AND MGR_NAME LIKE ‘PAUL%’

    how to achieve this wild card in macro optional?

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s