Using Ticket Tag in Custom Reports:How to Sum Ticket Tag Value?

Version 4 V4 Question
1

using the tutorial tracking number of person i have:

Guest:|{REPORT TICKET TOTAL:(TT=Guest)} ----> gives total amount from guest
{REPORT TICKET COUNT:(TT=Guest) ------> gives number of occurence of tag used

but how to know the total number of guest arrived?

and this report shows nothing

[#Pax Tickets:1,1]
[N]Guest,[C]Amount
{REPORT TICKET DETAILS:TT.Guest,TN.TotalAmount:(TT=Guest)}

i dont why the table report not working

2

What is your Tag Group name for Guest?

3

Because it is wrong syntax. it should look like this

[#Pax Tickets:1,1]
>[N]Guest|[C]Amount
{REPORT TICKET DETAILS:TT.Guest,TN.TotalAmount:(TT=Guest)}

BTW in the future this will work.

{REPORT TICKET DETAILS:([TT.Guest]).Sum,TN.TotalAmount:(TT=Guest)}

This means you will not have to use Table view to sum.

Example:

[Hours Worked:1, 1]

{REPORT ENTITY STATE DETAILS:L.EntityName,([L.Hours]).Sum:(ELSN=TCStatus) AND EntityStateLog.GetHours()>0 AND (ELSS=PunchIn)}

>Total Hours:  {REPORT ENTITY STATE DETAILS:([L.Hours]).Sum:(ELSN=TCStatus) AND EntityStateLog.GetHours()>0 AND (ELSS=PunchIn)}

Notice how ([L.Hours]).Sum works for Total to sum the Hours between both Entities without using Table View

Just something to look forward too. Thought I would show a teaser :stuck_out_tongue:

1 Like
4

for time being what is the solution…to use a json sql query ?

and when is the next version realeasing?

5

ya i had realized that and maange to change also but couldnt get the expected result

6

Yes SQL would work. You could design a query for this.

7

manage to create the sql query

SELECT
SUM(CONVERT(INT,substring(cast(TicketTags as nvarchar(4000)),
CharIndex(‘{“TN”:“Guest”,“TT”:0,“TV”:"’,cast(TicketTags as nvarchar(4000)))+29,
CharIndex(‘"}’,cast(TicketTags as nvarchar(4000)),
CharIndex(‘{“TN”:“Guest”,“TT”:0,“TV”:"’,cast(TicketTags as nvarchar(4000)))+27
)- CharIndex(‘{“TN”:“Guest”,“TT”:0,“TV”:"’,cast(TicketTags as nvarchar(4000)))-29)))
from Tickets
Where Date > ‘{Start}’ and Date < ‘{End}’

but in this case the order of the ticket tag is important i-e Guest ticket tag should ALWAYS be handled first else an error will occur

8

so waiting for the new version

9

@madiha, can you copy and paste the JSON string here and indicate which Tag within that string relates to the value you are trying to retrieve?

I don’t think it should matter where the Tag lands in the JSON string, so long as we have a definitive way to identify it, we should be able to parse it.

EDIT: never mind, it appears you are looking for the value shown below, correct?

"TN":"Guest","TT":0,"TV":"thisvaluehere"

If that is the case, notice how I omitted the opening brace {. If you do the same, it won’t matter where the value lands in the string - it could be first, middle, last…

EDIT2: unless the value after TT (0) can change? If that is the case, you’re correct, it won’t work as you’ve designed it, but you could get around that too.

Let me know how it works out…

10

well sorry to say but i dont know the function of this value can please state and i have followed the tutorial tracking number of persons

in the system its compulsory to state the number of pax when taking order

11

i have tried with omittng the brace but it still gave me error…because it wasnt finding from whole string

12

This should work if the Guest count < 10

SELECT 
substring([TicketTags],charindex('{"TN":"Guest","TT":0,"TV":"',[TicketTags])+len('{"TN":"Guest","TT":0,"TV":"'),1) as [GuestCount]
FROM [Tickets]
WHERE [Date] > '{Start}' and [Date] < '{End}'

EDIT: this is better because it should support Guest count up to 99:

SELECT 
CASE ISNUMERIC(substring([TicketTags],charindex('{"TN":"Guest","TT":0,"TV":"',[TicketTags])+len('{"TN":"Guest","TT":0,"TV":"'),2))
       WHEN 1 THEN convert(int,substring([TicketTags],charindex('{"TN":"Guest","TT":0,"TV":"',[TicketTags])+len('{"TN":"Guest","TT":0,"TV":"'),2))
       ELSE convert(int,substring([TicketTags],charindex('{"TN":"Guest","TT":0,"TV":"',[TicketTags])+len('{"TN":"Guest","TT":0,"TV":"'),1))
       END as [GuestCount]
FROM [Tickets]
WHERE [Date] > '{Start}' and [Date] < '{End}'
13

thanks but exe giving me “Conversion failed when converting the nvarchar value ‘:’ to data type int.”. i think i need to adjust the length…which part do i adjust. i have tried to play around but failed

14

Hard to say without seeing your data. Here, this is a little more foolproof, but I can’t know for sure unless you show the [TicketTags] field for the record that fails, which will be found after the last Row returned. Let me know if this works:

SELECT 
CASE isnull(charindex('{"TN":"Guest","TT":0,"TV":"',[TicketTags]),0)
WHEN 0 THEN 0
ELSE
 (
CASE ISNUMERIC(substring([TicketTags],charindex('{"TN":"Guest","TT":0,"TV":"',[TicketTags])+len('{"TN":"Guest","TT":0,"TV":"'),2))
       WHEN 1 THEN convert(int,substring([TicketTags],charindex('{"TN":"Guest","TT":0,"TV":"',[TicketTags])+len('{"TN":"Guest","TT":0,"TV":"'),2))
       ELSE convert(int,substring([TicketTags],charindex('{"TN":"Guest","TT":0,"TV":"',[TicketTags])+len('{"TN":"Guest","TT":0,"TV":"'),1))
       END
       )
END as [GuestCount]
FROM [Tickets]
15

i hv tried the query it didnt give error but showed 0 values only

this is the data in the field:

[{“TN”:“Guest”,“TT”:1,“TV”:“3”},{“TN”:“PMT 16.42.993 [Cash]”,“TT”:0,“TV”:“50.00”},{“TN”:“TND”,“TT”:0,“TV”:“50.00”},{“TN”:“CHG”,“TT”:0,“TV”:“3.10”},{“TN”:“PMT 17.55.901 [Cash]”,“TT”:0,“TV”:“50.00”}]

and tag:

8d7a4867468eb330.png1366×768 102 KB

16

You’re getting zeros because of the value after "TT":1 … the query is looking for "TT":0

So you could change that portion of the string throughout the query.

I have a feeling that the TT value is to indicate the Tagging Type, which you have set to Numeric. Did you change that recently? I ask because, in your original query, you were searching for "TT":0 and now your data is showing "TT":1

17

its numeric as shown below:

fcc7cc9370f121f1.png1366×768 102 KB

18

oh ya you are right in this case why is the sql giving me result!!

19

after changing the 0 to 1 the query is running. thank you for the help one more thing how do i sum the count?

20

Wrap the whole query with an aggregate-type query:

SELECT SUM([GuestCount]) as [GuestCountTotal]
FROM
(

SELECT 
CASE isnull(charindex('{"TN":"Guest","TT":1,"TV":"',[TicketTags]),0)
WHEN 0 THEN 0
ELSE
 (
CASE ISNUMERIC(substring([TicketTags],charindex('{"TN":"Guest","TT":1,"TV":"',[TicketTags])+len('{"TN":"Guest","TT":1,"TV":"'),2))
       WHEN 1 THEN convert(int,substring([TicketTags],charindex('{"TN":"Guest","TT":1,"TV":"',[TicketTags])+len('{"TN":"Guest","TT":1,"TV":"'),2))
       ELSE convert(int,substring([TicketTags],charindex('{"TN":"Guest","TT":1,"TV":"',[TicketTags])+len('{"TN":"Guest","TT":1,"TV":"'),1))
       END
       )
END as [GuestCount]
FROM [Tickets]

) gcsum
2 Likes