Hi All,
I have been try to set a default prompt value to my Months prompt. I have to set it through its MUN value.
[Sales].[Time Period].[Time Period].[Fiscal Months]->:[PC].[@MEMBER].[20140901-20140930]
Here the challenge I am facing is to find the last number of days of current month. I tried last of month function but was not able to exactly get the 30 or 31 number.
I tried with the below macro and getting some errors.
#'[Sales].[Time Period].[Time Period].[Fiscal Months]->:[PC].[@MEMBER].[' + timestampMask($current_timestamp,'yyyy') +timestampMask($current_timestamp,'mm')+'01-' + timestampMask($current_timestamp,'yyyy') + timestampMask($current_timestamp,'mm')+_last_of_month ((timestampMask($current_timestamp,'yyyy')+'-'+timestampMask($current_timestamp,'mm')+'-'+ timestampMask($current_timestamp,'dd'))]'#
I appreciate your help.
Thanks,
Nithya
Quote from: nithya1224 on 03 Nov 2014 07:36:16 AM
Hi All,
I have been try to set a default prompt value to my Months prompt. I have to set it through its MUN value.
[Sales].[Time Period].[Time Period].[Fiscal Months]->:[PC].[@MEMBER].[20140901-20140930]
Here the challenge I am facing is to find the last number of days of current month. I tried last of month function but was not able to exactly get the 30 or 31 number.
I tried with the below macro and getting some errors.
#'[Sales].[Time Period].[Time Period].[Fiscal Months]->:[PC].[@MEMBER].[' + timestampMask($current_timestamp,'yyyy') +timestampMask($current_timestamp,'mm')+'01-' + timestampMask($current_timestamp,'yyyy') + timestampMask($current_timestamp,'mm')+_last_of_month ((timestampMask($current_timestamp,'yyyy')+'-'+timestampMask($current_timestamp,'mm')+'-'+ timestampMask($current_timestamp,'dd'))]'#
I appreciate your help.
Thanks,
Nithya
Hi,
I think you're over-complicating this a little. How about this:
#'[Sales].[Time Period].[Time Period].[Fiscal Months]->:[PC].[@MEMBER].[' + timestampMask(_first_of_month($current_timestamp),'yyyymmdd')+ '-' + timestampMask(_last_of_month($current_timestamp),'yyyymmdd') + ']'#
MF.
Hi MFGF,
The solution you provided works fine when put on a data item.
I wanted to put that in the below expression instead of the static mun expression.
#promptmany('PrmMonth','mun','[Sales].[Time Period].[Time Period].[Fiscal Months]->:[PC].[@MEMBER].[20140801-20140831]','set(','',')')#
when I replace the above static mun with your expression it throws me an error. I tried copying this expression to a new data item and then using it..it says circular reference.
Thnaks,
Nithya
Thanks MFGF,
This worked, I had used the macro function twice in the experssion, my mistake.
#promptmany('PrmMonth','mun','[Sales].[Time Period].[Time Period].[Fiscal Months]->:[PC].[@MEMBER].[' + timestampMask(_first_of_month($current_timestamp),'yyyymmdd')+ '-' + timestampMask(_last_of_month($current_timestamp),'yyyymmdd') + ']'
,'set(','',')')#
Thanks,
Nithya
Quote from: nithya1224 on 03 Nov 2014 08:43:11 AM
Thanks MFGF,
This worked, I had used the macro function twice in the experssion, my mistake.
#promptmany('PrmMonth','mun','[Sales].[Time Period].[Time Period].[Fiscal Months]->:[PC].[@MEMBER].[' + timestampMask(_first_of_month($current_timestamp),'yyyymmdd')+ '-' + timestampMask(_last_of_month($current_timestamp),'yyyymmdd') + ']'
,'set(','',')')#
Thanks,
Nithya
I was about to post back and say it works fine for me, and ask you to post up your new expression. Looks like you figured it out though :)
MF.