NonEmptyCrossjoin
Returns the cross product of two or more sets as a set, excluding empty tuples and tuples without associated fact table data.
Syntax
NonEmptyCrossjoin(«Set1», «Set2»[, «Set3»...][, «Crossjoin Set Count»])
Remarks
The NonEmptyCrossjoin function returns the cross product of two or more sets as a set, excluding empty tuples or tuples without data supplied by underlying fact tables; because of this, all calculated members are automatically excluded. If «Crossjoin Set Count» is not specified, all specified sets are crossjoined and empty members are excluded from the resulting set. If «Crossjoin Set Count» is specified, the number of sets specified in «Crossjoin Set Count», starting with «Set1», are crossjoined. The remaining sets are used to determine, in the resulting crossjoined set, which members are considered nonempty.
For example, you want to view the total unit sales for each store in Beverly Hills involved with the Big Time Savings promotion, but only for those customers based in California. However, the following Multidimensional Expressions (MDX) statement returns a set containing the unit sales for all the cities in California with customers, grouped by the stores in Beverly Hills, involved with the Big Time Savings promotion; this set is a cross product of the three sets. The unit sales returned are only for the cross product of the three sets; the gross sales of the stores in Beverly Hills involved with the Big Time Savings promotion are not returned, but the individual unit sales involved with just the promotion itself, for each store and customer city, are.
NonEmptyCrossJoin([Store].[Beverly Hills].Children, [Customers].[CA].Children, {[Promotions].[Big Time Savings]})
The previous example was too narrow in scope to accomplish the task. By contrast, the following MDX statement simply crossjoins the first two sets and removes nonempty members from the returned set. Because the {[Promotions].[Big Time Savings]} set is not used, the preceding MDX statement is too wide in scope; the MDX statement includes too many tuples to accomplish the goal.
NonEmptyCrossJoin([Store].[Beverly Hills].Children, [Customers].[CA].Children)
The following MDX statement, by using the «Crossjoin Set Count» parameter, returns a set containing the unit sales for all of the cities in California with customers grouped by the stores in Beverly Hills; this set is a cross product of the first two sets. Only those members in the crossjoined set that participated in the Big Time Savings promotion are returned, however, accomplishing the task. The first two sets, specified in the «Crossjoin Set Count» parameter, were crossjoined, while the third set was used to determine which members of the crossjoined set were to be considered when determining if a crossjoin set member contained data.
NonEmptyCrossJoin([Store].[Beverly Hills].Children, [Customers].[CA].Children, {[Promotions].[Big Time Savings]},2)
The benefits of the NonEmptyCrossjoin function include faster, more efficient processing of crossjoins involving more than two sets, as well as the simpler syntax provided by the function. The same results can be obtained less effectively, using the Filter, Crossjoin, and IsEmpty functions as shown in the following MDX statement:
Filter(Crossjoin([Store].[Beverly Hills].Children, [Customers].[CA].Children), NOT IsEmpty([Promotions].[Big Time Savings])
As additional sets are added, the use of Filter, Crossjoin, and IsEmpty becomes increasingly impractical, because each Crossjoin statement is nested inside another Crossjoin statement to return the same results. For example, adding the [Product].Children set to the returned set using the NonEmptyCrossjoin function resembles:
NonEmptyCrossJoin([Store].[Beverly Hills].Children, [Customers].[CA].Children, [Product].Children, {[Promotions].[Big Time Savings]}, 3)
Performing the same functionality with the Filter, Crossjoin, and IsEmpty functions, on the other hand, resembles the following:
Filter(Crossjoin(Crossjoin([Store].[Beverly Hills].Children, [Customers].[CA].Children), [Product].Children), NOT IsEmpty([Promotions].[Big Time Savings]))
The preceding MDX statement is slower in execution and less readable than its NonEmptyCrossjoin-based counterpart.
Example
The following statement returns the set containing all the unit sales for all of the cities in California with customers grouped by the stores in Beverly Hills that participated in the Big Time Savings promotion:
NonEmptyCrossJoin([Store].[Beverly Hills].Children, [Customers].[CA].Children, {[Promotions].[Big Time Savings]},2)