[Solved] Dimensional filter using Date Prompt

[erwink]

Hi there

I'm relatively new to congas..so don't shot me  ;)

I need to create a report displaying data from startmonth to endmonth. Sound easy and is easy using relational filters. But I'm getting crazy trying to do it on dimensional filter.

My time hierarchy is about the same as in GO Sales (analysis) -I don't have the day level - so let's use this as example.

Letting Report Studio creating the prompt was straight forward. But replacing it by a Date Prompt will not work. O have always coercion error, and finally when I found out a way to use the _businesskey the processor don't want me to use extract or whatever to calculate the businessKey

following filter works
filter ([Inventory (analysis)].[Time].[Time].[Month], roleValue(_businessKey',[Inventory (analysis)].[Time].[Time].[Month])=201201)

from there i thought it should be straight forward. replace the integer 201201 with some extract from my date_prompt. Nothing worked.  Always error the xx function not supported in the context which it is used

I created a query item to calculate this integer and used this. But without success.

any idea how I can achieve to filter my data based on two prompt. Or at least like current_month to current_month+1y

Thank you
erwin

[cognos810]

Hello Erwink,
Your Month expression should be:

lastPeriods(-12,[Inventory (analysis)].[Time].[Time].[Month]->?p_Month? )
This will return you a set of 12 months in future including the one selected by the user.

lastPeriods(12,[Inventory (analysis)].[Time].[Time].[Month]->?p_Month? )
This will return you a set of 12 months in the past including the one selected by the user.


INTERSECT(
[LastN],[NextN]
)
If you want to return all months between two selected months by the user, where LastN and NextN are data items as such,
[LastN]:
lastPeriods(1000,[Complaints].[Date].[Year].[Month]->?p_End? )
[NextN]:
lastPeriods(-1000,[Complaints].[Date].[Year].[Month]->?p_Start?)

Change the 1000 depending on the TOTAL number of Months you can possibly have in your data. I used 1000 as in my data set I will never have data for more than 1000 months.

-Cognos810

[erwink]

Hi Cognos810

Thank you for your hint. last Periods an intersect are interesting functions. But unfortunately do not fix my issue

build in prompt works but is not really user friendly (dropdown of all April, than August etc)
I rather want to use the Date Prompt. But this prompt returns a date and not a member!

How do I need to modify the output of the Date Prompt to be able to use it in your examples.

I tried using MUN
Hardcoded like lastPeriods(-12,[Dimensions].[Time].[TimeH].[Month]->[all].[2014].[20142].[201406]) works
but if I put [all].[2014].[20142].[201406] in a separate Data Item and try as follow lastPeriods(-12,[Dimensions].[Time].[TimeH].[Month]->[promptMonth] it does'nt work

I tried using _businessKey
filter([Dimensions].[Time].[TimeH].[Month], roleValue('_businessKey', [Dimensions].[Time].[TimeH].[Month]) >=201504) works

but
filter([Dimensions].[Time].[TimeH].[Month], roleValue('_businessKey', [Dimensions].[Time].[TimeH].[Month]) >=substring(#sb(join('',split('-',prompt('ToDate','Date'))))#,0,4)) does not. Substr is unknown in macros :-)

I created a Data Item with substring(#sq(join('',split('-',prompt('ToDate','Date'))))#,1,6)  and used it as
filter([Dimensions].[Time].[TimeH].[Month], roleValue('_businessKey', [Dimensions].[Time].[TimeH].[Month]) >=[promptMonth])

but it didn't work Invalid coercion from 'memberSet' to 'value' for '[promptMonth]' in 'filter(members([__ns_0].[Time].[TimeH].[Month]),[__ns_0].[Time].[TimeH].[Month].[Monthkey] >= [promptMonth])'.

Or, would you use another way to prompt for dates?

[MFGF]

Hi Cognos810

Thank you for your hint. last Periods an intersect are interesting functions. But unfortunately do not fix my issue

build in prompt works but is not really user friendly (dropdown of all April, than August etc)
I rather want to use the Date Prompt. But this prompt returns a date and not a member!

How do I need to modify the output of the Date Prompt to be able to use it in your examples.

I tried using MUN
Hardcoded like lastPeriods(-12,[Dimensions].[Time].[TimeH].[Month]->[all].[2014].[20142].[201406]) works
but if I put [all].[2014].[20142].[201406] in a separate Data Item and try as follow lastPeriods(-12,[Dimensions].[Time].[TimeH].[Month]->[promptMonth] it does'nt work

I tried using _businessKey
filter([Dimensions].[Time].[TimeH].[Month], roleValue('_businessKey', [Dimensions].[Time].[TimeH].[Month]) >=201504) works

but
filter([Dimensions].[Time].[TimeH].[Month], roleValue('_businessKey', [Dimensions].[Time].[TimeH].[Month]) >=substring(#sb(join('',split('-',prompt('ToDate','Date'))))#,0,4)) does not. Substr is unknown in macros :-)

I created a Data Item with substring(#sq(join('',split('-',prompt('ToDate','Date'))))#,1,6)  and used it as
filter([Dimensions].[Time].[TimeH].[Month], roleValue('_businessKey', [Dimensions].[Time].[TimeH].[Month]) >=[promptMonth])

but it didn't work Invalid coercion from 'memberSet' to 'value' for '[promptMonth]' in 'filter(members([__ns_0].[Time].[TimeH].[Month]),[__ns_0].[Time].[TimeH].[Month].[Monthkey] >= [promptMonth])'.

Or, would you use another way to prompt for dates?

Hi,

The question about using date prompts with dimensional packages came up a little while ago. Here's the post:

http://www.cognoise.com/index.php/topic,26901

Date prompts are designed to work with relational date fields, not with members in a dimension. You're going to need to use tree prompts or value prompts with a dimensional source.

Cheers!

MF.

[CognosPaul]

You can actually use date prompts, but you need to transform the values to what the cube is expecting. To begin, you must use an attribute of the member. The business key is a bad practice, as there is no guarantee that the key will be related in any way to a date, if you're using a surrogate key for instance. How you do it is also dependent on the technology you're using. Some of them, like PowerCubes, only support text attributes, and are difficult to work with.

If you have a date attribute, you should be able to do something like:

Code Select Expand

filter([Cube].[Dim].[Hier].[Level], [Cube].[Dim].[Hier].[Level].[Date] >= #sq(prompt('startDate','date'))#)

Note that the sq function will wrap the date with single quotes, which some databases expect and some don't.

If you only have a numeric attribute, then you'll need to use macro functions to transform it to the correct format. There are a few ways. Assuming you only need yyyymmdd you could do something like:

Code Select Expand


#join('',split('-',prompt('startDate','date')))#


If you want to do any date manipulations, you'll need to convert it to a timestamp, and then format that with timestampMask.

Code Select Expand

#
timestampMask(
  _add_months(
  prompt(
     'startDate'
    ,'date'
    ,$current_timestamp
    ,''
    ,''
    ,'T00:00:00.00Z'
  )
  ,-1)
,'yyyymmdd')
#

This will only work correctly with your standard run-of-the-mill single select date prompts. Range prompts won't work with this at all.

[erwink]

Hi Paul
Thank you for your hints. I saw already most of them on you personal blog.

I was near one of your solution some days ago

filter([Dimensions].[Time].[TimeH].[Month], [Dimensions].[Time].[TimeH].[Month].[MonthId] >= YYYYMM)

i tried for hours to build this YYYYMM using substring, extract, etc without success ==> 'function' is unknown in macros
then i tried to calculate YYYYMM in a separate query calculation and  test against this ...[MonthId] >= [myMonth]) but this ended with Invalid coercion from 'memberSet' to 'value'...

Is there another function to trunc the YYYYMMDD to YYYYMM
Is it possible to calculate the value outside ( Query Calculation, Data Item, Variable) and use it in the query expression

Thank you
erwin

[CognosPaul]

Remember that most of the functions you're referencing are unknown in both the MDX standard and macros. Use the functions in the list.

In this case, since you're using a prompt, the timestampMask function is appropriate. Look at the specifications for the function and try modifying my example at the bottom of my previous reply.

[erwink]

Hi Paul .et.all

I finally found a post helping me (soory lost the page and can not say thx since I do not find the post anymore)

for GOsales and for my time structure you need to have something like that: [Dimensions].[Time].[TimeH].[Month]->[all].[2014].[20142].[201406]

to achieve that the query shall be as follow to filter on current month

#'[Dimensions].[Time].[TimeH].[Month]->[all].['+
timestampMask ($current_timestamp,'yyyy')+'].['+
timestampMask ($current_timestamp,'yyyy')+substitute('''','',substitute('''-','',substitute(timestampMask ($current_timestamp,'mm'),'',csv(grep(timestampMask ($current_timestamp,'mm'),array('01-1','02-1','03-1','04-2','05-2','06-2','07-3','08-3','09-3','10-4','11-4','12-4'))))))
+'].['+timestampMask ($current_timestamp,'yyyymm')+']'#

Is that not crazy!

[CognosPaul]

I recognize that code anywhere. You got that from one of Nimrod's posts.

In this case you're manually building the memberuniquename. What I was suggesting was to use a combination of timestampMask and the filter function. If you're on DMR use the solution you posted.

[erwink]

Hi Paul

I'll definitively try your solution which is simpler. I was looking for documentation about timestampMask and found this incredible thing. Still looking for some paper about this function. If you've something would be great.

Actually I need to transform what you suggested #join('',split('-',prompt('startDate','date')))# e.g 21050619 to 210506

Thank you for all
erwin

[erwink]

Thank you Paul

I tried like you proposed

filter([Dimensions].[Time].[TimeH].[Month],
[Dimensions].[Time].[TimeH].[Month].[MonthId] >= #timestampMask(prompt(     'startDate'    ,'date'    ,$current_timestamp    ,''   ,''    ,'T00:00:00.00Z'  ),'YYYYMM')# and  [Dimensions].[Time].[TimeH].[Month].[MonthId] < #timestampMask( _add_years (prompt(     'startDate'    ,'date'    ,$current_timestamp    ,''   ,''    ,'T00:00:00.00Z'  ),1), 'YYYYMM')#)

It works fine and is a lot easier to maintain and read :-)

erwin