topic Re: OLAP Viewer: NON EMPTY CROSS JOIN in SAS Web Report Studio

OLAP Viewer: NON EMPTY CROSS JOIN

RSB — Tue, 18 Jan 2011 15:00:25 GMT

I am getting a crossjoin in my query when I place the dimensions [Customer Type] and [Customer_Name] in the rows and the measure on the columns, all the customers are repeating for each customer type. To avoid this I can include NON EMPTY in my MDX query for example :

SELECT
{ [Measures].[Collateral Value] } ON COLUMNS,
NON EMPTY CrossJoin({ [Customer Type].[All Customer Type].Children }, { [Customer_Name].[All Customer_Name].Children }) ON ROWS
FROM [CUBE_COLLATERAL]

The problem is I have to surface the cube in the OLAP viewer as a data exploration, and since I am fairly a beginner in MDX i am not able to find a way how and where to specify the NON EMPTY during the cross join in the OLAP viewer or is there any way to create a custom measure/ member as a work around for this.

Thanks in advance.

Re: OLAP Viewer: NON EMPTY CROSS JOIN

AngelaHall — Fri, 21 Jan 2011 16:49:34 GMT

I know from SAS Web Report Studio 4.3, you can deselect the option 'Include members with only missing values' from the Data Menu.

Haven't been in Web OLAP Viewer in quite awhile, but there probably is a setting in that to do something similar.

~ Angela

Re: OLAP Viewer: NON EMPTY CROSS JOIN

twocanbazza — Mon, 24 Jan 2011 18:55:35 GMT

There is an option on the Data Exploration... Have a look under View-> Preferences...

I would also say you should re-consider using Data Explorations as they are been phased out in the 4.3 clients, ie OLAP Viewer ceases to exist. So I have been told by our local SAS office.

Barry