[NEW!] How to use Custom Reports Module 1.0

yes, that is part of the problem… since I still dont get it… will give it more time.

Thanks!!!

G.

Just look at it this way… its what helped me. Accounts screen is not an accounting report… you cannot read balances as a report.

The accounts screen is showing the PROCESS Samba is using to calculate Transactions and transaction documents.

So try to understand the process and then the custom reports will make more sense to you.

Accounts screen is like looking in at an engine running… You get a good view of how accounting is running in Samba by looking at accounts screen… But like the engine it does not give reports directly… you have to hook it to something… Engine hooks to a computer to get reports… accounts screen hooks to custom reports module.

Once you understand that the Accounts screen is the actual accounting process in front of your eyes it makes alot more sense.

If your good with engines and you understand how they run… a good mechanic can just look at an engine running and understand it without reports… same thing with Accounting screen… once you get a good undestanding of what it is doing you can read it like a report… but its not really an accounting report.

@emre maybe you should call it Accounting Engine instead of just Accounting :stuck_out_tongue:

Woo just tried the command button to load a report in 4.1.53 WORKS BEAUTIFULLY Thank you @emre

I am making a Refresh Live View button and it will refresh data on every press…

Sample:


1 Like

Hopefully i have some graphics to show you soon. Plan to make a custom background with some nice looking buttons… I will overlay transparent Automation buttons so it will look like custom made button graphics.

1 Like

Yes this is not exact Accounting. In fact I’ve simplified it :slight_smile: Maybe a little over simplified so it became too abstract.

I liked how you’ve described “Account Screen is not a Report”. We generally use reports to understand what happened it the past. Account Screen more useful to understand our current financial status. Also if configured properly it gives some idea about future. Basically how much you’ll receive and how much you’ll pay.

Finally ratios between some accounts gives some key metrics about your business performance. We’ll dive into that topic more in future releases.

I like how simple you made it. It really makes custom reports so useful. Personally making accounts simple… and then having the option of custom reports is a genius solution.

SELECT
Convert(nvarchar(20),Date,103),
min(datename(dw,[Date])) as WeekName,
Sum(CASE WHEN DATEPART(hour, [Date]) BETWEEN 12 AND 18 THEN TotalAmount ELSE 0 END),
Sum(CASE WHEN DATEPART(hour, [Date]) BETWEEN 19 AND 24 THEN TotalAmount ELSE 0 END),
Sum(CASE WHEN DATEPART(hour, [Date]) BETWEEN 00 AND 06 THEN TotalAmount ELSE 0 END)
from Tickets
Where Date > ‘{Start}’ and Date < ‘{End}’
GROUP BY Convert(nvarchar(20),Date,103)

Emre, how can i edit the above code to display information only related to a particular Menu Category? that is, how do i edit it so i can see the number of Burgers that we’ve sold per hour?

you cannot from there… this table is tickets and has not orders in there… that particular SQL only counts TICKETS

to do that you should use orders and do:

LEFT OUTER JOIN [MenuItems] on [Orders].[MenuItemId] = [MenuItems].[Id]

so you can get the groups of Menu Items.

Whit this SQL you will count group of Menu Items sold:

SELECT [MenuItems].[GroupCode],
       [MenuItemName]+(CASE WHEN [PortionName] = 'Normal' THEN '' ELSE ' ' + [PortionName] END),
       CONVERT(INT,SUM([Quantity])) as Cantidad

FROM [Orders]

LEFT OUTER JOIN [MenuItems] on [Orders].[MenuItemId] = [MenuItems].[Id]

WHERE [CreatedDateTime] > '{Start}' AND [CreatedDateTime] < '{End}' AND DecreaseInventory = 1 AND CalculatePrice <> 0

GROUP BY [MenuItems].[GroupCode],[MenuItemName]+(CASE WHEN [PortionName] = 'Normal' THEN '' ELSE ' ' + [PortionName] END)

ORDER BY Cantidad DESC

Thinking fast, you would need to do one more join, join tickets so you can get the [DATE].

G.

1 Like

@emre, this isn’t a huge deal, but is there something you could do to prevent this from happening, like execute under separate Clients?

EDIT: Please disregard this - it appears to have been an issue in the SQL that I pasted into the Scripts section.

I have 2 SQL scripts running in the same Report. They have different Handlers, so I assume they would be different connections, but it appears they are not.

@@HoursMyrna
>@@HoursMyrnaTTL

The scripts are nearly identical, but the second one performs aggregates (sums, counts) … it’s basically a SELECT (all daily data) within a SELECT (all summed data). Both scripts define the same variables so…

@@Handler1 executes with it’s declared variables, then @@Handler2 executes with the same variable declarations.

The reason I’m seeing this error is because both scripts are running under the same session/connection, so the Client thinks I am trying to re-declare variables that have already been declared… as if it is appending the 2nd script to the end of the first.

Sorry @QMcKay I don’t have much experience on that. Do you have an idea why that happens?

EDIT: Please disregard this - it appears to have been an issue in the SQL that I pasted into the Scripts section.

I have 2 SQL scripts running in the same Report. They have different Handlers, so I assume they would be different connections, but it appears they are not.

@@HoursMyrna
>@@HoursMyrnaTTL

The scripts are nearly identical, but the second one performs aggregates (sums, counts) … it’s basically a SELECT (all daily data) within a SELECT (all summed data). Both scripts define the same variables so…

@@Handler1 executes with it’s declared variables, then @@Handler2 executes with the same variable declarations.

The reason I’m seeing this error is because both scripts are running under the same session/connection, so the Client thinks I am trying to re-declare variables that have already been declared… as if it is appending the 2nd script to the end of the first.

I’m already creating new connections from code but I think there is a kind of pooling, cache behind that. It will be better if we can solve it in a different way because I think forcing it to create separate sessions for every @@'s might be a little expensive.

1 Like

Thanks @emre

MY BAD: it was an issue in the SQL that I pasted into the Scripts section. That said, I do have another issue…

TheScrollbar in the Script window is not functional, so I can’t see all the code…

@emre, please make the Scrollbar functional.

3 Likes

I like this, and I see there is a “page size” but would it be possible to get a nowrap flag?

@emre, using the following notation, how many variables can we pass in to SQL?

@@EmployeeHoursDaily:Employees

SQL (EmployeeHoursDaily):

-- set EntityType
declare @EntityType varchar(20) = '@1'

For example, can I do something like this?

@@@EmployeeHoursDaily:Employees:Myrna

SQL (EmployeeHoursDaily):

-- set EntityType
declare @EntityType varchar(20) = '@1'
-- set Employee Name
declare @Employee varchar(20) = '@2'

Sorry for the untested reply but as far as I can remember splitting values with comma should work. @@@ not needed.

I mean

@@EmployeeHoursDaily:Employees,Myrna

should work.

Excellent! That works! Awesome! That reduced the number of my SQL scripts from 8 to 2. Nice.

Thanks @emre!

Hi @emre can you please show me the code I need to build a report showing sales and tax by each day, over a month?

I’m trying to produce a report for my book keeper.

Thank you in advance for your help.

Hi @emre,

I was able to recreate the script for the Tips as you have here. I tried to add payment type to the script with no luck.

I was able to patch this together in the Custom Reports Property Editor.

It is showing the correct payment types if I skip the $0 transactions. Can this also show voided items? I really like the finished report Gerlando was able to create from your template.

Can you help me to correct this?
Thanks
Ray