Data Warehousing and Business Intelligence

9 March 2014

Arguments of the nonempty function

Filed under: Analysis Services — Vincent Rainardi @ 9:02 am

The nonempty function has two arguments: nonempty(a,b).
b is used to evaluate the emptiness.
b can be more than 1. (Note 1)
If b is not supplied SSAS will use the first member. (Note 2)

Example:

nonempty

a) nonempty(country, Risk) evaluates Risk; it returns France and Italy.
b) nonempty(country, Return) evaluates Return; it returns Spain and Italy.
c) nonempty(country, {Risk, Return}) evaluates both and returns all 3.
d) nonempty(country) evaluates Risk, like a). [Note 3]

Notes:
1. To be correct: “b can be a set with more than 1 member”.
2. To be correct: “If b is not supplied it will evaluate the current coordinates of the hierarchies and measures”
3. That is if we have {Risk, Return} on the column axis, i.e. the query is “select {Risk, Return} on columns, …”

Reference:
SSAS MDX Function Reference: link

Working MDX:
(on Adventure Works 2012 cube)

with member x as ([Reseller Sales Amount], [Date].Calendar.PrevMember)
member y as [Reseller Sales Amount]
select {y, x} on columns,
nonempty(Reseller.Reseller.Reseller, {y, x}) on rows
from [Adventure Works]
where [Date].[Calendar].[Calendar Quarter].&[2006]&[4]

Advertisements

2 Comments »

  1. Hi Hi , Based on your experience, Do you think Qlikview can replace an SSAS cube with SSRS reports assuming data is limited and can be handled by Qlikview?. Will be able to write all calculated measures in Qlikview and build hierarchies as well?. Please share thoughts?.

    Comment by BAlachandar Ganesan — 17 March 2014 @ 4:52 pm | Reply

    • Hi Balachandar, QlikView can’t replace all of the calculated measures in SSAS, for example: Existing, Hierarchize, DrillUpMember.

      Comment by Vincent Rainardi — 17 March 2014 @ 7:41 pm | Reply


RSS feed for comments on this post. TrackBack URI

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s

Create a free website or blog at WordPress.com.

%d bloggers like this: