How to Call a Custom Listing in MDX?

Question

Question

Evgeny Shvarov · Mar 31, 2018

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

Discussion (9)2

Log in or sign up to continue

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

score 1 · Answer 1 · 2018-03-31T11:03:23-04:00

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

score 0 · Answer 2 · 2018-03-31T11:08:50-04:00

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.

score 0 · Answer 3 · 2018-03-31T11:39:00-04:00

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

score 0 · Answer 4 · 2018-03-31T12:13:17-04:00

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.

score 0 · Answer 5 · 2018-04-02T03:16:00-04:00

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!

score 0 · Answer 6 · 2018-03-31T11:13:05-04:00

Robert Cemper · Mar 31, 2018

Oops. I just looked for MDX.

0 0

score 0 · Answer 7 · 2018-03-31T11:33:52-04:00

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

score 0 · Answer 8 · 2018-03-31T13:26:10-04:00

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)