1.
Click on insert and select the cross tab item.
2.
Place the cross tab in the report header for this example.
3.
Right click on the cross tab and select Cross-Tab Expert.
4.
Expand the “Customers” table under the Available Fields, select the
“ContactName” field, and add it to the rows collection.
5.
Expand the “Products” table under the Available fields, select the
“ProductName” field, and add it to the rows collection.
6.
Expand the “Orders” table under the Available fields, select the
“OrderDate field, and add it to the Columns collection.
7.
Expand the “Orders_Details” table under the Available fields, select the
“UnitPrice” field, and add it to the summarized fields.
Figure 1: The Cross-Tab Expert
Let’s go into more detail about what we just did. First, we
added 2 fields to the rows “collection”. The first field that was added was
“Customers.ContactName”. For each contact name in the returned data set, a row
will be printed in the cross tab. The second field added was the
“Products.ProductName” field. By adding a second row to the collection, a “sub
row” will be inserted for each product that the customer ordered. The next
field that was added was the “Orders.OrderDate”, to the columns collection.
This field will print a column for each unique value. We will go more into
options for this group later in the article. The last field that was added was
the “Orders_Details.UnitPrice" to the Summarized Fields collection. This
collection is where your data is summarized.
After you have completed those steps you can preview the
cross tab, but this is not exactly what we may have expected to see.
Figure 2: Customer Product Total Cross Tab Default
The first thing we need to work on is the grouping. The
requirements we specified for this report were to display the yearly totals for
each product that a customer ordered.
1.
For grouping the orders, right click on the Cross Tab and select “Cross Tab
Expert”.
2.
Click on the “Orders.OrderDate” field in the Columns Collection.
3.
Click on the “Group Options…” button.
4.
Select “for each year” from the "column will be printed" box.
Figure 3: Columns Group Options
While we are in the section expert, we need to change how
the “Order_Detais.UnitPrice” is being summarized. The default is “Count” and we
need a “Sum”
1.
Ensure you are in the “Cross Tab Expert”.
2.
Click on the “Order_Details.UnitPrice” field under the Summarized fields
collection.
3.
Click the “Change Summary…” button.
4.
Select “Sum” in the "calculate this summary" box.
Figure 4: Change Summarized Fields menu
At this point you should have a report that looks something
like this:
Figure 5: Customer Product Total Cross Tab Fixed
Group Options
