[SOLVED] Current Date for prompt or default value - DMR

[JamesR]

Hi All,

I've been studying posts for a couple days now, but still can't quite get my head around how to achieve this, so am looking for a push in the right direction if possible! I am using DMR data source.

I have a crosstab with the columns DAY, MTD and YTD which forms part of a report which is to be scheduled, so I need the option to default to the current date, but also the ability to run for another date chosen by the user.


YTD and MTD are driven from DAY:

YTD expression is: aggregate(currentMeasure within set periodsToDate([Presentation].[Target Start Date Financial Calendar (A)].[Target Start Financial Year].[Target Start Financial Year],[DAY]))

MTD expression is: aggregate(currentMeasure within set periodsToDate([Presentation].[Target Start Date Financial Calendar (A)].[Target Start Financial Year].[Target Start Financial Year Period],[DAY]))


DAY comes from a prompt based on my "Target Start Date Financial Calendar (A)" dimension which works perfectly when the user selects a date manually using a value prompt at the moment, my issue is I'm not sure how to properly structure the "current date" part.

Couple things I've been trying:

1. Create a static choice for the prompt by manually creating the MUN for the current date. I have a dimension "Relative Date" which I'm trying to use to create this as it follows the same structure (just not the same level unique name).

'[Presentation].[Target Start Date Financial Calendar (A)].[Target Start Financial Year].[Target Start Date (DD-MMM-YYYY)] -> [all].[' + rolevalue('_memberCaption',[Presentation].[Relative Date (A)].[Relative Date].[Relative Date Financial Year])+'].['+rolevalue('_memberCaption',[Presentation].[Relative Date (A)].[Relative Date].[Relative Date Financial Year Period])+'].['
+ rolevalue('_memberCaption',[Presentation].[Relative Date (A)].[Relative Date].[Relative Date (DD-MMM-YYYY)])+']'

giving me

[Presentation].[Target Start Date Financial Calendar (A)].[Target Start Financial Year].[Target Start Date (DD-MMM-YYYY)] -> [all].[2016/17].[2016/17 P8].[24-Nov-2016]


Then changing the expression for DAY to #prompt('p_selected_date','token')#

This partly works, except I get a coercion error when the DAY "member" is being used to calculate the MTD and YTD, so it's not seeing the MUN.

Invalid coercion from 'string' to 'member' for '[DAY]' in 'aggregate( currentMeasure within set periodsToDate([Presentation].[Target Start Date Financial Calendar (A)].[Target Start Financial Year].[Target Start Financial Year Period],[DAY]) )'.


2. Attempting to construct the MUN using current_timestamp, however I'm stuck on how to work out the financial year and period parts of the MUN, I've only worked out the DAY part:

#sb(timestampMask($current_timestamp,'dd') + '-' + substitute('''','',substitute('''','', 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'))#


I've been reading up on linkMember and other functions, but my "relative date" is a separate dimension in framework, not just another hierarchy to my "Target Start Date Financial Calendar (A)", so I don't think this will work.


Should I be creating a better relative time dimension/hierarchy in framework, or focusing on creating the MUN from the current_timestamp somehow, or even alter how the MTD and YTD work?

Any pointers greatly appreciated!

Thanks,

James

[JamesR]

Finally come up with a solution that appears to work  :)

Thanks anyway.

James

[BigChris]

Hi James,

Can you post your solution? There's always a chance that someone will come up with the same requirement and would benefit from your answer.

C

[JamesR]

Hi BigChris,

You're quite right, nothing worse than finding a post with the same issue, then no resolution!

My solution was to use

Code Select Expand

#prompt('p_selected_date','token')#
as the expression definition of my Member Set data item DAY.

I then set up a value prompt, populated with values from my time dimension "Target Start Date Financial Calendar (A)"

This successfully passed any date chosen from the time dimension, I just needed a way to dynamically create a MUN for the current date (-1 day) and set this up as a static choice for my prompt.

I needed to generate the MUN:

[Presentation].[Target Start Date Financial Calendar (A)].[Target Start Financial Year].[Target Start Date (DD-MMM-YYYY)]->[all].[2016/17].[2016/17 P9].[07-Dec-2016]

and it ended up looking like this:

Code Select Expand

#'[Presentation].[Target Start Date Financial Calendar (A)].[Target Start Financial Year].[Target Start Date (DD-MMM-YYYY)] -> [all].[' + timestampMask(_add_months(_add_days($current_timestamp,-1),-3),'yyyy') + '/' + substitute('20','',timestampMask(_add_months(_add_days($current_timestamp,-1),9),'yyyy')) +'].[' + timestampMask(_add_months(_add_days($current_timestamp,-1),-3),'yyyy') + '/' + substitute('20','',timestampMask(_add_months(_add_days($current_timestamp,-1),9),'yyyy')) + ' P' + substitute('0','',timestampMask(_add_months(_add_days($current_timestamp,-1),-3),'mm')) +'].[' + timestampMask(_add_days($current_timestamp,-1),'dd') + '-' + substitute('''','',substitute('''','', substitute(timestampMask(_add_days($current_timestamp,-1),'mm'),'',csv(grep (timestampMask(_add_days($current_timestamp,-1),'mm'),array('01Jan','02Feb','03Mar','04Apr','05May','06Jun','07Jul','08Aug','09Sep','10Oct','11Nov','12Dec'))))))+ '-' + timestampMask(_add_days($current_timestamp,-1),'yyyy')+']'#

I can now select a date, or "Current Date" in my prompt, and the correct MUN is passed to my query, which populates my DAY column, which in turn populates MTD and YTD.

Not the most elegant solution, but it works. Now I need to tidy up the prompt by moving the date choices to a tree prompt, and only having "Current Date" and "Custom Date" as a option (the later which will refer to the choice made in the tree prompt).

Thanks,

James