Quote from: phantomluvs on 04 Sep 2015 12:27:27 AM
Seeking help from all Multidimensional reporting experts
We are using TM1 as our OLAP data source for reporting. The time dimension created in TM1 doesn't have Current month / current period item that can be used in report studio.
Below is the MNU of a month:
My MNU: [Monthly Income Statement].[t_time].[t_time].[Year]->:[TM].[t_time].[t_time].[@MEMBER]. [Jun '15]
I need help with calculating current month data item in report studio. I have tried many methods
• Find Current Period using closingPeriod Function
o closingPeriod ([sales_and_marketing_cs].[Time].[Time].[Month])
• Find Current Period by Filtering on Measure Data
o item(tail(filter(members([sales_and_marketing].[Time].[Time].[Month]), tuple([Revenue], currentMember([sales_and_marketing].[Time].[Time])) is not null), 1), 0)
Also tried constructing [Jun '15] using #sb(timestampMask($current_timestamp,'mm'))#)), which doesn't work
Hi,
You could try the following to build the MUN:
#'[Monthly Income Statement].[t_time].[t_time].[Year]->:[TM].[t_time].[t_time].[@MEMBER].[' + 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'))))))+' '+substitute('20','''',timestampMask($current_timestamp,'yyyy'))+']'#
Incidentally, the MUN you posted above contains a space between the [@MEMBER]. and [Jun '15] parts - I assume this is a typo?
The expression I posted above will always return the MUN of the month member that corresponds with the system date (eg today is 4th September 2015 so it will return the last part as [Sep '15])
Cheers!
MF.
You are Awesome Muppet, it worked like a charm.
Yes the space is a typo.