[Solved] Prompt Macro - filtering level by prompted caption with default

[JGirl]

First time using a prompt macro, and its a big challenge for me.....I have a Month level in my dimension that I want to filter by caption returning a single member which is the 'reporting month'.  I can do this using a default static text value of 201208 as per the code below:

item(filter([Cube].[Reporting Period].[Reporting Period].[Month], caption([Cube].[Reporting Period].[Reporting Period].[Month]) = #sq(prompt('YYYYMM', 'string', '201208'))#),0)

But if I have a data item called [DefaultMonth] which contains the value (with quotes) '201208', I can't get the right syntax to substitute it into the bolded part of the expression above so that it becomes the default value when no prompt value is selected? (I actually want to use a calculation from the current date in the [DefaultMonth] data item, but I'm trying to start simple).  All I'm getting is syntax errors because I don't know the right way to tell the macro to read the value held in the data item.

Who can help?

[pricter]

If I understand correctly you want to use a prompt to get a month and if nothing is selected then to use the value of the [Default Member].

So try to use the following expression

Code Select Expand

#prompt('Time','memberuniquename','[DefaultMonth]')#
and add [DefaultMonth] in your query.

[JGirl]

Not quite.  I don't want to substitute a default member (as I want to calculate the default caption value from the system date and filter the month set by caption on this).  I'm prompting on the member caption (a string value), and accordingly, my DefaultMonth data item will be a string in YYYYMM format rather than a MUN.

What I've got to now, is the following

Code Select Expand

item(filter([Cube].[Reporting Period].[Reporting Period].[Month], caption([Cube].[Reporting Period].[Reporting Period].[Month]) = #prompt('YYYYMM', 'string', '[DefaultMonth]')#),0)

My default data item is a hardcoded string with definition [DefaultMonth]

Code Select Expand

'201208'

This works how it should so far - Now what I want to do now is change the definition of [DefaultMonth] so that it is a string calcualted from the system date rather than hard coded.  So, if I change the definition of [DefaultMonth] to be

Code Select Expand

cast(extract(year,current_date) * 100 + 1,varchar(6)) the original expression no longer returns any values, even though the [DefaultMonth] expression above returns the value '201210'

Any thoughts?  I suspect Cognos hates that I'm trying to use a relational style expression to calculate the current month, but I don't really have any other options that don't involve changing the cube, and I'm trying to avoid it.

[cognostechie]

Is the [DefaultMonth] getting executed before the Prompt gets executed? and the expression you have will not give you 201210 but will give you 201201 and it is still hard coded because 1 is added as a static value.

Try this:

cast(extract(year,current_date) * 100 + extract(month,current_date) , varchar(6))

[JGirl]

Whoops....The 201201 vs 201210 was a typo - I was trying to prove that I could use some calculated components in the expression as opposed to a completely static value so I hadn't bothered with the month component as yet. 

Regardless, setting [DefaultMonth] as
cast(extract(year,current_date) * 100 + extract(month,current_date),varchar(6))

brings back '201210' , but the filter by caption expression still doesnt return anything, even though there is definitely a member with this caption in the dataset.

Setting solve orders so that [DefaultMonth] is executed first and everything else later doesn't change the outcome unfortunately :(

[pricter]

What is the Member unique name of the members?

For example in Go Sales(analysis) sample package is

[Product forecast (analysis)].[Time dimension].[Time dimension].[Month]->[all].[2004].[20041].[200401]

[JGirl]

My MUNs include the quarter as well as the month, which is why I've taken the approach of filtering the month level set by caption rather than trying to generate the MUN.

For example, my October 2012 member has
Caption =  '201210'
MUN = [Cube].[Reporting Period].[Reporting Period].[Month]->:[TM].[Reporting Period].[Reporting Period].[@MEMBER].[2012 Q4^2012-10]

[pricter]

You are right that is difficult to construct the MUN where the quarter is part of it.

In your case I would try a different approach.

I will create a data item in which I find the last month of my time dimention for example your [Default time]
and use it this data item in the expression that I wrote in my previous post.

[JGirl]

Do you think the existing approach that I'm taking (filtering by caption) could ever work if I got the syntax correct?  I guess I'm going to need to look at alternatives like the one you mentioned (although my cube months are forward populated so it might be tricky to find the 'current'), or perhaps a cube change but I've got to admit I hate an unresolved issue!

[pricter]

In my view the "problem" with your approach is that usually in cubes you do not have current day data. Usually the current day in cubes is the previous day in real life.

I manage to solve the mystery by using the following calculation

Code Select Expand


item(filter([The_Great_Outdoors_Sales_Test].[Years].[Years].[Month],
caption([The_Great_Outdoors_Sales_Test].[Years].[Years].[Month]) =
#prompt('YYYYMM', 'string',sq(timestampMask (_add_years ($current_timestamp,-7), 'YYYYMM'))
)#),0)


You do not need the _add_years function I just only use since my test data has values from 2004 to 2007

[JGirl]

AWESOME!  I've tweaked it slightly so it uses _add_months, -1 to always pick up the prior month and it works perfectly!

You've solved a week long headache for me thankyou   :)

[riteshsinghrathod]

Hi Guys ,

Just wanted to Thank You All !!  for this wonderful post :)

Have just now implemented this... and it works very well :)

Thank You !!

[Chamopak]

In my view the "problem" with your approach is that usually in cubes you do not have current day data. Usually the current day in cubes is the previous day in real life.

I manage to solve the mystery by using the following calculation

Code Select Expand


item(filter([The_Great_Outdoors_Sales_Test].[Years].[Years].[Month],
caption([The_Great_Outdoors_Sales_Test].[Years].[Years].[Month]) =
#prompt('YYYYMM', 'string',sq(timestampMask (_add_years ($current_timestamp,-7), 'YYYYMM'))
)#),0)


You do not need the _add_years function I just only use since my test data has values from 2004 to 2007

Hi this is what I was looking for.

Now I need some "extra help" to be able to apply this in some diff scenario. (Tried it but not getting it to work)

- Show Current year [WORKING]:
item(filter([CUBE].[Year].[Year].[Year], caption([CUBE].[Year].[Year].[Year]) =
#prompt('YYYY', 'string',sq(timestampMask ($current_timestamp, 'YYYY')))#),0)

- Show previous year: [NEED CODE]

- Show current Month   [NEED CODE]

- Show previous Month [NEED CODE]

- Show current Year to date for each increasing month(MMM_YTD)  [NEED CODE]

- Show Previous Year to date for each increasing month(MMM_YTD) [NEED CODE]

Can someone help me write this code ?  ;D