Data Exports feature


#1

I notice Data Exports feature under Manage > Reports, but nothing mentioned on the forum about it. What does this do? Can I use it to create CSV exports? Any info much appreciated. Thanks :smile:

EDIT: Ok I notice it exports the report to a txt file. If for example I use the {REPORT INVENTORY} tag I am getting output like this:

>Local Warehouse||
Svedka|4,900|ML
Absolute|4,280|ML
Belvedere|455|ML

Taking same format from the inventory report with the table header

[Inventory Report:1,1, 1]
{REPORT INVENTORY}

I get this:

Local Ware            
Svedka       4,900  ML
Absolute     4,280  ML
Belvedere      455  ML

How do I reformat this so I get results like this:

Product,Quantity,Unit
Svedka,4900,ML
Absolute,4280,ML
Belvedere,455,ML

Then I guess I can just save as a .csv and I have a way of getting data out in CSV format.

(I’m just using above as an example to try to learn / understand how this feature works)


Exporting Customer Records from SQL DATABASE
How to extract customer's date in excel sheet
Export Daily Sales as Text File with fixed-length fields data
Uploading Entiy Data online for use witha website
Exporting and Importing From SQL Scripts
State Internal Taxes
Inventory Purchase report per supplier
How to write Report with JavascriptTotal Sale and Total Order For per day
Sambapos and sage
Custom Hourly Report
#2

I clipped this from a Staff discussion on this feature. The following is all quoted by @emre.

###SYNTAX
{REPORT ORDER DETAILS:::}

###Orders XML

<orders>
{REPORT ORDER DETAILS: 
  O.MenuItemName,
  O.Quantity,
  O.Price,
  ([O.Quantity]*[O.Price]-[O.ExactTotal]),
  O.ExactTotal
:O.MenuItemName.Contains("Toasted")
:  <order>
    <name>"{0}"</name>
    <quantity>{1}</quantity>
    <price>{2}</price>
    <service>{3}</service>
    <total>{4}</total>
  </order>}
</orders>

###Orders CSV with Summary

Report
Index,Name,Quantity,Price,Service,ExactTotal
{REPORT ORDER DETAILS: 
  <row>,
  O.MenuItemName,
  O.Quantity,
  O.Price,
  ([O.Quantity]*[O.Price]-[O.ExactTotal]),
  O.ExactTotal,
:
:"{0}","{1}","{2}","{3}","{4}","{5}"}
,,,,,=SUM(F3:F<row-1>)

###Orders CSV Group

[Report:1,1,1,1,1,1]
Index,Name,Quantity,Price,Service,ExactTotal
@Breakfast,Wraps,Soup
{REPORT ORDER DETAILS: 
  O.ItemGroup,
  O.MenuItemName,
  O.Quantity,
  O.Price,
  ([O.Quantity]*[O.Price]-[O.ExactTotal]),
  O.ExactTotal,
:(MG=$1)
:"{0}","{1}","{2}","{3}","{4}","{5}"}
"$1 Total",,,,,=SUM(F<row-{REPORT ORDER COUNT:(MG=$1)}>:F<row-1>)


We can prepare CSV files that support Excel functions.

<row> tag will replace with row index. We can use expressions like <row+2> or <row-1> to reference previous, next rows…

Excel displays it fine.

PS: Because of Excel’s language settings (TR) I’ve used ; as delimiter.


Export customers details name phone etc to excel
How convert PDF (XPS Reports) to Excel?
Daily inventory count!
Profit&Loss Report with Data Export (CSV)
[Solved] Data Export Links Fail
What happened to CSV builder?
Custom Report Module - How to Add Payment Type & Department
Menu Price .txt file
Reports Versus Printer Templates
Gift certificates reports multiply by -1
Export customers details name phone etc to excel
Export customers details name phone etc to excel
Data Export for Inventory
#3

You can replace the ; delimiter with one your regional settings uses. Mine is , for example.


#4

Oohhhh very nice :slight_smile: XML as well!

This is just what I was looking for! :smiley: Yeah it works and I got it to export with your examples.

This has just saved me a lot of hassle of building external CSV reports via MSSQL.


#5

Sorry I forgot we discussed it in staff. We should have done feature report for it. I completely forgot.


#6

This interface on Report Explorer does not yet exist.
Data Exports appears in Report Explorer when you configure at least 1 Data Export Template.

Here is the Action …


#7

No I believe it exists now, at least in 5.1.45 it does:


#8

As a feature request, would be nice to be able to set the file type for the report.

If I set a filename, it of course follows the name I give. If I don’t, it wants to save as .txt, and I have to set to “all files” in the dialog then type filename with extension. What would be good is either the dialog gives choices (txt, csv, xml), or we can set the default type for the report.

Also, should disable/remove Save and Print buttons in Data Export section of reports, they serve no purpose (it prints or saves the actual page as seen on screen, with the report links - it doesn’t actually save/print the report).


[Solved] Data Export Links Fail
#9

Wow, absolutely correct. I didn’t have that last Data Export link until I created an Export Template, but now I do… :smile:


#10

While talking about it I should mention two features.

  • You can use printer tags like {CALL:X} or {DATE:X} in file names.
  • If you type a printer name as file name it will just print that export as it seen on screen. That might be useful to send data to web services via url printer or send data to serial port with port printer.

I’m noting requests.


#11

I’ve implemented this for 5.1.49 version.


#12

I’m trying to export daily sales totals by date to CSV, using the report template taken from Sales Report monthly with just a few modifications to suit my setup and support CSV output.

Here is the template:

Date,TicketSales,Discount,Total,Cash,Visa,Master,Amex,UnionPay
{REPORT TICKET DETAILS:
	T.Date,
	([T.TotalAmount]-[CA.Discount]-[T.Tax]).sum,
	CA.Discount.sum,
	T.TotalAmount.sum,
	PA.Cash.sum,
	PA.Visa.sum,
	PA.Master.sum,
	PA.Amex.sum,
	PA.UnionPay.sum
:(TS.Status=Paid)
:"{0}","{1}","{2}","{3}","{4}","{5}","{6}","{7}","{8}"}

It is just exporting the header tow and the data is all blank:

Any ideas? Have I got the syntax wrong?


#13

Are you sure there is data for it to export? What does the preview show?

That syntax looks good to me.


#14

Yes I have the identical report added as well as a normal report:

This was for the same period as I got the empty results in CSV. And any other period I also just get empty results.

The preview shows the same - header row but no data. As I think the preview is for the current work period I put through a few more transactions and confirmed they showed on other reports in preview mode. However the CSV one just shows header row only:


#15

Mark seems like formatting you’ve applied confuses template parser. I’ve fixed that but for a quick solution you can try converting that format to a one liner.


#16

Yeah I confirm it works if I put the formatted part on one line.

I thought it ignores whitespace, because also others above were posting with formatting and I thought the parser would ignore it. I guess they just posted for readability.


#17

it works but I think you added extra white space.

EDIT: Nevermind your right… odd.


#18

You thought right. It will work correctly with whitespace and tab characters on next update. That was a specific issue for 5.1.53.


#19

Hi - I am trying to create a data export for inventory… is this possible? If so could someone please show me the syntax… Or is there a list somewhere so I can figure it out myself… Thanks


#20

HI @markjw add the line as i did at the bottom:
[Purchases by Supplier:1, 1,1,1]
{REPORT INVENTORY TRANSACTION DETAILS:
,T.AccountName
, T.ItemGroup
, T.Quantity
, T.TotalPrice.Sum
,
:
:{0},{1},{2},{3}}