Execute the following Microsoft SQL Server T-SQL database script in SSMS Query Editor to demonstrate the preparation of a multi-dimensional sales analysis report applying a CTE - Common Table Expression - with 7 INNER JOINs. The following dimensions are used in the analysis: GeoRegion, Country, SalesPerson, Category, SubCategory, Product, Color, Size.

-- SQL cte - SQL inner join - SQL multidimensional report - SQL multi-table queries

USE AdventureWorks2008;

WITH cteSalesDetail AS (

SELECT

   pp.LastName+', '+pp.FirstName AS SalesPerson,

   convert(date,soh.OrderDate) AS [OrderDate],

   SUM(sod.OrderQty) AS Qty,

   SUM(sod.LineTotal) AS LineTotal,

   st.[Group] AS [GeoRegion], 

   st.Name AS Country,

   pc.Name AS Category,

   psc.Name AS SubCategory,

   prod.Name AS Product,

   COALESCE(prod.Color,'') AS Color,

   COALESCE(prod.Size, '') AS Size,

   soh.SalesOrderNumber AS [OrderNumber]

FROM Sales.SalesPerson AS sp

   INNER JOIN Sales.SalesOrderHeader AS soh

      ON sp.BusinessEntityID = soh.SalesPersonID -- Primary Key - Foreign Key JOIN

   INNER JOIN Person.Person AS pp

      ON pp.BusinessEntityID = sp.BusinessEntityID -- PK-FK JOIN

   INNER JOIN Sales.SalesOrderDetail AS sod

      ON sod.SalesOrderID = soh.SalesOrderID

   INNER JOIN Production.Product AS prod

      ON sod.ProductID = prod.ProductID

   INNER JOIN Sales.SalesTerritory AS st

      ON st.TerritoryID = sp.TerritoryID

   INNER JOIN Production.ProductSubcategory AS psc

      ON prod.ProductSubcategoryID = psc.ProductSubcategoryID

   INNER JOIN Production.ProductCategory AS pc

      ON pc.ProductCategoryID = psc.ProductCategoryID

GROUP BY pc.Name, soh.OrderDate, soh.SalesOrderNumber,

    psc.Name, prod.Name,

    pp.LastName+', '+pp.FirstName,

    st.CountryRegionCode, st.Name,st.[Group],

    prod.Color, prod.Size

HAVING st.[Group] != 'North America' )

SELECT

  GeoRegion, Country, SalesPerson, Category, SubCategory, Product,

  Color, Size,

  QtyTotal=SUM(Qty),

  DollarTotal='$'+convert(varchar, convert(money,SUM(LineTotal)),1)

FROM cteSalesDetail

GROUP BY GeoRegion, Country, SalesPerson, Category, SubCategory,

         Product, Color, Size

ORDER BY GeoRegion, Country, Category, SubCategory, Product,

             Color, Size

GO

/* Partial results

GeoRegion   Country     Category    SubCategory       Product

Europe      Germany     Bikes       Road Bikes        Road-750 Black, 52

Europe      Germany     Bikes       Road Bikes        Road-750 Black, 58

Europe      Germany     Bikes       Touring Bikes     Touring-1000 Blue, 46

Europe      Germany     Bikes       Touring Bikes     Touring-1000 Blue, 50

Europe      Germany     Bikes       Touring Bikes     Touring-1000 Blue, 54

*/