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
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.
Hi, Dan!
Have you seen this conversation?
There is USA map in DeepSee Web which shows how you can tie any data to polygons and color it according to the data value.
I have seen it, but I need this: https://developers.google.com/maps/documentation/javascript/examples/lay...
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:
For details, see the class reference for %DeepSee.Utils.
Cool!
the VM I was experimenting with, is still on 2015.2. Time to upgrade.
Thanks Joe!