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.
>>DRILLTHROUGH SELECT FROM [HOLEFOODS] %LISTING [Listing]

   #  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.

>>DRILLTHROUGH SELECT FROM [HOLEFOODS] %LISTING [Custom Listing]

ERROR #5001: Listing does not exist: ('Custom Listing' @pos 63)
>>
  • 0
  • 0
  • 165
  • 6
  • 3

Answers

  • 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.

    Evgeny,

    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. 
    >>DRILLTHROUGH SELECT FROM [HoleFoods] %LISTING [Customer Info]
     
       #  ID        Store Loc Country   Product   Customer  Latitude  Longitude
       1: 1         Tokyo     Japan    
    Free-rang
       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

    similar:

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

    Hi, Robert!

    Thanks for the answer!

    Yes, %LISTING this works perfectly with listings declared in the cube definition.

    But if you open Analyzer you can see extra "Custom" listings, which are available in the pivot, but not available via %LISTING.

     

    If you use the custom listing in Analyzer and view the MDX that it generates, it will look something like this:

    DRILLTHROUGH SELECT FROM [HOLEFOODS] RETURN Outlet->City "City",Comment "Comment"

    You should be able to construct this however you want, assuming the return fields exist in your source (or fact table if you are using DRILLFACTS)

    Hi, Peter, thanks!

    The question was that everything which is listed in pivot-settings-dropdown is available to call with %LISTING in MDX, except with "custom listing". 

    Which can be called with

    DRILLTHROUGH .... RETURN fields 

    MDX instruction, as you wrote. Thanks!

    Comments

    What is exactly that custom listing? Is it defined in Listing Group (those ones should work using %LISTING)?

    Have no idea, Alessandro.

    Just figured out, that there are some extra listings (in addition to those we have in the cube) available for pivots in Analyzer. %Listing only works (for me) with those which are declared in the cube.

    You can see (and alter custom listing) if you open Detail Listings section in Analyzer (version 2017.2).