Question
· May 31, 2016

API to retrieve MDX from a save pivot?

Hello,

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.

TIA!

 

Dan

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.

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.