Sales Per Hour Report

Thanks @Jesse but this does not work?

{REPORT SQL DETAILS@@SalesPerDay:'Sun':F.Day,=M[(F.Amount)],F.Tickets,F.AvgTx,F.Percent}

I assume that is convert to Money?

So @emre, any reason why either of these two formats do not work?

Formatting not obeyed:

{REPORT SQL DETAILS@@SalesPerDay:'Sun':F.Day,F.Amount,F.Tickets,F.AvgTx,F.Percent::{0}|${1}|{2}|${3}|{4}}

{REPORT SQL DETAILS@@SalesPerDay:'Sun':F.Day,=M[(F.Amount)],F.Tickets,F.AvgTx,F.Percent}

As a side note to formats not being obeyed:

#@JTRTech winner, winner, Chicken Dinner!
SELECT [Day], '$' + CAST([Amount] AS VARCHAR(15)), [Tickets], [AvgTx], [Percent] FROM @tbl_Sales

Nice work 'JTR - thanks Q & K - just see what Emre might say about format for future reference.

Hmm :slight_smile: SambaPOS processes @@Handler scripts as a single report so it is not suitable to embed into REPORT SQL DETAILS tag. This tag expects an SQL Script, not a SQL handler.

REPORT SQL DETAILS tag should work fine for graphql. I fixed it after next update and said “known issues fixed” but I should have explained it better. Sorry for that.

OK @emre - well JTRTech solution work very well - you just need to state that formatting is not suitable as the handling as implemented by QMcKay worked perfectly also.

1 Like

@@handler does work with {REPORT SQL DETAILS:x} and you yourself even documented it that way - that we are allowed to use T-SQL or a @@handler. Even the parameters work, and are injected into the @@SQL. The difference is that T-SQL uses parameters that follow semi-colon, while @@SQL uses parameters that follow colon.

Of course that will work, but the idea is to not need to embed such things within the script so that it works for anyone (any currency) without modification, and then you format the final output as you see fit - we should not need to change the SQL.

Well, sorry @QMcKay it was exciting for me at the time! Just going crazy with getting reports finalized so to have a little win was great. I totally agree with what you say:

That is how our experience has shaped us - less going back and redoing things 2x, 3x etc I HATE THAT! Would not of even got close without your help…

Really?? lol… seems like I’m a little lost there. You’re right I checked source code and it tries to handle it. I’ll test it with your latest backup.

1 Like

Neither of those should work AT ALL, since you are missing the colon after DETAILS.

In any case, I am curious why the formatting does not work. I mean, both of those will still output the numbers, but it appears to be completely ignoring =M( and the $ symbol as well.

For example, I tried this, and it does not break anything, but it ignores everything other than {1}

=('$'+'{1}')

I have a suspicion that the last part (the formatting portion) is being ignored completely, and it is simply providing output according to the F.field selection…

@QMcKay can you remember how we used it? It seems like I implemented using @@ handlers in report sql details tag for a different use (out of custom reports). As I guessed when used in custom reports custom report parser parses @@ handlers before executing report tags and it overwrites whole report.

I mean that will work…

{REPORT SQL DETAILS@@SalesPerDay:‘Sun’:F.Day,F.Amount,F.Tickets,F.AvgTx,F.Percent::{0}|${1}|{2}|${3}|{4}}

That will also work

{REPORT BLAH BLAH DETAILS@@SalesPerDay:‘Sun’:F.Day,F.Amount,F.Tickets,F.AvgTx,F.Percent::{0}|${1}|{2}|${3}|{4}}

as @@SalesPerDay:‘Sun’ handler replaces whole tag with the result of SQL Script. So it seems to ignore tag formatting.

Well that is interesting to note :wink: Can that behavior be changed @emre?

The first time we used it is in the Account Statement Tutorial - but in this case, the tag is used inside a Printer Template with a @@GetStatement, with no parameters …

Sorry to be bringing this thread back up, however I was trying to implement the Sales Per hour Script.
But ended up with

In the script I got

declare @StartDate datetime = convert(varchar(25),'{Start}',126)
declare @EndDate datetime = convert(varchar(25),'{End}',126)
declare @StartHour int = @1 -- set the First Hour you want to track, or supply via report parm
declare @EndHour int = @2  -- set the Last Hour you want to track, or supply via report parm

/************************************/

declare @Hour int = 0
declare @TotalSales money = 0.00

declare @tbl_Sales table
(
[ID]  INT IDENTITY(1,1) NOT NULL 
,[Hour] varchar(5)
,[Amount] decimal(9,2)
,[Tickets] int
,[AvgTx] decimal(5,2)
,[Percent] decimal(5,2)
)

set @Hour = @StartHour

WHILE @Hour < @EndHour+1
BEGIN
INSERT INTO @tbl_Sales
SELECT
  @Hour as [Hour]
, SUM(CASE WHEN (DATEPART(hour,[Date])>=@Hour AND DATEPART(hour,[Date])<@Hour+1)
           THEN [TotalAmount]
           ELSE 0 END) as [Amount]
, COUNT(CASE WHEN (DATEPART(hour,[Date])>=@Hour AND DATEPART(hour,[Date])<@Hour+1)
           THEN 1
           ELSE null END) as [Tickets]
,0,0
FROM [dbo].[Tickets]
WHERE [Date]>=@StartDate AND [Date]<=@EndDate
SET @Hour = @Hour + 1
END

INSERT INTO @tbl_Sales
SELECT 'TTL', sum([Amount]), sum([Tickets]), 0, 0 FROM @tbl_Sales

set @TotalSales = (SELECT [Amount] FROM @tbl_Sales WHERE [Hour]='TTL')

UPDATE @tbl_Sales SET
 [AvgTx] = (CASE WHEN [Tickets]=0 THEN 0 ELSE [Amount]/[Tickets] END)
,[Percent] = [Amount]/@TotalSales * 100

SELECT CASE
WHEN isnumeric([Hour])=1
THEN (CASE WHEN [Hour]<10 THEN '0'+[Hour] ELSE [Hour] END)+':00'
ELSE [Hour]
END as [Hour]
, [Amount], [Tickets], [AvgTx], [Percent]
 FROM @tbl_Sales

any idea where im going wrong?

Restart SambaPOS.

This happens sometimes when you create new Reports that contain @@SQL.

Ahh yep, I thought so, it started working as soon as I restarted. All good :smiley:


I Try What u show Above It Show This Message before Hour is Invalid Column so i change HH also same error that why can u Help Me Please @QMcKay

Youll need to show you report settings

i Try To Fix whole night now show this message again


another one

We wont be able to help you unless you show us your report syntax.

image
image
image
image
that 2 are script i copy above show
i want my report like this5f211a120699075f827a4d9248d4f426673e9d2b

please help me

Hi there,

I was trying

  • Report per Day
  • Report per Hour

In both case I have an error (translated):

Varchar data type conversion to datetime gives a value outside of the interval

It rings a bell but not sure on how to correct it. I think I saw that error when playing around with date time in a WHERE clause. I had to write datetime like that

  • 2020-03-15T14:40:00
    instead of
  • 2020-03-15 14:40:00

So I guess, that error is from this instruction:

(DATEPART(hour,[Date])>=@Hour

But I don’t know how to solve it.