Database Reference
In-Depth Information
■
often, reporting software will add the aggregations for you. When this happens, do not include the
aggregate values in your SQL code. This is also true for parameters; some applications filter the results using
parameters only after all the results have been returned to the application. A common example for both of these can
be found within Microsoft Excel. Although it is possible to submit parameter values and consume aggregate data,
you are usually better off letting Excel do the aggregation and filtering for you after all the data has been returned.
Note
exerCise 13-1. Creating report Queries
in this exercise, you create your own custom report query similar to the one we just walked though. To
make this process simpler, we do not include parameters at this point. You combine data from three tables,
aggregate the values, and then format your results. if you build the query a little at a time, as described in
this chapter, you will find your query will be completed in no time at all!
1.
Review the data in the FactSales table by executing the following SQL code in a
query window:
SELECT * from FactSales
Review the data in the DimStores table by executing the following SQL code in a
2.
query window:
SELECT * from DimStores
Review the data in the DimDates table by executing the following SQL code in a
3.
query window:
SELECT * FROM DimDates
Combine the data from these tables into a report query that returns store names,
4.
store iDs, order dates, and the sum quantity by store and date. Your results should
look like Figure
13-16
.
if you get stuck, the answers for this exercise are in the downloadable example files for this topic. Look in
the folder for Chapter 13.
Figure 13-16.
The results needed for the sales by stores report
