Custom Report P&L profit and loss

I believe it is the % of units sold of a product from total qty of all products sold.
Similar to the % value for product group on work period but by qty rather than value.
Ie 100 x £1 coffees would give higher % than 4 x £25 steaks.
In an example if sales for a period were;
100 x Coffee £1 = Total Product Sales @ £100
4x Steak £25 = Total Product Sales @ £100
50 x Beer £4 = Total Product Sales @ £200
The sales % as on work period would show;
Coffee = £100 = 25%
Steak = £100 = 25%
Beer = £200 = 50%
Total Sales = £400 (100%)
The participation sales would be based on QTY giving these results;
Coffee = x100 = 64.9%
Steak = x4 = 2.6%
Beer = x50 = 32.5%
Total QTY= x154 (100%)

So it is one of those

[PL Report1:5, 3, 3, 3, 3]
>Name|Qty|Price|PS%|PC%
{REPORT COST DETAILS:[C.Name]+'.'+[C.PortionName],
C.Quantity,
C.AvgPrice,
([C.AvgPrice]*[C.Quantity]).Percent,
C.Quantity.Percent,
}
3 Likes

That looks like what I understand Participation % to be.

Love it!!! Haha!!!

i can copy using SQL management studio … if i installed V5 and put the database link string… will it connect direct ??

thank you so much for your great effort

Yes, using SSMS, do a backup of the DB first. Then you can restore if anything goes wrong. You can also restore to different named DB. For example, backup your V4 DB and restore it as SambaPOS5.

Then install V5
Change the connection string.
Logout out.
Close.
Restart SambaPOS.

thank you so much . i will be trying that.

i installed V5 on my laptop and connected to the network. and put my database string username and password to my V4 DB SQL server… it connected successfully … but everything is blank ?? as if i installed samba for the 1st time… ?? any advise ?

Definatly get the database name right?
If database name is not exactly the same samba will create a new one.
Use MSSQL Manager and check to see if you have multiple databases in the SQL instance.

You’ll create a backup on V4 and restore it on V5. If you don’t already have you can temporarily enable V4’s backup module in trial mode.

Q sent you a step by step tutorial for that on his previous post. You probably missed…

for some reason backup module will not show at all… i dont see it… under samba market… even though under add on folder i can see the DLL file… ?

In Samba Market, within SambaPOS, ensure you login using the link in the top right.

I find it just as easy to use SSMS though. In SSMS, right-click on your DB, then select Tasks > Backup Database. You can restore it in the same fashion by choosing Tasks > Restore Database.

To rename the DB during Restoration is easy too… Follow this post…

1 Like

Hi Emre. Can the report calculate total of the “PM” ?

Yes it can you can use .sum at end of report expression to sum that into single row.

1 Like

[PL Report:5, 3, 3, 3, 3, 3, 3, 3]

Name|Qty|Sales|Cost|Cost%|PM%|PM|P%
{REPORT COST DETAILS:[C.Name]+’.’+[C.PortionName],
C.Quantity.Sum,
[([C.AvgPrice][C.Quantity]).Sum],
[([C.Cost]
[C.Quantity]).Sum],
[($4/$3)];%##.00;-%##.00,
[($3-$4)/$3];%##.00;-%##.00,
[($3-$4)];#.00,
[(($3-$4)/$4)];%##.00;-%##.00}`

can u show me where to add .sum to get total “PM”?

Create a duplicate of report template under the PL report and remove Product Name column so it will sum all numbers.

This is not specific to PL report. For example.

{REPORT TICKET DETAILS:T.TotalAmount.Sum} 

gives the sum of all tickets.

{REPORT TICKET DETAILS:T.Date,T.TotalAmount.Sum} 

gives sum of all tickets by date.

So merging two templates will lists tickets by date and adds sum totals at the end.

To fix column alignments you can add a Total: Label like…

[Report:1,1]
{REPORT TICKET DETAILS:T.Date,T.TotalAmount.Sum} 
Total:|{REPORT TICKET DETAILS:T.TotalAmount.Sum}
2 Likes

This Report is fairly detailed and gives Group Sums and Grand Total Sums …

[Products:5,5, 3, 3, 3, 3, 3, 3, 3]
@{REPORT COST DETAILS:M.GroupCode.asc::,}
>Name|Portion|Qty|Sales|Cost|Cost%|PM%|PM|P%
>>GROUP|$1|||||||
{REPORT COST DETAILS:C.Name.asc,C.PortionName,
C.Quantity.Sum,
[([C.AvgPrice]*[C.Quantity]).Sum],
[([C.Cost]*[C.Quantity]).Sum],
[($5/$4)];#0.00 %;-#0.00 %,
[($4-$5)/$3];#0.00 %;-#0.00 %,
[($4-$5)];0.00,
[(($4-$5)/$5)];#0.00 %;-#0.00 %:M.GroupCode="$1"}

>Totals for:|{REPORT COST DETAILS:[='$1'],C.Quantity.Sum,[([C.AvgPrice]*[C.Quantity]).Sum],[([C.Cost]*[C.Quantity]).Sum],[($4/$3)];#0.00 %;-#0.00 %,[($3-$4)/$2];#0.00 %;-#0.00 %,[($3-$4)];0.00,[(($3-$4)/$4)];#0.00 %;-#0.00 %:M.GroupCode=="$1"}

>>TOTALS||Qty|Sales|Cost|Cost%|PM%|PM|P%
> ||{REPORT COST DETAILS:C.Quantity.Sum,[([C.AvgPrice]*[C.Quantity]).Sum],[([C.Cost]*[C.Quantity]).Sum],[($3/$2)];#0.00 %;-#0.00 %,[($2-$3)/$1];#0.00 %;-#0.00 %,[($2-$3)];0.00,[(($2-$3)/$3)];#0.00 %;-#0.00 %}

[Groups:5,5, 3, 3, 3, 3, 3, 3, 3]
>Group||Qty|Sales|Cost|Cost%|PM%|PM|P%
{REPORT COST DETAILS:M.GroupCode.asc,'',
C.Quantity.Sum,
[([C.AvgPrice]*[C.Quantity]).Sum],
[([C.Cost]*[C.Quantity]).Sum],
[($5/$4)];#0.00 %;-#0.00 %,
[($4-$5)/$3];#0.00 %;-#0.00 %,
[($4-$5)];0.00,
[(($4-$5)/$5)];#0.00 %;-#0.00 %}

>>TOTALS||Qty|Sales|Cost|Cost%|PM%|PM|P%
> ||{REPORT COST DETAILS:C.Quantity.Sum,[([C.AvgPrice]*[C.Quantity]).Sum],[([C.Cost]*[C.Quantity]).Sum],[($3/$2)];#0.00 %;-#0.00 %,[($2-$3)/$1];#0.00 %;-#0.00 %,[($2-$3)];0.00,[(($2-$3)/$3)];#0.00 %;-#0.00 %}
1 Like

Thanks a lot Emre for explained that to me.

1 Like

Thanks QMcKay.
Solved by using this solution.

1 Like