Understanding the GROUP BY clause

Aggregate data is what reports are made of; speaking ANSI SQL, this means GROUP BY. MicroStrategy applies GROUP BY to all the attributes present on a report grid. Well, this is not completely correct, but we begin with this simple concept:

Every attribute on a grid is reflected in the GROUP BY clause of the resulting SELECT statement.

Hence, we have:

• Metrics that represent the aggregation (sum, count, and so on) of facts

• Attributes BY which we GROUP the data

I hope this is clear enough, because this concepts will return every now and then with metrics. In this recipe, anyway, we'll see an example of how it works.

Getting ready

We are using now the SalesAmount metric and the Product attribute. Before going on we need to establish a connection between the fact table and the attribute table, so that MicroStrategy knows how to JOIN them.

The connection is represented by the FK (ProductKey) in FactInternetSales. In other words, the ID form of Product is present in both...