Using dynamic date and #sb() in a query expression in a graph category

[Karibu]

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:

Code Select Expand

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:

Code Select Expand

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

Code Select Expand

#sb() in a Query Expression. Even if I make a Query Expression below, I get an error message:

Code Select Expand

descendants([Ressu_11_01].[TIME].[TIME].[Month]->:[PC].[@MEMBER].#sb(20121201-20121231)#,
[Ressu_11_01].[TIME].[TIME].[Day])

Error message I get is

QE-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