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

    In spite of the fact that your queries do not show dates nor the PARTEPOLICIAL cube, I do not think related cubes change anything from what suggested in answer 2 here: https://community.intersystems.com/post/filters-deepsee-dashboards . Below I rephrase what was suggested:

    A) Create a "myDate" pivot variable used as "Literal" and of type "Day" in your main cube, which I will assume is EVENTOSPOLICIALES.

    B) Define two calculated dimensions. First get the spec of the two dates from your main cube. You can drag and drop dates and inspect the MDX query. For example, the specs will be something like:

    [Some dimension].[H1].[Fecha Creacion]

    [Relation to PARTEPOLICIAL].[Some other dimension].[H1].[Fecha Parte]

    C) Create two calculated dimensions with Local Storage. The expressions should use the specs in B and the pivot variable in A, for example:

    [Some dimension].[H1].[Fecha Creacion].$variable.myDate

    [Relation to PARTEPOLICIAL].[Some other dimension].[H1].[Fecha Parte].$variable.myDate

    D) Use the calculated dimensions as filter or in rows/columns for your pivots. The dashboard will have to have a widget with a Apply Pivot Variable control.

    You can find the link to Management Portal in the documentation:  http://docs.intersystems.com/latest/csp/docbook/DocBook.UI.Page.cls?KEY=GSA_using_portal 

    To see if the instance is running: I suggest getting familiar with the ccontrol command from terminal: http://docs.intersystems.com/latest/csp/docbook/DocBook.UI.Page.cls?KEY=GSA_using_instance#GSA_using_instance_control

    For example, ccontrol list will list basic information about your instance. This is an example on my (Linux) computer where you can see that the first instance is down and the second is running: 

    amarin> ccontrol list

    Configuration 'C152'
        directory:    /home/amarin/intersystems/cache20152
        versionid:    2015.2.1.705.0
        datadir:      /home/amarin/intersystems/cache20152
        conf file:    cache.cpf  (SuperServer port = 56778, WebServer = 57778)
        status:       down, last used Fri Jul  7 09:37:47 2017

    Configuration 'C161'
        directory:    /home/amarin/intersystems/cache20161
        versionid:    2016.1.0.610.0
        datadir:      /home/amarin/intersystems/cache20161
        conf file:    cache.cpf  (SuperServer port = 56777, WebServer = 57777)
        status:       running, since Wed Nov  8 11:33:16 2017
        state:        ok

    No problem, your case (no time part in the timestamp, just get the fact that comes last in your source table) is even easier. Once you make the fact order in your source table work this plugin should work.

    This plugin uses SensorReading, but it is possible to make it more dynamic (hint: implement a filter definition and %OnGetListingFields). 



    /// Use this plugin in a calculated measure as %KPI("User.LastFact","LASTFACT",1,"%CONTEXT")
    Class User.PluginLast Extends %DeepSee.KPIPlugIn

    /// Cube(s) that this plug-in is based on.
    Parameter BASECUBE = "*";

    /// SourceTable
    Parameter LISTINGSOURCE = "SourceTable";

    Parameter PLUGINTYPE = "Pivot";

    // The ID is probably not even needed:
    Parameter LISTINGFIELDS = "SensorReading, %ID as ID";

    XData KPI [ XMLNamespace = "http://www.intersystems.com/deepsee/kpi" ]
    <kpi name="User.LastFact" displayName="LastFact" caption="LastFact" >

    <filter name="%cube" displayName="Subject Area"/>
    <property name="LASTFACT" displayName="LastFact" description="" />


    /// Get the base query for this plug-in.
    Method %OnGetMDX(ByRef pMDX As %String) As %Status
        Set tBaseCube = ""

        // Use %cube filter to find the base cube
        If $IsObject(..%filterValues) {
            If (..%filterValues.%cube'="") {
                Set tBaseCube = ..%filterValues.%cube

        If (tBaseCube'="") {
            Set pMDX = "SELECT FROM "_tBaseCube
        Quit $$$OK

    /// Compute the LastFact of values within the result set.
    Method %OnCompute(pSQLRS As %SQL.StatementResult, pFactCount As %Integer) As %Status
        Set tSC = $$$OK
        Try {
            Set tErrMsg = ""
            While (pSQLRS.%Next(.tSC)) { 
                If $$$ISERR(tSC) {
                    Set tErrMsg = $System.Status.GetErrorText(tSC)
                // Get the last fact added to the table. If unsure use pSQLRS.ID
                Set tValue = pSQLRS.SensorReading
                If ($ZSTRIP($P(tValue,":"),"<>W")="ERROR") {
                    Set tErrMsg = $P(tValue,":",2)

            // place answer in KPI output
            Set ..%seriesCount = 1
            Set ..%seriesNames(1) = "LastFact"
            If (tErrMsg'="") {
                Set ..%data(1,"LASTFACT") = tErrMsg
            } Else {
                Set ..%data(1,"LASTFACT") = tValue
        Catch(ex) {
            Set tSC = ex.AsStatus()
        Quit tSC


    In Steve's example there are multiple (two) facts in one day, and he need to get the latest. IMO this means that Steve need to get the time of the reading (or maybe get the reading in a certain day that was added to the source table last). When using time dimensions we do not have a level more granular than DayMonthYear that can compare the time part of a reading.

    I tried working around this problem but so far without success. With a plugin we have access to the lowest-level data, in particular the whole time stamp containing the time of the reading (I am assuming Steve used in his source class a regular time stamp). In my test I was able to use that piece of information to get all readings in one single period (day, month,..), compare the time stamps, and return only the most recent.

    Unless Steve is ok with using Average as the aggregation function for his SensorReading measure as suggested by Asaf

    Hello Razvan,

    I assume you are starting from the last figure in the tutorial where the pivot in the figure has the Count measure on Columns.

    Your calculated member is considered a measure because it performs a calculation, not a "pure" aggregation of facts. Two measures cannot be crossjoined (that is you cannot place one under the other, or one in the Columns and the other in the Measures box),and for this reason you see an error. In this page you can find more details on why you cannot crossjoin two measures:



    That said, try creating a "Percentage" calculated measure with this expression: 

    100 * (%CELLZERO(-1,0) - %CELLZERO(-2,0))/%CELLZERO(-1,0)

    and place it in the Columns box (do not crossjoin it with Count though!). 

    I hope this helps.

    One way to do this is by using a pivot variable. Create the same pivot variable "Region" in both pivots on which your widgets are based. These pivot variables should return the members, in your example Asia, Europe, N. America, S. America. You can define the manually or in a termlist, or use a kpi to retrieve them. 

    For the example in the screenshot below I created a HoleFood2 cube with a Outlet2.H1.Region2 level. This level is "incompatible" with an Outlet.H1.Region level in HoleFoods. In my manual Region pivot variable I simply defined two regions, which can be selected manually. 

    Once you have these two pivot variables create a calculated dimension on each pivot using the pivot variable. In your example in HoleFoods the expression should be Outlet.[$variable.Region]. Place the calculated dimension on Filters.

    This is how I did it in HoleFoods:

    and this is how I did it in HoleFoods2:

    Finally, add an ApplyVariable control on one of your widgets with "*" as target. Selecting a region will filter both widgets.

    %LAST evaluates a measure or numeric expression for the last non-empty member of a set, so I do not think that is what you want.

    I am using %LASTCHILD, but you cannot append it to [DateOfSale].[Actual].[MonthSold].Members because %LASTCHILD only works on specific member. For the moment I found this solution:

    WITH  MEMBER [DateOfSale].[LastDayOfMonth] AS '[DateOfSale].[Actual].[MonthSold].CurrentMember.LASTCHILD' 

    SELECT [Measures].[Amount Sold] ON 0,


    The resulting headers are ugly and I have not yet been able to successfully use [DateOfSale].[Actual].[MonthSold].CurrentMember.Properties("Name")

    I do not think there is a way to ask the user to select a user before displaying the dashboard. 

    Maybe you can try setting the filter as required in the dashboard settings and as default value using a run-time variable. The run-time variable should return the current username. To return the current user start from the COS $username special variable. 

    In the options for pivot table you can use Count instead of Grand Total as Summary. However, you will have to choose between the Grand Total and Count. If you calculate the grand total in some other way (for example by placing another [Outlet].[H1].[Region].Members and then Gear icon > Compute Aggregate > SUM), that COUNT will also count the cell with the Grand Total. 

    Otherwise you can create a "Row Number" calculated measure with the expression below and place it on measures next to Revenue


    Hello Samuel,

    This is definitely not a newbie question, and ParallelPeriod could be very useful for the MDX you need. However, your pivot table has to be dynamic enough to work with different periods (Quarter, Month, Week). For this reason we might have to work on a KPI. I have two questions:

    1) If I understand you correctly you want to be able to select any period (such as Jan-2014 or Week 2 of 2015), not only the current one (such as Nov-2016 or Q4-2016). Is that correct?

    2) If so, what filters you like to appear on your dashboard? For example, would you like to have a dropdown menu to select Quarter/Mont/Week and a "dynamic" filter showing a Quarter/Month/Week for all years in your data?

    Hello Alexandre,

    Yes, DeepSee supports hierarchical structures. 

    DeepSee models are based on cubes, which define elements such as Measures, Dimensions, Listings, etc. The hierarchical structure in your question can be modeled using a Dimension containing one hierarchy with three levels. The levels in the hierarchy are, in order, the Group, Name, and Code levels. Once you define this model in Architect you will be able to use the hierarchical structure in DeepSee Analyzer.

    This is the link to a video in the Video Portal. The video I link explains how to define Dimensions in DeepSee cubes: http://video.intersystems.com/video/Video.Pages.VideoLibrary.cls?video=2663141302001&playlistid=2621105743001

    This is the link to the "Defining DeepSee Models" book in the documentation: http://docs.intersystems.com/latest/csp/docbook/DocBook.UI.Page.cls?KEY=D2MODEL

    I recommend reading section 2.2 "Dimensions, Hierarchies, and Levels". There you will find an example based on the Patients cube where an Age dimension (AgeD) includes one hierarchy with three levels ordered from the least to the most granular:

    I also recommend reading section 4.6 "Defining Hierarchies Appropriately".