Custom Report P&L profit and loss

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