Cognos Analytics

Cognos Analytics

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

 View Only

  • 1.  Built in Calendars - Modifications

    Posted Fri December 03, 2021 09:16 AM
    Hi all, I have a question on the built in calendars for Gregorian and Fiscal.  My client would like the week to be Sun to Sat instead of Mon - Sun.  Is there a way to modify these calendars easily to do this?  The only changes would be to the WEEK fields I believe.

    Thanks!

    ------------------------------
    Razorbx13
    ------------------------------

    #CognosAnalyticswithWatson


  • 2.  RE: Built in Calendars - Modifications

    Posted Fri December 03, 2021 03:31 PM

    Hi,
    The Calendars are included in the install as Data modules. You can edit these definitions to suit your needs. Steps:
    1 - navigate to Team content->Calendars and open Gregorian Calendar
    2 - modify the Current Week and Prior Week definitions. (see below for the updated definitions).
    3 - Save as -> Gregorian Calendar Sunday=1
    4 - Edit a Data Module that is already integrated with Calendars
    5 - On the sources tab, select Gregorian Calendar and from the context menu select Relink.. 
    6-  Select the Calendar Data module saved in step 3

    Prior Week change the expression to:

    // This is a template expression that is used by the column property 'Lookup reference'.
// To pass validation, the line below must remain as a comment. Do not remove the forward slashes.
// validate: 1 = 1
#$_this.parent.idForExpression# >=
   #_add_days(queryValue($_this.parent.split.ref + '.dWeek',
              $_this.parent.split.ref + '.TheDate = ' +
              queryValue($_this.parent.split.ref + '.PW_TheDate',
                         $_this.parent.split.ref + '.TheDate = ' + _add_days($_as_of_date,1))
            ),-1)#
AND
#$_this.parent.idForExpression# <
   #_add_days(queryValue($_this.parent.split.ref + '.dWeek',
               $_this.parent.split.ref + '.TheDate = ' + _add_days($_as_of_date,1)),-1)#



    Current Week change the expression to:

    // This is a template expression that is used by the column property 'Lookup reference'.
// To pass validation, the line below must remain as a comment. Do not remove the forward slashes.
// validate: 1 = 1
#$_this.parent.idForExpression# >=
     #_add_days(queryValue($_this.parent.split.ref + '.dWeek',
                $_this.parent.split.ref + '.TheDate = ' + _add_days($_as_of_date,1)),-1)#
AND
#$_this.parent.idForExpression# <
    #_add_days(queryValue($_this.parent.split.ref + '.dWeek',
               $_this.parent.split.ref + '.TheDate = ' +
               queryValue($_this.parent.split.ref + '.NW_TheDate',
                         $_this.parent.split.ref + '.TheDate = ' + _add_days($_as_of_date,1)),-1)
)#


    I tested these modifications and I think I got them right. In essence the reference date, $_as_of_date is shifted forward by 1 day, then the range that is computed is shifted back by 1 day. Both operations use the _add_days macro function.

    I hope this helps.



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

    Original Message


  • 3.  RE: Built in Calendars - Modifications

    Posted Mon December 06, 2021 10:58 AM
    Hi @HENK CAZEMIER, Thank you for sharing your advanced insights. We already modified the calender module to fit our business needs. ​So we added a lot of filters and tried to sort them into folders to avoid a single big list. Unfortunately all of our attempts ended in some error message. If you know a way how to put the filters into (sub-)folders please let me know. Best regards, Philipp

    ------------------------------
    Philipp Hornung
    ------------------------------

    Original Message


  • 4.  RE: Built in Calendars - Modifications

    Posted Wed December 08, 2021 01:34 PM
    Edited by System Admin Fri January 20, 2023 04:20 PM

    It's usually helpful to include the text of an error message so people can have a better idea about what has happened.

    I'm assuming that it is something like this:

    XQE-MSR-0026 'Prior_YTD' cannot be applied because its parent does not have lookup reference definition.

    In this case the message is saying that the Prior YTD relative time filter can't be applied to its parent object, which normally would be a measure, which would normally create a relative time measure.  Because the filter is in a folder, the parent of the relative time filter is the folder. This is wrong as the folder (or set of nested folders) should not affect things as folders are not namespaces.

    In addition to that bug, the error message has a grammar error.  It should say '...have a lookup...'



    ------------------------------
    IAN HENDERSON
    ------------------------------

    Original Message


  • 5.  RE: Built in Calendars - Modifications

    Posted Wed December 08, 2021 02:03 PM
    Sorry, I thought I did include it.  The prior week works fine.  The current week change based on the above recommendation gave me this.  Thanks for your help.

    XQE-CON-0007 XQE error encountered: Macro function '_add_days' has 1 arguments, but requires 2.
    Text to expand:
    // This is a template expression that is used by the column property 'Lookup reference'.
    // To pass validation, the line below must remain as a comment. Do not remove the forward slashes.
    // validate: 1 = 1
    #$_this.parent.idForExpression# >= #_add_days(queryValue($_this.parent.split.ref + '.dWeek', $_this.parent.split.ref + '.TheDate = ' + _add_days($_as_of_date,1)),-1)#
    AND
    #$_this.parent.idForExpression# < #_add_days(queryValue($_this.parent.split.ref + '.dWeek', $_this.parent.split.ref + '.TheDate = ' + queryValue($_this.parent.split.ref + '.NW_TheDate', $_this.parent.split.ref + '.TheDate = ' + _add_days($_as_of_date,1)),-1) )#
    RSV-SRV-0042 Trace back:
    RSReportService.cpp(786): XQEException: CCL_CAUGHT: RSReportService::processImpl()
    RSReportServiceMethod.cpp(260): XQEException: CCL_RETHROW: RSReportServiceMethod::process(): asynchRunSpecification_Request

    ------------------------------
    Razorbx13
    ------------------------------

    Original Message


  • 6.  RE: Built in Calendars - Modifications

    Posted Mon December 13, 2021 01:58 PM

    The expression for the current week filter which Henk pasted inadvertently has a wee typo.  Use this expression for the current week filter.

    // This is a template expression that is used by the column property 'Lookup reference'.
// To pass validation, the line below must remain as a comment. Do not remove the forward slashes.
// validate: 1 = 1
#$_this.parent.idForExpression# >=
     #_add_days(queryValue($_this.parent.split.ref + '.dWeek',
                $_this.parent.split.ref + '.TheDate = ' + _add_days($_as_of_date,1)),-1)#
AND
#$_this.parent.idForExpression# <
    #_add_days(queryValue($_this.parent.split.ref + '.dWeek',
               $_this.parent.split.ref + '.TheDate = ' +
               queryValue($_this.parent.split.ref + '.NW_TheDate',
                         $_this.parent.split.ref + '.TheDate = ' + _add_days($_as_of_date,1))),-1
)#


    Here is the difference.

    Current week filter expression with the closing bracket for queryValue in the right place

    I used the expression editor's parenthesis matching functionality to find the matching pairs of parentheses and discovered that the closing parenthesis for the second queryValue function was in the wrong place.

    Once I fixed that the current week filter expression the XQE error will go away and the expected SQL is generated.

    Matching parentheses
    Fragment of the resulting SQL.   The as of date is April 15, 2012, which is a Sunday.
    Here is a fragment of the generated SQL where the as of date is April 7, 2012, which was a Saturday.


    ------------------------------
    IAN HENDERSON
    ------------------------------

    Original Message


  • 7.  RE: Built in Calendars - Modifications

    Posted Tue December 14, 2021 09:56 AM

    This worked, thank you!!!




    Original Message


  • 8.  RE: Built in Calendars - Modifications

    Posted Tue December 14, 2021 05:37 AM
    Edited by System Admin Fri January 20, 2023 04:25 PM
    Hi @IAN HENDERSON, Thank you for your answer and sorry for my late response. Indeed this is the error message we get (XQE-MSR-0026). We also tried to use ".parent.parent." instead of ".parent." in the filter definition but only got other error messages. What I didn't get from your explanation: Is there a way to fix it? For us (by a different expression)? Or is it possibly a bug (folder vs. namespace)? ​

    ------------------------------
    Philipp Hornung
    ------------------------------

    Original Message


Related Content