Daniel Kutac · May 31, 2016

API to retrieve MDX from a save pivot?


is there any API available that would return an MDX query string for a given saved pivot name? I could not find anything except for pivots with manual MDX. But that's not my case, I need to get MDX for any pivot.




0 488
Discussion (9)2
Log in or sign up to continue

Here it is:

/// Returns MDX string used to create pivot.<br>
/// <b>pPivotName</b> - fullname of pivot. Eg: "KPIs & Plugins/HoleFoods.pivot". Case insensitive.<br>
/// <b>pStatus</b> - Status of query execution.<br>
/// <b>MDX</b> - MDX query with filters<br> /// <b>BaseMDX</b> - MDX query without filters<br>
ClassMethod GetMdx(pPivotName As %String, Output MDX, Output BaseMDX) As %Status
    #dim tPivot As %DeepSee.Dashboard.Pivot
    #dim tPivotTable As %DeepSee.Component.pivotTable
    set MDX = ""
    set BaseMDX = ""

    set tPivot = ##class(%DeepSee.UserLibrary.Utils).%OpenFolderItem(pPivotName,.pStatus)
    return:'$IsObject(tPivot) $$$OK
    return:$$$ISERR(pStatus) pStatus

    set tPivotTable = ##class(%DeepSee.Component.pivotTable).%New()
    set pStatus = tPivot.%CopyToComponent(tPivotTable)
    return:$$$ISERR(pStatus) pStatus

     // returns tQueryText - mdx without filters
    set rs = tPivotTable.%CreateResultSet(.pStatus, .tParms, .tFilterInfo, .tAdvancedFilters, .BaseMDX)
    return:$$$ISERR(pStatus) pStatus

    set pStatus = tPivotTable.%GetFilterInfo(.tFilterInfo, .tAdvancedFilters)
    //return:$$$ISERR(pStatus) pStatus

    if (($d(tFilterInfo)=0) &&($d(tAdvancedFilters)=0)) {
        set MDX = BaseMDX // no filters, so we're good
    } else {
        // returns tQueryText - mdx with filters
        set rs = tPivotTable.%CreateResultSet(.pStatus, .tParms, .tFilterInfo, .tAdvancedFilters, .MDX)
        return:$$$ISERR(pStatus) pStatus

    // Remove \n
    set MDX = $TR(MDX, $C(10), "")
    set BaseMDX = $TR(BaseMDX, $C(10), "")

    return pStatus

It is a part of MDX2JSON REST API.

Why do you need MDX expression as a query string, through?

Thank you Eduard!

You saved me a lot of time! I am working on a small project where I am implementing Google HeatMap as a new way of displaying DeepSee geographical data. and for that, I just want to supply the source of data - the saved pivot definition, retrieve the MDX and execute it and build array of GEO points.

You can use MDX2JSON or DeepSee REST API for pivot execution -> JSON conversion.

There is a heatmap option in the "enhanced map portlet". I've uploaded it with a demo dashboard here.

In 2016.1, we added several methods to %DeepSee.Utils to get and execute MDX from a pivot table:

  •  %GetMDXFromPivot(): returns MDX text for a pivot table. Optionally executes the query.
  •  %ExecutePivot(): executes the MDX for a pivot table. Optionally returns the associated %DeepSee.ResultSet.
  •  %GetResultSetFromPivot(): returns the %DeepSee.ResultSet for a pivot table.  Optionally executes the query.

For details, see the class reference for %DeepSee.Utils.


the VM I was experimenting with, is still on 2015.2. Time to upgrade.

Thanks Joe!