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}
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
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}:,}
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
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:
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?
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.
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)
[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)}
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.
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)}