How to transfrom date to time dimension member in periodsToDate

[erwink]

Hi there

I had some month ago the issue of transforming current timestamp to dimension member.

Today I've a similar issue. Instead of using the current timestamp I have to use a date out of the DB

the member is as [all].[2016].[20163].[201608]

my try:

total ([RequestedHours] within set periodsToDate ([Year],'[Dimensions].[BYTime].[BYTimeH].[Month]->[all].['
+IF (  cast(substring ('2016-08-8',6,2),int)>9 ) THEN     ( cast((cast(substring ('2015-10-8',1,4),int)+1),char(4) ) ) ELSE      ( substring ('2016-08-8',1,4) )
+'].['
+substring ('2016-08-8',1,4)+cast((cast((cast(substring ('2016-08-8',6,2),int)/3.1),int)+1),char(1))
+'].['
+substring ('2016-08-8',1,4)+substring ('2015-10-8',6,2)+ ']'))

I tested each of the level in a data item, and the results where correct

But running those together seams not to be allowed. What do I need to do, to transform string to member

error is:
Invalid coercion from 'string' to 'level' for '[Year]' in 'total( [RequestedHours] within set periodsToDate([Year],'[Dimensions].[BYTime].[BYTimeH].[Month]->[all].[' || if (cast(substring('2015-10-8' from 6 for 2) as int) > 9) then (cast(cast(substring('2015-10-8' from 1 for 4) as int) + 1 as character (4))) else (substring('2015-10-8' from 1 for 4)) || '].[' || substring('2015-10-8' from 1 for 4) || cast(cast(cast(substring('2015-10-8' from 6 for 2) as int) / 3.1 as int) + 1 as character (1)) || '].[' || substring('2015-10-8' from 1 for 4) || substring('2015-10-8' from 6 for 2) || ']') )'.

Thank you for your help
erwin

[bdbits]

The problem is that your the date portion of you expression resolves to a string, but PeriodsToDate wants a level. I would probably fix this with a prompt macro. So your expression becomes something like this (untested, adjust as needed).

total ([RequestedHours] within set periodsToDate ([Year],#prompt('thedate','token','[Dimensions].[BYTime].[BYTimeH].[Month]->[all].['
+IF (  cast(substring ('2016-08-8',6,2),int)>9 ) THEN     ( cast((cast(substring ('2015-10-8',1,4),int)+1),char(4) ) ) ELSE      ( substring ('2016-08-8',1,4) )
+'].['
+substring ('2016-08-8',1,4)+cast((cast((cast(substring ('2016-08-8',6,2),int)/3.1),int)+1),char(1))
+'].['
+substring ('2016-08-8',1,4)+substring ('2015-10-8',6,2)+ ']')))

[erwink]

Hi bdbits

is there another way to transform a string to a member. Because  it seems that "if then else" is not working in a macro and substring is a unknown function


or  what would be if I tried to put the date LastActualImportMonth in the db as Identifier instead of date. Would it work to have
total ([RequestedHours] within set periodsToDate ([Year], [Dimensions].[Plan-Actual_MS].[LastActualImportMonth])

thanks
erwin

[bdbits]

While we could get a working #prompt macro, a data-driven solution is in my opinion a much better one.  :-)

[tjohnson3050]

See if this older post helps:

Usually dimensional models will include relative time.  Recently I faced a requirement to efficiently calculate the mun for the prior month using a dimensional model with no relative time.  The model was a Cognos DMR model.

The following function uses a prompt macro to query the current date from the report server, then constructs a member unique name (MUN) for the current month.  This assumes a calendar hierarchy with levels for Year, Quarter and Month.  It also assumes the quarter and month business keys are constructed a certain way (YYYYQ) and (YYYYMM).  The macro function timestampMask can return the year and the month from the current timestamp, and a case statement derives the quarter number. Then the dimensional function for previous member (prevMember) returns the prior month.  The good thing about this method is that the function (including the case statement) current month is resolved quickly before the query is generated.

In the function below, the [Namespace].[CalendarDimension].[CalendarHierarchy].[Month]-> portion would obviously need to be replaced by the appropriate names in your model.

prevMember ([Namespace].[CalendarDimension].[CalendarHierarchy].[Month]->[all].
#sb(timestampMask($current_timestamp,'yyyy'))#.
#sb(timestampMask($current_timestamp,'yyyy')+
case timestampMask($current_timestamp,'mm')
when '01' then '1'
when '02' then '1'
when '03' then '1'
when '04' then '2'
when '05' then '2'
when '06' then '2'
when '07' then '3'
when '08' then '3'
when '09' then '3'
when '10' then '4'
when '11' then '4'
when '12' then '4'
end)#.
#sb(timestampMask($current_timestamp,'yyyy') + timestampMask($current_timestamp,'mm'))#
)

[erwink]

hi,

sorry that I didn't answer earlier. Thought this was closed and didn't get any email

Your tip is good for current_timestamp, but I need a date form a query

erwin