Task 6:

Add grand totals

Each number that is displayed in the cross tab represents the sales total of a particular product for a particular state. In this procedure, you add grand totals to display the total sales of all products for each state, the total sales of each product, and the total of all sales across products and states.

1	Choose Layout to return to the layout editor.

2	Select the cross tab by clicking on the tab in the lower left corner of the cross tab. Make sure you select the entire cross tab, not just a part of it. Property Editor displays the general properties of the cross tab, as shown in Figure 17‑12.

Figure 17‑12

Property Editor displays the general properties of the selected cross tab

3	In Property Editor, choose the Row Area tab.

4	Choose Grand Totals, then choose Add.

5	On Grand Total, shown in Figure 17‑13, use the default values, then choose OK.

Figure 17‑13

Creating grand totals in the cross-tab row area

A new row is added to the bottom of the cross tab, as shown in Figure 17‑14. In this row is a label that displays Grand Total and a data element that displays [EXTENDED_PRICE].

Figure 17‑14

Cross tab including a new row to display grand totals

6	In Property Editor, choose the Column Area tab.

7	Choose Grand Totals, then choose Add.

8	On Grand Total, use the default values, then choose OK.

A new column is added to the cross tab, as shown in Figure 17‑15.

Figure 17‑15

Cross tab with a new column to display grand totals

9	Preview the report. Grand totals appear in the last row and last column of the cross tab.

How to create data sets for a multi-dataset cube

In this procedure, you create two data sets:

n	A fact data set, SalesTotal, to retrieve the data for calculating the sales totals

n	A dimension data set, Productlines, to retrieve data about the product lines

Note that we are not creating a separate data set for the year dimension, as is typical in a star schema. It is sometimes too complicated to create a pure star schema design when working with data stored in an OLTP system.

1	Create a new data set named SalesTotal. Create the following query:

select CLASSICMODELS.ORDERDETAILS.PRODUCTCODE,

CLASSICMODELS.ORDERS.SHIPPEDDATE,

CLASSICMODELS.ORDERDETAILS.QUANTITYORDERED * CLASSICMODELS.ORDERDETAILS.PRICEEACH as "EXTENDED_PRICE"

 

from CLASSICMODELS.ORDERDETAILS, CLASSICMODELS.ORDERS

where CLASSICMODELS.ORDERS.ORDERNUMBER = CLASSICMODELS.ORDERDETAILS.ORDERNUMBER

and CLASSICMODELS.ORDERS.STATUS = 'Shipped'

n	The query retrieves PRODUCTCODE data because it is the key to later link to the PRODUCTLINE dimension.

n	The query retrieves SHIPPEDDATE data to use for the year dimension.

n	The query creates a calculated column, EXTENDED_PRICE, whose values will be aggregated to calculate the sales totals.

n	The query creates a join between the Orders and OrderDetails tables to get all the necessary data about the orders. Because the data set is retrieving data from an OLTP database, joins are unavoidable.

n	The query contains a filter condition to retrieve order data for orders that have been shipped, and therefore, that have been paid.

2	Create a new data set named Productlines. Create the following query:

select CLASSICMODELS.PRODUCTS.PRODUCTLINE,

CLASSICMODELS.PRODUCTS.PRODUCTCODE

from CLASSICMODELS.PRODUCTS

n	The query retrieves PRODUCTLINE data to use for the PRODUCTLINE dimension.

n	The query retrieves PRODUCTCODE data because it is the key that the SalesTotals data set will need to reference.

How to create a multi-dataset cube

1	In Data Explorer, right-click Data Cubes, then choose New Data Cube.

2	On the Dataset page of Cross Tab Cube Builder, supply the following information, as shown in Figure 17‑16:

1	In Name, specify a descriptive name, such as Sales Cube, for the cube.

2	In Primary dataset, select SalesTotal. In a multi-dataset cube, the fact data set that retrieves the data to calculate measures is the primary data set.

Figure 17‑16

Name and primary data set specified for a cube

3	Choose Groups and Summaries to define the dimensions and measures for the cube. The Groups and Summaries page, shown in Figure 17‑17, displays the available data sets and fields.

Figure 17‑17

Groups and Summaries page shows the available data sets and fields

4	Define the year dimension of the cube.

1	Under the Sales Totals (Primary) data set, drag SHIPPEDDATE and drop it under Groups (Dimensions) in the drop location that displays (Drop a field here to create a group).

2	On Add Group, use the default group name.

Group Level displays the different ways to group the dates. To display the dates as they appear in the data source, select Regular Group. To group the dates by any of the time periods, select Date Group.

3	Select Date Group, then select year, as shown in Figure 17‑18.

Figure 17‑18

Group Level showing the year group selected

4	Choose OK to save the year dimension.

5	Define the product line dimension.

1	Under the Productlines data set, drag PRODUCTLINE and drop it under Groups (Dimensions) in the drop location that displays (Drop a field here to create a group). On Add Group, use the default group name.

2	Under the Productlines data set, drag PRODUCTCODE and drop it on the PRODUCTLINE dimension. This action creates a hierarchical relationship between PRODUCTLINE and PRODUCTCODE.

6	Define the cube’s measure. Under the Sales Totals (Primary) data set, drag EXTENDED_PRICE and drop it under Summary Fields (Measures) in the drop location that displays (Drop a field here to create a summary field).

The Groups and Summaries page, shown in Figure 17‑19, displays the dimensions and measure that you define.

Figure 17‑19

Groups and Summaries page shows the dimensions and measure

7	Link the data in the dimensions with the fact data set.

1	Choose Link Groups. The Link Groups page displays the Productline dimension you created and the primary (fact) data set.

2	Link the PRODUCTCODE field in both items, as shown in Figure 17‑20.

Figure 17‑20

Link Groups page shows how the dimension and fact data sets are linked

8	Choose OK to save the cube. You can now build a cross tab that uses data from this cube.

Figure 17‑21 shows a cross tab that uses the year and PRODUCTLINE dimensions and the EXTENDED_PRICE measure from the cube.

Figure 17‑21

Cross tab design

Figure 17‑22 shows the generated cross tab.

Figure 17‑22

Cross tab output