Hi,
I have month prmpt which is having Jan..Dec if i run the report dynamically it will pick the current month only.(Feb)
Any ideas.
Thanks
Isn't that just a filter, then? Why have a prompt if you don't want to prompt?
MF.
it's not a filter because if run for next month it would pick mar like that.
Do you mean you want to set the default value of the prompt to be the current month?
Hi,
This sounds exactly a filter to me. You filter would be something like:
[Your month item] = extract(month,current_date)
This assumes your month values are integers eg 2 for this month. If you run the report again next month it would resolve to 3 for March.
Why would this be a prompt? Prompts are a means of asking the user a question at runtime - it sounds like there is no question to ask here?
MF.
I just wondered if the requirement was to be able to run it for any given month, but to run it for the current month by default. :-\
Exactly. what i had done in my quaery is i took data item and applied as descendents([Total year],3) so it show jan, feb,.....Nov, Dec
and here i have to do in value prompt if users run the report without selections it should pick the current month.
Hi MF,
As u said i just added the filter and saying an error i.e.XQE-V5-0011 V5 syntax error found in expression, invalid token [Months] found after [Months]=extract([Months],current_date)
please see my previous post how i had done. please suggest
Are you using a dimensional or a relational package?
If you want a prompt, what I suggested will not work anyway. It was a solution for a filter for a relational package.
MF.
am using DMR. is there any way like JS.
Hello Experts,
Still iam facing the issue, i didn't resolve this functionality yet. Nobody done this requirement before? if yes please let me know the answer.
Once again iam repeating my issue: in my value prompt iam showing ja,Feb,...........Dec and if users run this report it should show always current month(if it is feb: Feb, If it is March:March etc..) Iam using DMR model and cube as datasource.
Thanks,
How can you use both DMR and a Cube as a data source ? Assuming you are using a DMR, this should work:
Create a filter on your Date field :
[Date] >= _first_of_month ( current_date ) and
[Date] <= _last_of_month ( current_date )
If you are using a cube, it comes with relative time categories, and MTD is one of them which can be used as a slicer.
If you ever use something like:
[Month] = extract(month, current_date)
then make sure to use the 'year' also, otherwise it will pick up data for the same month of last year too.
this has been discussed in another thread before...
1. set your time dimension so that its last member (value) is the current month. then use closingPeriod.
2. if that is not desirable, you have to create a dimension with a level which value is the current month.
Please see my hierarchy in attached file and the calc am using those are
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 valu
if am using [Month] = extract(month, current_date) and am getting an error says :XQE-V5-0011 V5 syntax error found in expre. [month]=extract([month],current_date),invalid token [month] found after the above expression
please suggest to get current month always.and iam using tm1 cube. Please let me know if thisn't make sense for you.
Thanks,
Hi,
What do the MUNs of your month members look like? The caption you are describing isn't necessarily the ID used in the MUN. You will probably end up using a macro to get hold of the current month - using the macro timestampmask function. It hinges on the value used in your MUN, really, though. We can perhaps use the macro to construct the correct MUN for the current month.
Take a look at this post and see if you can adapt this approach (from Nimrod) to resolve your issue:
http://www.cognoise.com/index.php/topic,24020.0.html
MF.
Hi,
I am not able to understand and i never use like this. could you please explain little bit more as per my requirement.
Thanks,
Right, let's try a step by step:
first, expand your time dimension and select any month member (Members are the one with the blue rectangle icon). Right click on the member and select Properties, and you will see there a property called Member Unique Name. This is usually structured like this:
[cube].[dimension].[hierarchy].[level]->:[PC].[@MEMBER].[Name]
Once you have that, copy it. Look at the structure - we're interested in the last bit - how is a month MUN constructed.
For our example, we'll suppose it is built like this:[Sales].[Time].[Time].[Month]->[Time].[2010].[201003] (That's March 2010, yyyymm).
So, you create a data item like this:
#'[Sales].[Time].[Time].[Month]->[Time].['+ timestampMask($current_timestamp,'yyyy')+'].['+timestampMask($current_timestamp,'yyyy')+timestampMask($current_timestamp,'mm')+']'#
Which will dynamically translate (Today) to [Sales].[Time].[Time].[Month]->[Time].[2014].[201402]
which is what you actually need. Things might get complicated if you have a quarter level (In which case the MUN will usually contain a level which counts for the quarter), in which case create a new hierarchy without the quarter.
Please see my hierarchy and iam not able to get if am using the below
#'[rp_gcrs].[rp_period].[rp_period]->:[TM].[rp_period].[rp_period].[@MEMBER].['+ timestampMask($current_timestamp,'yyyy')+']'#
error message:(The V5 Query could not be planned by the Query Service. The 'BaseMember' node is invalid for the OLAP Transformation Library and the Query Service Planner is currently not able to resolve it.)
i dnt know am following the correct way.
You probably aren't, because there's an error message. Could you paste here the MUN of a month member in your cube?
below is my MUN of month
[rp_gcrs].[rp_period].[rp_period]->:[TM].[rp_period].[rp_period].[@MEMBER].[Jan 2009]
timestampMask($current_timestamp,'yyyy') = 2014
you need to modify the expression to return Jan 2014 (for example).
as always google and a lot of experimentation is your best friend...
of course there are good souls here who will create the formula for you... but doing things yourself and experimenting a lot will speed up your learning process :)
Khayman, you get an applaud from me.
But still, for the people in the future and to give srinu a head start - timestampMask($current_timestamp,'mm') will return a number (3). The easiest way to translate this numeric value to a string (Mar) is via a parameter map set in the FM. And with that, you have all the pieces of the solution, and all you need to do is tie them neatly in a bow.
Hi Nimrod,
I have year prmpt and month prompt and its coming from period dimension(Hierachy previously attached) and my reqirement is if i select year and month, i want to show in chart query is previous 8quarters(including current), for this i have achieved how i hv achieved wil explain; iam taking period hierarchy and am selecting the root members so use and display value is same and able to show all years.
Month:see my feb27th post where i have used some calculated items. here am using use value as:index(1,2,3..) and display value as:Month
based on the year and month selection am able to show previous 8quarters it's not giving me a problem.
The complexicity what iam facing is i need to display year as current year always adn month as current month, based on defaults(year and month) i have to show previous 8quarters. I am using this filter in my chart query:[Index] <=?Month? and [Index] > (?Month?)-21 and am able to get previous 8quarters without default selections.
am showing qtr-yrs in my chart x-axis and calc:levels ([rp_gcrs].[rp_period].[rp_period],1)
am able to convert from month varchar to numeric but where i have to use iam not sure
Case
when [Month]= 'Jan' then '1'
when [Month]= 'Feb' then '2'
when [Month]= 'Mar' then '3'
when [Month] ='Apr' then '4'
when [Month] ='May' then '5'
when [Month] ='Jun' then '6'
when [Month] ='Jul' then '7'
when [Month] ='Aug' then '8'
when [Month] ='Sep' then '9'
when [Month] ='Oct' then '10'
when [Month] ='Nov' then '11'
else '12'
end
is it make sence?is it possible?pls help me.
Thanks,
Anu
Right. Let's take a step back. I'll try to be as clear and precise as I can, and as helpful as possible. Please bear in mind, though, that it won't be possible for me to hand over a solution, for two reason: the main one is that I don't have your data, requirements or dev time, the secondary reason is that handing over answers deprives you of the right you have to learn and grow as a developer from this forum, which is what I love about it.
So, down to business. I'll start with what I think you're doing wrong:
You're using a dimensional model. Over dimensional, the usage of detail filters and case statements is a big no-no. Detail filters over relational mess the data up - have CognosPaul give you one of his rants on the matter if you want to know why. Case statements are also not recommended.
Now, I'd like to define the key point of you requirement: you require a report to run by default for current year and month, and then for 8 quarters back. Now, 8 quarters back isn't an issue: you can use dimensional functions such as parallelPeriod, parent, lag and so on. So, what we're left with is setting up the default value to current year and current month. To do that, a method that has been offered is constructing the month data item by using a macro. That's what I'm going to focus on.
Your month members are constructed like this:
[rp_gcrs].[rp_period].[rp_period]->:[TM].[rp_period].[rp_period].[@MEMBER].[Jan 2009]
We'll use macro to create a member like that which corresponds with the current month:
So, the base structure is this:
#'[rp_gcrs].[rp_period].[rp_period]->:[TM].[rp_period].[rp_period].[@MEMBER].['+<here we will put in code for month>+' '+<here we will put in code for the year>+']'#
The outcome will be a member unique name for this month's member.
Now, the code for this year is easy:
timestampMask($current_timestamp,'yyyy')
So, our calculation now looks like this:
#'[rp_gcrs].[rp_period].[rp_period]->:[TM].[rp_period].[rp_period].[@MEMBER].['+<here we will put in code for month>+' '+timestampMask($current_timestamp,'yyyy')+']'#
The fact that the member unique name uses MMM format makes calculating the month a bit more complex, because timestampMask doesn't support MMM. So, we take the longer route - here's the calc:
substitute(timestampMask($current_timestamp,'mm'),'',csv(grep (timestampMask($current_timestamp,'mm'),array('01Jan','02Feb','03Mar','04Apr','05May','06Jun','07Jul','08Aug','09Sep','10Oct','11Nov','12Dec'))))
(If you need an explanation for that, then here we go: I create an array of months with their numbers, look for the number of the current month, and replace the number with '' so just the name remains. Personally, if I may say so myself, I think this is pure poetry).
So, the final data item should look something like that:
#'[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')+']'#
This should return current month on your data set. You should be able to figure the rest out from here.
Hope this helps.
Hi,
Where i have to add this calculation/filter? is it in detailed filter/slicer? if it is in detailed filter am getting an error(RSV-VAl-0010 Failed to load report expression), sorry to ask simple question.
Thanks,
Just as a data item. This will be your current month data item.
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
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.
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,
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.
Quote from: srinu_anu2007 on 05 Mar 2014 06:52:00 AM
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:
Quote from: Nimrod Avissar on 05 Mar 2014 06:02:32 AM
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.
My god, that's brilliant Nimrod! Very well done!
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...
Quote from: srinu_anu2007 on 05 Mar 2014 06:52:00 AM
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.
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,
Quote from: srinu_anu2007 on 05 Mar 2014 10:43:56 PM
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.
I may have given an elegant pass, but you, Sir Muppet, brought home a spectacular touch down.
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
Quote from: srinu_anu2007 on 06 Mar 2014 06:08:18 AM
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.
Quote from: Nimrod Avissar on 06 Mar 2014 04:26:14 AM
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 :)
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....
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)
descendants([2009];2) will return Jan 2009, Feb 2009 ... etc
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,
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.
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.
Quote from: srinu_anu2007 on 06 Mar 2014 11:16:55 PM
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?
(https://image.ibb.co/j3z7fF/Levels_zpsc96152e6.png)
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.
Quote from: srinu_anu2007 on 07 Mar 2014 01:00:39 AM
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.
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')+']'#))
Hi,
PFA and let me know whether you are able to see or not.
Thanks,
PFA.
Quote from: Nimrod Avissar on 07 Mar 2014 04:16:52 PM
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 :)
Quote from: srinu_anu2007 on 09 Mar 2014 11:19:57 PM
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.
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:
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:
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:
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.
Quote from: Nimrod Avissar on 11 Mar 2014 01:14:22 AM
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:
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:
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:
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.
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,
Quote from: srinu_anu2007 on 12 Mar 2014 06:26:30 AM
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.
Quote from: srinu_anu2007 on 12 Mar 2014 06:26:30 AM
if iam using the code still iam getting an error. lastPeriods(12,lastChild(lastChild (#prompt('Year','MUN')#)))
Quote from: srinu_anu2007 on 12 Mar 2014 06:26:30 AM
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:
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
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!
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.
Quote from: srinu_anu2007 on 13 Mar 2014 12:55:13 AM
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,
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,
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).
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:
<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.
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,
Quote from: srinu_anu2007 on 13 Mar 2014 12:55:13 AM
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.
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!
Quote from: srinu_anu2007 on 13 Mar 2014 05:58:42 AM
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.
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,
Quote from: Nimrod Avissar on 13 Mar 2014 07:16:53 AM
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:
Quote from: srinu_anu2007 on 13 Mar 2014 05:58:42 AM
still I will now try with your new comments and your previous comments...
I'm going to assume this :)
MF.
Quote from: srinu_anu2007 on 13 Mar 2014 07:21:15 AM
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.
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.
Quote from: MFGF on 13 Mar 2014 07:21:50 AM
I'm going to assume this :)
MF.
I sure hope you're right :)
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,
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,
Quote from: srinu_anu2007 on 14 Mar 2014 07:03:27 AM
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.
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,
Hi Srinu,
Let's put some context here. In the solution I offered, I specifically wrote it would be better to use static choices for the month prompt, so you don't need a month query.
Quote from: Nimrod Avissar on 13 Mar 2014 04:59:41 AM
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.
Now, if you tried my solution and it doesn't work, please let me know what specifically didn't work. If you haven't yet tried my solution, I suggest you do - at least it would give this a direction. If this is a new requirement for another report, I suggest you start a new thread with it, so as to avoid confusion.
Hi Nimrod,
What ever you provided the solution it's working perfectly but my user wants to set in report page itself so finally we got it and its working perfectly. JS for current year: <script type="text/javascript">
var form = getFormWarpRequest();
var list = form._oLstChoicesYear_Prompt;
var flag;
var currentYear = new Date().getFullYear();
function defaultYearSelection()
{
for (i = 0; i < list.length; i++)
{
if( list.options.value == currentYear)
{
list.options.selected =true;
flag=i;
break;
}
}
}
function userYearSelection()
{
for (i = 0; i < list.length; i++)
{
if (list.options.selected)
{
list.options.selected =true;
break;
}
}
}
if (flag == undefined)
{
defaultYearSelection();
setTimeout('oCV' + preFix + '.promptAction(\'finish\')', 10);
}
else
{
userYearSelection();
}
</script>
and for MOnth JS: <script type="text/javascript">
var form = getFormWarpRequest();
var listMonth = form._oLstChoicesMonth_Prompt;
var flag1;
var Months=new Array(12);
Months[0]="Jan";
Months[1]="Feb";
Months[2]="Mar";
Months[3]="Apr";
Months[4]="May";
Months[5]="Jun";
Months[6]="Jul";
Months[7]="Aug";
Months[8]="Sep";
Months[9]="Oct";
Months[10]="Nov";
Months[11]="Dec";
var currentMonth = new Date().getMonth();
function defaultMonthSelection()
{
for (i = 0; i < listMonth.length; i++)
{
if( listMonth.options.text == Months[currentMonth])
{
listMonth.options.selected =true;
flag1=i;
break;
}
}
}
function userMonthSelection()
{
for (i = 0; i < listMonth.length; i++)
{
if (listMonth.options.selected)
{
listMonth.options.selected =true;
break;
}
}
}
if (flag1 == undefined)
{
defaultMonthSelection();
setTimeout('oCV' + preFix + '.promptAction(\'finish\')', 10);
}
else
{
userMonthSelection();
}
</script>
Quarter JS: <script type="text/javascript">
var form = getFormWarpRequest();
var listMonth = form._oLstChoicesQuarter_Prompt;
var flag1;
var Months=new Array(12);
Months[0]="Q1";
Months[1]="Q1";
Months[2]="Q1";
Months[3]="Q2";
Months[4]="Q2";
Months[5]="Q2";
Months[6]="Q3";
Months[7]="Q3";
Months[8]="Q3";
Months[9]="Q4";
Months[10]="Q4";
Months[11]="Q4";
var currentMonth = new Date().getMonth();
function defaultMonthSelection()
{
for (i = 0; i < listMonth.length; i++)
{
if( listMonth.options.text == Months[currentMonth])
{
listMonth.options.selected =true;
flag1=i;
break;
}
}
}
function userMonthSelection()
{
for (i = 0; i < listMonth.length; i++)
{
if (listMonth.options.selected)
{
listMonth.options.selected =true;
break;
}
}
}
if (flag1 == undefined)
{
defaultMonthSelection();
setTimeout('oCV' + preFix + '.promptAction(\'finish\')', 10);
}
else
{
userMonthSelection();
}
</script>
using those JS iam getting dynamic defaults for current year(2014) and month(Mar) and Quarter(Q1)
1.for getting current year last 2 years i have used your expression.
2. for getting current month, i have used the filter in my month query is :caption([periods])=?Year_P? and this is for optional
3. for getting previous 7 quarters we have used this filter and optional:[Index] <=?Param_Month? and [Index] > (?Param_Month?)-21
Thanks for one and all and especially for Nimrod.
Maybe I saw this post too late.. It sure went too far and lot of valuable forum members made significant contributions with so much dedication and patience !
Not sure if the OP has an option to do this in the model but I always prefer to do this type of thing in the model
itself. Some time back I posted lot of relative time categories including what is required by the OP and those
work with the DMR also. That post should still be on the forum somewhere.
The fact that DMR is OLAP over relational made me think about doing it this way. Instead of reading the
data from relational format and restructuring it into OLAP and then cutting it in the report, my idea was to cut it before DMR gets that data so no performance issues.
By doing this in the model, it is also possible to drill down on those last few quarters !
Hi Nimrod Avissar,
Actually she wants, value prompt need to select current month-using default selection.
Hi Srinu,
Are you get the solution from this. If you get the solution please tell me. I am also having same problem.
Thanks & Regards,
Chandrasekar.
Quote from: Chandrasekar on 15 Jul 2014 11:41:24 AM
Hi Nimrod Avissar,
Actually she wants, value prompt need to select current month-using default selection.
1. This is a monster-thread as it is. If you need help, I suggest you open a new thread.
2. You will have to be more specific regarding your requirements in the new thread.
Hi Nimrod Avissar,
I need to select current month is a default selection in the value prompt. Using dimensional model.
MUN is [SalesReporting].[Month].[Month]->:[TM].[Month].[Month].[@MEMBER].[FY]
Thamks & Regards,
Chandrasekar
Quote from: Chandrasekar on 15 Jul 2014 11:20:18 PM
Hi Nimrod Avissar,
I need to select current month is a default selection in the value prompt. Using dimensional model.
MUN is [SalesReporting].[Month].[Month]->:[TM].[Month].[Month].[@MEMBER].[FY]
Thamks & Regards,
Chandrasekar
Hmmmm.
Nimrod suggested two things:
Quote from: Nimrod Avissar on 15 Jul 2014 03:27:31 PM
1. If you need help, I suggest you open a new thread.
and
Quote from: Nimrod Avissar on 15 Jul 2014 03:27:31 PM
2. You will have to be more specific regarding your requirements in the new thread.
You don't appear to have listened to either of these suggestions.
MF.
He actually did.... :)