Export data from HAC using Impex

[former_member657513]

Hi,

I am trying to export data from HAC. I have below flexible search query output which i need to export data into a csv file or xls. Is Impex export the only way to export data? In that case can i perform group by functions, calculate count and avg functions using impex syntax?

select {oe.order}, count({oe.order}), avg({oe.quantity}) from {OrderEntry AS oe JOIN Order AS o ON {oe.order}={o.PK} left JOIN CouponRedemption AS cr ON {cr.order}={o.pk} left JOIN Singlecodecoupon AS scc ON {cr.couponcode}={scc.couponid} JOIN Customer AS c ON {o.user}={c.pk}} where {c.Name} not like 'Guest' and {o.deliverycost}=0 and {scc.couponid}='VAN50' and {oe.creationtime} >= TO_DATE('2018.09.22','YYYY.MM.DD') AND {oe.creationtime} <= TO_DATE('2018.09.28','YYYY.MM.DD') group by {oe.order}

[former_member624549]

You can create a itemtype based on that query. Then you export the itemtype in hmc/Backoffice. At least with old hmc it was possible to create that itemtype dynamically and change the query on the fly. You require a hmc/backoffice configuration for the itemtype to show. Havent tried with Backoffice but together with the new Excel Export this could be a pretty efficient reporting feature no need for coding or deplyoment.

[andyfletcher · Accepted solution]

I find it easiest to do this sort of thing from a Groovy script. You can then export just the column data (i.e. the result of your aggregate functions. The important bit is to set the resultClassList attribute to match the column type. You can just set them all to String.class or set specific columns to the correct Integer.class, Double.class etc.

See my previous post https://answers.sap.com/questions/12763692/cronjob-sending-flexiblesearch-results.html?childToView=1...

You can then run this adhoc or schedule as a scripting job. We also send reports using Groovy's MarkupTemplateEngine http://groovy-lang.org/templating.html to send html emails with tables in for something more immediately readable but csvs are great for opening in Excel.

[former_member657513 · Accepted solution]

Thank you Andrew for the reply its very helpful. I was able to extract data using groovy and email the result.

[geffchang · Accepted solution]

If any of the answers have been helpful for you, please upvote them. If you think it's the correct answer, please accept them also. :)

[geffchang]

I understand your question, but I don't have a good way of explaining all the answers. So, I'll just answer parts of your question. Hopefully, it will give you some idea.

What you are showing is not Impex per se, but a FlexibleSearch query. You can use database functions like COUNT, as long as it is supported by the database you are using.

You can get the result from the FlexibleSearch query via HAC: Console > FlexibleSearch. You can also do an Export (using Impex and FlexibleSearch query) in HAC: Console > Impex Export.

However, the custom attributes / columsn cannot be used with an Impex header (for Export), if the column/attribute does not exist in the Type. For example, OrderEntry does not have a count attribute. So, you cannot build an Impex that has something like:

INSERT_UPDATE OrderEntry;count; ...

However, the FlexibleSearch query itself is valid, and will return data (using FlexibleSearch). It just cannot be exported with an Impex header.

References:

• FlexibleSearch Tips and Tricks: https://help.hybris.com/1808/hcd/8bc36ba986691014b48be171221d1f4f.html ^ Contains some functions (for mySQL and Oracle), which you can use

• Export CronJob: https://help.hybris.com/1808/hcd/bef643dde9674a388da4a1f8985bc0ac.html

[former_member657513]

Thank you Geff for the reply its helpful.