Need help in custom report

I would like to share a modified Orders by Officer report its optional but improves it. This report will work even for new Officer entities added without the need to edit report.

Report:

[Orders by Officer:2, 1, 1]
>Item|Quantity|Total
@{CALL:officers.getofficers()}
>>Orders by $1
{REPORT ORDER DETAILS:O.MenuItemName,O.Quantity.Sum,O.ExactTotal.Sum:(TEN.Officer=$1)}
>$1 Total|{REPORT ORDER DETAILS:O.ExactTotal.Sum:(TEN.Officer=$1)}

Script:

function getofficers(){
var qry = "SELECT e.[Name] FROM [Entities] e JOIN [EntityTypes] et on et.[Id]=e.[EntityTypeId] WHERE et.[Name] = 'Officer' ORDER BY e.[Name]";
var r = sql.Query(qry).Join(',');
return r;
}

PS. @emre I see what you did with >> is that new? If not I must have missed that.

@Jesse that hack also can be used instead of SQL. We’re benefiting from grouping and formatting features here.

@{REPORT ORDER DETAILS:EN.Officer,O.Quantity.Sum::{0}:,}
1 Like

Wow good to know. I am trying to figure out what its doing. But thats a different conversation.

So a simpler version of what I just showed would be ignore the script completely and use this report.

[Orders by Officer:2, 1, 1]
>Item|Quantity|Total
@{REPORT ORDER DETAILS:EN.Officer,O.Quantity.Sum::{0}:,}
>>Orders by $1
{REPORT ORDER DETAILS:O.MenuItemName,O.Quantity.Sum,O.ExactTotal.Sum:(TEN.Officer=$1)}
>$1 Total|{REPORT ORDER DETAILS:O.ExactTotal.Sum:(TEN.Officer=$1)}
1 Like

Yes > is bold >> is bolder. :slight_smile:

1 Like

That changes a lot. That feature right there really expands what you can do with Report Tags previously I thought were only available with SQL.

{REPORT ORDER DETAILS:EN.Officer,O.Quantity.Sum::{0}:,}
^--------------------^-------------------------^^---^-^

So we have 5 sections here

  1. Tag Name.
  2. Fields.
  3. Expression (left empty)
  4. Line format (as we have only {0} first field displayed. We use that feature on data export templates.)
  5. Line delimiter. (,) means all lines delimited with , char.

As it is also a reporting tag it displays only values exists in reporting rage. Good for this sample but might be a disadvantage for some cases.

2 Likes

@a.samy I think you also have a specific ticket type so you may want to include it as well. So assuming your ticket type is named as Officer Ticket, you can change expressions like…

(TEN.Officer=$1) and (TY=Officer Ticket)

WOW , u guy’s rock
i want to say thank u very much for all this work , it work perfect

but the sales by product still don’t work and the item sales by product tag

That will be available with next update. 5.1.51 there was something he added specifically for it to work.

LOL look what I said on the top of my post.

1 Like

I think you missed an arrow.

3 Likes

Too bad I don’t have @QMcKay’s big red arrow.

3 Likes

btw. you can use Custom Product Tags instead. Assuming you’ll configure a custom tag named Type you can change report like that.

[Sales by Product Tags:1, 1]
{REPORT ORDER DETAILS:MT.Type,O.ExactTotal.Sum}
>Total|{REPORT ORDER DETAILS:O.ExactTotal.Sum}

Anytime …

4 Likes

wow look how you’ve placed them without covering text :wink: Really precise work.

3 Likes

Report with Product tag,
How to make like this
Sales item Report
Group : Food (this is product tag)
Group Code : Breakfast
Item 1 10 (sum qty) 1000(Total)
item 2
item 3
Total … 1000

Group Code : Soup
Item s1 10 (sum qty) 1000(Total)
item s2
item s3
Total … 1000

Group : Drink
Group Code : Juice
Item j1 10 (sum qty) 1000(Total)
item j2
item j3
Total … 1000

What is sum qty? Is this a report or a ticket?

yes, it report end of period, sum qty is total sales item at the day

What is total then? Isn’t that the same thing?

Total is Total Amount in price