New post
Question
Evgeny Shvarov · Mar 31, 2018

How to Call a Custom Listing in MDX?

#InterSystems IRIS BI (DeepSee) #Listings #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 338
Discussion (9)3
Log in or sign up to continue
Robert Cemper · Mar 31, 2018

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
1 0
Evgeny Shvarov · Mar 31, 2018

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.

 

0 0
Evgeny Shvarov · Mar 31, 2018

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

0 0
Evgeny Shvarov · Mar 31, 2018

Thank you, Alessandro! That's clear now. I guess Custom Listing is being executed with DRILLTHROUGH + field names by comma: there is an option in Drillthrough documentation.

0 0
Evgeny Shvarov · Apr 2, 2018

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!

0 0
Alessandro Marin · Mar 31, 2018

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

0 0
Alessandro Marin · Mar 31, 2018
  • 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.
    1 0
    Peter Steiwer · Mar 31, 2018

    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)

    0 0