Dynamic current Month selection

[srinu_anu2007]

PFA.

[MFGF]

Ahem. Your Gracious Muppetness, I'm afraid you mixed up the positions in a lastPeriods function...Integer first, member second.
Sp, the expression should be something like this:
lastPeriods(7,parent(#'[rp_gcrs].[rp_period].[rp_period]->:[TM].[rp_period].[rp_period].[@MEMBER].['+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')+']'#))

Oh my! Thanks Nimrod! I'm a little pushed for time, but that's no excuse for posting up misleading and syntactically wrong expressions. Thanks a million for debugging my befuddled attempts :)

Let's see if this fixes the issue :)

[MFGF]

Hi,

PFA and let me know whether you are able to see or not.

Thanks,

Sigh. As I feared you have no levels defined. I'm not around tomorrow but I will take a look on Wednesday if I get chance. Determined not to let this beat me!! :)

MF.

[navissar]

Let's recap everything really quick so we're focused. Srinu, you've been offered two expressions. One was for your month query, and one was for your chart query.
For your month query, His Royal Muppetness MFGF has offered the following expressions:
descendants([Your year level] -> ?Year?, 2)
This expression fails because you don't have levels defined in you hierarchy (Ugh!).
I suggest the following expression instead, it just might work:

Code Select Expand

lastPeriods(12,lastChild(lastChild (#prompt('Year','MUN')#)))
This will basically take your selected year (Say, 2013), and go look for its last child (Q4 2013), and then look for its last child (Dec 2013), and then bring the 12 months before that (Jan 2013-Dec 2013) Which is what we wanted. So let's try that.

The second expression, for your chart area, was a combined effort between MFGF who did all the heavy lifting, and myself. The suggested expression wounded up as:

Code Select Expand

lastPeriods(7,parent(#'[rp_gcrs].[rp_period].[rp_period]->:[TM].[rp_period].[rp_period].[@MEMBER].['+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')+']'#),7)
which gave you an error. The reason you got an error, I think, is because the order of the arguments in lastPeriods is wrong. Try this on for size:

Code Select Expand

lastPeriods(parent(#'[rp_gcrs].[rp_period].[rp_period]->:[TM].[rp_period].[rp_period].[@MEMBER].['+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')+']'#))

Let us know how it goes.

[MFGF]

Let's recap everything really quick so we're focused. Srinu, you've been offered two expressions. One was for your month query, and one was for your chart query.
For your month query, His Royal Muppetness MFGF has offered the following expressions:
descendants([Your year level] -> ?Year?, 2)
This expression fails because you don't have levels defined in you hierarchy (Ugh!).
I suggest the following expression instead, it just might work:

Code Select Expand

lastPeriods(12,lastChild(lastChild (#prompt('Year','MUN')#)))
This will basically take your selected year (Say, 2013), and go look for its last child (Q4 2013), and then look for its last child (Dec 2013), and then bring the 12 months before that (Jan 2013-Dec 2013) Which is what we wanted. So let's try that.

The second expression, for your chart area, was a combined effort between MFGF who did all the heavy lifting, and myself. The suggested expression wounded up as:

Code Select Expand

lastPeriods(7,parent(#'[rp_gcrs].[rp_period].[rp_period]->:[TM].[rp_period].[rp_period].[@MEMBER].['+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')+']'#),7)
which gave you an error. The reason you got an error, I think, is because the order of the arguments in lastPeriods is wrong. Try this on for size:

Code Select Expand

lastPeriods(parent(#'[rp_gcrs].[rp_period].[rp_period]->:[TM].[rp_period].[rp_period].[@MEMBER].['+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')+']'#))

Let us know how it goes.

Great stuff Nimrod! Thanks so much for this! I hope it gives Srinu what he needs. I also hope he comes back and updates us if/when it works :)

MF.

[srinu_anu2007]

Hi Nimrod,

Thanks for your time and help.

i think you have understand my requirement, i have given 3queris 1. month query 2. year query and chart query.

based on current year and month should filte in chart quey i.e. prev 7quaters.

if iam using the code still iam getting an error. lastPeriods(12,lastChild(lastChild (#prompt('Year','MUN')#)))

again am repeating my req. you have aleady my hieachy i.e

from month query: want to show jan to dec and year query should show current year and last 2 years(this is  new requirement) previous it was 2009 to 2017 and chart query should filter based on dynamics and if they select year as 2013 and feb should filter last 7quarters.
Note: year should always current+previous 2years and month: Current+remaining

Thanks,

[MFGF]

if iam using the code still iam getting an error. lastPeriods(12,lastChild(lastChild (#prompt('Year','MUN')#)))

You're going to have to help us out here. We can't see the error because we're not looking over your shoulder. What is the error?

MF.

[navissar]

if iam using the code still iam getting an error. lastPeriods(12,lastChild(lastChild (#prompt('Year','MUN')#)))

year query should show current year and last 2 years(this is  new requirement) previous it was 2009 to 2017 and chart query should filter based on dynamics and if they select year as 2013 and feb should filter last 7quarters.
Note: year should always current+previous 2years and month: Current+remaining

Thanks,

I think I got it. Again.
Now, let's re-write your Year query. If I remember correctly, your Year query is used in a list box prompt.
So, let's do this:
I don't know how a year member of yours is built, but I'm guessing it's something like this:
[rp_gcrs].[rp_period].[rp_period]->:[TM].[rp_period].[rp_period].[@MEMBER].[2013].
You can check for yourself what a year member's MUN is in your time dimension, and if I'm wrong post it here and I'll give you a new expression. Given that I'm not wrong, follow these steps:
1. Drag in a new data item to your year query.  Name it "Display Value". Set it up like this:

Code Select Expand


lastPeriods(3,#'[rp_gcrs].[rp_period].[rp_period]->:[TM].[rp_period].[rp_period].[@MEMBER].['+timestampMask($current_timestamp,'yyyy')+']'#)

This should give you current year + 2 years back.
2. Drag in another data item, name it "use Value", set it up as follows

Code Select Expand


roleValue ('_memberUniqueName',currentMember([rp_gcrs].[rp_period].[rp_period]))

3. Remove anything else from the query.
4. Set your year prompt to use "Use value" as the use value, "Display value" as the display value.

This should also solve your month query issue - I'm guessing your year prompt doesn't pass MUN which is why you got an error.

Let us know!

[srinu_anu2007]

Thanks for your time and help.

1. I am dragging the Year hierarchy and when it's prompting iam selecting radio button as "Root Members" so it gives me all the years only(2009,2010....2016,2017)

2. how it works lastperiod function? i have only root members of that [rp_period] hierarchy i.e. only years and it's not combination of anything like 2009 Q1, 2009 Q4.
3. roleValue ('_memberUniqueName',currentMember([rp_gcrs].[rp_period].[rp_period]))
   Internal error. The query could not be planned by the Query Service.
PFA of my prompts mock up and chart sample.

[srinu_anu2007]

Thanks for your time and help.

1. I am dragging the Year hierarchy and when it's prompting iam selecting radio button as "Root Members" so it gives me all the years only(2009,2010....2016,2017)

2. how it works lastperiod function? i have only root members of that [rp_period] hierarchy i.e. only years and it's not combination of anything like 2009 Q1, 2009 Q4.
3. roleValue ('_memberUniqueName',currentMember([rp_gcrs].[rp_period].[rp_period]))
   Internal error. The query could not be planned by the Query Service.
PFA of my prompts mock up and chart sample.

At very first time running the report it should be 2014 year and month:mar and chart:2014 Q1...like that and again if they want to chage the year and month it should be filter the previous 7quarters.

Thanks,

[srinu_anu2007]

Hi Nimrod/MF,

Around this hierarchy we need to set current month and current year and current quarter(this for in next report).PFA.

Thanks,

[navissar]

OK. Obviously I fail to pass something along. My Grandma always used to say that if you're lost in the woods, you should seek another way to go. So let's try another way altogether.
I will comment this, though: I have setup similar to what you requested up and running. I know it to work. You're getting an error because you did not set up the years query as I have suggested (I never suggested you to use "Root Members"); and it is frustrating to have an error thrown back to you when your suggestions were only partially filled. Also, I'm not sure if I didn't understand properly for the last million posts, or that you have changed your requirements as you went along; if it's the latter, then I have to say that it's not cool - sending the good people of this board to run around for solutions and after they spend their time finding them - throwing them away because your requirements have changed.

So, I'm going to try a different approach one last time.
Step 1: Create a prompt page. Put your year and month prompts in it (Leave them on your report page as well).
Change your month prompt: remove the query it is attached to and set static choices like so: "Jan","Feb","Mar" and so on. Make sure use and display values are the same. See screenshot attached.
Step 2: select your year prompt on the prompt page, in properties change the property "Name" to "yearPR". Select the month prompt on the prompt page, in properties change the property "Name" to "monthPR".
Step 3: Set up your chart query's quarters to the following expression, which assumes your year prompt returns a caption (As it had in previous posts here. If it changed, change it back or find another way; I cannot cater to every single whim).

Code Select Expand

lastPeriods(7,parent(#'[rp_gcrs].[rp_period].[rp_period]->:[TM].[rp_period].[rp_period].[@MEMBER].['+prompt('month','string')+' '+prompt('year','integer')+']'#))

Step 3: drag an HTML Item under the prompts on the prompt page and copy the following inside:

Code Select Expand

<script>
var fW = (typeof getFormWarpRequest == "function" ? getFormWarpRequest() : document.forms["formWarpRequest"]);
if ( !fW || fW == undefined)   
{
fW = ( formWarpRequest_THIS_ ? formWarpRequest_THIS_ : formWarpRequest_NS_ );
}
var yearPr=fW._oLstChoicesyearPR;
var monthPr=fW._oLstChoicesmonthPR;
var today=new Date();
var curYear=today.getUTCFullYear();
var curMonth=today.getMonth();
var textMonths=["Jan","Feb","Mar","Apr","May","Jun","Jul","Aug","Sep","Oct","Nov","Dec"];
var curMonthText=textMonths[curMonth];
function selectCurDates(prompt,value){
for(var i=0;i<prompt.length;i++){
if(prompt[i].innerText==value){
prompt[i].selected=true;
break;
}
}
}
selectCurDates(yearPr,curYear);
selectCurDates(monthPr,curMonthText);
var suffix = "";
if (fW.elements["cv.id"]){
suffix = fW.elements["cv.id"].value;
}
setTimeout(function(){eval("oCV"+suffix+".promptAction('finish');");},200);
</script>
Step 4: Optional: put all the prompt on the prompt page in a block and set visible to no. Remove all prompt buttons.

Result: when the user runs the report year and time are selected by script to this year and time and the report is loaded. Then the user can select whatever year-month they want. The quarters set in the chart query takes the year prompt value and the month prompt value to construct the selected month, and derives quarters from there.

Good luck.

[srinu_anu2007]

Hi Nimrod,

still I will not try with your new comments and your previous comments, am getting last 2years and current year using the below 1. expression and not able to get the below 2nd point

1.) lastPeriods(3,#'[rp_gcrs].[rp_period].[rp_period]->:[TM].[rp_period].[rp_period].[@MEMBER].['+timestampMask($current_timestamp,'yyyy')+']'#)

2.) roleValue ('_memberUniqueName',currentMember([rp_gcrs].[rp_period].[rp_period]))

Thanks,

[MFGF]

1. I am dragging the Year hierarchy and when it's prompting iam selecting radio button as "Root Members" so it gives me all the years only(2009,2010....2016,2017)

Hi,

I can't see anywhere in Nimrod's suggestion that involved dragging the Year hierarchy into a reporting object. Can you explain what you are trying to achieve and how it relates to the problem we are trying to help you to solve? Did the original issue get solved and this is something new??

MF.

[Lynn]

srinu_annu2007,
Despite all the excellent information provided it seems to me that you are not grasping the fundamentals. These powerhouse people are telling you what you need to know but you aren't listening and you don't appear to be researching further to understand the concepts presented. By now you should be able to extrapolate concepts and techniques to solve similar requirements.

Attached is a document that explains dimensional functions and includes examples. It is more robust than what you find in the Report Studio User Guide. It is based on Cognos 8 and I've never found an updated one for 10, but these functions are no different between the two versions anyway as far as I know.

My suggestion is that you use this, and other resources such as posts on this forum and other online resources, to learn more about the dimensional style of reporting and the use of dimensional functions.

Good Luck!

[navissar]

Hi Nimrod,

still I will not try with your new comments and your previous comments,

Well, that just about sums it up for me. Maybe someone else here will be able to help you better than I can.

[srinu_anu2007]

Hi MF/Lynn,

This is something new to get current year and last 2years in the drop down and not yet resolved my origional solution and i saw many post's but i never find my requirement related in this forum because my hierarchy was different and it's related to chart query and majer disadvantage is i dont know how to work with DMR.

Lynn, i tried my level best before posting my requirement and i was not at all comfortable to work in DMR(don't have experience) and now i got learned many things from you guys especially Nimrod. Thank you very much Nimrod for your time and help. i will let you know once my issue get resolved.

Thanks,

[MFGF]

Well, that just about sums it up for me. Maybe someone else here will be able to help you better than I can.

Hmmm. I strongly suspect Srinu meant to type:

still I will now try with your new comments and your previous comments...

I'm going to assume this :)

MF.

[MFGF]

Hi MF/Lynn,

This is something new to get current year and last 2years in the drop down and not yet resolved my origional solution and i saw many post's but i never find my requirement related in this forum because my hierarchy was different and it's related to chart query and majer disadvantage is i dont know how to work with DMR.

Lynn, i tried my level best before posting my requirement and i was not at all comfortable to work in DMR(don't have experience) and now i got learned many things from you guys especially Nimrod. Thank you very much Nimrod for your time and help. i will let you know once my issue get resolved.

Thanks,

My advice is to try Nimrod's suggested approach and post back the results. I suspect you are nearly there with this!

MF.

[navissar]

Hi Srinu,
I think I got your requirement.
When it was broken down into parts, you kept getting partial answers.
You're trying to cascade years and months, after the report had run for a default selection of this month (And 7 quarters back). 
Each of these requirements is easy on its own. Running a report for a default selection of current month - that's the expression MFGF and I gave you. Filtering months by years - that's the months expression that I gave you. But all together, they require some more stitching.
For example, Cognos is kind of weird about refreshing prompt queries. This means that if you select and submit a year, your month's selection won't change, because the prompt query will not be refreshed. So doing the cascade the way you wanted, with a filter expression on the month query, will be difficult.
My latest solution takes into consideration the whole thing. To avoid writing long, complex expressions, I took a shortcut using Javascript - I let the script do the default selection, and then all I need to do is filter based on selection, which is easy. Also, changing months the way I have to static values means you don't need a cascade (Months are funny that way: there are always twelve of them in a year). I think this should bring you where you're going. It's not the most elegant way, but I think it definitely falls under "good enough".
Try it, verbatim please, and let us know.

[navissar]

I'm going to assume this :)

MF.

I sure hope you're right :)

[srinu_anu2007]

Exactly that is my requirement. I will try to incorporate all these things in my report and let see how it comes and i will let you know.

I always to avoid using JS and hence i think we dont have an option. may be i am wrong while using your suggestions.

Thanks,

[srinu_anu2007]

Hi Nimrod/MF,

I want to get all months in my drop down(jan,feb,mar) and PFA of my hierarchy and hence iam using index in my use value instead of i want to use some thing differ and you provided the code to get last 7quarters it might get work. Please help me.

lastPeriods(12,lastChild(lastChild (#prompt('Year','MUN')#))) you provided this code is it get the same thing and after #prompt what i have to give is it prompt name(miscellanious:name) or parameter name and should i keep the same MUN?

Thanks,

[MFGF]

Hi Nimrod/MF,

I want to get all months in my drop down(jan,feb,mar) and PFA of my hierarchy and hence iam using index in my use value instead of i want to use some thing differ and you provided the code to get last 7quarters it might get work. Please help me.

lastPeriods(12,lastChild(lastChild (#prompt('Year','MUN')#))) you provided this code is it get the same thing and after #prompt what i have to give is it prompt name(miscellanious:name) or parameter name and should i keep the same MUN?

Thanks,

Sorry - I don't understand. Can you explain in simple terms so a muppet like me can understand? :)

MF.

[srinu_anu2007]

Hi,

I am using in my month query


1) dragging one data item and here is calc. am applying:levels ([package].[dim].[hierarchy],2) and result is jan 2009 ,feb 2009 etc. and names it as M

2)substring(caption([M]),1,3) and iam getting is jan.......dec and named it as Month. this is my display value and iam using use value as: Index

so insted of Index i want to use some different calc. You look at my previous attached file.

Thanks,