Variance Report

If you look at this Template. I want to get the desired results, ia pulling from End of day record? when Change figures in the End of Day Records. (EDR)?

[Stock Take / End Of Day Record:2,1,1,1,1,1,1,1,1]
>>Item|Unit Cost|Op Stock|Sold|Purchases|Expected|Count|Variance|Variance$
{REPORT CONSUMPTION DETAILS:C.Name.asc,C.Cost,C.InStock.Sum,C.Consumption,C.Added,C.Prediction,C.PhysicalInventor,[C.PhysicalInventory]-[C.Prediction],[($2*$8)]}
>>Totals||OP-Totals|Sold Tot|Pur Tot|Exp Tot|Physical|Variance|Var@Cost
>Totals|{REPORT CONSUMPTION DETAILS:C.Cost.Sum,C.InStock.Sum,C.Consumption.sum,C.Added.Sum,C.Prediction.Sum,C.PhysicalInventory.Sum,[C.PhysicalInventory.Sum]-[C.Prediction.Sum],[($2*$8)]}
1 Like

You have C.PhysicalInventor instead of C.PhysicalInventory

1 Like

Try this one

I am still trying to figure out how I can sum a column to produce total value of the variance report.

[Stock Take / End Of Day Record:2,1,1,1,1,1,1,1,1]
>>Item|Unit Cost|Op Stock|Sold|Purchases|Expected|Count|Variance|Variance$
{REPORT CONSUMPTION DETAILS:C.Name.asc,C.Cost,C.InStock.Sum,C.Consumption,C.Added,C.Prediction,C.PhysicalInventory,[($6-$7)],[(($6-$7)*$2)]:(PCW=Local Warehouse)}

A total row would be a separate report expression underneath using .Sum on the column/fields

1 Like

JTRTech is correct, but there is more to consider to make it work.

In your field selection, if you want to use .sum (or other aggregates such as .avg) on 1 (or more) of the fields, then the other fields need to be “groupable” or omitted entirely.

That means your other fields must either be omitted completely, or at the very least, they need to return the exact same value for every record/row of data. For example, you cannot use .asc on 1 field and .sum on another field in the same list. So this will not work:

{REPORT CONSUMPTION DETAILS:C.Name.asc,C.Cost,C.InStock.Sum,C.Consumption,C.Added,C.Prediction,C.PhysicalInventory: ...}

You want the Sum of C.InStock but the other fields violate the aggregate grouping. These fields will all return a different value for each row, so no Sum can be made:

C.Name.asc (even without .asc, this still returns a different value for every record/row)
C.Cost
C.Consumption
C.Added
C.Prediction
C.PhysicalInventory

You can omit all of those fields, or do an aggregate (.sum or .avg) or a limit (.min or .max) on each of them, except for C.Name because it is a STRING that cannot be summed or averaged (you could do min/max on C.Name, but that doesn’t make much sense here).

One of these would work:

{REPORT CONSUMPTION DETAILS:C.InStock.Sum: ...}

{REPORT CONSUMPTION DETAILS:C.Cost.sum,C.InStock.Sum,C.Consumption.sum,C.Added.sum,C.Prediction.sum,C.PhysicalInventory.sum: ...}
1 Like

Thank You it Looks Nice and Physical count is showing okay in the report just okay but the Question is how do I see My Shortages or there is another way of putting Physical Inventory apart from the end of day record to come up with the Variance as a shortage or Over?
Thank Please assist.

As of I now I know only of putting it via End Of Day Records

Okay. I tried But Its not showing That there is a shortage… its just giving Me the Current Figer and not showing what went Missing.
@Castech Do u get the Shortages or Some Missing Products as Variance?
Thanks.

Yes I am getting shortages as variance and their variance value.

Send the current output and show your report code.

Okay! I see this is Interesting, I can See Its Making a good Understanding of Shortage analysis…
Are you using this as well?

[Stock Take / End Of Day Record:2,1,1,1,1,1,1,1,1]
>>Item|Unit Cost|Op Stock|Sold|Purchases|Expected|Count|Variance|Variance(AMT)
{REPORT CONSUMPTION DETAILS:C.Name.asc,C.Cost,C.InStock.Sum,C.Consumption,C.Added,C.Prediction,C.PhysicalInventory,[($6-$7)],[(($6-$7)*$2)]:(PCW=Local Warehouse)}

It Was 35 and I sold 5… I had Put the Current Inventory as 26 and that is what is on the Report.

I don’t know where you are getting it wrong because I tested your code it’s working as it supposed to.


Add a new End Of Day record and try to view the report again

It looks like you haven’t saved your end of day record because I see no count there. NB: You can only SAVE end of day record when the workperiod is ended. Otherwise the SAVE button will remain greyed out

Okay thanks Let me Look at the Issues you have advised…

hi team, sorry i have revived this old topic,can someone demonstrate how to deal with stock variances by adjusting End Of The Day records manually.

After you End Work Period you can go to Manage > Inventory > End of Day Records > Edit most recent record and set new values in Current Inventory column.

When you start new Work Period one Current Inventory will become InStock.

Thank-you so much A tech,i didn’t know that you have to do that before opening a new work period.Otherwise,i wasn’t able so save after adjustment.

1 Like