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)
>>Comments
Evgeny,
My SAMPLES has no listing 'Custom Listing' in HoleFoods
But this works:
DeepSee Command Line Shell
----------------------------------------------------
Enter q to quit, ? for help.
# 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:
# 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.
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).
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.
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!
Oops. I just looked for MDX.
What is exactly that custom listing? Is it defined in Listing Group (those ones should work using %LISTING)?
- 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.
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)