Hi!
I have member like this:
[cube].[dimension].[hierarchy].[level]->:[BW].[0CALDAY].#sb(substitute ('-';'';substitute ('-';'';prompt('[nper]';'date'))))# - [cube].[dimension].[hierarchy].[level]->:[BW].[0CALDAY].[20141215]
#nper# working by calendar prompt and return beginning of period.
I need change the prompt to return day-1.
I try to use the _add_days in the following example, but faced with the challenge of converting - "Can not convert value string '2014-12-15' to data type timestamp with time zone."
#sb(substitute ('-';'';substitute ('-';'';_add_days(prompt('[nper]';'date');-1))))#
how i can change my prompt expression for using _add_days function, or there are other ways to return -1 day?
Egor.
Quote from: manias on 15 Dec 2014 06:59:17 AM
Hi!
I have member like this:
[cube].[dimension].[hierarchy].[level]->:[BW].[0CALDAY].#sb(substitute ('-';'';substitute ('-';'';prompt('[nper]';'date'))))# - [cube].[dimension].[hierarchy].[level]->:[BW].[0CALDAY].[20141215]
#nper# working by calendar prompt and return beginning of period.
I need change the prompt to return day-1.
I try to use the _add_days in the following example, but faced with the challenge of converting - "Can not convert value string '2014-12-15' to data type timestamp with time zone."
#sb(substitute ('-';'';substitute ('-';'';_add_days(prompt('[nper]';'date');-1))))#
how i can change my prompt expression for using _add_days function, or there are other ways to return -1 day?
Egor.
How about using the prevMember() function around your initial expression - ie
prevMember([cube].[dimension].[hierarchy].[level]->:[BW].[0CALDAY].#sb(substitute ('-';'';substitute ('-';'';prompt('[nper]';'date'))))#)
Would this work for you?
MF.
MF, thanks for reply!
When I use the prevMember function, I get an error:
XSB-ERR-0016 Execution of MDX failed:
XSB-ERR-0028 BAPI Error while executing Function BAPI_MDDATASET_SELECT_DATA:
RFC Error: For characteristic nper, enter value in a permitted format.
As source i have sap bw.
Egor.
Quote from: manias on 15 Dec 2014 07:47:54 AM
MF, thanks for reply!
When I use the prevMember function, I get an error:
XSB-ERR-0016 Execution of MDX failed:
XSB-ERR-0028 BAPI Error while executing Function BAPI_MDDATASET_SELECT_DATA:
RFC Error: For characteristic nper, enter value in a permitted format.
As source i have sap bw.
Egor.
Hmmmm. That's worrying. What happens if you use the lag() function, with 1 as the number of members to lag - ie
lag([cube].[dimension].[hierarchy].[level]->:[BW].[0CALDAY].#sb(substitute ('-';'';substitute ('-';'';prompt('[nper]';'date'))))#,1)
MF.
I get same error. I think will continue to be an error similar to the return of the previous member.
Trying to figure out how to substitute the value -1
Egor.