Customer Report Returns SQL Error

-- url of the page with the problem -- : offeringsjewelry.com
-- HikaShop version -- : 2.6.3
-- Joomla version -- : 3.5.8
-- PHP version -- : 5.4
-- Browser(s) name and version -- : All
-- Error-message(debug-mod must be tuned on) -- : 1054 Unknown column 'cat.category_id' in 'where clause' SQL=SELECT *, SUM( case when o.order_currency_id = '2' then o.order_full_price end ) AS Total, COUNT(o.order_id) AS order_number FROM #__hikashop_order as o LEFT JOIN #__hikashop_user as a ON o.order_user_id=a.user_id LEFT JOIN #__users as u ON u.id=a.user_cms_id WHERE a.user_partner_activated=0 AND cat.category_id IN ('0','2','34','88','35','13','15','16','17','18','23','24','25','26','27','28','30','31','32','85','33','36','37','38') AND a.user_created > 1435509022 AND a.user_created

Hello,

I am trying to create a report that lists the best customers by total Sales.

Here's the screenshot of my configuration:


Here's the error I get:
1054 Unknown column 'cat.category_id' in 'where clause' SQL=SELECT *, SUM( case when o.order_currency_id = '2' then o.order_full_price end ) AS Total, COUNT(o.order_id) AS order_number FROM #__hikashop_order as o LEFT JOIN #__hikashop_user as a ON o.order_user_id=a.user_id LEFT JOIN #__users as u ON u.id=a.user_cms_id WHERE a.user_partner_activated=0 AND cat.category_id IN ('0','2','34','88','35','13','15','16','17','18','23','24','25','26','27','28','30','31','32','85','33','36','37','38') AND a.user_created > 1435509022 AND a.user_created < 1467045022 AND a.user_id IS NOT NULL AND o.order_type='sale' GROUP BY a.user_id ORDER BY Total DESC LIMIT 20

Thanks,

Dawn

Hi,

Thank you for your feedback. I've added a few patches on our end so that it works properly.
Download again the install package on our website and install it on yours and that should fix the problem.

Hello Nicolas,

I reinstalled the package as you suggested. I am now getting a different error when I try to run a report using the same settings.

Here's the error
1064 You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near '+' at line 1 SQL=SELECT *, SUM( case when o.order_currency_id = '2' then o.order_full_price end ) AS Total, COUNT(o.order_id) AS order_number FROM #__hikashop_order as o LEFT JOIN #__hikashop_user as a ON o.order_user_id=a.user_id LEFT JOIN #__users as u ON u.id=a.user_cms_id LEFT JOIN #__hikashop_order_product AS prod ON prod.order_id = o.order_id LEFT JOIN #__hikashop_product AS p ON prod.product_id = p.product_id LEFT JOIN #__hikashop_product_category AS cat ON cat.product_id = p.product_id OR cat.product_id=p.product_parent_id LEFT JOIN #__hikashop_category AS categ ON cat.category_id = categ.category_id LEFT JOIN #__users AS b ON a.user_cms_id=b.id WHERE a.user_partner_activated=0 AND cat.category_id IN ('0','2','34','88','35','13','15','16','17','18','23','24','25','26','27','28','30','31','32','85','33','36','37','38') AND a.user_id IS NOT NULL AND o.order_type='sale' GROUP BY a.user_id ORDER BY Total DESC LIMIT 20+

Hi,

Please replace your "20+" value in the report configuration in order to put just a number (20).
I'll add some patchs in HikaShop in order to avoid such kind of issue in the future.

Regards,

Hello,

Thank you for the instruction on removing the + sign. We can now generate a report. The information in the report is very dense however. Is there a way to simplify the results to only display certain information about the customers? Attached is the exported CSV. And here is a screenshot of the exported CSV: screencast.com/t/dMpcuQFeD7G

Is this normal? We would like to only export the information that appears in the report within Hikashop (Name, username, email, etc).

Thanks

Hi,

The problem is that you're opening your CSV with Excel without telling it which separator to use. By default it uses , with your version. But the CSV uses ; by default in HikaShop.
There is a setting in the HikaShop configuration to change the CSV separator for the generated CSVs, and likewise, there is a setting in your Excel to select the separator to use for reading the CSV:
superuser.com/questions/396469/how-to-co...port-into-excel-2010
Then, you'll be able to easily delete the columns you don't need in your CSV and navigate through it.
There is no setting in HikaShop to configure how reports export the data they generate.

Great! Thank you so much for all the help.