Gael Duhamel [MVP]

To follow up my post on Power Pivot and Commerce Server, I decided to show you how to achieve three different dashboards and give to you the excel file.

The goal here is not to show you how to create the data source, the relationships and so. You can find very valuable information on a lot of web sites:

• Créer des rapports simples et efficaces pour Commerce Server grâce à PowerPivot (aka Gemini)
• Tutoriel PowerPivot pour Excel – Partie 1/2 : Construction d’un modèle PowerPivot à partir de plusieurs sources de données
• Power Pivot : How To

To achieve theses scenarri you must have:

• Excel 2010
• Addin Power Pivot pour Excel 2010
• A Sql connection to the Commerce Server databases

The databases

To create our different dashboard, we need to connect to two databases:

• Profiles (here SPGael_profiles) : Owns all the profile information for the customers
  • In this table, I have a custom field to store the birthday date. All the others data are native in Commerce Server
• Transactions (here SPGael_transactions) : Owns all the orders information
  • All the data are the native one with Commerce Server.

Select the tables :

• Orders 
  • DiscountsApplied : Owns all the marketing discounts applied on the orders;
  • LineItems : Owns all the line items in the orders;
  • OrderAddresses : Owns all the adresses used for the check out (billing and shipping);
  • OrderForms : Owns the container for the line items (one order has from 1 to n OrderForms and  one OrderForms has from 1 to n LineItems);
  • PurchaseOrderPayments : Owns the different payment methods used during the checkout;
  • PurchaseOrders : Owns the general information about the orders;
  • Shipments : Owns the shipping information used during the checkout;
  • ShippingDiscounts : Owns the shipping discounts applied on an order;
• Customers 
  • UserObjet : Owns the customer information

General dashboard

Order’s dashboard

Customers’ dashboard

Some useful tricks to create your dashboards

Here are some useful trick I’ve used to create my 3 dashboards:

• Top x
  • Click on the filter report;
  • Select filter by value;
  • Select the TOP10 option;
  • In the dialog box choose set the value;

• In a pie, to choose the values which move to the mini plot:
  • Click right on the pie chart
    • Choose the horizontal axis data to edit
  • In the dialog box 
    • In the drop down list selec “Percentage value”
    • In the combo box select the threshold value

• Hide the filter buttons
  • Click right on the filter button
  • Select “Hide all field buttons on chart’”

• Connect several charts/pivot table to the same sclicer
  • Select a slicer
  • In the slicer’s option tab, select  « Pivot table connections »
  • In the dialog box tick the pivot table/chart’s checkbox you have to connect
  • Click ok