R10 Gold Modify Report

[mbartens]

I have a client who would like a report based on Products that would show the Product, Price, Quantity, Total, Order Number, Order Date, Order Total. They want to search a date range.
I explained that the Order Total, Order Date and Order Number columns would be repeated .
How would I go about doing this? Thank you.

[mazhar]

We already have a report called "Sales By Product" which lists product with total sales. In this case you are looking for a report where you want to see every order the products is in. Perhaps you should borrow the UI from "Sales By Product" report while use a custom nhibernate query to load and bind desired data.

Following simple SQL should get you the data. You can run this by Nhibernate and use the result set for report.

Code: Select all

SELECT O.OrderDate, O.OrderNumber, OI.Name, OI.Price, OI.Quantity, OI.Quantity * OI.Price, O.TotalCharges FROM ac_OrderItems AS OI
INNER JOIN ac_Orders AS O ON OI.OrderId = O.OrderId
WHERE OI.OrderItemTypeId = 0 AND OI.ProductId > 0
ORDER BY O.OrderDate DESC

Code: Select all

var result = NHibernateHelper.CreateSQLQuery("YOUR QUERY HERE")
                .List();

[mbartens]

Thank you. So I will need to use the source code then?

[jguengerich]

You don't need the AbleCommerce source code (meaning the code to the CommerceBuilder.dll). But you will need to create a new web page (aspx / aspx.cs pair) and edit the source in the .cs file to make it do what you want. The "Sales By Product" report that mazhar suggested you borrow from is at Admin/Reports/SalesByProduct.aspx (& .aspx.cs).

[mazhar]

Yes Jay is correct May. You don't need source code, it can be done in front end codes just like Jay explained.

[mbartens]

I'm a little late getting back to this. Thank you for your help. Another question. If I was to do this on the front end by modifying SalesByProduct how do I attach the SQL query to the ObjectDataSource (ProductBreakdownDS)?

[mazhar]

You don't have to use the datasource, you can simply manually bind in code behind or even create your custom class which can be used with ObjectDataSource.

[mbartens]

I created my own class. Thank you so much for the help!