Creating Reports For Discounts
If you’re running an E-Commerce store in Kentico, there’s a good chance you’ve used discount functionality. Seeing data about the various discounts you’ve applied can be difficult as this information is stored as XML markup in the database table for orders. In its raw form, data about discounts is stored on an order-by-order basis, and not in a very visually pleasant form. This article will go over the process of creating a report to display aggregated data about discounts.
Constructing a Query
For our first discount column to review, let’s look at the OrderItemProductDiscounts column from the COM_OrderItem table. This holds Catalog discounts and Volume discounts, which affect the unit price of the products in the cart.
In order to make a report about this data, we need a query that will pull it from the database. In order to simplify the XML parsing that we have to do later, we’ll create a table variable that holds relevant joined table data, and convert the NVARCHAR field OrderItemProductDiscounts to an XML column.
--Create Table
DECLARE @ProductDiscountsTable TABLE(
OrderItemId INT,
ProductDiscountXml XML,
OrderID INT,
OrderGrandTotal DECIMAL(18,2)
);
--Populate Table
INSERT INTO @ProductDiscountsTable (OrderItemID, ProductDiscountXml, OrderID, OrderGrandTotal)
(
SELECT OrderItemID
,cast(OrderItemProductDiscounts as XML)
,OrderID
,OrderGrandTotal
FROM COM_OrderITem
JOIN COM_Order ON OrderItemOrderID = OrderID
JOIN COM_OrderStatus ON OrderStatusID = StatusID
WHERE StatusOrderIsPaid=1
);
Here, our table variable contains the IDs of the order item and the order it belongs to, the XML summary of discounts, and the grand total of the order. You may want to include different columns depending on what data you want to display in your report. We’re casting the ProductDiscountXML column from NVARCHAR (its datatype in COM_OrderItem) into an XML column, which is necessary for the operator we will use for looking through this XML. There’s also a join to the COM_OrderStatus table, so that we can make sure only paid orders are being taken into account.
The CROSS APPLY operator will allow us to essentially treat the XML like a nested table within each row, and select from it.
The next part of the query will pull from the plain columns in our table, as well as the data in ProductDiscountXml.
--Select from table and XML
SELECT Child.discountName AS [DiscountName]
,SUM(Child.discountValue) AS [TotalDiscount]
,SUM(PDT.OrderGrandTotal) AS [TotalSpentOnOrders]
,COUNT(PDT.OrderID) AS [NumberOfOrders]
FROM @ProductDiscountsTable PDT
CROSS APPLY
(
SELECT Summary.c.value('Name[1]','varchar(max)') AS discountName
,Summary.c.value('Value[1]','decimal(18,2)') AS discountValue
FROM ProductDiscountXml.nodes('/Summary/Item') Summary (c)
) Child
GROUP BY Child.discountName;
Here, the CROSS APPLY operator makes the data from the XML accessible under the name Child in the parent query. It works similarly to an INNER JOIN of a one-to-many relationship, at least from a high-level, in that it will only return rows for each valid piece of data found in the XML based on the specifications provided. In the example above, you can see our data is coming from items with Name and Value elements, found under the Summary then Item elements. When no such entry is found, the row is not included.
Creating a Report
Now that we have this query, we can add it to a report.
- In the Reporting application, select the E-commerce report category, and click the New report button (optionally, you can create a new Discount category for it).
- Name the report Discounts or something similar, and save it.
- On the General tab, scroll down and click the New button beside the Tables dropdown.
- Set the Display name to Product Discounts or something similar.
- Paste the full query into the Query field. At this point it should look like this:
--Create Table
DECLARE @ProductDiscountsTable TABLE(
OrderItemId INT,
ProductDiscountXml XML,
OrderID INT,
OrderGrandTotal DECIMAL(18,2)
);
--Populate Table
INSERT INTO @ProductDiscountsTable (OrderItemID, ProductDiscountXml, OrderID, OrderGrandTotal)
(
SELECT OrderItemID
,cast(OrderItemProductDiscounts as XML)
,OrderID
,OrderGrandTotal
FROM COM_OrderITem
JOIN COM_Order ON OrderItemOrderID = OrderID
JOIN COM_OrderStatus ON OrderStatusID = StatusID
WHERE StatusOrderIsPaid=1
);
--Select from table and XML
SELECT Child.discountName AS [DiscountName]
,SUM(Child.discountValue) AS [TotalDiscount]
,SUM(PDT.OrderGrandTotal) AS [TotalSpentOnOrders]
,COUNT(PDT.OrderID) AS [NumberOfOrders]
FROM @ProductDiscountsTable PDT
CROSS APPLY
(
SELECT Summary.c.value('Name[1]','varchar(max)') AS discountName
,Summary.c.value('Value[1]','decimal(18,2)') AS discountValue
FROM ProductDiscountXml.nodes('/Summary/Item') Summary (c)
) Child
GROUP BY Child.discountName;
- Click on Save & Close. Now your new table will appear in the Tables dropdown.
- Choose your new Product Discounts table in the dropdown and click on Insert, and then Save the report.
- Switching to the View tab of the report, you should see either a table of data about these kinds of discounts, or a No results found message of some kind.
Adding Parameters
Now we can add parameters to the report which will let us specify a date range for the data we want.
- Go to the Parameters tab of the report and click New field.
- Set the Data type to Date, the Field caption to Start Date, and the Field name to StartDate.
- Set a Default value for the field using the date picker control. In my case, I’m going to choose January 1, 2017, because I know it is before my site’s order data existed, and 2020 is nearby in the year dropdown of the form control making it easy to adjust the filter to a specific, recent timeframe.
- Repeat the process of creating and setting a default value of some point in the future for a field named EndDate and captioned End Date.
- Once both parameters are saved, switch back to the General tab, so that we can adjust our table query to account for these parameters
- In the Tables dropdown, select the Product Discounts table and choose the Edit action from the “…” menu.
- In the Query, add AND OrderDate >= @StartDate and AND OrderDate <= @EndDate after the line WHERE StatusOrderIsPaid=1.
- Click Save & Close. Now on the View tab, you should be able to use the two date parameters to filter the range of the data.
Expanding the Report
Finally, we can add additional tables for the other types of discounts to our report. The same StartDate and EndDate parameters can be used for all of the discount types, so we don’t need to re-create them.
For Line-level or Order-item-level discounts, such as Product coupons and Buy X Get Y discounts, the following query will pull data from the OrderItemDiscountSummary column of COM_OrderItem
--Create Table
DECLARE @OrderItemDiscountTable TABLE(
OrderItemId int,
OrderItemDiscountXml XML,
OrderID INT,
OrderGrandTotal DECIMAL(18,2)
);
--Populate Table
INSERT INTO @OrderItemDiscountTable (OrderItemID, OrderItemDiscountXml,OrderID,OrderGrandTotal)
(
SELECT OrderItemID
,cast(OrderItemDiscountSummary as XML)
,OrderID
,OrderGrandTotal
FROM COM_OrderITem
JOIN COM_Order ON OrderID = OrderItemOrderID
JOIN COM_OrderStatus ON OrderStatusID = StatusID
WHERE StatusOrderIsPaid=1
AND OrderDate >= @StartDate
AND OrderDate <= @EndDate
);
--Select from table and XML
SELECT Child.discountName AS [DiscountName]
,SUM(Child.discountValue) AS [TotalDiscount]
,SUM(OIDT.OrderGrandTotal) AS [TotalSpentOnOrders]
,COUNT(OIDT.OrderID) AS [NumberOfOrders]
FROM @OrderItemDiscountTable OIDT
CROSS APPLY
(
SELECT Summary.c.value('Name[1]','varchar(max)') AS discountName
,Summary.c.value('Value[1]','decimal(18,2)') AS discountValue
FROM OrderItemDiscountXml.nodes('/Summary/Item') Summary (c)
) Child
GROUP BY Child.discountName;
For Order discounts, the next query will pull from the OrderDiscounts column of the COM_Order table. This query, unlike the previous two examples, does not require a JOIN to the COM_OrderItem table.
--Create table
DECLARE @OrderDiscountTable TABLE(
OrderId int,
OrderDiscountXml XML,
OrderGrandTotal DECIMAL(18,2)
);
--Populate table
INSERT INTO @OrderDiscountTable (OrderId, OrderDiscountXml, OrderGrandTotal)
(
SELECT OrderID
,cast(OrderDiscounts as XML)
,OrderGrandTotal
FROM COM_Order
JOIN COM_OrderStatus ON OrderStatusID = StatusID
WHERE StatusOrderIsPaid=1
AND OrderDate >= @StartDate
AND OrderDate <= @EndDate
);
--Select from table and XML
SELECT Child.discountName AS [DiscountName]
,SUM(Child.discountValue) AS [TotalDiscount]
,SUM(ODT.OrderGrandTotal) AS [TotalSpentOnOrders]
,COUNT(ODT.OrderID) AS [NumberOfOrders]
FROM @OrderDiscountTable ODT
CROSS APPLY
(
SELECT Summary.c.value('Name[1]','varchar(max)') AS discountName
,Summary.c.value('Value[1]','decimal(18,2)') AS discountValue
FROM OrderDiscountXml.nodes('/Summary/Item') Summary (c)
) Child
GROUP BY Child.discountName;
Finally, the following query will gather information about Gift card vouchers from the OrderOtherPayments column of COM_Order:
--Create table
DECLARE @OrderGiftCardTable TABLE(
OrderId int,
OrderGiftCardXml XML,
OrderGrandTotal DECIMAL(18,2)
);
--Populate table
INSERT INTO @OrderGiftCardTable (OrderId, OrderGiftCardXml, OrderGrandTotal)
(
SELECT OrderID
,cast(OrderOtherPayments as XML)
,OrderGrandTotal
FROM COM_Order
JOIN COM_OrderStatus ON OrderStatusID = StatusID
WHERE StatusOrderIsPaid=1
AND OrderDate >= @StartDate
AND OrderDate <= @EndDate
);
--Select from table and XML
SELECT Child.discountName AS [DiscountName]
,SUM(Child.discountValue) AS [TotalDiscount]
,SUM(OGT.OrderGrandTotal) AS [TotalSpentOnOrders]
,COUNT(OGT.OrderID) AS [NumberOfOrders]
FROM @OrderGiftCardTable OGT
CROSS APPLY
(
SELECT Summary.c.value('Name[1]','varchar(max)') AS discountName
,Summary.c.value('Value[1]','decimal(18,2)') AS discountValue
FROM OrderGiftCardXml.nodes('/Summary/Item') Summary (c)
) Child
GROUP BY Child.discountName;
Now, with all these tables saved, you can insert them into your report on the General tab, and add any kind of formatting and labelling you want to the Layout.
In the end, the report should look something like this:
Note that custom discount types, which implement certain Kentico interfaces, may show up alongside the default discounts in these XML columns if such customizations exist in your project.
Summary
Essentially, the process of creating these reports boils down to first creating SQL queries that will read through the XML summaries using CROSS APPLY, adding them to a report as tables, adding parameters to the report, and then adjusting the queries to account for those parameters. Once finished, these reports bring together data about the usage of discounts in your shop. Hopefully they’ll help you analyze how well your discounts are working and inform your strategic decisions moving forward.