Data Exports feature

V5 Question
21

Hi, Please help. I’ve read about exporting to .xls but still can get this report to be transfered in columns. Please help… I have followed the instructions but I still can`t.

[Sales:5, 2, 2,1]
@{REPORT ORDER DETAILS:O.MenuItemName,O.ExactTotal.Sum.desc:(ODI=True):{0}:,}
>{REPORT ORDER DETAILS:O.MenuItemName,O.Quantity.Sum.desc,O.ExactTotal.Sum.desc:(ODI=True) and O.MenuItemName="$1"}
{REPORT ORDER DETAILS:'     '+[O.PortionName],O.Quantity.Sum.desc,O.ExactTotal.Sum.desc:(ODI=True) and O.MenuItemName="$1":2}

Thanks in advance!!!

22

See the last bit on the first line? :{0}:,
Add this to the other lines, this is what sets the output format and separator ie the comma and you export to csv and open in excel

23

Thanks! I did that and the format is better, just one thing if you dont mind, one number is still wrong as In the real report it shows as this…
Española 14 3,620.66
Tabla mixta 10 3,350.3

and in the report that I’m oppening in .xls, it shows as this:
Española 143 620.66
Tabla mixta 103 350.3

I asume is because of the comma, but I can’t that coma in the formula:
[Sales:5, 2, 2,1]
@{REPORT ORDER DETAILS:O.MenuItemName,O.ExactTotal.Sum.desc:(ODI=True):{0}:,}

{REPORT ORDER DETAILS:O.MenuItemName,O.Quantity.Sum.desc,O.ExactTotal.Sum.desc:(ODI=True) and O.MenuItemName=“$1”:{0}:,}
{REPORT ORDER DETAILS:’ '+[O.PortionName],O.Quantity.Sum.desc,O.ExactTotal.Sum.desc:(ODI=True) and O.MenuItemName=“$1”:2:{0}:,}

thanks a lot!

24

So it shows the number split into two columns where the comma would be in the report?

25

Exactly it should be like this:
Española 14 3,620.66
Tabla mixta 10 3,350.3

As the last number is currency $$, it has a comma and .xls is taking it as a space.

So .xls is showing me the result as:
Española 143 620.66
Tabla mixta 103 350.3

How can I fix it?

26

So is it a space or a new cell?
Sure its not your regional settings in windows?
If its in a single cell either way you should be able to define formatting in excel

27

Presentación1.pdf (54.8 KB)
2.pdf (48.3 KB)

Hope this helps… it also shows the Word “normal” ???

28

and just saw that if are small figures it doesnt separate them at all.

example:
ate normal8716.02

and it should be:
ate 8 (pieces) (Total of) 716.2

29

This is not the correct way to use data exports feature. You have just taken part from another report (either in your system or from the forum) and pasted it into the data export.

Since you only have one {0} you are getting the output with spaces between fields. Excel is doing it correctly - you have one , so it assumes that is your comma to delimit the columns.

What you need to do instead is explicitly state your output and delimiters (i.e. comma) in output format. Have a look at the example earlier in this topic:

3 Likes
31

This is because “Normal” is your portion, and you have put in your report template that you want the portion, this line:

So I think you don’t really understand how it works and have just pasted this from somewhere and thought it will “just work”.

So what exactly do you want to show in your report? I am guessing you don’t want portion given this comment. Do you want it to export exactly like the 2.pdf file you uploaded?

32

No it’s because the fields haven’t been defined in the report. With data exports, you need to define the field formatting. Putting {0} means you just get exactly what {REPORT ORDER DETAILS} is outputting, which in this case is just each column separated by a space or tab. You need to state the exact output format, so for example "{0}","{1}","{2}","{3}","{4}","{5}" like I referenced to the earlier post.

1 Like
33

Thanks to both!!

You are correct, I’m not into systems so I do not know the “language” so yes I did copy the report of the ítem sale report which I find it very useful.

I want to know: How many ítems I sold (Ítem name and quantity) and the sum that that sales represent (4)

thank you guys!!!

34

Do you want it to export exactly like the 2.pdf file you uploaded?

yes

35

Here you go:

[Sales:1,1,1]
Name,Quantity,ExactTotal
@{REPORT ORDER DETAILS:O.MenuItemName,O.ExactTotal.Sum.desc:(ODI=True):{0}:,}
{REPORT ORDER DETAILS: 
  O.MenuItemName,
  O.Quantity.Sum.desc,
  O.ExactTotal.Sum.desc
:(ODI=True) and O.MenuItemName="$1"
:"{0}",{1},{2}}
"TOTAL",{REPORT ORDER DETAILS:O.Quantity.Sum:(ODI=True)},{REPORT ORDER DETAILS:O.ExactTotal.Sum:(ODI=True)}

That will produce something like this:

image
image.png1366×587 36.3 KB

My total row seems a bit off - the total from the quantity column is 41 but it’s saying 45. I’m not sure why right now, maybe someone else can suggest a reason?

This will open up in Excel correctly in 3 columns.

4 Likes
36

Markjw thank you very much I really appreciate it. It opens like this,
Presentación1.pdf (59.2 KB)

but is good enough. you really helped and I will study the commands to further reports. THANKS!!!

37

This is because your regional settings in Windows use a ; instead of , for separator.

Try updating the template to use ; instead of , as the separators.

For example:

[Sales:1,1,1]
Name;Quantity;ExactTotal
@{REPORT ORDER DETAILS:O.MenuItemName,O.ExactTotal.Sum.desc:(ODI=True):{0}:,}
{REPORT ORDER DETAILS: 
  O.MenuItemName,
  O.Quantity.Sum.desc,
  O.ExactTotal.Sum.desc
:(ODI=True) and O.MenuItemName="$1"
:"{0}";{1};{2}}
"TOTAL";{REPORT ORDER DETAILS:O.Quantity.Sum:(ODI=True)},{REPORT ORDER DETAILS:O.ExactTotal.Sum:(ODI=True)}
2 Likes
38

Hello, do you know why 2 sándwiches which have sales, recepies, inventory etc doesn´t appear in the report?

We do have had sales and nothing

39

You should really start a new topic as this has nothing to do with data exports

We have no idea why it doesn’t show as you haven’t given us an info on how the product/recipe is setup

Youll need to show screenshots of your product setup, inventory setup and recipe

And then a copy of your report settings, also what report are you referring to??

1 Like