11 November 2016

The totals and the NonEmptyCrossJoin function

Let’s suppose we need a report with the following structure:

Client1 Product1 Price2 Amount1
Client1 Product2 Price1 Amount2
Client1 TotalAmount1
Client2 Product3 Price2 Amount3
Client2 Product2 Price1 Amount4
Client2 Product1 Price1 Amount5
Client2 TotalAmount2
AllClients GrandTotalAmount

Excel is able to generate, but it’s laggy. Let’s start from the query without totals:

Let’s get the total for each client:

Let’s unite two queries:

Boom, it looks like the performance of the query will last forever. Why do the two queries perform way faster separately than together? We should blame Union, also known as the +, function for that. Separately, we throw the empty members away dynamically. When it comes to two big crossjoined sets to be united before the query start it takes time, depending on the number of members, not the query result. Luckly, there is a NonEmptyCrossJoin function which will filter the empty members out dynamically. Thus, the performance is okay once again:

Happy querying. ;)


© Danylo Korostil