Dynamic current Month selection

[srinu_anu2007]

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

[MFGF]

Isn't that just a filter, then? Why have a prompt if you don't want to prompt?

MF.

[srinu_anu2007]

it's not a filter because if run for next month it would pick mar like that.

[BigChris]

Do you mean you want to set the default value of the prompt to be the current month?

[MFGF]

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.

[BigChris]

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. :-\

[srinu_anu2007]

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.

[srinu_anu2007]

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

[MFGF]

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.

[srinu_anu2007]

am using DMR. is there any way like JS.

[srinu_anu2007]

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,

[cognostechie]

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.

[Francis aka khayman]

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.

[srinu_anu2007]

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,

[MFGF]

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.

[srinu_anu2007]

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,

[navissar]

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.

[srinu_anu2007]

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.

[navissar]

You probably aren't, because there's an error message. Could you paste here the MUN of a month member in your cube?

[srinu_anu2007]

below is my MUN of month

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

[Francis aka khayman]

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 :)

[navissar]

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.

[srinu_anu2007]

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

[navissar]

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 Expand

#'[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 Expand

timestampMask($current_timestamp,'yyyy')

So, our calculation now looks like this:

Code Select Expand

#'[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 Expand

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 Expand

#'[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.

[srinu_anu2007]

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,