Sales Per Hour Report

JTRTech · July 26, 2016, 1:02pm

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}

pauln · July 26, 2016, 1:04pm

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

JTRTech · July 26, 2016, 1:04pm

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

pauln · July 26, 2016, 1:06pm

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 · July 26, 2016, 1:07pm

I knew you would say that LOL

Go for the solution before then

QMcKay · July 26, 2016, 6:51pm

{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 · July 26, 2016, 9:56pm

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 · July 26, 2016, 10:28pm

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

Jesse · July 26, 2016, 10:35pm

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

So =M[(F.AvgTx)]

pauln · July 27, 2016, 2:12am

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

pauln · July 27, 2016, 2:13am

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 · July 27, 2016, 2:15am

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 · July 27, 2016, 2:25am

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 · July 27, 2016, 11:01am

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 · July 27, 2016, 11:33am

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 · July 27, 2016, 1:45pm

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

pauln · July 27, 2016, 1:50pm

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…

emre · July 27, 2016, 1:52pm

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 · July 27, 2016, 2:02pm

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 · July 27, 2016, 2:12pm

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