Sales Per Hour Report

[pauln]

Yea but we are not using the {REPORT SQL DETAILS:x} it a REPORT SCRIPT being called simply by - @@SOMESCRIPT

[JTRTech]

Would it not work in a {REPORT SQL DETAILS:x}

[pauln]

I knew you were going to say that! It a big script, the one above for Daily Sales, has “Q” all over it. It has @DECLARES and all sorts of stuff out of script context so not sure how you put it into REPORT SQL DETAILS.

[JTRTech]

@pauln

You I knew you were going to say that!

I knew you would say that LOL

Go for the solution before then

[QMcKay]

{REPORT SQL DETAILS:x} supports @@SQL handlers and $parameters, so theoretically, you could use the {formattedOutput}

I need to dig up the proper syntax and delimiters, but this:

[Sales Per Hour: 15, 25, 15, 20, 20]
>Hour|Sales|Tx|AvgTx|%Sales
@@SalesPerHour:6,22

And the Fields coming out of the SQL are:

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

Then the Report should look something like this:

[Sales Per Hour: 15, 25, 15, 20, 20]
>Hour|Sales|Tx|AvgTx|%Sales
{REPORT SQL DETAILS:@@SalesPerHour;9,23:F.Hour,F.Amount,F.Tickets,F.AvgTx,F.Percent::{0}|{1}|{2}|${3}|${4}}
                    ^^^            ^^^^ ^^^^^^^^^^^^^^^^^^^^^^^^^
              @@handler     $parameters Fields........

That might not be exactly correct, but should be close. The semi-colon (;) is important.

EDIT: I just tried that, and it dos not work. Get this CommandText not initialized error. Not sure why. Something small is missing. @emre?

EDIT2: the following does not work either. In fact, whenever I use ;parm1,parm2 I get that CommandText error, even if the SQL has no @parms within it…

[Sales Per Hour: 15, 25, 15, 20, 20]
>Hour|Sales|Tx|AvgTx|%Sales
@9,23
{REPORT SQL DETAILS:@@SalesPerHour;$1,$2:F.Hour,F.Amount,F.Tickets,F.AvgTx,F.Percent::{0}|{1}|{2}|{3}|{4}}

[QMcKay]

This appears to work, without the semi-colon. But I can’t get the $ symbol to appear …

[Sales Per Hour: 15, 25, 15, 20, 20]
>Hour|Sales|Tx|AvgTx|%Sales
{REPORT SQL DETAILS:@@SalesPerHour:7,23:F.Hour,F.Amount,F.Tickets,F.AvgTx,F.Percent::{0}|{1}|{2}|${3}|{4}}
                                  ^                                                              ^
                      no semi-colon                                              $ does not appear

Top of SQL @@SalesPerHour …

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

[Jesse]

There are some tricks to get that to work. Let me look up one of ny past examples.

[Jesse]

Try =M[(field)] inline with the field.

So =M[(F.AvgTx)]

[pauln]

Great attempts @QMcKay - so I managed to convert to a {REPORT SQL DETAILS:x} but has not effect on Output?

[pauln]

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?

[pauln]

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}

[pauln]

@JTRTech

‘$’ + CAST(PaymentAmount AS VARCHAR(15))

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.

[emre]

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.

[pauln]

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.

[QMcKay]

@emre

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.

@@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.

@pauln

@JTRTech winner, winner, Chicken Dinner!

SELECT [Day], ‘$’ + CAST([Amount] AS VARCHAR(15))

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.

[pauln]

@QMcKay

Of course that will work

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:

@QMcKay

anyone (any currency) without modification

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…

[emre]

@QMcKay

@@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.

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.

[QMcKay]

@pauln

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}

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…

[emre]

@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.

[QMcKay]

@emre

That will also work:

{REPORT BLAH BLAH DETAILS@@SalesPerDay:‘Sun’

… as @@SalesPerDay:'Sun' handler replaces whole tag with the result of SQL Script.

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

@emre

can you remember how we used it?

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 …

@QMcKay

The Template fields must match the names of the SQL fields being returned, so I modified the field names to match the SQL names:

Customer Account Statements and Payments (Custom)

This Tutorial will demonstrate how to build a Custom Account Handling Screen for Customers. It allows for Printing detailed Customer Account Statements, making Payments to Customer Accounts (via the built-in Payment Screen), and Crediting Customer Accounts. Requires SambaPOS 5.1.56 or higher Operations The Customer Account Operations Screen we will build and Automate is shown on the right. [image] Quick Nav: Account Type and Account for Transfers (for Account Payments via Paym…