Dynamic current Month selection

[navissar]

Just as a data item. This will be your current month data item.

[srinu_anu2007]

then what about use value and display value, in my query am already displaying use:index and display:Month

(1) 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 iam my display value)

where do i implement your calculation in report?

use and display value shold be below code.
#'[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')+']'#

Thanks

[navissar]

Hey Srinu.
I'm not sure if it is me who does not understand you or vice versa, but I find that I'm unable to help because I fail to understand the requirement. I find myself doing guesswork trying to figure out what you need.
I propose that you make a mock-up of what you need in Excel, and upload that.
Are you using prompts? which? Is there a prompt page? Do you require that the default value OF THE PROMPT be this month? Do you require that the report will first run for this month and only then the user will be able to choose another one? Please, try to be clear and precise and tell the full story, otherwise all the work we're doing here is just a waste of time.

[srinu_anu2007]

your calculation is fine iam not getting any error but the thing is where i have to link that data item(calculated-whatever you provided) in report. you can say in your terms where you have used in your sample report. like we have to use that data item as use value and display value? we are almost in final stage. pls help. whatever i explained in previous post's that's the requirement.

Thanks,

[navissar]

I read through the thread. Like I wrote, I still cannot understand what the requirement is. I'm afraid there's not much I can do without a better understanding of what you are trying to do. Maybe someone else here got it better and can help.

[Lynn]

your calculation is fine iam not getting any error but the thing is where i have to link that data item(calculated-whatever you provided) in report. you can say in your terms where you have used in your sample report. like we have to use that data item as use value and display value? we are almost in final stage. pls help. whatever i explained in previous post's that's the requirement.

Thanks,

srinu_anu2007....There are a bunch of questions that Nimrod asked that you did not answer. I am similarly bewildered by what you are trying to achieve. I refer you again to the questions:

Hey Srinu.
I'm not sure if it is me who does not understand you or vice versa, but I find that I'm unable to help because I fail to understand the requirement. I find myself doing guesswork trying to figure out what you need.
I propose that you make a mock-up of what you need in Excel, and upload that.
Are you using prompts? which? Is there a prompt page? Do you require that the default value OF THE PROMPT be this month? Do you require that the report will first run for this month and only then the user will be able to choose another one? Please, try to be clear and precise and tell the full story, otherwise all the work we're doing here is just a waste of time.

[CognosPaul]

My god, that's brilliant Nimrod! Very well done!

[Michael75]

Nimrod, FWIW you get an applaud from me, for exceptional fortitude and perseverance in circumstances where many a good man (or good woman, Lynn :)) would have abandoned ship long ago...

[MFGF]

your calculation is fine iam not getting any error but the thing is where i have to link that data item(calculated-whatever you provided) in report. you can say in your terms where you have used in your sample report. like we have to use that data item as use value and display value? we are almost in final stage. pls help. whatever i explained in previous post's that's the requirement.

Thanks,

Ok. Let's take stock. Nimrod gave you an expression that is (in my humble opinion) pure genius. You say the expression is fine and does not return any errors. That's good.

Here's a key, important question. Where have you entered this expression? Please try your best to explain this in as much detail as you can.

For example:

Did you enter it into a query item or calculation in the query of your prompt?
or
Did you enter it into a calculation in the report object?
or
Did you enter it into a query item or calculation in the query of your report object?
or
Did you enter it into a detail filter?
or
Something else?

We're not sitting next to you looking at your report, so we can't know what you are doing. We can guess, but the repsonses we are getting back seem confusing. This is why you need to be as clear as you can.

Cheers!

MF.

[srinu_anu2007]

1.Am using value prompts for year and month and both are assigning to different queries(Year_query,MOnth_Query)
2. Am developing those prompts in my report page
3. while you running the report that should be the current month(March) and Current Year(2014) and if u run for the next month that should be Apr month and if u run for next year that should be 2015
4. Iam using chart query in my report and it is relating to the  Year and Month prompts because based on dynamic selection it should return previous 7 quarters including current quarter
5. Now if i run the report i have to select year prmpt as 2014 and month prompt as mar so the result is in my chart is Q1 2014,Q42013,Q3 2013, Q2 2013, Q1 2013,Q4 2013, Q3 2012
6. here user's dont want to select year and month it should be dynamic current month and current year
7. in my month prompt query iam using use value as:Index and display value as:Month(this Month is calculated item to get 3 letters from month like jan, feb, mar etc....) and the filter is: caption([rp_period])in?Year? this optional filter and it was in detailed filter.
8. in my chart query iam using the detail filter as: [Index] <=?Month?  and [Index] > (?Month?)-21 to get previous 7 quarters(7*3=21)
   Qtr_Year as data item iam using in x-axis in my chart and calc is:levels ([rp_gcrs].[rp_period].[rp_period],1) it will return Q1 2014 like that.

now what iam trying is as per Nimrod suggestion iam adding the new data item into my month query and pasting the calculation which is provided by you, now where i have to use in my month query to get current month.

you have seen my hierarchy in my earlier posts. Please suggest to get dynamic current month and year and based on these to return previous 7quarters.


Thanks,

[MFGF]

1.Am using value prompts for year and month and both are assigning to different queries(Year_query,MOnth_Query)
2. Am developing those prompts in my report page
3. while you running the report that should be the current month(March) and Current Year(2014) and if u run for the next month that should be Apr month and if u run for next year that should be 2015
4. Iam using chart query in my report and it is relating to the  Year and Month prompts because based on dynamic selection it should return previous 7 quarters including current quarter
5. Now if i run the report i have to select year prmpt as 2014 and month prompt as mar so the result is in my chart is Q1 2014,Q42013,Q3 2013, Q2 2013, Q1 2013,Q4 2013, Q3 2012
6. here user's dont want to select year and month it should be dynamic current month and current year
7. in my month prompt query iam using use value as:Index and display value as:Month(this Month is calculated item to get 3 letters from month like jan, feb, mar etc....) and the filter is: caption([rp_period])in?Year? this optional filter and it was in detailed filter.
8. in my chart query iam using the detail filter as: [Index] <=?Month?  and [Index] > (?Month?)-21 to get previous 7 quarters(7*3=21)
   Qtr_Year as data item iam using in x-axis in my chart and calc is:levels ([rp_gcrs].[rp_period].[rp_period],1) it will return Q1 2014 like that.

now what iam trying is as per Nimrod suggestion iam adding the new data item into my month query and pasting the calculation which is provided by you, now where i have to use in my month query to get current month.

you have seen my hierarchy in my earlier posts. Please suggest to get dynamic current month and year and based on these to return previous 7quarters.


Thanks,

Great! That's much more useful - thanks! :)

The first point is that you are using a detail filter in your month query. Don't do this - it's really not a good thing to do! Delete this filter and replace the Index item in your Month query with a calculation that has a dimensional expression in it. Your Time hierarchy has year, quarter and month levels, so to get the months for the desired year without using a detail filter you will need to use the following expression in your calculation:

descendants([Your year level] -> ?Year?, 2)

This will start at the chosen (prompted) year in the hierarchy, go down two levels from there to the Month level, and bring back all the Month members that belong to the year.

The second point (as Nimrod advised) is that you are using a detail filter in your chart query. Don't do this - it is not a good thing! Delete this filter. Has it gone? Good - that's a relief :)

Now you need to change the chart to use a dimensional expression that includes Nimrod's clever macro expression to derive the current month if the prompt is left empty. You also need the expression to bring back a wider time period spanning the prior 21 months.

Replace the expression in your Qtr_Year item in the chart to be:

lastPeriods(#'[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')+']'#,21)

This is Nimrod's macro expression inside a lastPeriods() function, asking for 21 months up to and including the current month (or the month selected in the prompt).

Does this give you what you need?

Cheers!

MF.

[navissar]

I may have given an elegant pass, but you, Sir Muppet, brought home a spectacular touch down.

[srinu_anu2007]

Have you seen my hierarchy? how it looks like, please have a look into my previous attachements.

descendants([Your year level] -> ?Year?, 2): [Your year level] means is it mun?

i dont know how to handle DMR, first time am using. if you look at my hierarchy you will come to know.

i dont understand this state: replace the Index item in your Month query with a calculation that has a dimensional expression in it: i am already keeping index data item in my month query.

Hierarchy ex: i have dimension->hierarchy->members folder->2009(member) under member Q1 2009, Q2 2009..Q4 2009

[MFGF]

Have you seen my hierarchy? how it looks like

descendants([Your year level] -> ?Year?, 2): [Your year level] means is it mun?

i dont know how to handle DMR, first time am using. if you look at my hierarchy you will come to know.

i dont understand this state: replace the Index item in your Month query with a calculation that has a dimensional expression in it: i am already keeping index data item in my month query.

Hierarchy ex: i have dimension->hierarchy->members folder->2009(member) under member Q1 2009, Q2 2009..Q4 2009

Hi,

Which view do you have set in your package tree - the member tree view or the metadata tree view? There are buttons at the top above the package - one is "View members tree" and the one next to it is "View metadata tree". Is the "View member tree" button currently selected? If so, press the "View metadata tree" button. Now when you expand your hierarchy you will see the level names displayed. When I refer to [Your year level] I mean the Year level from this view of the package - just drag it in and drop it in the appropriate place in the expression.

When I said "replace the Index item in your Month query with a calculation that has a dimensional expression in it" what I mean is this:

You currently have a query in your report that provides the Month values to be displayed in your Month prompt. This is the query I am referring to here - I think you said it is named Month_Query? Earlier you described this query as so:
"In my month prompt query I am using use value as:Index and display value as:Month(this Month is calculated item to get 3 letters from month like jan, feb, mar etc....) and the filter is: caption([rp_period])in?Year? this optional filter and it was in detailed filter."
The changes to this query we recommend you make are:
a) Delete the detail filter from the query
b) Delete the "Index" query item from the query (the one you are using as the Use Value in your prompt)
c) Add a new calculated item to this query with the expression descendants([Your year level] -> ?Year?, 2) (see above for what [Your year level] means)
d) Set this calculated item as the Use Value in your prompt.

Something else I just noticed when I re-read your first message from today is that you need the last 7 quarters in your chart rather than the last 21 months. The expression I included earlier in your chart is for the last 21 months, so I guess I'd better revise it to give you what you need?

Starting with Nimrod's macro, first we use the Parent() function to get the quarter the month belongs to. Then we use the lastPeriods() function around this to get the last 7 quarters. This makes the expression:

latPeriods(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)

Put this in as the expression for Qtr_Year in your chart query and delete the detail filter from this query.

Let us know if it works!

MF.

[MFGF]

I may have given an elegant pass, but you, Sir Muppet, brought home a spectacular touch down.

Ah, no. Too kind Nimrod, but you get the plaudits for this :)

[Francis aka khayman]

You guys ever get any work done? I could barely get my own reports running... much more analyze the problems people posts in this forum....

[srinu_anu2007]

descendants([Your year level] -> ?Year?, 2) is it correct if i use this in month query and anyway i tried but am getting an error says invalid expression. let's have a look into the attachement. [2009] is my level 0 and under that [Q1 2009] is my level 1 and nder that [Jan 2009] is my level 2 which one i have to consider and i tried with all these options and am getting above error(dragging those levels to my expression window)

[Francis aka khayman]

descendants([2009];2) will return Jan 2009, Feb 2009 ... etc

[srinu_anu2007]

Thanks khayman for your immediate response.

please find the mockup's of my report and let me explain what i need.

in the attachement by default it is current year as :2013 and month as:nov(consider it is Q4) and in my chart query i have to display 2013 Q4,2013 Q3.......previous 7quarters.

chart seems to wrong but example purpose only i have attached the chart.

current situation i need to be set year as current year always:2014 and month as always current:Mar it should return in chart 2014 Q1,2013 Q4,2013 Q3...previous 7quarters.

Thanks,

[Francis aka khayman]

srinu, much as i would love to help you on your report, fact is we can only solve a problems such as report errors. it is still up to you to put everything together to make a report that satisfies your requirements.

for example if a prompt is not working, we can help you solve why, and help create the proper formula.
if a filter is not working we can help you diagnose why and what steps to take.
but it will still be you to get these working components together to create your report.

if i can make a suggestion, break down the issues into small pieces:

1. prompt ---> get it to work first getting the correct value
2. chart --> get it to display correctly using a fixed value like Year = 2014, Month = March
3. whole report-->how to put together value from prompt to replace fix value in working chart

we can help you solve them one by one and it will be up to you to put them together to create a working report. also read in other threads... as mentioned earlier there is another thread about MUN and current month dimensional prompt. i was also able to solve my get the current year/month in prompt issue by reading those posts.

[srinu_anu2007]

Hi khayman,

i read those posts and it was not helping to get me the right reslts and also my hierarchy is something different compared to others i feel. in my report development is over expect these dynamics.

I am able to to show in chart for last 7quarters ratherthan using dynamic selection bt the requirement is without user selection it should display when you run the report.

MF,
if iam using in my chart query iam getting an errror message: 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')+']'#),7)
error:The argument at position 2 of the function 'lastPeriods' is invalid in data item 'Qtr_Yrs' of query 'Chart_query', expected one of the following types: 'member'. if you find sometime free time please have look into the attachments's.

Let's assume if the above code works in my chart query is it takes current year as 2014 and current month as Mar by default?

As per my hierarchy please give me your valuable suggesions.

Thanks for all your time and help.

[MFGF]

descendants([Your year level] -> ?Year?, 2) is it correct if i use this in month query and anyway i tried but am getting an error says invalid expression. let's have a look into the attachement. [2009] is my level 0 and under that [Q1 2009] is my level 1 and nder that [Jan 2009] is my level 2 which one i have to consider and i tried with all these options and am getting above error(dragging those levels to my expression window)

Hi Srinu,

In the image of the hierarchy you posted you are showing the Members folder expanded underneath the rp_period hierarchy. We can't see what's below this, though - the image is cut off. Can you close up the Members folder and show us an image of the hierarchy again? Do you see the same kinds of levels that I have in my sample cube below?



If so, when I refer to [Your year level] I mean the level that corresponds to your year members within the hierarchy - mine (as you can see) is called "Year". It is possible you don't have levels defined (as you are using a TM1 cube and they don't necessarily get added unless the cube developer adds them manually) but we can't see this from the image you have posted, and if you do have them it will make things easier for us. If not, we can still do this but it means we need another prompt macro. Can you take a look and either post up another image or just advise?

Cheers!

MF.

[MFGF]

MF,
if iam using in my chart query iam getting an errror message: 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')+']'#),7)
error:The argument at position 2 of the function 'lastPeriods' is invalid in data item 'Qtr_Yrs' of query 'Chart_query', expected one of the following types: 'member'. if you find sometime free time please have look into the attachments's.

Let's assume if the above code works in my chart query is it takes current year as 2014 and current month as Mar by default?

As per my hierarchy please give me your valuable suggesions.

Thanks for all your time and help.

Hmmm. It didn't work? Ok. Let's try to debug the expression:

Can you remove the "lastPeriods(" from the beginning and the ",7)" from the end of the expression and try again? Does this deliver a single quarter member or do you get another error?

Apologies if I have given you an expression that doesn't work - we just need to figure out why :)

Cheers!

MF.

[navissar]

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')+']'#))

[srinu_anu2007]

Hi,

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

Thanks,