[Skip to main content](#vessel-content) # Unbale to export orders with amount of products per order ## - **[maartenxbosmans@gmail.com](/forum/user/60008-maartenxbosmans-gmail-com "View maartenxbosmans@gmail.com's Profile")** - [![maartenxbosmans@gmail.com's Avatar](https://www.hikashop.com/media/kunena/core/svg/person.svg)](/forum/user/60008-maartenxbosmans-gmail-com "View maartenxbosmans@gmail.com's Profile") Topic Author - [ Offline](/forum/user/60008-maartenxbosmans-gmail-com) Less More - Posts: 3 - Thank you received: 1 - - 3 years 11 months ago [\#346629](#346629) by [maartenxbosmans@gmail.com](/forum/user/60008-maartenxbosmans-gmail-com "View maartenxbosmans@gmail.com's Profile") *[Unbale to export orders with amount of products per order](/forum/36-reports-statistics/905361-unbale-to-export-orders-with-amount-of-products-per-order#346629 "-- HikaShop version -- : 4.6.2 -- Joomla version -- : 4.2.5 -- PHP version -- : 8.1.11 I am trying to look for a way to perform an export of our orders which shows per order, how many of each product was chosen. I am currently doing this with a Mass Action, using "order" as the datasource and only exporting order_id, order_product_code and order_product_quantity Let's say I am only selling 4 items: Coke, Sprite, Milk and Juice. The first customer places an order for 3 cokes The next customer orders 1 juice and 1 sprite The next customer orders 4 times milk The next customer orders 1 Sprite + 1 Coke + 2 Juices and 5 times Milk The last customer orders 2 times Sprite and 1 Milk In an ideal world, I would have an export showing the following: (see attached screenshot 1) With Hikashop, the best I seem to get is: (see attached screenshot 2) ID 1 only has data for the fields relating to item1 since the customer ordered 1 product ID 2 has data for the fields item1 and item2 since the customer ordered 2 products ... ID 4 has data for all the fields since the customer ordered 4 products Hikashop uses the order in which a customer adds products to his cart to populate the fields item1_productcode, item2_productcode etc.. Item1_productcode can, in my example, contain 4 possible values.. Since the amount of columns is different per row and since the values in the productcode or set based on the ordere a product is added, it is impossible with Excel to transform the data into my example above 'for the ideal world'. What I would need is the 4 possible values (Coke, Sprite, Milk, Juice) as the column header and per order (row) the amount of articles that was chosen for that product. Does anybody have an idea on how to create this export which seems to be rather logical functionality... Any help would be much appreciated!")* was created by *[maartenxbosmans@gmail.com](/forum/user/60008-maartenxbosmans-gmail-com "View maartenxbosmans@gmail.com's Profile")* -- HikaShop version -- : 4.6.2 -- Joomla version -- : 4.2.5 -- PHP version -- : 8.1.11 I am trying to look for a way to perform an export of our orders which shows per order, how many of each product was chosen. I am currently doing this with a Mass Action, using "order" as the datasource and only exporting order_id, order_product_code and order_product_quantity Let's say I am only selling 4 items: Coke, Sprite, Milk and Juice. The first customer places an order for 3 cokes The next customer orders 1 juice and 1 sprite The next customer orders 4 times milk The next customer orders 1 Sprite + 1 Coke + 2 Juices and 5 times Milk The last customer orders 2 times Sprite and 1 Milk In an ideal world, I would have an export showing the following: (see attached screenshot 1) [ ![SCREENSHOT_1.jpg](/forum/attachment/55921) ](/forum/attachment/55921 "SCREENSHOT_1.jpg") With Hikashop, the best I seem to get is: (see attached screenshot 2) [ ![SCREENSHOT_2.jpg](/forum/attachment/55920) ](/forum/attachment/55920 "SCREENSHOT_2.jpg") ID 1 only has data for the fields relating to item1 since the customer ordered 1 product ID 2 has data for the fields item1 and item2 since the customer ordered 2 products ... ID 4 has data for all the fields since the customer ordered 4 products Hikashop uses the order in which a customer adds products to his cart to populate the fields item1_productcode, item2_productcode etc.. Item1_productcode can, in my example, contain 4 possible values.. Since the amount of columns is different per row and since the values in the productcode or set based on the ordere a product is added, it is impossible with Excel to transform the data into my example above 'for the ideal world'. What I would need is the 4 possible values (Coke, Sprite, Milk, Juice) as the column header and per order (row) the amount of articles that was chosen for that product. Does anybody have an idea on how to create this export which seems to be rather logical functionality... Any help would be much appreciated! - [ ![SCREENSHOT_2.jpg](/forum/attachment/55920) ](/forum/attachment/55920 "SCREENSHOT_2.jpg") [ ](/forum/attachment/55920?download=1 "SCREENSHOT_2.jpg") - [ ![SCREENSHOT_1.jpg](/forum/attachment/55921) ](/forum/attachment/55921 "SCREENSHOT_1.jpg") [ ](/forum/attachment/55921?download=1 "SCREENSHOT_1.jpg") Please [Log in](/login?return=aHR0cHM6Ly93d3cuaGlrYXNob3AuY29tL2ZvcnVtLzM2LXJlcG9ydHMtc3RhdGlzdGljcy85MDUzNjEtdW5iYWxlLXRvLWV4cG9ydC1vcmRlcnMtd2l0aC1hbW91bnQtb2YtcHJvZHVjdHMtcGVyLW9yZGVyP3RtcGw9bWFya2Rvd24=) or [Create an account](/component/users/registration?Itemid=101) to join the conversation. - **[nicolas](/forum/user/63-nicolas "View nicolas's Profile")** - [![nicolas's Avatar](https://www.hikashop.com/media/kunena/avatars/resized/size144/users/avatar63.png?1793428657)](/forum/user/63-nicolas "View nicolas's Profile") - [ Away](/forum/user/63-nicolas) Less More - Posts: 86541 - Thank you received: 14304 - - 3 years 11 months ago [\#346635](#346635) by [nicolas](/forum/user/63-nicolas "View nicolas's Profile") Replied by *[nicolas](/forum/user/63-nicolas "View nicolas's Profile")* on topic *[Unbale to export orders with amount of products per order](/forum/36-reports-statistics/905361-unbale-to-export-orders-with-amount-of-products-per-order#346635 "-- HikaShop version -- : 4.6.2 -- Joomla version -- : 4.2.5 -- PHP version -- : 8.1.11 I am trying to look for a way to perform an export of our orders which shows per order, how many of each product was chosen. I am currently doing this with a Mass Action, using "order" as the datasource and only exporting order_id, order_product_code and order_product_quantity Let's say I am only selling 4 items: Coke, Sprite, Milk and Juice. The first customer places an order for 3 cokes The next customer orders 1 juice and 1 sprite The next customer orders 4 times milk The next customer orders 1 Sprite + 1 Coke + 2 Juices and 5 times Milk The last customer orders 2 times Sprite and 1 Milk In an ideal world, I would have an export showing the following: (see attached screenshot 1) With Hikashop, the best I seem to get is: (see attached screenshot 2) ID 1 only has data for the fields relating to item1 since the customer ordered 1 product ID 2 has data for the fields item1 and item2 since the customer ordered 2 products ... ID 4 has data for all the fields since the customer ordered 4 products Hikashop uses the order in which a customer adds products to his cart to populate the fields item1_productcode, item2_productcode etc.. Item1_productcode can, in my example, contain 4 possible values.. Since the amount of columns is different per row and since the values in the productcode or set based on the ordere a product is added, it is impossible with Excel to transform the data into my example above 'for the ideal world'. What I would need is the 4 possible values (Coke, Sprite, Milk, Juice) as the column header and per order (row) the amount of articles that was chosen for that product. Does anybody have an idea on how to create this export which seems to be rather logical functionality... Any help would be much appreciated!")* Hi, I don't see a way to do that inside HikaShop. It's possible to generate a report to get the number of sales of each products (one per row) like this: Code: Name Quantity prod1 1 prod2 5 prod3 4 ... But there is no option to keep it split by order. That would require a complex MySQL query like this: Code: SELECT o.order_id, SUM(CASE WHEN op.product_id = XX THEN op.order_product_quantity ELSE '' END) as Coke, SUM(CASE WHEN op.product_id = YY THEN op.order_product_quantity ELSE '' END) as Sprite, SUM(CASE WHEN op.product_id = ZZ THEN op.order_product_quantity ELSE '' END) as Milk, SUM(CASE WHEN op.product_id = AA THEN op.order_product_quantity ELSE '' END) as Juice, FROM #__hikashop_order o JOIN #__hikashop_order_product op ON op.order_id = o.order_id GROUP BY o.order_id ORDER BY o.order_id; where XX, YY, ZZ and AA would be the id of the products Coke, Sprite, Milk and Juice, and #__ the prefix of your tables (setting in the joomla configuration). You could run that MySQL query in your PHPMyAdmin to get the export you want. Please [Log in](/login?return=aHR0cHM6Ly93d3cuaGlrYXNob3AuY29tL2ZvcnVtLzM2LXJlcG9ydHMtc3RhdGlzdGljcy85MDUzNjEtdW5iYWxlLXRvLWV4cG9ydC1vcmRlcnMtd2l0aC1hbW91bnQtb2YtcHJvZHVjdHMtcGVyLW9yZGVyP3RtcGw9bWFya2Rvd24=) or [Create an account](/component/users/registration?Itemid=101) to join the conversation. Board Categories HikaShop Install & Update Product & Category Display Content Creation Sales and Orders Orders management Reports & Statistics Customers Checkout Payment Methods Shipping Methods Import & Export (CSV, XLS…) Specific Features Affilation Taxes Wishlist Vote Carousel & Slider Mass Actions Filters & Search Discount & Coupon Feature Request Integrations Showcase General Talk about HikaShop Development HikaShop 3.0 Beta Translation Commercial Jobs HikaShop Components HikaMarket Market: Feature requests Market: How to? Market: Bug Report HikaSerial General talk about HikaSerial Serial: Bug Report Serial: How to? Support en Français de HikaSerial (French Support) HikaSubscription HikaAuction Auction: Feature requests Auction: How to? Auction: Bug report Hikashop around the world Support en Français (French Support) Russian FAQ. Курс выживания с Hikashop. Частые вопросы и о Расширения Hikashop Наши сайты на Hikashop 日本語版サポート(Japanese Support) Thai German Archives Bug Report How to? [](/forum/topics/feed/mode-topics?format=feed&type=rss) Time to create page: 0.046 seconds [Powered by](/forum/credits) [Kunena Forum](https://www.kunena.org)