MDX Generate()

I'm writing this post because I forget stuff all the time. This is one of those things I learned a few months ago and nearly forgot already...

One of the trickiest parts of dealing with MDX is shared members. They look different than regular members so a lot of functions don't recognize the stored and shared members as being the same. If you want them to look the same you need to use the Generate() function.

Suppose you want to compare a list of children to the currently selected member. It won't usually work if those children are shared members.

The following formula will yield a false result when the currently selected member is Flat Panel or HDTV or any of those shared members you see above.

IsChild([High End Merchandise].children,Products.CurrentMember)

In order to "clean" the shared nomenclature from the member you need to use Generate(). This will return a true result.

Contains([Products].CurrentMember,
               {Generate([High End Merchandise].Children
                                 {StrToMbr(Products.CurrentMember.Member_Name)})})

In this case Generate() loops through the set of children, returning the the cleaned up product name  into a new set. That set is then evaluated against the current product selection.

Branching in MaxL

A fairly common task I run across when writing MaxL scripts is to have some logic where a certain file loads or a calc runs one day and a different file loads or a different calc runs on a different day. I'd usually write multiple MaxL scripts to handle this and call a different script on a different day. I'm not sure why it took me so long to figure this out, but it's really not difficult to handle this in one script.

1:  login $1 $2 on $3;  
2:    
3:  echo 'script starting';  
4:    
5:  goto $4;  
6:    
7:  define label '0';  
8:  echo 'Today is Sunday';  
9:  goto 'Finished';  
10:    
11:  define label '1';  
12:  echo 'Today is Monday';  
13:  goto 'Finished';  
14:    
15:  define label '2';  
16:  echo 'Today is Tuesday';  
17:  goto 'Finished';  
18:    
19:  define label '3';  
20:  echo 'Today is Wednesday';  
21:  goto 'Finished';  
22:    
23:  define label '4';  
24:  echo 'Today is Wednesday';  
25:  goto 'Finished';  
26:    
27:  define label '5';  
28:  echo 'Today is Thursday';  
29:  goto 'Finished';  
30:    
31:  define label '6';  
32:  echo 'Today is Friday';  
33:  goto 'Finished';  
34:    
35:  define label '7';  
36:  echo 'Today is Saturday';  
37:  goto 'Finished';  
38:    
39:  define label 'Finished';  
40:  echo 'script is finished';  
41:    
42:  logout;  
43:  exit;  

When I call the MaxL script, I pass in User ID, Password, Hostname and then a number for the day of the week. Only the part between the label and the goto will be executed.