Custom Reports

[robgrigg]

Hi.

I want to write a custom report simular to the monthly sales report by grouped by product rather than date.

Whilst I can write this from scratch it would be a real pain. Any chance I can get the code for the Monthly sales report and then I can simply modify this code.

Cheers,

Rov.

[robgrigg]

Further to this,

would it be possible to get the SQL query for the monthly sales report?

This will save me hours figuring out the schema.

Thanks.

Rob.

[jmestep]

I think you've asked for source code snippets before? If you're going to be doing customizations where you need it, it really is a remarkable bargain to purchase it when you consider the time you will save trying to figure out things and how to customize. I can't even guess how many hours it has saved me and what a gold mine it is.
If you don't want to purchase it, you could still get some of the info you are asking for by running SQL profiler as you do things on the site.

[robgrigg]

um, interesting. I thought I had the source code?

Is there an additional option where I can buy the source for the reports?

If so I will .

R.

[jmestep]

I'm not sure exactly where the product is located on the able site, but it would be on www.ablecommerce.com.
The default install has a great amount of code, but if you are wanting things like this:

would it be possible to get the SQL query for the monthly sales report?

and the code is not in the admin page, then you would need the source code.

[s_ismail]

Yes offcourse you can write your own custom report without source code.

[mazhar]

Yes offcourse you can't write your own custom report with out source code.

Any type of custom report can be written without source code. AbleCommerce doesn't limit new report creation to source code. In above scenario Rob is looking for some functionality that is available out of the box and its in back end code. That's why he can't access that query without back end source. For new reports you can create any type or report with standard package purchase of AbleCommerce. All you need is to put your custom data access code to accomplish reporting. We have some topics about how to access data and write custom queries you can check them out here http://wiki.ablecommerce.com/index.php/ ... uilder_API

[ReWired]

It's funny never really going down the programer track, but rather skilled at many , master of none, I usually have bad days and then good days where I just get it to work (Frequently with help from all Thanks).

I'm also trying to crack the Custom reports, and have found what I read so far a little lacking or well I'm certainly missing something.

I'm wondering if able or some one would offer up a "hello world" example for doing a custom SQL query returned in the reports section.

As an example I might suggest creating a simple two column for product id and product name.

select ProductId, Name
from ac_Products
ORDER BY ProductId

If the community feels this is too basic a question and I need to join a self help (pay a contractor) - Please just say so.

Thank you

Luke

[mazhar]

You can do the following to load all products from store

Code: Select all

//LOAD ALL PRODUCTS
ProductCollection products = ProductDataSource.LoadForStore();

Read following two topics
http://wiki.ablecommerce.com/index.php/ ... cess_Layer
http://wiki.ablecommerce.com/index.php/Custom_Queries

[plugables]

I'm wondering if able or some one would offer up a "hello world" example for doing a custom SQL query returned in the reports section.

As an example I might suggest creating a simple two column for product id and product name.

select ProductId, Name
from ac_Products
ORDER BY ProductId

Luke

For such basic queries you will never need to write custom SQL. You can simply use the AbleCommerce API to handle most of these requirements.
As pointed out by Mazhar above, you want to load all products just use ProductDataSource.LoadForStore().
You can do something slightly more complicated with AbleCommerce API. Want to load all products with price greater than 100? Use this

Code: Select all

ProductCollection products = ProductDataSource.LoadForCriteria(" Price > 100 ");

[ReWired]

For such basic queries you will never need to write custom SQL.

Hi all, I understand that to be the case but wanted to use a simple qwery as an example for the purpose of HELLO WORLD to understand the process, but not do all the work.

My current back end query that I want to integrate is.

Select SUM (ac_OrderItems.Price), ac_Orders.BillToFirstName, ac_Orders.BillToLastName, ac_Orders.BillToEmail, ac_Orders.BillToAddress1, ISNULL(ac_Orders.BillToAddress2, ''), ac_Orders.BillToCity, ac_Orders.BillToProvince, ac_Orders.BillToPostalCode, ac_Orders.BillToCountryCode
FROM ac_OrderItems, ac_Orders
WHERE ac_Orders.OrderId = ac_OrderItems.OrderId AND ac_OrderItems.ProductId IN (105,62,65,66,68,102,100) AND ac_Orders.OrderDate between '2010-08-23 07:00:00.000' and '2010-08-31 07:00:00.000'
Group By ac_Orders.BillToLastName, ac_Orders.BillToFirstName, ac_Orders.UserId, ac_Orders.BillToEmail, ac_Orders.BillToAddress1, ISNULL(ac_Orders.BillToAddress2, ''), ac_Orders.BillToCity, ac_Orders.BillToProvince, ac_Orders.BillToPostalCode, ac_Orders.BillToCountryCode
Having Sum (ac_OrderItems.Price) > 0

Your direction as always is greatly appreciated.

[mazhar]

Seems like this query will require a join between orders and orderitem tables. You will be required to write a custom query to execute and fetch results of this query. Read the first example discussed here http://wiki.ablecommerce.com/index.php/Custom_Queries

[ReWired]

Sorry still banging my head does anyone have a sample completed one I could look at so I can see the entire document / example.. this was the reason I was looking for a hello world.. Also to that Point I was looking at http://wiki.ablecommerce.com/index.php/ ... cess_Layer and unfortunately refers to a file that is no longer part of the 7.04 build.

[heinscott]

Here is about the easiest "Hello World" type example I could think of...
Just a simple select from the ac_Stores table bound to a GridView with the columns autogenerated.
For the sake of simplicity, I chose to just use the one page aspx format (no code behind).
Hope this helps.

Scott

Code: Select all

<%@ Page Language="C#" MasterPageFile="~/Admin/Admin.master" Title="Example Report" Inherits="CommerceBuilder.Web.UI.AbleCommerceAdminPage" %>
<%@ Register TagPrefix="ComponentArt" Namespace="ComponentArt.Web.UI" Assembly="ComponentArt.Web.UI" %>
<%@ Register Assembly="CommerceBuilder.Web" Namespace="CommerceBuilder.Web.UI.WebControls" TagPrefix="cb" %>
<%@ Import Namespace="CommerceBuilder.Data" %>
<%@ Import Namespace="System.Data" %>
<script runat="server">
    
    protected void Page_Load(object sender, EventArgs e)
    {
        if (!Page.IsPostBack)
        {
            buildGrid();
        }
    }

    protected void buildGrid()
    {
        StringBuilder selectQuery = new StringBuilder();
        selectQuery.Append("Select * from ac_Stores");
        Database db = Token.Instance.Database;
        System.Data.Common.DbCommand selectCommand = db.GetSqlStringCommand(selectQuery.ToString());
        using (DataSet ds = db.ExecuteDataSet(selectCommand))
        {
            if (ds.Tables.Count > 0)
            {
                ExampleGrid.DataSource = ds.Tables[0];
                ExampleGrid.DataBind();
            }
        }
    }

</script>

<asp:Content ID="Content1" ContentPlaceHolderID="MainContent" Runat="Server">
    <asp:GridView ID="ExampleGrid" runat="server" AutoGenerateColumns="true">
    </asp:GridView>
</asp:Content>

[yaletowner]

I would like to pass 2 parameters to the query from a drop down list. Something that would look like:

Code: Select all

        StringBuilder selectQuery = new StringBuilder();
        selectQuery.Append("Select * from ac_Products where name like @param1+'%'+@param2+'%'");

Can anyone point me in the right direction or post an example of how this can be done.

[mazhar]

For a simple query like this where data is available from single table for example in your case you are trying to search against name column in product table you can use LoadForCritera method of corresponding DataSource class. For example datasource class for Product table is ProductDataSource.

Code: Select all

ProductCollection products = ProductDataSource.LoadForCriteria(string.Format("Name LIKE '%{0}%' ","product name to search"));

[yaletowner]

Can you give me an example on how to pass 2 parameters selected from a drop down list to be used in this query.

[s_ismail]

It can be like this

Code: Select all

ProductCollection products = ProductDataSource.LoadForCriteria(string.Format("Name LIKE '%{0}%' OR Name LIKE '%{1}%' ", param1, param2))

[david-ebt]

Rob,

Did you find a way to get your custom report done inside AC? We've just put up some new AbleCommerce custom reports on our website http://www.ecombuildertoday.com/ablecommerce-reports. While none of our reports is specific to your request, each report does contain the full source code. On our site you can see the look of the report page as well as the results page. Our reports also let you download the search results in CSV or Excel format. The Order Sales Report runs the following query.

Code: Select all

"select o.OrderId, o.OrderNumber, o.OrderDate, o.TotalCharges, o.TotalPayments, oi.ProductTotal, oi.ShippingHandlingTotal, oi.TaxTotal, oi.COGS, oi.CouponTotal, oi.OtherTotal, oi.CreditTotal, oi.DiscountTotal, oi.GiftCertPayment, oi.GiftCertTotal, oi.GiftWrapTotal from ac_orders o join ac_orderstatuses os on o.orderstatusid = os.orderstatusid join (select orderid, SUM(ProductTotal) as ProductTotal, SUM(ShippingHandlingTotal) as ShippingHandlingTotal, SUM(TaxTotal) as TaxTotal, SUM(DiscountTotal) as DiscountTotal, SUM(CouponTotal) as CouponTotal, SUM(OtherTotal) as OtherTotal, SUM(GiftCertTotal) as GiftCertTotal, SUM(CreditTotal) as CreditTotal, SUM(GiftCertPayment) as GiftCertPayment,SUM(GiftWrapTotal) as GiftWrapTotal, SUM(COGS) as COGS from (select oi.orderid, ProductTotal = case when oi.OrderItemTypeId = 0 then sum(oi.price * oi.quantity) else 0 end, ShippingHandlingTotal = case when oi.OrderItemTypeId in (1,2) then SUM(oi.price * oi.quantity) else 0 end, TaxTotal = case when oi.OrderItemTypeId = 3 then SUM(oi.price * oi.quantity) else 0 end, DiscountTotal = case when oi.OrderItemTypeId = 4 then SUM(oi.price * oi.quantity) else 0 end, CouponTotal = case when oi.OrderItemTypeId = 5 then SUM(oi.price * oi.quantity) else 0 end, OtherTotal = case when oi.OrderItemTypeId = 6 then SUM(oi.price * oi.quantity) else 0 end, GiftCertTotal = case when oi.OrderItemTypeId = 7 then SUM(oi.price * oi.quantity) else 0 end, GiftWrapTotal = case when oi.OrderItemTypeId = 8 then SUM(oi.price * oi.quantity) else 0 end, CreditTotal = case when oi.OrderItemTypeId = 9 then SUM(oi.price * oi.quantity) else 0 end, GiftCertPayment = case when oi.OrderItemTypeId = 10 then SUM(oi.price * oi.quantity) else 0 end, sum(oi.CostOfGoods * oi.Quantity) as COGS, sum(oi.TaxAmount) as Taxes from ac_OrderItems oi group by oi.OrderId, oi.orderitemtypeid) totals group by orderid) oi on oi.orderid = o.OrderId where o.OrderDate between @StartDate and @EndDate and o.StoreId = @StoreId and os.IsActive = 1 order by o.OrderNumber"

This report could be modified to work with your query. If you don't have the time, contact us through our website and we can meet up to discuss how we might be able to help.