Using Drillthrough to View Product Sales or Shopping Basket Events

When you use drillthrough to view the details of the Product Sales or Shopping Basket Events report, more items might appear in the details than would be expected from the count indicated in the PivotTable report. This behavior can occur when a product is mapped to multiple categories. This is by design: the product exists in multiple categories under the category currently being examined.

To display correct rollup information in the PivotTable, the Product Sales and Shopping Basket Events reports introduce extra information into the details data. Commerce Server uses this extra data to correctly calculate the aggregate data when it examines sales and events per product category. When you use the drillthrough feature, this hidden data is exposed.

If you want to ensure that only one row is returned for each product in the drillthrough results, you must ensure that each product is mapped to at most one product category. This can be accomplished by using the CSDW_MakePrimaryProductCategoryInfo stored procedure to map each product to its primary parent category. This ensures that each product will be mapped to one and only one category in the category hierarchy.

To specify a product-to-category mapping based on the primary parent category, issue the following command in the SQL Data Warehouse. The Report preparation DTS task will automatically execute this procedure when it is run, creating the new product category tree.

UPDATE StoredProcInfo set [Name] = N'CSDW_MakePrimaryProductCategoryInfo' where [Name] = N'CSDW_MakeProductCategoryInfo'

Copyright © 2005 Microsoft Corporation.
All rights reserved.