Hi everybody. :) I'm a new member of this community, all though I have been reading interesting topics here for some time. I am a report studio developer and I think this is propably the best place to find knowledge so I respectfully present my case here. 8)
I have a dimensional cube and graph where I present dates in X-axis. I made a Query Expression into Category (X-axis) which looks like this:
descendants([Ressu_11_01].[TIME].[TIME].[Month]->:[PC].[@MEMBER].[20121201-20121231],
[Ressu_11_01].[TIME].[TIME].[Day])It works and it shows all the dates of December. However, my need is to replace
[20121201-20121231] with a dynamic expression, which shows the dates of current month to date. For example, Jan. 15th 2013 the string needs to be
[20130101-20130115], February 2nd it needs to be
[20130201-20130202]. Alternatively, it can also show the whole month instead of just up til current date, respectively any day in January it can be
[20130101-20130131] and any day in February it can be
[20130201-20130228]. Either way, shouldn't be the problem.
Consequently, I made a Query Expression to look like this:
children([Ressu_11_01].[TIME].[TIME].[Month]->:[PC].[@MEMBER].#sb(timestampMask($current_timestamp,’yyyymm’)+’01-’+ TO_CHAR(_last_of_month(date2timestamp(Today())),'yyyymmdd'))#)Purpose is to piece the starting date of current month by taking Year and Month from
current_timestamp and then add 01 --> Today it should show 20130101. Then Add minus in between dates and use
_last_of_month to extract the last day of month and turn it to characters using TO_CHAR -function (not sure if it works on dimensional datasource?).
The problem: However, it seems that I can't use
#sb() in a Query Expression. Even if I make a Query Expression below, I get an error message:
descendants([Ressu_11_01].[TIME].[TIME].[Month]->:[PC].[@MEMBER].#sb(20121201-20121231)#,
[Ressu_11_01].[TIME].[TIME].[Day])Error message I get is
QuoteQE-DEF-0459 CCLException
QE-DEF-0260 Parsing error before or near position 74 descendants([Ressu_11_01].[AIKA].[AIKA].[Kuukausi]->:[PC].[@MEMBER].#sb(
QE-DEF-0299 Expanding #sb( from position: 69 to position: 74 Text fragment #sb(.
I would be very grateful to know if I can use
sb() in a query expression or solve this problem another way.
Best Regards,
Karibu