Custom Report P&L profit and loss

[emre]

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

[JTRTech]

@emre

C.Quantity.Percent,

That looks like what I understand Participation % to be.

[RickH]

@QMcKay

am

Love it!!! Haha!!!

[mghoniem]

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

[mghoniem]

thank you so much for your great effort

[QMcKay]

@mghoniem

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

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.

[mghoniem]

thank you so much . i will be trying that.

[mghoniem]

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 ?

[JTRTech]

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.

[emre]

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…

How to Upgrade v4 Database to a New V5 Database

How to migrate V4 Database to a New V5 Database using Database Backup Module : REMINDER Always backup your database to a location you know is safe before attempting anything like what is mentioned below. You can temporarily enable V4 database backup module on trial mode to create V4 backup. Step 1 Navigate to Settings > Database Backups > Settings Enable Allow overwriting database files if database names does not match during restore. option Enable Migrate after restoration. …

[mghoniem]

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… ?

[QMcKay]

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…

@QMcKay

One way is to make a backup of your V4 DB using SSMS (SQL Server Management Studio).

Then, using SSMS, restore that backup, but when you do so, instruct it to restore to a new database. Just change the name of the Destination.

[ordinary_person]

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

[Jesse]

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

[ordinary_person]

[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”?

[emre]

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}

[QMcKay]

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

[ordinary_person]

Thanks a lot Emre for explained that to me.

[ordinary_person]

Thanks QMcKay.
Solved by using this solution.