Dynamic member selection on a column

[cognos05]

My member in a dimension look like this

AllFiscalyears as a consolidated parent member and has the below child
Fy2008
Fy2009
Fy2010
Fy2011
Fy2012
Fy2013
Fy2014
Fy2015

Fy 2008 will have child as 2008 jan, 2008 feb and so on.


My report will show the columns such as current fiscal year that comes from a seperate member and last two completed fiscal years from the above.

So I used the function  lag(lastchild([All Fiscal Years]),1) which will give me the last Fiscal Year on a data item.
So what happened was before july 2014 we added the FY 2015 for some reason..so whole of my report column changed to one vale ahead.


So I tried to use a if else condition on my data item and then would determine by how much should I lag in the lag function.

I can compare only with the caption of my last member say will give me FY2015, I should split to last 4 year value

I am trying to compare like
if(substring(caption(lastchild([All Fiscal Years])),2,4))=number2string(extract('Year',date2timestamp(Today())))
Then lag(lastchild([All Fiscal Years]),number based on calc)
Else lag(lastchild([All Fiscal Years]),number based on calc)

but this is throwing an error like invalid token  '=' cant be used. Will I be able to compare this and give a condition on then based on the result like  lag(lastchild([All Fiscal Years]),number based on calc)

Your help is appreciated.

Thanks,
Nithya

[teresa.danna@gmail.com]

When I get an = sign error it usually has something to do with the type of variables. It doesn't understand how they can be equal if they're different types of variables, IE Varchar = Integar... Try cast() on both sides.

if(substring(caption(lastchild([All Fiscal Years])),2,4))=number2string(extract('Year',date2timestamp(Today())))

[cognos05]

Teresa,

Both are string right, i use number to string and convert it on the right and use substring on the left. TO  what should i Cast, I am not sure of the syntax.
I also wanted to check , if it is possible to take the caption of a member and and compare it with current year and then assign another member based on the result in the data item expression.

Thanks,
Nithya

[teresa.danna@gmail.com]

Try

cast(substring(caption(lastchild([All Fiscal Years])),2,4)),varchar(4))

=

cast(number2string(extract('Year',date2timestamp(Today()) )),varchar(4))

It should work with the second one cast as just varchar(4), however if that doesn't work try casting the substring as well. I know it doesn't make sense really, but this was the only way I could get around a similar problem.

Hopefully this helps!

[MFGF]

Well, you may get that to work if you're lucky, but it will be a pretty inefficient way of doing things. Instead we can try to create the MUN of the desired year member with a macro. To do this we need to know exactly what the MUN of a year member looks like.

In the package tree on the left, expand the members folder of your year level, right-click on any of the Year members, and select Properties. Find the Member Unique Name property and post up the MUN for us.

Cheers!

MF.

[cognos05]

This is the Mun name for All Fiscal Years ->    [Sales].[Months].[Months]->:[TM].[Months].[Months].[@MEMBER].[All Fiscal Years]
Below this we have child member like
                                 [Sales].[Months].[Months]->:[TM].[Months].[Months].[@MEMBER].[FY2003]
                                [Sales].[Months].[Months]->:[TM].[Months].[Months].[@MEMBER].[FY2004]
I should check the current month and year and if its 2014 Jan my last Fiscal year will be FY2013 , I should choose that MUN

My expression would be to find last completed Fiscal Year member

I thought of using something like if (number2string ((extract('Year',date2timestamp(Today())))) = lastchild([All Fiscal Years])) then check for current month and if it is less than 6 then
last fiscal year = lag(lastchild([All Fiscal years],1)
or else
if(currentyear < lastchild([All Fiscal Years]) and if difference in year is 1 ,then check for month and if it is less than 6
last fiscal year =lag(lastchild([All Fiscal years],2)

Thanks,
Nithya

[MFGF]

Thanks,

So - you can create a query calculation like this to get the year member corresponding to the year of the current date:

#'[Sales].[Months].[Months]->:[TM].[Months].[Months].[@MEMBER].[FY' + timestampMask($current_timestamp,'yyyy') + ']'#

Now you have this you can use lag() or prevMember() to get the preceding year member.

What do your Month member MUNS look like?

What are the rules you need to use to get the desired year? In one place you mention always getting the previous year. In another you mention getting the previous year if the month is January. Then you mention getting the previous year if the month is less than 6...

MF.

[cognos05]

Rule is to get the last completed fiscal year.

Which would be-  if today is may 2014 , then last completed fiscal year would be FY2013
                            if tody is july 2014 - last completed FY would be FY 2014

Thanks,
Nithya

[MFGF]

Rule is to get the last completed fiscal year.

Which would be-  if today is may 2014 , then last completed fiscal year would be FY2013
                            if tody is july 2014 - last completed FY would be FY 2014

Thanks,
Nithya

So - basically the year (parent) of the month six months before this month? Would that work?

We can do that with a similar macro for the current month member, using parent(lag(xxx,6)) where xxx is the macro pointing to the current month. It's rather difficult to advise you of the syntax for the macro unless you tell us what a Month member MUN looks like though...

You could probably figure it out yourself from what you saw for the Year member. If you do, please post up the solution to help others :)

Cheers!

MF.

MF.

[cognos05]

i understand what you are telling.
Parent(lag(xxx,6)) would work perfectly.

I am using this syntax.

My month MUN looks like this
[Sales].[Months].[Months]->:[TM].[Months].[Months].[@MEMBER].[FY2003^Jul 2002]

So I tried with this expression :

#'[Sales].[Months].[Months]->:[TM].[Months].[Months].[@MEMBER].[FY'+timestampMask($current_timestamp,'yyyy') + '+^+timestampMask($current_timestamp,'mm')+' '+timestampMask($current_timestamp,'yyyy')+ ']'#

I get an error for month value , i gave'mm' but its a 3 letter mon. I am not sure of the syntax.

Thanks,
Nithya

[MFGF]

i understand what you are telling.
Parent(lag(xxx,6)) would work perfectly.

I am using this syntax.

My month MUN looks like this
[Sales].[Months].[Months]->:[TM].[Months].[Months].[@MEMBER].[FY2003^Jul 2002]

So I tried with this expression :

#'[Sales].[Months].[Months]->:[TM].[Months].[Months].[@MEMBER].[FY'+timestampMask($current_timestamp,'yyyy') + '+^+timestampMask($current_timestamp,'mm')+' '+timestampMask($current_timestamp,'yyyy')+ ']'#

I get an error for month value , i gave'mm' but its a 3 letter mon. I am not sure of the syntax.

Thanks,
Nithya

I see - alphabetic month names. In that case we need to refer back to a gem of a technique posted up originally by Nimrod Avissar here.

Try this:

#'[Sales].[Months].[Months]->:[TM].[Months].[Months].[@MEMBER].[FY' + timestampMask($current_timestamp,'yyyy') + '^' + substitute(timestampMask($current_timestamp,'mm'),'',csv(grep (timestampMask($current_timestamp,'mm'),array('01Jan','02Feb','03Mar','04Apr','05May','06Jun','07Jul','08Aug','09Sep','10Oct','11Nov','12Dec'))))+' '+timestampMask($current_timestamp,'yyyy')+']'#

Now you have the month you can use the lag and parent functions as previously indicated.

Cheers!

MF.

[cognos05]

Hi MFGF,

The expression doesnt gives any error , but I coudnt see the data item selects the meber value. I added a data item in column and added the expression but nothing comes up for column
#'[Sales].[Months].[Months]->:[TM].[Months].[Months].[@MEMBER].[FY' + timestampMask($current_timestamp,'yyyy') + '^' + substitute(timestampMask($current_timestamp,'mm'),'',csv(grep (timestampMask($current_timestamp,'mm'),array('01Jan','02Feb','03Mar','04Apr','05May','06Jun','07Jul','08Aug','09Sep','10Oct','11Nov','12Dec'))))+' '+timestampMask($current_timestamp,'yyyy')+']'#

MUN Value
[Sales].[Months].[Months]->:[TM].[Months].[Months].[@MEMBER].[FY2003^Jul 2002]

I checked each line with the MUN it seems to be the same, Except for the substitiute method, it should just substitute May or Jun.I am not sure what it substitutes.
It was working fine for the year one.
Is there a way to see the expression value. I checked the view tabular data but nothing comes up,

Thanks,
Nithya

[cognos05]

Could  the cap  "^" symbol creating an issue and not selecting the appropriate member ?

[srinu_anu2007]

the below expression will gives you last 4 years
lastPeriods(3,#'[p.name].[d.name].[H.name].[Year as level]->:[TM].[d.name].[H.name].[@MEMBER].['+timestampMask($current_timestamp,'yyyy')+']'#)

the below javascript will gives current fiscal year-1(2014-1=2013)

<script type="text/javascript">

var form = getFormWarpRequest();
var listYear = form._oLstChoicesYear_Prompt(Miscellanious Property ID Name);
var flag;
var currentYear = new Date().getFullYear();

function defaultYearSelection()
{
   for (i = 0; i < listYear.length; i++)
   {
      if( listYear.options.value == currentYear-1)
      {
           listYear.options.selected =true;
           flag=i;
           break;
      }   
   }
}

function userYearSelection()
{
   for (i = 0; i < listYear.length; i++)
   {
      if (listYear.options.selected)    
      {
          listYear.options.selected =true;
          break;
      }
   }
}


if (flag == undefined)
{
   defaultYearSelection();
   if (getFormWarpRequest().elements["cv.id"].value == "RS")
   {
      window.setTimeout('oCVRS.promptAction(\'finish\')',10);
   }
   else
   {
      window.setTimeout('oCV_NS_.promptAction(\'finish\')', 200); //this is the timeout
   }
}
else
{
   userYearSelection();   
}


</script>

Hope it will help you.

Thanks,

[cognos05]

Hi Srini,

I dont want to use javascript and i am not using any prompts. I want to achieve this through Macro which MFGF posted, for some reason my months macro is not giving me the value.

Thanks,

[MFGF]

Hi MFGF,

The expression doesnt gives any error , but I coudnt see the data item selects the meber value. I added a data item in column and added the expression but nothing comes up for column
#'[Sales].[Months].[Months]->:[TM].[Months].[Months].[@MEMBER].[FY' + timestampMask($current_timestamp,'yyyy') + '^' + substitute(timestampMask($current_timestamp,'mm'),'',csv(grep (timestampMask($current_timestamp,'mm'),array('01Jan','02Feb','03Mar','04Apr','05May','06Jun','07Jul','08Aug','09Sep','10Oct','11Nov','12Dec'))))+' '+timestampMask($current_timestamp,'yyyy')+']'#

MUN Value
[Sales].[Months].[Months]->:[TM].[Months].[Months].[@MEMBER].[FY2003^Jul 2002]

I checked each line with the MUN it seems to be the same, Except for the substitiute method, it should just substitute May or Jun.I am not sure what it substitutes.
It was working fine for the year one.
Is there a way to see the expression value. I checked the view tabular data but nothing comes up,

Thanks,
Nithya

When you define this expression you should see a resolved MUN appear in the area below the expression window. Can you check the resolved MUN matches the MUN you require?

MF.


Sent from my iPad using Tapatalk HD

[cognos05]

I dont see any resolved MUN value below the expression window, but its the same for the Year MUN, but it gives me the result.

#'[Sales].[Months].[Months]->:[TM].[Months].[Months].[@MEMBER].[FY' + timestampMask($current_timestamp,'yyyy') + '^' + substitute(timestampMask($current_timestamp,'mm'),'',csv(grep (timestampMask($current_timestamp,'mm'),array('01Jan','02Feb','03Mar','04Apr','05May','06Jun','07Jul','08Aug','09Sep','10Oct','11Nov','12Dec'))))+' '+timestampMask($current_timestamp,'yyyy')+']'#

MUN Value
[Sales].[Months].[Months]->:[TM].[Months].[Months].[@MEMBER].[FY2003^Jul 2002]

I think is the '^' operator creating an issue or some thing to do with my month mun . I even tried to hard core May instead of month calculation and still it doesnt work. But when I directly copy my MUN name of month in the data item it works. I am wondering what could be the reason.

Thanks,
Nithya

[cognos05]

Hi MFGF,
Sorry, in my previous messageI mentioned it wrongly, so i think the the issue is with substitute function

If I hard core Month value as Jul it is giving me the result in the same expression.

#'[Sales].[Months].[Months]->:[TM].[Months].[Months].[@MEMBER].[FY' + timestampMask($current_timestamp,'yyyy') + '^' + 'Mar'+' '+timestampMask($current_timestamp,'yyyy')+']'#


Thanks,
Nithya

[cognos05]

Expression used :
#'[Sales].[Months].[Months]->:[TM].[Months].[Months].[@MEMBER].[FY' + timestampMask($current_timestamp,'yyyy') + '^' + substitute(timestampMask($current_timestamp,'mm'),'',csv(grep (timestampMask($current_timestamp,'mm'),array('01Jan','02Feb','03Mar','04Apr','05May','06Jun','07Jul','08Aug','09Sep','10Oct','11Nov','12Dec'))))+' '+timestampMask($current_timestamp,'yyyy')+']'#

Error Recieved :
Member reference "[Sales].[Months].[Months]->:[TM].[Months].[Months].[@Member].[FY2014^'May' 2014]" does not exist

Actual MUN :
[Sales].[Months].[Months]->:[TM].[Months].[Months].[@MEMBER].[FY2014^May 2014]

I dont know why a single quote is added in front of the month and how to get rid of it . Actually this is causing the error and the syntax for substitute seems to be correct. This is a tm1 source cube and if i value this substitue expression seperately it gives only May. but it also doesnt allows me to store this value in another data item and then use the text here.

Any help is appreciated.

Thanks,
Nithya

[navissar]

Hi,
Thanks for directing me to this post and sorry for the delay in answer - it's a holiday where I am.
Anyway, it's too difficult to try to figure out what in this expression causes the single quotes. Easier to just kick them out. Try this on for size:

Code Select Expand

#'[Sales].[Months].[Months]->:[TM].[Months].[Months].[@MEMBER].[FY' + timestampMask($current_timestamp,'yyyy') + '^' +substitute('''','',substitute('''','', substitute(timestampMask($current_timestamp,'mm'),'',csv(grep (timestampMask($current_timestamp,'mm'),array('01Jan','02Feb','03Mar','04Apr','05May','06Jun','07Jul','08Aug','09Sep','10Oct','11Nov','12Dec'))))))+' '+timestampMask($current_timestamp,'yyyy')+']'#

[cognos05]

Thanks Nimrod and MFGF !!

It selects the value now, i dont no for wat reason the earlier expression doesnt work. You had replaced empty with '''. Thanks a lot.
I am using this calculation in my most of  my reports.
Thanks,
Nithya

[navissar]

Glad it worked for you!

[MFGF]

Hi,
Thanks for directing me to this post and sorry for the delay in answer - it's a holiday where I am.
Anyway, it's too difficult to try to figure out what in this expression causes the single quotes. Easier to just kick them out. Try this on for size:

Code Select Expand

#'[Sales].[Months].[Months]->:[TM].[Months].[Months].[@MEMBER].[FY' + timestampMask($current_timestamp,'yyyy') + '^' +substitute('''','',substitute('''','', substitute(timestampMask($current_timestamp,'mm'),'',csv(grep (timestampMask($current_timestamp,'mm'),array('01Jan','02Feb','03Mar','04Apr','05May','06Jun','07Jul','08Aug','09Sep','10Oct','11Nov','12Dec'))))))+' '+timestampMask($current_timestamp,'yyyy')+']'#

There are many, many occasions when I am so impressed by your answers I am left speechless...

The rest of this message is being telepathically communicated until I regain the use of speech. Thank you for such great contibutions! I am in complete awe...

MF.

[navissar]

Ah, Sir Muppet, you are too kind.

[Lynn]

I agree with the muppet and would further stipulate that your future holiday plans should be severely curtailed ;)