A Large Complicated Setup

[KuriGohan_And_Kameha]

This is my entire report

[Sales Total:1, 1]
{REPORT TICKET TYPES:!PreOrder && TotalAmount >= 0}

[Ring Ups By Department:1, 1]
Dry Cleaning|{ACCOUNT TRANSACTION TOTAL:Dry Cleaning Sale Transaction:Dry Cleaning}
Shoes|{ACCOUNT TRANSACTION TOTAL:Shoes Sale Transaction:Shoes}
Tailoring|{ACCOUNT TRANSACTION TOTAL:Tailoring Sale Transaction:Tailoring}
Photos|{ACCOUNT TRANSACTION TOTAL:Photo Sale Transaction:Photos}
Accessories|{ACCOUNT TRANSACTION TOTAL:Accessories Sale Transaction:Accessories}

==>>TOTAL|[={ACCOUNT TRANSACTION TOTAL:Dry Cleaning Sale Transaction:Dry Cleaning} + {ACCOUNT TRANSACTION TOTAL:Shoes Sale Transaction:Shoes} + {ACCOUNT TRANSACTION TOTAL:Tailoring Sale Transaction:Tailoring} + {ACCOUNT TRANSACTION TOTAL:Photo Sale Transaction:Photos} + {ACCOUNT TRANSACTION TOTAL:Accessories Sale Transaction:Accessories}]

[Pre Orders:1, 1]
{REPORT TICKET TYPES:PreOrder && TotalAmount >= 0}

[Refunds:1, 1]
{REPORT TICKET TYPES:TotalAmount < 0}

[Payments:2, 1, 2]
{REPORT PAYMENT DETAILS:P.Name,P.Amount.Percent,P.Amount.Sum:Payment.Amount > 0}
>Total|{REPORT PAYMENT DETAILS:P.Amount.Sum:Payment.Amount > 0}

[Order States:2,1,2]
{REPORT ORDER STATES}
{REPORT ORDER DETAILS:OSV.Collections=Collected}

[Refund Payments:2, 1, 2]
{REPORT REFUND PAYMENTS}

[Ticket Details:2, 1, 2]
>>Ticket Counts
@!{TICKET TYPE LIST}
$1|{REPORT TICKET COUNT:(TY=$1)}|{REPORT TICKET TOTAL:(TY=$1)}
Total|{REPORT TICKET COUNT}|{REPORT TICKET TOTAL}
Amount per Ticket||[=F(TN('{REPORT TICKET TOTAL}')/TN('{REPORT TICKET COUNT}'))]
>>Order Counts
@!{TICKET TYPE LIST}
$1|{REPORT ORDER COUNT:(TY=$1)}|{REPORT ORDER TOTAL:(TY=$1)}
Total|{REPORT ORDER COUNT: }|{REPORT ORDER TOTAL: }
Orders per Ticket||[=F(TN('{REPORT ORDER COUNT: }')/TN('{REPORT TICKET COUNT: }'))]
Amount per Order||[=F(TN('{REPORT ORDER TOTAL: }')/TN('{REPORT ORDER COUNT: }'))]
>>Ticket Counts per State
{REPORT TICKET STATES}
>>Order Counts per State
{REPORT ORDER STATES}

[Payment Details:2, 1, 2]
@{TICKET TYPE LIST}
@{TAX TYPE LIST}
>$1
{REPORT PAYMENT DETAILS:P.Name,P.Amount.Percent,P.Amount.Sum:(TY=$1)}
{REPORT CALCULATION DETAILS:C.Name,C.X,C.CalculationAmount.Sum:(TY=$1)}
$2||{REPORT TICKET DETAILS:TX.$2.Sum:(TY=$1)}

[User Sales:1, 1] 
{REPORT ORDER DETAILS:O.User,O.ExactTotal.Sum}
@{REPORT PAYMENT DETAILS:P.User,P.Amount.Sum::{0}:,} 
[Settled by $1:1, 1, 1] 
{REPORT PAYMENT DETAILS:P.Name,P.Amount.Percent,P.Amount.Sum:(PU=$1)} 
>Total Income|{REPORT PAYMENT DETAILS:P.Amount.Sum:(PU=$1)}

Unfortunately I don’t have SQL 2016 I have SQL 2014

I’m experimenting with this:

[test:1,1,1,1]
{REPORT ORDER DETAILS:OS.Collection,T.TicketNumber,O.MenuItemName,T.LastPaymentDate}

But it still shows old tickets on old workperiods. Maybe its because they were reserved/ordered in during the old work period, because I see tickets Checked in/Collected entirely instead of per order show on this work period.

[KuriGohan_And_Kameha]

The edit does not seem to be making any changes. Does it need anything else?

[QMcKay]

I only edited your post so that it contains code formatting so it can be properly read. Don’t use the blockquote function for reports and other code, use the code function. I did not change anything in your report.

I have not had time to look at your report yet.

[KuriGohan_And_Kameha]

It is mostly just the work period report, with an extra part at the top for the transaction details.

[KuriGohan_And_Kameha]

Trying this report, The second part is correct, as it is showing my ordered in items, it is te first part that is not working, any ideas why?

[Dry Cleaning Collected:1,1,1,1,1,1]
>CUSTOMER|ITEM|PORTION|QTY|TOTAL
{REPORT ORDER DETAILS:EC.Customer.Full Name.asc,O.MenuItemName,O.PortionName,O.Quantity,O.ExactTotal:(ODI=True) && (TCET=Customers) && (OS.Collection==Collected) && (MG=Black) OR (MG=Blue) OR (MG=Red) OR (MG=Brown) OR (MG=Checked) OR (MG=Cream) OR (MG=Fawn) OR (MG=Green) OR (MG=Grey) OR (MG=Lime) OR (MG=Navy) OR (MG=Orange) OR (MG=Dry Cleaning) OR (MG=Pink) OR (MG=Purple) OR (MG=Striped) OR (MG=White) OR (MG=Yellow)}
>> TOTAL|||||[=F(TN('{REPORT ORDER DETAILS:O.ExactTotal.sum:(ODI=True) && (TCET=Customers) && (OS.Collection==Collected) && (MG=Black) OR (MG=Blue) OR (MG=Red) OR (MG=Brown) OR (MG=Checked) OR (MG=Cream) OR (MG=Fawn) OR (MG=Green) OR (MG=Grey) OR (MG=Lime) OR (MG=Navy) OR (MG=Orange) OR (MG=Dry Cleaning) OR (MG=Pink) OR (MG=Purple) OR (MG=Striped) OR (MG=White) OR (MG=Yellow)}'),'#,#0.00')]

[Dry Cleaning Ordered:1,1,1,1,1,1]
>CUSTOMER|ITEM|PORTION|QTY|TOTAL
{REPORT ORDER DETAILS:EC.Customer.Full Name.asc,O.MenuItemName,O.PortionName,O.Quantity,O.ExactTotal:(ODI=True) && (TCET=Customers) && (OS.Collection==Ordered In) && (MG=Black) OR (MG=Blue) OR (MG=Red) OR (MG=Brown) OR (MG=Checked) OR (MG=Cream) OR (MG=Fawn) OR (MG=Green) OR (MG=Grey) OR (MG=Lime) OR (MG=Navy) OR (MG=Orange) OR (MG=Dry Cleaning) OR (MG=Pink) OR (MG=Purple) OR (MG=Striped) OR (MG=White) OR (MG=Yellow)}
>> TOTAL|||||[=F(TN('{REPORT ORDER DETAILS:O.ExactTotal.sum:(ODI=True) && (TCET=Customers) && (OS.Collection==Ordered In) && (MG=Black) OR (MG=Blue) OR (MG=Red) OR (MG=Brown) OR (MG=Checked) OR (MG=Cream) OR (MG=Fawn) OR (MG=Green) OR (MG=Grey) OR (MG=Lime) OR (MG=Navy) OR (MG=Orange) OR (MG=Dry Cleaning) OR (MG=Pink) OR (MG=Purple) OR (MG=Striped) OR (MG=White) OR (MG=Yellow)}'),'#,#0.00')]

The collected items should be different and the total should be 205,00

[QMcKay]

@KuriGohan_And_Kameha

(OS.Collection==Collected)
(OS.Collection==Ordered In)

Both the first and second part have that incorrect in 2 places. For bracketed constraints, use only single =

[KuriGohan_And_Kameha]

I have just changed this, and unfortunately realised that the reports aren’t working at all in any way required. It’s strange for me because I have a report closely resembling this one that does work & sort correctly, only using GStatus and not Collection

[KuriGohan_And_Kameha]

[Voided Gifted & Refunded Items:2,1,1,1]
{REPORT ORDER DETAILS:O.MenuItemName,O.PortionName,O.Quantity,OS.GStatus:(OS.GStatus=Gift) OR (OS.GStatus=Refund) OR (OS.GStatus=Void)}

This is the report

[KuriGohan_And_Kameha]

I think almost I have it now!

[Dry Cleaning Collected:1,1,1,1,1,1]
>ITEM|PORTION|QTY|TOTAL
{REPORT ORDER DETAILS:O.MenuItemName,O.PortionName,O.Quantity,O.ExactTotal:(OS.Collection=Collected):(MG=Black) OR (MG=Blue) OR (MG=Red) OR (MG=Brown) OR (MG=Checked) OR (MG=Cream) OR (MG=Fawn) OR (MG=Green) OR (MG=Grey) OR (MG=Lime) OR (MG=Navy) OR (MG=Orange) OR (MG=Dry Cleaning) OR (MG=Pink) OR (MG=Purple) OR (MG=Striped) OR (MG=White) OR (MG=Yellow)}
>> TOTAL|||||[=F(TN('{REPORT ORDER DETAILS:O.ExactTotal.sum:(OS.Collection=Collected):(MG=Black) OR (MG=Blue) OR (MG=Red) OR (MG=Brown) OR (MG=Checked) OR (MG=Cream) OR (MG=Fawn) OR (MG=Green) OR (MG=Grey) OR (MG=Lime) OR (MG=Navy) OR (MG=Orange) OR (MG=Dry Cleaning) OR (MG=Pink) OR (MG=Purple) OR (MG=Striped) OR (MG=White) OR (MG=Yellow)}'),'#,#0.00')]

[Dry Cleaning Ordered:1,1,1,1,1,1]
>ITEM|PORTION|QTY|TOTAL
{REPORT ORDER DETAILS:O.MenuItemName,O.PortionName,O.Quantity,O.ExactTotal:(OS.Collection=Ordered In):(MG=Black) OR (MG=Blue) OR (MG=Red) OR (MG=Brown) OR (MG=Checked) OR (MG=Cream) OR (MG=Fawn) OR (MG=Green) OR (MG=Grey) OR (MG=Lime) OR (MG=Navy) OR (MG=Orange) OR (MG=Dry Cleaning) OR (MG=Pink) OR (MG=Purple) OR (MG=Striped) OR (MG=White) OR (MG=Yellow)}
>> TOTAL|||||[=F(TN('{REPORT ORDER DETAILS:O.ExactTotal.sum:(OS.Collection=Ordered In):(MG=Black) OR (MG=Blue) OR (MG=Red) OR (MG=Brown) OR (MG=Checked) OR (MG=Cream) OR (MG=Fawn) OR (MG=Green) OR (MG=Grey) OR (MG=Lime) OR (MG=Navy) OR (MG=Orange) OR (MG=Dry Cleaning) OR (MG=Pink) OR (MG=Purple) OR (MG=Striped) OR (MG=White) OR (MG=Yellow)}'),'#,#0.00')]

This worked perfectly, even showing me items from an older work period, it only doesn’t sort anymore on group code.

[QMcKay]

@KuriGohan_And_Kameha

it only doesn’t sort anymore on group code

It won’t sort unless you have the field in your selection, like O.GroupCode.asc (or whatever the word is for Group Code).

[KuriGohan_And_Kameha]

I think I meant filtering not sorting, sorry.
It’s not filtering this part:
(MG=Black) OR (MG=Blue) OR (MG=Red) OR (MG=Brown) OR (MG=Checked) OR (MG=Cream) OR (MG=Fawn) OR (MG=Green) OR (MG=Grey) OR (MG=Lime) OR (MG=Navy) OR (MG=Orange) OR (MG=Dry Cleaning) OR (MG=Pink) OR (MG=Purple) OR (MG=Striped) OR (MG=White) OR (MG=Yellow)

As it is showing me items from all the other groups.

And when I change the : in this bit:
(OS.Collection=Collected):(MG=Black)

to and it doesn’t work anymore.

[QMcKay]

@KuriGohan_And_Kameha

(OS.Collection=Collected):(MG=Black)

That is not valid. The constraints must be in the correct section…

{REPORT BLAH:fields:contraints:format}

That gave me an idea. There is a fairly new feature for “server side filters” which allow you to do “special” things like use filters that are not available to the report by specifying actual DB Table Column Names. Here is an example:

[ELG Event Log:1,9]
@{REPORT TASK DETAILS:T.Id.asc:(TST=ELG Event Log) && 'content.Contains("Cancel")' OR 'content.Contains("Void")' OR 'content.Contains("Gift")' OR 'content.Contains("Price Change")' OR 'content.Contains("Reopen")':,}

>>{REPORT TASK DETAILS:T.Id,[T.Identifier]+' (Task Id/Ident)':(TST=ELG Event Log) && T.Id=$1}
>>{REPORT TASK DETAILS:TSC.terminalName,[TSC.userName]+' (Terminal/User)':(TST=ELG Event Log) && T.Id=$1}
>>{REPORT TASK DETAILS:TSC.workperiodId,[TSC.eventCount]+' (WPID/eventCount)':(TST=ELG Event Log) && T.Id=$1}
>>{REPORT TASK DETAILS:TSC.ticketId,[TSC.ticketNo]+' (Ticket Id/No)':(TST=ELG Event Log) && T.Id=$1}
>{REPORT TASK DETAILS:[T.Name]:(TST=ELG Event Log) && T.Id=$1}

{REPORT TASK DETAILS:T.ContentText:(TST=ELG Event Log) && T.Id=$1}

… specifically this part:

{REPORT TASK DETAILS:T.Id.asc:(TST=ELG Event Log) && 'content.Contains("Cancel")' OR 'content.Contains("Void")' OR 'content.Contains("Gift")' OR 'content.Contains("Price Change")' OR 'content.Contains("Reopen")':,}

In the Tasks DB Table, there is a column named “Content” and the “Contains” operator is akin to SQL “LIKE” in a WHERE clause. You can also just use equality operator, like:

'content="Cancel"'

More info:

@emre

Server Side Expressions for Custom Reports

[KuriGohan_And_Kameha]

This might work if i figure out how to compare the MenuItemId Fields between the Orders and the MenuItems tables, as Orders doesn’t have a ItemGroup Option.

-This leads to a probability, isn’t this why my error is occuring? I’ve never used the (MG=) option before.-

Never mind, my error is different now, a ordered in Item is shoing on both reports

[KuriGohan_And_Kameha]

Never mind, It’s not an error, the item was ordered and collected on the same day, so obviously it should show on both reports.