There is an old saying, A picture is worth a thousand words. Unfortunately, all too often, a picture needs a little helping text to explain it. In the reporting world, it is common to want to display multiple objects, most commonly a chart and a table or matrix, on the same report, both sourced from the same data. This technique is known as having multiple data regions on the same report.
For this report, we want to plot the total sales by year for each of the sales territories in our sales fact table. This will naturally fit well into a chart, in order to quickly perform a comparison between territories. However, the management also needs to know the exact values for each territory by year.
To accomplish this, we'll place two objects on the report, both a chart and a matrix. For the query, we'll use the following:
SELECT c.[Sales Territory]
, YEAR(s.[Invoice Date Key]) AS [Invoice Year]
, SUM(s.[Total Excluding Tax]) AS...