· Mar 31, 2018

How to Call a Custom Listing in MDX?

Hi, DeepSee experts!

I need to call a listing programmatically other than the default. 

I can do that with %LISTING keyword. E.g. in Samples:

SAMPLES>d $SYSTEM.DeepSee.Shell()
DeepSee Command Line Shell
Enter q to quit, ? for help.

   #  ID   Date City Chan Prod Unit Reve Disc Comm
   1: 475  01/1 Madr Reta Life 1    0.92 20%
   2: 843  01/1 Manc Onli Frui 1    4.95 0%
   3: 808  01/1 Osak Onli Bund 4    79.8 0%

But how can I call custom listings in MDX? E.g. there are 3 custom listings available in SAMPLES HoleFoods Cube:

And they work in pivot but %LISTING says that it doesn't know them.


ERROR #5001: Listing does not exist: ('Custom Listing' @pos 63)
Discussion (9)2
Log in or sign up to continue


My SAMPLES has no listing 'Custom Listing' in HoleFoods

But this works:

SAMPLES>d $SYSTEM.DeepSee.Shell()
DeepSee Command Line Shell
Enter q to quit, ? for help. 
   #  ID        Store Loc Country   Product   Customer  Latitude  Longitude
   1: 1         Tokyo     Japan    
   2: 2         Shanghai  China     Lifesaver
   3: 3         Sao Paolo Brasil    Onion rin
   4: 4         Paris     France    Ziti (box
   5: 5         Santiago  Chile     Donuts (d
   6: 6         Brasilia  Brasil    Ziti (box
   7: 7         Madrid    Spain     Donuts (d
   8: 8         Chicago   USA      
Tortellin 46168     39.716036 -86.39506
   9: 9         Frankfurt Germany   Swiss Che
  10: 10        Tokyo     Japan     Onion rin


 >>DRILLTHROUGH SELECT FROM [Patients] %LISTING [Custom SQL listing]
   #  PatientID      FavoriteColor
   1: SUBJ_100301    Green
   2: SUBJ_100302    Orange
   3: SUBJ_100303    Yellow
   4: SUBJ_100305    Orange
   5: SUBJ_100306    Purple
   6: SUBJ_100307    Orange
   7: SUBJ_100308    Blue
  • Those listings in italics are defined in Listing Groups. You can find them in SMP > DeepSee > Tools > Listing Group Manager > Open > Additional Listing for HoleFoods Sample
    They work in MDX:
    Do $SYSTEM.DeepSee.Shell()
    >>DRILLTHROUGH SELECT FROM [holefoods] %LISTING [Another Sample Listing by Date]
    • "Custom Listing" is the listing that you construct in pivots using Listing Fields, see here: Display a Detail Listing. I have not checked the code but to me it makes sense that, if defined on the pivot, "Custom Listing" will not be available for general MDX queries.