Sales Per Hour Report

That goes in the {REPORT SQL DETAILS: tag, could you put your SQL into that?

Here is my price list report;

[Price List:5 ,3,3, 1, 2]
>Product | Portion | Group | Band | Price
{REPORT SQL DETAILS:
	SELECT
		product.[GroupCode] AS [Group],
		product.[Name] AS [Product],
		portion.[Name] AS [Portion],
		price.[PriceTag] AS [Price Band],
		price.[Price] AS [Price]
	FROM [MenuItems] product 
	LEFT JOIN [MenuItemPortions] portion 
	ON product.[Id] = portion.[MenuItemId] 
	LEFT JOIN [MenuItemPrices] price
	ON price.[MenuItemPortionId] = portion.[Id]
	ORDER BY product.[GroupCode]
	:F.Product,F.Portion,F.Group,F.Price Band,F.Price::{0} |{1}| {2}| {3}| £{4}
}

Just checked and works fine just like;

:F.Product,F.Portion,F.Group,F.Price Band,F.Price::{0} |{1}| {2}| {3}| ${4}

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

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

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.

I knew you would say that LOL

Go for the solution before then :wink:

1 Like

{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}}

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

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

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

So =M[(F.AvgTx)]

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

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.