Data Analytics: Exploiting the Data Warehouse - Data Warehouse Systems: Design and Implementation - page 369

Fig. 9.15 Definition of the dashboard for the Northwind case study in Reporting
Services using SQL Server Data Tools
GROUP BY DATEPART(yy, T.Date), DATEPART(mm, T.Date),
DATENAME(mm, T.Date) )
SELECT MS.Year, MS.Month, MS.MonthName, MS.MonthlySales,
PYMS.MonthlySales AS PreviousYearMonthlySales,
MS.MonthlySales / PYMS.MonthlySales AS Percentage
FROM MonthlySales MS, MonthlySales PYMS
WHERE MS.Month = PYMS.Month AND PYMS.Year = MS.Year - 1 AND
NOT (MS.Year = 1998 AND MS.Month = 5)
The above query computes the monthly sales amount in a temporary table
denoted MonthlySales . In addition to the year and the month, the table
obtains in the column MonthName the first three letters of the month name
to be used for the labels of the x -axis of the chart. Then, the main query
joins this table twice to obtain the monthly sales amount together with that
of the previous year. The last line of the main query excludes the values from