Data item macro syntax help

[dax]

Hi

I am attempting to return the "current period" within a data item.  My actual problem is a little more involved but if I can get this GO example working then I'm fairly certain I can get the actual problem fixed.

I have attached an example report.  What I want to do in this example is the following logic:

If the month today is April then return the sales for March
If the month today is May then return the sales for April
etc...

I have tried many different expressions and none of them will validate.  The one I have at the moment is:

#sb(
case month(getDate()) when (3) then ('February 2007') when (4) then ('March 2007') when (5) then ('April 2007') end)
)#

but this gives a parsing error ("Parsing error before or near position: 16 of "sb(case month"

If I simply change the data item to #sb('March 2007')# it works ok.  So, I think my problem is something to do with the case statements and maybe quotes?

A little more information:

1.  I am using SQL Server so the GetDate function won't work for anyone on Oracle or some other platform.
2.  In my real world problem I need to check the current day and hour on the server and then return a different time member accordingly.  For example, if it's May the 5th and after 2pm on the server then I want the member "April" returned, otherwise I want "March" returned.
3.  Ideally I would have this value stored in the database.  Currently our DBA is flat out getting other stuff done so I could be waiting weeks before he gets round to doing this.


Can anyone see what I'm doing wrong here?   Let me know if any more information is needed and I'll post it. 

Thanks
Dax

[MFGF]

Hi,

So you're using a DMR package? The example you posted is against the GO Data Warehouse (analysis) package, which is dimensional...

First question - why are you using a macro approach for this? Is it a viable alternative to look at the closing period (ie the last month) of the Month level and bring back the month immediately preceding it? If so, you could use an expression such as:

lag(closingPeriod([Your Month level]),1)

If you have future months in your time dimension, this will not work as an approach, though. In that case a macro approach would be required.

One key question - your example refers to 'March 2007' - why? Are you looking for the prior month of the current year or are you looking for a corresponding prior month in an earlier year? If the latter, how are you deciding on the appropriate year?

The key to understanding the expression for the macro lies in knowing the structure of the MUN (Member Unique Name) of your Month members. Although a month may display in your report as 'March 2007' (for example), this is the caption of the month, not the ID which is used to locate it in the package. Your macro would need to assemble the relevant MUN, and you need to understand its structure to be successful.

Within the GO Data Warehouse (analysis) package, the 'March 2010' member has a MUN of [Sales].[Time].[Time].[Month]->[Time].[2010].[20101].[201003] and your macro would need to build up this MUN in order to locate this member. Tom Johnson posted a fabulous example of how to build this (catering for the embedded quarter in the MUN) in this post.

Cheers!

MF.

[dax]

Hi MFGF

Thanks for your reply and apologies for the delay in responding - just had a hectic last few days of the week.

I am open to any possible alternatives as long as I can get it to work - I am using a macro as I don't know how I'd check today's date and hour using any other method.  I do have future periods in my time dimension but it is currently set at a maximum of Dec 2016, so I could possibly use your Lag/ClosingPeriod idea, but if new dates get added at some point then it would probably break the report.

I am using March 2007 because in my GO Data Warehouse that is the most recent period that has any data in it.  In reality I would be using the current year.  I just used 2007 so the example report would return some data.


Thank you for referring me to that very useful post of Tom's - but for some reason I can't even get that to validate without a parsing error.  I copied the exact expression that he posted - I do realise that it won't work because the GO model has a different MUN structure but I wouldn't have thought that would matter i.e. it should at least parse.  Can you get that expression to validate?  I am on version 10.1.1 if that makes a difference.

Thanks
Dax

[dax]

I've been playing round with it for the last couple of hours and it's driving me nuts!  It does not seem to like when I use a construct such as case when or if then. Here are two expressions from my real life model:

This validates and runs fine.

Code Select Expand

[Business View].[Calendar Dimension].[Fiscal Years].[Fiscal Period]->[all].[2014].#sb(

'201404'

)#

But this doesn't:

Code Select Expand

[Business View].[Calendar Dimension].[Fiscal Years].[Fiscal Period]->[all].[2014].#sb(

if (1=1) then ('201404') else ('201403')

)#

Here is the first part of the error message (I can post the whole lot if that would be of use - but it just refers to a load of cpp files):

QE-DEF-0299 Expanding: #sb( if (1 from position: 82, to position: 94, text fragment: #sb( if (1.QE-DEF-0260 Parsing error before or near position: 94 of: "[Business View].[Calendar Dimension].[Fiscal Years].[Fiscal Period]->[all].[2014].#sb( if (1"QE-DEF-0299 Expanding: #sb( if (1 from position: 82, to position: 94, text fragment: #sb( if (1.RSV-SRV-0042 Trace back:RSReportService.cpp(722): QFException: CCL_CAUGHT

I seem to be missing something in the syntax but I cannot figure out what.  Anyway, it's 1030pm here so going to give up and look again on Monday.  If anyone can point out what I'm doing wrong it will be greatly appreciated :)

Cheers
Dax

[MFGF]

I could be wrong, but I don't think macros support if-then-else constructs.

MF.

[dax]

Are you able to get the expression in Tom's post to validate?  If you are then that would at least mean that the problem is on our end.

Thanks,
Dax

[navissar]

Macros aren't free text, and they don't respond well to case or if statements.
What you are trying to achieve is best achieved by not using a macro, but rather by finding the current month and lagging it one on month level.
If you must, you could use a macro, but it's a blue-blooded pain in the lower back.
I'll give you an example and you can run with it from there if you must. This macro will return the current month on the GO DW (Analysis). This will be different based on your datasource and how your date members are created. in GO DW (Analysis), the basic structure for a month member MUN is:

Code Select Expand

[Sales].[Time].[Time].[Month]->[Time].[2010].[20101].[201003]
So, after the arrow there's [Time].[Year].[Year+one character quarter].[yyyymm].
We can construct that using macro like this:

Code Select Expand

#'[Sales].[Time].[Time].[Month]->[Time].['+
timestampMask ($current_timestamp,'yyyy')+'].['+
timestampMask ($current_timestamp,'yyyy')+substitute('''','',substitute('''-','',substitute(timestampMask ($current_timestamp,'mm'),'',csv(grep(timestampMask ($current_timestamp,'mm'),array('01-1','02-1','03-1','04-2','05-2','06-2','07-3','08-3','09-3','10-4','11-4','12-4'))))))
+'].['+timestampMask ($current_timestamp,'yyyymm')+']'#

Piece of cake, right? You can see how crooked the method for getting the quarter is (Can be made simpler with a parameter map).
If your data is structured differently, you'll need to create this differently.

I'll let you take it from there.

[dax]

Hi Nimrod

Thanks for your reply.  I have no strong desire to use a macro - I just want to solve the problem at hand, but with the limitation that at this stage I cannot get the database modified to include this data anywhere.

I would still like to know how Tom got his expression to work as it seems as though his technique could be modified to do exactly what I need.

Thanks also for the macro example you posted - despite its complexity it is useful for getting a better understanding of the syntax.

Anyway, I have decided for now that I will simply go into the relevant report each month and update it manually - not ideal but will be ok until I get the necessary data added to the database.

Cheers
Dax

[navissar]

Hi dax,
note that Tom's expression and mine do exactly the same, only Tom's expression uses a case statement which as far as I know isn't supported in macros. I will look into how he got it to verify, however, my syntax does just that including catering for quarters (This mechanism, of using grep over an array, can also be used to fetch month names if member names are constructed in such a manner).

[nblank]

Below an example of using a case in a macro:

####Filter date_from
[DATE] >=
#prompt('p_date_from','token', 'case when _day_of_year(Current_date) < 32 then _add_years(_add_days(Current_date ; (_day_of_year(Current_date)*-1)+1);-1) else _add_days(Current_date ; (_day_of_year(Current_date)*-1)+1 ) end','','','')#

####Filter date_to
[DATUM] <=
#prompt('p_date_to','token', 'case when _day_of_year(Current_date) < 32 then _last_of_month(_add_months (current_date;-1)) else Current_date end','','','')#

Perhaps you can use the syntax with your case.

[MFGF]

Below an example of using a case in a macro:

####Filter date_from
[DATE] >=
#prompt('p_date_from','token', 'case when _day_of_year(Current_date) < 32 then _add_years(_add_days(Current_date ; (_day_of_year(Current_date)*-1)+1);-1) else _add_days(Current_date ; (_day_of_year(Current_date)*-1)+1 ) end','','','')#

####Filter date_to
[DATUM] <=
#prompt('p_date_to','token', 'case when _day_of_year(Current_date) < 32 then _last_of_month(_add_months (current_date;-1)) else Current_date end','','','')#

Perhaps you can use the syntax with your case.

Ah - I see. You are not using the case statement directly within the macro - you are returning it as the default string to be parsed by the Cognos query engine if the prompt is left blank.

MF.