Author Topic: Dynamic current Month selection  (Read 21431 times)

Offline srinu_anu2007

  • Community Leader
  • *****
  • Join Date: Jul 2012
  • Posts: 179
  • Forum Citizenship: +0/-1
Re: Dynamic current Month selection
« Reply #15 on: 27 Feb 2014 07:57:32 am »
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,

Offline Nimrod Avissar

  • Statesman
  • ******
  • Join Date: Feb 2011
  • Posts: 556
  • Forum Citizenship: +86/-1
  • Try again. Fail again. Fail better.
Re: Dynamic current Month selection
« Reply #16 on: 27 Feb 2014 09:37:07 am »
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.
"I'm nobody, who are you?"

Offline srinu_anu2007

  • Community Leader
  • *****
  • Join Date: Jul 2012
  • Posts: 179
  • Forum Citizenship: +0/-1
Re: Dynamic current Month selection
« Reply #17 on: 28 Feb 2014 01:47:50 am »
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.

Offline Nimrod Avissar

  • Statesman
  • ******
  • Join Date: Feb 2011
  • Posts: 556
  • Forum Citizenship: +86/-1
  • Try again. Fail again. Fail better.
Re: Dynamic current Month selection
« Reply #18 on: 01 Mar 2014 10:52:27 am »
You probably aren't, because there's an error message. Could you paste here the MUN of a month member in your cube?
"I'm nobody, who are you?"

Offline srinu_anu2007

  • Community Leader
  • *****
  • Join Date: Jul 2012
  • Posts: 179
  • Forum Citizenship: +0/-1
Re: Dynamic current Month selection
« Reply #19 on: 04 Mar 2014 12:57:51 am »
below is my MUN of month

[rp_gcrs].[rp_period].[rp_period]->:[TM].[rp_period].[rp_period].[@MEMBER].[Jan 2009]

Offline khayman

  • Statesman
  • ******
  • Join Date: Jun 2009
  • Posts: 437
  • Forum Citizenship: +25/-3
    • CognosM
Re: Dynamic current Month selection
« Reply #20 on: 04 Mar 2014 01:09:30 am »
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 :)

Offline Nimrod Avissar

  • Statesman
  • ******
  • Join Date: Feb 2011
  • Posts: 556
  • Forum Citizenship: +86/-1
  • Try again. Fail again. Fail better.
Re: Dynamic current Month selection
« Reply #21 on: 04 Mar 2014 02:45:08 am »
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.
"I'm nobody, who are you?"

Offline srinu_anu2007

  • Community Leader
  • *****
  • Join Date: Jul 2012
  • Posts: 179
  • Forum Citizenship: +0/-1
Re: Dynamic current Month selection
« Reply #22 on: 04 Mar 2014 06:59:00 am »
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
« Last Edit: 04 Mar 2014 07:00:49 am by srinu_anu2007 »

Offline Nimrod Avissar

  • Statesman
  • ******
  • Join Date: Feb 2011
  • Posts: 556
  • Forum Citizenship: +86/-1
  • Try again. Fail again. Fail better.
Re: Dynamic current Month selection
« Reply #23 on: 04 Mar 2014 03:42:07 pm »
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:
Code: [Select]
#'[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:
Code: [Select]
timestampMask($current_timestamp,'yyyy')
So, our calculation now looks like this:
Code: [Select]
#'[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:

Code: [Select]
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:

Code: [Select]
#'[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.
"I'm nobody, who are you?"

Offline srinu_anu2007

  • Community Leader
  • *****
  • Join Date: Jul 2012
  • Posts: 179
  • Forum Citizenship: +0/-1
Re: Dynamic current Month selection
« Reply #24 on: 05 Mar 2014 03:00:31 am »
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,

Offline Nimrod Avissar

  • Statesman
  • ******
  • Join Date: Feb 2011
  • Posts: 556
  • Forum Citizenship: +86/-1
  • Try again. Fail again. Fail better.
Re: Dynamic current Month selection
« Reply #25 on: 05 Mar 2014 04:06:41 am »
Just as a data item. This will be your current month data item.
"I'm nobody, who are you?"

Offline srinu_anu2007

  • Community Leader
  • *****
  • Join Date: Jul 2012
  • Posts: 179
  • Forum Citizenship: +0/-1
Re: Dynamic current Month selection
« Reply #26 on: 05 Mar 2014 04:57:47 am »
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
« Last Edit: 05 Mar 2014 05:43:58 am by srinu_anu2007 »

Offline Nimrod Avissar

  • Statesman
  • ******
  • Join Date: Feb 2011
  • Posts: 556
  • Forum Citizenship: +86/-1
  • Try again. Fail again. Fail better.
Re: Dynamic current Month selection
« Reply #27 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.
"I'm nobody, who are you?"

Offline srinu_anu2007

  • Community Leader
  • *****
  • Join Date: Jul 2012
  • Posts: 179
  • Forum Citizenship: +0/-1
Re: Dynamic current Month selection
« Reply #28 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,

Offline Nimrod Avissar

  • Statesman
  • ******
  • Join Date: Feb 2011
  • Posts: 556
  • Forum Citizenship: +86/-1
  • Try again. Fail again. Fail better.
Re: Dynamic current Month selection
« Reply #29 on: 05 Mar 2014 07:48:20 am »
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.
"I'm nobody, who are you?"

 



       
Twittear