Episerver Commerce Report: Sales by day

 We have an option TimeRangesInDays that generate reports, for example 30, 90, or 180 days.

https://docs.developers.optimizely.com/customized-commerce/docs/option-configuration-classes
 
So when you run the job "Collect Order Data for Reports" it will update order data based on TimeRangesInDays to the table OrderReportData, then that data will be used in procedure [ecf_OrderReportData_AggregateByDay] when you generate the report

 
 
In case you want to change the sort order, you can modify the procedure with OrderCreated DESC
ALTER PROCEDURE [dbo].[ecf_OrderReportData_AggregateByDay] 
@TimeZoneOffset DECIMAL(38, 9) = 0 
AS 
BEGIN 
SELECT 
CONVERT(DATE, DATEADD(minute, @TimeZoneOffset, OrderCreated)) AS OrderCreated, 
MarketId, 
Currency, 
        Site, 
COUNT(OrderGroupId) AS NumOfOrders, 
SUM(TotalQuantity) AS NumOfItems, 
SUM(TaxTotal) AS TaxTotal, 
SUM(ShippingTotal) as ShippingTotal, 
SUM(HandlingTotal) as HandlingTotal, 
SUM(TotalDiscountAmount) AS DiscountsTotal, 
SUM(SubTotal) AS SubTotal, 
SUM(Total) AS Total 
FROM OrderReportData 
GROUP BY CONVERT(DATE, DATEADD(minute, @TimeZoneOffset, OrderCreated)), MarketId, Currency, Site 
ORDER BY OrderCreated DESC, MarketId, Currency, Site -- Newest orders first 
END
 Here is the result:






Comments

Post a Comment

Popular posts from this blog

Optimizely Content Graph: minimal setup for testing

Image
 1. Create a test page: [ContentType(DisplayName = "Test Page", GUID = "462d1812-7385-42c3-8073-c1b7481e7b21", Description = "TestPage", AvailableInEditMode = true, GroupName = GroupNames.Content)] public class TestPage : PageData { [CultureSpecific] [UIHint(UIHint.Textarea)] [Display(Name = "Test", GroupName = SystemTabNames.Content, Order = 1)] public virtual string Test { get; set; } } 2. Add this in Startup services.AddContentGraph(configureOptions: options => { options.SynchronizationEnabled = false; options.Include.ContentTypes = new string[] { typeof(TestPage).Name, }; options.OnlySyncContentTypesInWhitelistToSchema = true; options.ContentVersionSyncMode = Optimizely.ContentGraph.Cms.Configuration.ContentVersionSyncMode.PublishedOnly; }); 3. Create a test page in UI    4. Run the index job again {      "took": 0,  ...
Read more

Episerver ServiceAPI: Example of how-to properly call the media upload

Image
For the example of how-to properly call the media upload, you can refer to my Postman collection: https://github.com/KhanhPham2411/EpiserverAPI_PostmanCollection/blob/main/ServiceAPI/ServiceAPI_Collections.json   For the detail steps: Copy the raw content to Postman: https://raw.githubusercontent.com/KhanhPham2411/EpiserverAPI_PostmanCollection/main/ServiceAPI/ServiceAPI_Collections.json   2. Get ServiceAPI NetCore Token   3. Post to Assets/Import Example media file: https://github.com/KhanhPham2411/EpiserverAPI_PostmanCollection/blob/main/ServiceAPI/01-CreateMediaWithBlob.zip 4. To verify your import is working, use that task id to get task log:      
Read more

SameSite Cookie login troubleshooting

Image
  We will use network tab to capture redirect request when logging in to found the root cause F12 to open developer console and switch to Network tab Login to the Commerce Manger site => We would focus on the request that has Status 302 and Initiator CommerceManager.aspx Click on that request and switch to Cookies tab => Response Cookie is  yellow  mean it has been blocked and that is why it keep showing you the login page 🙂 Obviously, as it's blocked, no cookies have been used for the next request So how do we resolve it, we saw 2 exclamation mark in columns Secure and SamesSite, just  hover  on it and there will be a  hint  to solve :v When hover on SameSite exclamation mark, there will be a message "This Set-Cookie didn't specify a "SameSite" attribute and was defaulted to "SameSite=Lax", and was blocked because it came from a cross-site response which was not the response to top-level navigation. The Set-Cookie had to have been set with ...
Read more