Cognos Analytics

 View Only
  • 1.  Tree Prompt Default Selections for Report using a Transformer Cube

    Posted Fri July 10, 2020 02:42 PM
    I have a Date Hierarchy from a Transformer Cube that provides date members Year, Quarter, Month, and Week for the last 3 years (in this case 2018, 2019, 2020).
    I want to set the default selection for a Tree Prompt to the current year (2020) and then allow the business user to select any member as they see fit.

    I can set the default selection to the value [Cube Name].[Date Hierarchy].[Date Hierarchy].[Year]->:[PC].[@MEMBER].[2020] and this works for now.
    However, when we role into 2021 this will not be the most current year, and eventually, this will not work at all when [2020] is not a valid member.

    How can I set the default selection to in effect be the last child in the Year level?

    Any help will be gratefully appreciated.
    Thanks in advance,

    ------------------------------
    Adam McIlravey
    ------------------------------

    #CognosAnalyticswithWatson


  • 2.  RE: Tree Prompt Default Selections for Report using a Transformer Cube

    Posted Mon July 13, 2020 11:13 AM
    We have a relational data base and so this answer may not work for cubes.  

    I compare the current year to the prior year in many of our reports.  Create a data item that extracts the year from (current_date).  This will always be the current year.  We work on a fiscal year that begins July 1st, so I have to do a bit of extra coding for that.  This example shows the Oracle Sysdate function, but you can use the current_date instead.

    IF ([Month] > 6 ) THEN
    (extract ( year, {sysdate}))
    ELSE
    (extract ( year, {sysdate})+1)

    You didn't ask but to keep only current selections, I'll often filter the results to only records greater than (current year - 3).  Then year to year, the report always displays the last three years of data.

    ------------------------------
    Jeanie Krieger
    Business Analyst
    California State University, Sacramento
    Sacramento CA
    ------------------------------



  • 3.  RE: Tree Prompt Default Selections for Report using a Transformer Cube

    Posted Mon July 13, 2020 12:10 PM
    Thanks @Jeanie Krieger

    However, when using a Transformer Cube, it seems that I am only able to select "Packaged Items" as default selections.
    Only the members in the levels Year, Quarter, Month, etc... are able to be selected.

    For example:
    2020
       2020 Q1
          2020 Jan
               1/1/2020
               1/15/2020
          2020 Feb
    etc..

    It doesn't appear that there isn't any way to edit the default values (we are using CA 11.0.13).
    I was thinking that I'm either going to have to force a prompt screen without defaults or change the values of the Year members to be TY, LY, and 2Y or something else that is consistent - But, I just don't like the way this is presented to the business.
    Is there a Java Script or other methodology that might work?

    Thanks in advance,

    ------------------------------
    Adam McIlravey
    ------------------------------



  • 4.  RE: Tree Prompt Default Selections for Report using a Transformer Cube

    Posted Mon July 13, 2020 01:25 PM
    Hi Adam, are you using relative time categories in your Transformer model?  You should be able to refer to a relative time member such as [Current Year] which will dynamically update as the year changes.
    Hope this helps.

    ------------------------------
    Albert Valdez
    albert@intelalytics.com
    ------------------------------



  • 5.  RE: Tree Prompt Default Selections for Report using a Transformer Cube

    Posted Mon July 13, 2020 01:34 PM

    Thanks @Albert Valdez

    That is exactly what I've now done.
    Originally, my relative time members were the values of the years (2018, 2019, and 2020).
    However, I have now changed them to be Current Year, Last Year, and 2 Years Ago.

    This will always work for me now, but, sadly, it is not as "pretty" as having the actual years be the member​s.

    Thanks again,



    ------------------------------
    Adam McIlravey
    ------------------------------