Data Exports feature

V5 Question
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)

3 Likes
2

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

SYNTAX

{REPORT ORDER DETAILS:<expression>:<line format>:<delimiter>}

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>)

000100.jpg2515×1465 320 KB

000101.jpg2515×1465 257 KB

We can prepare CSV files that support Excel functions.

000102.jpg2518×1465 369 KB

<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.

000103.jpg1351×1381 281 KB

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

000104.jpg2518×1465 458 KB

000105.jpg1543×1450 287 KB

5 Likes
3

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

1 Like
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.

1 Like
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:

Pasted image1024×768 83.3 KB

2 Likes
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).

Pasted image743×254 24.6 KB

1 Like
9

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

Pasted image775×446 21.1 KB

1 Like
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.

3 Likes
11

I’ve implemented this for 5.1.49 version.

1 Like
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:

Pasted image901×294 13.4 KB

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:

Pasted image1586×414 21.5 KB

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.

1 Like
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.

2 Likes
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}}