Help with Reports

I am struggling with creating a report i need and the export format. I need a report to include:

Order subtotal (without tax)
Tax Collected
Date shipped
County and State shipped to

Please note I have set up the site to accommodate for FL tax rules. The countries=state and state-counties.

In addition when I am trying to import the attached csv into excel I cannot get the columns to line up properly.



Thanks for any help. I really need that report for the accountant. I purchased an extension from ARI soft where you can plug in a SQL query and it provides a nice html table but they looked at my sql dump file and said: Countries and states are stored in strange format. For example:

"country_UnitedStates_639322704"

"country_Montenegro_19668"

Thanks again.

Hi there,

I'm not I understand everything. First of all, could you tell me which type of report you are using and post a screenshot of your report configuration?
Secondly, what's the point of the export? What kind of data you want to export, orders to ship? order created? I need more informations on this point


With theses informations it will be easier for me to help you.

My client has a report that needs to include the following:

1. Order Date
2. Order Number
2. Order shipped date
3. Order Subtotal (I don't need individual products listed but just a subtotal of all products)
4. Order Tax amount
5. Order Discount amount
6. Order Shipping amount
7. Order Total amount
8. State (which is in the country field)
9. County (which is in the state field for FL only taxes-this field now provides the county within FL that it was taxed on)

Note: We had to adjust state and country fields to accommodate for the tax laws in Florida as per your instructions.

This is a two part question.
1. Can you help me with with producing a hikashop report that will provide me with a report based on the above criteria?
2. If I wanted to query the database directly, what would be the SQL statement for this query look like to provide the same results. The reason I ask this is because of a 3rd party product from ARI soft which queries the database and displays the results on the front end in a nice html format. The were trying to help me with a query but stated that there was a strange format you guys were using in the two tables I (see below). They looked at my sql dump file and said: Countries and states are stored in strange format. For example:

"country_UnitedStates_639322704"

"country_Montenegro_19668"

The challenge with my customer (and I am sure others) is that when we export the HikaShop Reports there is a lot of clean up to do not to mention you cannot just look at a csv file and make sense of it. You have to import into excel and there is still clean up to do afterwards.

We created an order report, see attached but it does not contain everything we need and takes a lot of time cleaning up. See attachment please. Thanks for your help.

the challenge with that is they cannot filter by date so i am back now to adapting the hikashop report to get that info. Is that at all possible?

I have version 2.1.3 Is that the version you are referring too? Also, what is the code i need to modify please and what do I need to modify it to in order to get the csv to export properly. Thanks.

Hi there,

Since we didn't modified this file between 2.1.3 and 2.2.0 it should work the same way yes.

We are getting close now with ARI Soft's front end report. This is the query without taxes below. We need help determining where to query tax for an order:

SELECT
O.order_number,
DATE_FORMAT(DATE_ADD('1970-01-01', INTERVAL O.order_created SECOND), '%d %b %y') AS OrderCreated,
IF(O.order_shipping_price <> 0.00, FORMAT(O.order_shipping_price, 2), '-') AS Shipping,
IF(O.order_discount_price <> 0.00, FORMAT(O.order_discount_price, 2), '-') AS Discount,
FORMAT(O.order_full_price, 2) AS TotalPrice,
County.zone_name AS County,
State.zone_name AS State
FROM
#__hikashop_order O LEFT JOIN #__hikashop_address A
ON O.order_billing_address_id = A.address_id
LEFT JOIN #__hikashop_zone County
ON A.address_state = County.zone_namekey
LEFT JOIN #__hikashop_zone State
ON A.address_country = State.zone_namekey
ORDER BY
O.order_created ASC


I have attached what the front end report looks like, sweet. I just need help adding the taxes. Thanks so much.

Hi there,

Taxes are already included in the order_full_price column so I don't understand why you need a query on the tax.
If you want to display the tax price for each order I would suggest to make a LEFT JOIN ##__hikashop_order_product as op ON op.order_id=o.order_id and then make a sum of the order_product_tax column.

Thanks, that worked. I needed taxes separated for Florida as we have to pay by county!

I cannot seem to get the tax right on my query when there are multiple quantities of the same product. You can see by the attached image what I am referring to. I am also including the query so hopefully you can help me correct the error. Everything works fine on this query with the exception above. Thanks.

SELECT
O.order_number,
DATE_FORMAT(DATE_ADD('1970-01-01', INTERVAL O.order_created SECOND), '%d %b %y') AS OrderCreated,
IF(O.order_shipping_price <> 0.00, FORMAT(O.order_shipping_price, 2), '-') AS Shipping,
IF(O.order_discount_price <> 0.00, FORMAT(O.order_discount_price, 2), '-') AS Discount,
FORMAT(O.order_full_price, 2) AS TotalPrice,
County.zone_name AS County,
State.zone_name AS State,
FORMAT(SUM(OP.order_product_tax), 2) AS Tax
FROM
#__hikashop_order O LEFT JOIN #__hikashop_address A
ON O.order_billing_address_id = A.address_id
LEFT JOIN #__hikashop_zone County
ON A.address_state = County.zone_namekey
LEFT JOIN #__hikashop_zone State
ON A.address_country = State.zone_namekey
LEFT JOIN #__hikashop_order_product OP
ON O.order_id = OP.order_id
WHERE
DATE_ADD('1970-01-01', INTERVAL O.order_created SECOND) BETWEEN '2013-07-01 00:00:00' AND '2013-07-31 23:59:59'
GROUP BY
O.order_id
ORDER BY
O.order_created ASC

I think you have to multiply by the order_quantity:
SUM(OP.order_product_tax*order_product_quantity)

Thanks that is just what the doctor ordered.