Cognos Analytics

Cognos Analytics

Connect, learn, and share with thousands of IBM Cognos Analytics users! 


#Product
#Analytics
#CognosAnalytics
#Analyticstools
#TechXchange Partner
#TechXchange Session
#TechXchange Presenter
#AI
 View Only

  • 1.  Manufacturing a MUN

    Posted 09/09/21 09:58 AM
    Cognos Analytics Experts,

    I am working with a PA data source and the client has a timescale that is a bit inconsistent in its naming.  The element name is 2021-M06, 2021-M07 etc for each month of the year.  However they are a fiscal calendar business but they use the calendar year and month for the n level elements.  For QTD and YTD rollups they convert to fiscal year/calendar month naming with FY 2022-M06 QTD and FY 2022-M06 YTD.  The dimension has a myriad of hierarchies making ancester MDX statements unusable.  I am trying to develop a report where they only need be prompted for the month and the QTD/YTD elements will be derived.

    In the attributes of each element are the values needed to construct the related rollup name, Fiscal Year and Month ID.  My intention is to construct the MUN by combining the attribute for fiscal year + attribute for Month ID + 'QTD' or 'YTD' depending.  To do this I have three queries, the month prompt query, a query that constructs the rolllup name by taking the month selected and retrieving and combining the attributes, and a third query which is the report that puts the rollup element on the page.  The page is associated with the second query and a Master Detail relationship is established for the rollup name derived in query 2 to the prompt parameter (QTD ID) for the rollup element itself.  The macro to generate the MUN is:

    #'[Global Planning PL].[Time Month].[Time Month].[Month]->:[TMR].[Time Month].[Time Month].['+prompt('QTD ID', 'token')+']'#

    The report right now is just a list with the element I'm trying to get.  When I run the report 'No Data' is what is displayed (I use a default value for the month selector).  However, when I run the query 3 as 'View tabular data', I am prompted for QTD ID and when input the report renders fine.  I feel that the prompt parameter isn't being generated or is NULL but I can't see what's coming across as the parameter value.  Is there a way to do so in Cognos?  I tried to use the function 'ParamValue' but I get an error message that says the vendor specific function is not supported (?!).

    Thanks for any insight.

    Dean

    #CognosAnalyticswithWatson


  • 2.  RE: Manufacturing a MUN

    Posted 09/09/21 02:02 PM

    Hi Dean,
    Could it be that the parameter value was supplied earlier.
    In Reporting there is an action to clear the parameter values.
    Alternatively:  When you save the report and run it from the portal, does it prompt?
    // Henk



    ------------------------------
    Kind regards,
    HENK CAZEMIER
    ------------------------------



  • 3.  RE: Manufacturing a MUN

    Posted 09/09/21 09:19 PM
    Henk,

    I have run from the portal and yes, I am prompted for the QTD ID.  So this would seem to indicate the parameter is not being supplied by the second query but not sure how to resolve.

    Thanks,

    Dean

    ------------------------------
    Dean Watts
    ------------------------------

    Original Message


  • 4.  RE: Manufacturing a MUN

    Posted 09/10/21 10:03 AM

    Hi Dean,
    When you're in Reporting, it will remember the prompt values that were entered and use these during validation/preview. You can clear these parameters from the More menu in the top right hand.

    You will be prompted when running the report from the Run menu or from the portal.
    I hope this helps. 
    // Henk



    ------------------------------
    Kind regards,
    HENK CAZEMIER
    ------------------------------

    Original Message


  • 5.  RE: Manufacturing a MUN

    Posted 09/13/21 07:45 AM
    Edited by System Admin 01/20/23 04:29 PM
    Dean, to verify your MUN is generated as intended, you can create an item and add your macro to it, with a sq() around it.
    #sq('[Global Planning PL].[Time Month].[Time Month].[Month]->:[TMR].[Time Month].[Time Month].['+prompt('QTD ID', 'token')+']')#

    prompts me, I add "MACRO_FOR_DEAN"
    and the result is:

    ​

    ------------------------------
    Sandeep Dhirad
    Elkjøp Nordic
    ------------------------------



Related Content