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

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
------------------------------

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
------------------------------

Original Message:
Sent: Mon July 13, 2020 11:13 AM
From: Jeanie Krieger
Subject: Tree Prompt Default Selections for Report using a Transformer Cube

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
------------------------------

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
------------------------------

Original Message:
Sent: Mon July 13, 2020 12:09 PM
From: Adam McIlravey
Subject: Tree Prompt Default Selections for Report using a Transformer Cube

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
------------------------------


Original Message:
Sent: Mon July 13, 2020 11:13 AM
From: Jeanie Krieger
Subject: Tree Prompt Default Selections for Report using a Transformer Cube

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

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
------------------------------

Original Message:
Sent: Mon July 13, 2020 01:24 PM
From: Albert Valdez
Subject: Tree Prompt Default Selections for Report using a Transformer Cube

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
------------------------------


Original Message:
Sent: Mon July 13, 2020 12:09 PM
From: Adam McIlravey
Subject: Tree Prompt Default Selections for Report using a Transformer Cube

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


Original Message:
Sent: Mon July 13, 2020 11:13 AM
From: Jeanie Krieger
Subject: Tree Prompt Default Selections for Report using a Transformer Cube

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