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?
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 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.
@@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.
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 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
.
Youll need to show you report settings
We wont be able to help you unless you show us your report syntax.
that 2 are script i copy above show
i want my report like this
please help me
Hi there,
I was trying
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
So I guess, that error is from this instruction:
(DATEPART(hour,[Date])>=@Hour
But I don’t know how to solve it.