PL P&L Profit Loss Report

V5 Question
1

Loving this Report. Starting a new Topic since I made some additions, to dicuss the small problem I have come across …

[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|{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 %: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, 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],
[($4/$3)];#0.00 %;-#0.00 %,
[($3-$4)/$2];#0.00 %;-#0.00 %,
[($3-$4)];0.00,
[(($3-$4)/$4)];#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 %}

Pasted image983×339 23.4 KB

The problem exists in the 1st Table.

This is the line with the issue:

> |Totals|{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 %:M.GroupCode=="$1"}

Specifically, this part:

[($2-$3)/$1];#0.00 %;-#0.00 %

I understand why this happens. The var $1 is “double-assigned”. First, it is assigned using @parmeterlist, here:

@{REPORT COST DETAILS:M.GroupCode.asc::,}

But it can also act upon the Field List, here:

> |Totals|{REPORT COST DETAILS:C.Quantity.Sum,[([C.AvgPrice]*[C.Quantity]).Sum],[([C.Cost]*[C.Quantity]).Sum]
                               ^^^^^^^^^^^^^^ ^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^ ^^^^^^^^^^^^^^^^^^^^^^^^^^^^^
                                     $1                       $2                              $3

So it seems it is preferring the @parmlist over the Field list when it comes to $1, and being that it is non-numeric (it is a GroupCode), the calculation [($2-$3)/$1] fails and returns 0.

Any thoughts on a way to workaround this?

2

Hi @QMcKay - I came across this recently as well and my workaround was to create an extra variable with the same value I think? So when it got consumed by one process I had a spare for something else but I moved away from that and cannot provide you an example.

In other cases I am just regenerating the variable each time which is a bit cumbersome…

1 Like
3

Can shifting parameters by one help? You can include Total column in report like that.

[REPORT:1,1]
{REPORT SQL DETAILS:SELECT 3+5 as V:='Total',F.V}
4

Ok, I’ll bite. Can that be used in report tags other than SQL DETAILS?

Something like this?

{REPORT COST DETAILS:Q:=[C.Quantity.Sum], // assign value to 'Q' ?
P:=[([C.AvgPrice]*[C.Quantity]).Sum],     // assign value to 'P' ?
C:=[([C.Cost]*[C.Quantity]).Sum],         // assign value to 'C' ?
[($P-$C)/$Q];#0.00 %;-#0.00 %             // calculate using assignments?
}
5

Still having trouble with this, even though, given:

$1 = C.Quantity.Sum  // well not really - $1 contains a GroupCode from the @list so I need to "replace" it with this value instead
$2 = [([C.AvgPrice]*[C.Quantity]).Sum]
$3 = [([C.Cost]*[C.Quantity]).Sum]

And what I want to calculate:

[($2-$3)/$1]

I thought something like this would work:

[($2-$3)/([C.Quantity.Sum])]

But alas, it does not.

I have tried many types of “shifting” using varName:=something as well, but either I don’t have the syntax correct, or it isn’t a feature supported by Report Tags other than SQL DETAILS… see my previous post for one attempt.

6

LOL, well, this:

> |Totals|{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 %:M.GroupCode=="$1"}

Changed to this:

>Totals|{REPORT COST DETAILS:'',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"}

That ^ works, by using a “dummy” field in the lead, effectively “shifting” all $vars by 1. The dummy field where I use an empty quoted string '' … can I put just a plain string there somehow? Or better yet, $1 to show the GroupCode? Doesn’t seem to work, but might have not tried proper syntax yet.

Still, I would be interested to know if there is another way, because now my “Totals” word appears in the 2nd column instead of the 1st.

Pasted image1046×614 51.7 KB

7

YES!

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

Wonderful …

Pasted image1319×746 93.4 KB

Complete Report:

[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, 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],
[($4/$3)];#0.00 %;-#0.00 %,
[($3-$4)/$2];#0.00 %;-#0.00 %,
[($3-$4)];0.00,
[(($3-$4)/$4)];#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 %}

Pasted image1314×1400 163 KB

1 Like
8

So in a nice sense Q - you are a freak! haha

1 Like
9

I tried this one and when I am adding it its showing just blank results! what I do wrong?? Please help!

10

You are using version 5 right?

1 Like
11

Yes I am using V5 :smiley:

12

Ok and you have inventory correctly setup with recipes etc? You are doing inventory purchases?

1 Like
13

Yes, all is set and ready!

14

Ok so can you copy and paste your report you are using?

15

You have transactions right? You have group names for products?

16

Latest version of this Report …

[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 %}
2 Likes
17

sorry for the ignorance, what is PM? and P? my native language is spanish.

18

Profit margin and profit I assume seeing how that’s what this post is about.

19

Yes but… understand something, English is not my native language…

20

I understand. So how does profit and profit margin get abbreviated in Spanish typically? I’m curious I don’t use much Spanish.