TM1 Rest api - MDX query for zero suppression

[kavitha2002]

Hello Everyone,

I would like retrieve the cellsets with zero suppressed using MDX query. I dont have any idea on how to achieve.

Any hints or ideas, would be appreciated. Thanks

[macsir]

SELECT
NON EMPTY {[DimCP_Population].[Dr Key].[Dr Key]}
ON rows,
{
[Measures].[PatientCnt] ,[Measures].[TotalPatientCnt]
,[Measures].[PrevalencePercent]

}
ON columns
FROM [StrategyCompanionDWCube]

[kavitha2002]

Hi @macsir,

Thanks a lot, it worked...

I want the zero suppression for both columns and rows, so added NON EMPTY for column as well in below.. its working..

SELECT
NON EMPTY {[DimCP_Population].[Dr Key].[Dr Key]}
ON rows,
NON EMPTY {
[Measures].[PatientCnt] ,[Measures].[TotalPatientCnt]
,[Measures].[PrevalencePercent]

}
ON columns
FROM [StrategyCompanionDWCube]


Is there a way to get only the row elements which is non-zero ?? Is it possible??

[macsir]

I don't quite get your point of getting only the row elements which is non-zero???

[Wim Gielis]

I do understand it and it’s something I would like to do as well but in the Cube viewer. It’s the set of elements within the rows (and columns) dimension(s) that survive the zero suppression operation, and only those elements. It’s so terribly inefficient to snapshot to Excel and copy-paste back the found elements. Or worse, select those elements manually in the Subset Editor. Or write MDX expressions for it.

I tried to write an AutoHotkey script for it, to no avail.

In PA we can do a “Keep”, though.

You get the idea

[lotsaram]

I do understand it and it’s something I would like to do as well but in the Cube viewer. It’s the set of elements within the rows (and columns) dimension(s) that survive the zero suppression operation, and only those elements. It’s so terribly inefficient to snapshot to Excel and copy-paste back the found elements. Or worse, select those elements manually in the Subset Editor. Or write MDX expressions for it.

Yep! The fact that the Architect/Perspectives cube viewer doesn't allow selecting row and column members to be able to copy to the clipboard is quite an annoying design fail.