Automaticly fill Current year & Previous YTD Prompt (TM1 & CA 11.0.11)

[Chamopak]

Hi,
I am using a TM1 db as backend and Cognos 11.0.11 as Reporting tool.

I am trying to automate a report that has to get the following automatically:
IF Year Prompt is empty:
- IF January then Previous year else Current year

IF YTD Prompt is empty:
- IF current date is <16 of current month then Previous YTD else Current YTD

I know there are a lot of Gurus out there that can figure this out  ;D

[Andrei I]

If you need to run this report as a batch report (without user interaction) then use prompt macro and define its default value using dimensional functions ( e.g. fiter )
If you need to specify default values for the prompt in interactive report then you have to use Prompt API and/or Javascript.

[Chamopak]

If you need to run this report as a batch report (without user interaction) then use prompt macro and define its default value using dimensional functions ( e.g. fiter )
If you need to specify default values for the prompt in interactive report then you have to use Prompt API and/or Javascript.

Can you help me out with like a screenshot how I can make a prompt Marco?

I am breaking my head and maybe I got tunnel vision by trying without seeing the solution.

[Andrei I]

If you need to run this report as a batch report (without user interaction) then use prompt macro and define its default value using dimensional functions ( e.g. fiter )
If you need to specify default values for the prompt in interactive report then you have to use Prompt API and/or Javascript.

Which option would you like to implement?

[Chamopak]

Which option would you like to implement?

without user interaction please :-)

[Andrei I]

Assuming you have data items in your query as follows:
[Current Year]
[Previous Year]
[Month Name]

[Selected Year] is a calculation based on the macro
then
[Selected Year]:= #prompt('par_Year','MUN',' item( filter( [Current Year],  [Month Name]='January'), [Previous Year]), 0 )')#

Essentially you have to come up with an MDX expression for your default Prompt Value.

[Chamopak]

Assuming you have data items in your query as follows:
[Current Year]
[Previous Year]
[Month Name]

[Selected Year] is a calculation based on the macro
then
[Selected Year]:= #prompt('par_Year','MUN',' item( filter( [Current Year],  [Month Name]='January'), [Previous Year]), 0 )')#

Essentially you have to come up with an MDX expression for your default Prompt Value.

Thank you, I will need to work on that then.  and yes I have my
[Current Year]
[Previous Year]
[Current Month]
[Previous Month]

[Chamopak]

Thank you, I will need to work on that then.  and yes I have my
[Current Year]
[Previous Year]
[Current Month]
[Previous Month]

GOT A PIECE TO WORK THANKS TO AN OTHER POST
(https://www.cognoise.com/index.php/topic,36344.0.html)
Now I need some "extra help" to be able to apply this in some diff scenario. (Tried it but not getting it to work)

- Show Current year [WORKING]:
item(filter([CUBE].[Year].[Year].[Year], caption([CUBE].[Year].[Year].[Year]) =
#prompt('YYYY', 'string',sq(timestampMask ($current_timestamp, 'YYYY')))#),0)

- Show previous year: [NEED CODE]

- Show current Month   [NEED CODE]

- Show previous Month [NEED CODE]

- Show current Year to date for each increasing month(MMM_YTD)  [NEED CODE]

- Show Previous Year to date for each increasing month(MMM_YTD) [NEED CODE]

Can someone help me write this code ?  ;D

[MFGF]

GOT A PIECE TO WORK THANKS TO AN OTHER POST
(https://www.cognoise.com/index.php/topic,36344.0.html)
Now I need some "extra help" to be able to apply this in some diff scenario. (Tried it but not getting it to work)

- Show Current year [WORKING]:
item(filter([CUBE].[Year].[Year].[Year], caption([CUBE].[Year].[Year].[Year]) =
#prompt('YYYY', 'string',sq(timestampMask ($current_timestamp, 'YYYY')))#),0)

- Show previous year: [NEED CODE]

- Show current Month   [NEED CODE]

- Show previous Month [NEED CODE]

- Show current Year to date for each increasing month(MMM_YTD)  [NEED CODE]

- Show Previous Year to date for each increasing month(MMM_YTD) [NEED CODE]

Can someone help me write this code ?  ;D

Hi,

Before you continue down this route, it's worth asking about the cube you're using and what data it contains. For example:
1. Do you have a specific Date dimension with (eg) Year and Month levels in a hierarchy, or are Year and Month separate dimensions?
2. When you look at the Year members, is the cube populated with future years, or is the current year the last member? What about Month members - is the current month the lat member or are there future months in there?
3. Is there an All Years member at the top of the hierarchy?

The answers above may help in addressing the questions you have about how to achieve specific requests, and might mean that your current year expression might also be modified to make it simpler and more efficient. Eg it might be possible to use closingPeriod() perhaps, or lastChild()

A couple of the others could probably be done using prevMember() or lag() functions.

Cheers!

MF.

[Chamopak]

Hi,

Before you continue down this route, it's worth asking about the cube you're using and what data it contains. For example:
1. Do you have a specific Date dimension with (eg) Year and Month levels in a hierarchy, or are Year and Month separate dimensions?
2. When you look at the Year members, is the cube populated with future years, or is the current year the last member? What about Month members - is the current month the lat member or are there future months in there?
3. Is there an All Years member at the top of the hierarchy?

The answers above may help in addressing the questions you have about how to achieve specific requests, and might mean that your current year expression might also be modified to make it simpler and more efficient. Eg it might be possible to use closingPeriod() perhaps, or lastChild()

A couple of the others could probably be done using prevMember() or lag() functions.

Cheers!

MF.

Hey MF,

Well the cubes are not the best build. So using prevMember does not work completely (tried that, but will have a problem during year switch for the Months and the YTD members

and in Jan it should automatically look at Dec of the Year Before.
Year:
[CUBE].[Year].[Year].[Year]                                                                   example: 2015 , 2016, 2017, 2018, 2019,2020, 2021 (even though it will be empty till the year has arrived)  etc.
[CUBE].[Month].[Month].[Quarter] (the YTD are in this part)                    example: Jan YTD, Feb YTD, Mar YTD, etc.
[CUBE].[Month].[Month].[Month]                                                           example: Jan, Feb, Mar, Apr, etc.

if you checkout the attachment, in the P_SelectedYear is the current code and it works.
I need to have one that works for the P_Selectect_YTD (and if can for the future for the months).
the PrevYear Currently has PrevMember function.

And the main complexity it must be able to do is when its certain date of the month or before it must be able to do the combination Previous month, and when its later than that day it must do Current month.

Yes a whole bible  8)

[Andrei I]

Can you show an example of your cube Date dimension  ?
And an example of an output you want to get?

[Chamopak]

Can you show an example of your cube Date dimension  ?
And an example of an output you want to get?

Hi ,
I have attached a file with screenshots and this text:


AFTER THE 25TH DAY OF THE MONTH
P_SelectedYear:    Current Year (automaticly)
P_SelectedYTD:      Current YTD
PrevYear:               Previous Year (works with PrevMember)


BEFORE THE 25TH DAY OF THE MONTH
P_SelectedYear:    IF Jan, then Previous Year, else Current Year (automatically)
   
P_SelectedYTD:      Previous YTD
PrevYear:               Previous Year of P_SelectedYear (works with PrevMember)

[Andrei I]

If I get it right you have two Date dimensions: Year and Month.

Please confirm my assumptions:
1) The Month dimension has everything you need. You do not even need to use the Year dimension.
2) You always run report as of Current Date. So you need to analyze current_date to figure out 25th day.

Then you should come up with MDX expressions which select correct members from the
Month dimension according to your calendar conditions.

[Chamopak]

If I get it right you have two Date dimensions: Year and Month.

Please confirm my assumptions:
1) The Month dimension has everything you need. You do not even need to use the Year dimension.
2) You always run report as of Current Date. So you need to analyze current_date to figure out 25th day.

Then you should come up with MDX expressions which select correct members from the
Month dimension according to your calendar conditions.

1) The Month dimension has everything you need. You do not even need to use the Year dimension = unfortunately no, It would have been great. The Year member in this dimension has no children (as would have been logical) check out new screenshot


2) You always run report as of Current Date. So you need to analyze current_date to figure out 25th day. = Report will be run on the 25th and on the 10th of each month automatically  and on the 25th will see data of the current month and on the 10th will see previous month because then all bookings must be completed. 

[Andrei I]

I got it now.
Try this:

Add to your Query data items as follows:
// calculate dates index
// current day index
[DayID]:= # timestampMask ( $current_timestamp, 'dd') #
// current month index
[MonthID]:= # timestampMask ( $current_timestamp, 'mm') #
// current year index
[YearID]:= # timestampMask ( $current_timestamp, 'yyyy') #


//selected year index
[Selected Year ID]:= if ( [MonthID] = 1 and [DayID] <=25 ) then ([YearID] -1) else ( [YearID])
//prior year index
[Prior Year ID]:= [Selected Year ID]-1

//selected month index
[Selected Month ID]:= if ( [MonthID] = 1 and [DayID] <=25 ) then (12) else ([MonthID])
[Prior Month ID]:= if ( [Selected Month ID]= 1) then (12) else ([Selected Month ID]-1)

//calculate Member Levels
[Year Level]:=[CUBE].[Year].[Year].[Year]
[Quarter Level]:=[CUBE].[Month].[Month].[Quarter]
[Month Level]:=[CUBE].[Month].[Month].[Month]

//calculate members

//possibly need to cast to string
[Selected Year]:=filter( [Year Level], caption([Year Level])= [Selected Year ID] )

[Prior Year]:= prevMember( [Selected Year])

// Zero based
[Selected Month] := item([Month Level], [Selected Month ID]-1)
[Prior Month]    := item([Month Level], [Prior Month ID]-1)


[Selected YTD]:= filter ([Quarter Level], caption([Quarter Level])=caption([Selected Month])+' YTD' )
[Prior YTD]   := filter ([Quarter Level], caption([Quarter Level])=caption([Prior Month]   )+' YTD' )

Depending on your layout you migth need to use a Tuple to calculate an intersection of your dimensions

Please let me know if it helps

[Chamopak]

I got it now.
Try this:

Add to your Query data items as follows:
// calculate dates index
// current day index
[DayID]:= # timestampMask ( $current_timestamp, 'dd') #
// current month index
[MonthID]:= # timestampMask ( $current_timestamp, 'mm') #
// current year index
[YearID]:= # timestampMask ( $current_timestamp, 'yyyy') #


//selected year index
[Selected Year ID]:= if ( [MonthID] = 1 and [DayID] <=25 ) then ([YearID] -1) else ( [YearID])
//prior year index
[Prior Year ID]:= [Selected Year ID]-1

//selected month index
[Selected Month ID]:= if ( [MonthID] = 1 and [DayID] <=25 ) then (12) else ([MonthID])
[Prior Month ID]:= if ( [Selected Month ID]= 1) then (12) else ([Selected Month ID]-1)

//calculate Member Levels
[Year Level]:=[CUBE].[Year].[Year].[Year]
[Quarter Level]:=[CUBE].[Month].[Month].[Quarter]
[Month Level]:=[CUBE].[Month].[Month].[Month]

//calculate members

//possibly need to cast to string
[Selected Year]:=filter( [Year Level], caption([Year Level])= [Selected Year ID] )

[Prior Year]:= prevMember( [Selected Year])

// Zero based
[Selected Month] := item([Month Level], [Selected Month ID]-1)
[Prior Month]    := item([Month Level], [Prior Month ID]-1)


[Selected YTD]:= filter ([Quarter Level], caption([Quarter Level])=caption([Selected Month])+' YTD' )
[Prior YTD]   := filter ([Quarter Level], caption([Quarter Level])=caption([Prior Month]   )+' YTD' )

Depending on your layout you migth need to use a Tuple to calculate an intersection of your dimensions

Please let me know if it helps

you are right about the cast, I created all the Data Items and when I added Selected Year to my crosstab I got an error  (screenshot attached)

[Andrei I]

//possibly need to cast to string
[Selected Year]:=filter( [Year Level], caption([Year Level])= [Selected Year ID] )

Did you try this:
[Selected Year]:=filter( [Year Level], caption([Year Level])= cast( [Selected Year ID] ,char(4) )

[Chamopak]

Did you try this:
[Selected Year]:=filter( [Year Level], caption([Year Level])= cast( [Selected Year ID] ,char(4) )

I just did that and I got an error, when I was modifying my message you already posted  8)



so I casted it, and now I am getting the following Error:

XQE-GEN-0026 Not supported OLAP/Relational/OLAP data model nesting in the expression: 'filter([CUBE].[Year].[Year].[Year]; rolevalue('_memberCaption'; [CUBE].[Year].[Year].[Year]) = cast(if((01 = 1 AND 27 <= 25); (2020-1); 2020); varchar))'.
CAF-WRN-2082 An error has occurred. Please contact your administrator. The complete error has been logged by CAF with SecureErrorID:2020-01-27-13:43:14.556-#174

[Andrei I]

Unfortunately I can't test anything with an OLAP package at the moment.
Let's avoid mixing Relational expressions and Dimensional.
Use dimensional only.

Try this (you might need to modify it)

[Selected Year]:=
item(
   union (
     filter( [Year Level], caption([Year Level])= #sq(timestampMask ( $current_timestamp, 'yyyy') -1 )#
       and #timestampMask ( $current_timestamp, 'mm') # = 1 and timestampMask ( $current_timestamp, 'dd')<=25
     ),
     filter( [Year Level], caption([Year Level])= #sq(timestampMask ( $current_timestamp, 'yyyy')#  )
   )
,0)

[Chamopak]

Unfortunately I can't test anything with an OLAP package at the moment.
Let's avoid mixing Relational expressions and Dimensional.
Use dimensional only.

Try this (you might need to modify it)

[Selected Year]:=
item(
   union (
     filter( [Year Level], caption([Year Level])= #sq(timestampMask ( $current_timestamp, 'yyyy') -1 )#
       and #timestampMask ( $current_timestamp, 'mm') # = 1 and timestampMask ( $current_timestamp, 'dd')<=25
     ),
     filter( [Year Level], caption([Year Level])= #sq(timestampMask ( $current_timestamp, 'yyyy')#  )
   )
,0)

Thank you for your patience and time. I am getting an Error saying there is a COMMA or Bracket missing.
looking to see if I can figure it out where.

[Andrei I]

Actually it was missing brackets.
Try updated expression:

[Selected Year]:=
item(
   union (
     filter( [Year Level], caption([Year Level])= #sq(timestampMask ( $current_timestamp, 'yyyy') -1 )#
       and #timestampMask ( $current_timestamp, 'mm') # = 1 and #timestampMask ( $current_timestamp, 'dd')# <=25
     ),
     filter( [Year Level], caption([Year Level])= #sq(timestampMask ( $current_timestamp, 'yyyy')#  ) )
   )
,0)

[Andrei I]

Or even better
[Selected Year]:=
item(
   union (
     filter( [Year Level], caption([Year Level])= #sq(timestampMask ( $current_timestamp, 'yyyy') -1 )#
       and #timestampMask ( $current_timestamp, 'mmdd') # <= 125
     ),
     filter( [Year Level], caption([Year Level])= #sq(timestampMask ( $current_timestamp, 'yyyy')#  ) )
   )
,0)

[Chamopak]

Actually it was missing brackets.
Try updated expression:

[Selected Year]:=
item(
   union (
     filter( [Year Level], caption([Year Level])= #sq(timestampMask ( $current_timestamp, 'yyyy') -1 )#
       and #timestampMask ( $current_timestamp, 'mm') # = 1 and #timestampMask ( $current_timestamp, 'dd')# <=25
     ),
     filter( [Year Level], caption([Year Level])= #sq(timestampMask ( $current_timestamp, 'yyyy')#  ) )
   )
,0)

Same Error. Ca it be that is missing something like the ,0 or something in the first filter? just trying to see what is different.

screenshot attached

[Andrei I]

item(
   union (
     filter( [Year Level], caption([Year Level])= #sq(timestampMask ( $current_timestamp, 'yyyy') -1 )#
       and #timestampMask ( $current_timestamp, 'mm') # = 1 and #timestampMask ( $current_timestamp, 'dd')# <=25
     ),
     filter( [Year Level], caption([Year Level])= #sq(timestampMask ( $current_timestamp, 'yyyy') )#   )
   )
,0)

[Chamopak]

THANK YOU.
Did find one more think though, but team work (your super smart, and me trying to be smart brains together

item(
   union (
     filter( [Year Level], caption([Year Level])= #sq(timestampMask ( $current_timestamp, 'yyyy') ,  -1 )#
       and #timestampMask ( $current_timestamp, 'mm') # = 1 and #timestampMask ( $current_timestamp, 'dd')# <=25
     ),
     filter( [Year Level], caption([Year Level])= #sq(timestampMask ( $current_timestamp, 'yyyy') )#   )
   )
,0)


and I go data for 2020

Now time for next step.   8)