I need "Last Unit Cost" which is in the "Inventory Analysis 1-1" container  but I need it in the Customer Sales container - how do I add it?

Views: 85

Reply to This

Replies to This Discussion

Good Morning Bill,

We will investigate the above and get back to you with possible solution

Regards

Prasad

Hello Bill

How good is your knowledge of SQL?

Using the connector module you should be able to do this. However it should also already be part of the line cost amount field within the sales analysis container.

But if you must indeed know the SQL code to add to a custom report you are creating, here it is:

CASE 

   WHEN "LineItem"."ItemClass" IN (0,4,5) THEN "LineItem"."LaborCost"

   WHEN "LineItem"."ItemClass" IN (1,3,9,10,11) THEN ISNULL("BuyInfo"."LastUnitCost","LineItem"."LaborCost")

ELSE 0

END 'Last Unit Cost'

Sorry for asking a question and going on vacation.  Thank you for the rely.  My knowledge of SQL is non existent.  Where would this code get input?  I do get a Line Cost Amount with the sales container but it show an amount if it is a purchased part for us.  Under the Inventory container there is a "Last Unit Cost" line that calculates Bill of Materials which is mostly what we have.

To be able to design your own report, you have to use the connector module.

This enables you to "tap into" the database and build your own reports from the ground up or edit existing containers.

If this is a costing issue in the sales analysis,  download the latest available sales analysis report using the free report utility. I believe this resolved the issue with assembly items (bill of materials)

However if you want to edit the existing reports or design yours from scratch, I suggest getting a certified consultant (if you do not feel comfortable with SQL)

You were correct - the newest sales analysis does have the Last Unit Cost for Bill of Material products.  Thank you.

How can I pull out a certain item?  Meaning we have a line item in the Bill of Materials called Direct Labor.  We make a lot of sub assemblies so the Bill of Materials have multiple Direct labors that I need the total of and be able to pull it out.  Any suggestions?

Hello

If the specific item is already set as an inventory item you should be able to filter the report just for that one line item

RSS

The Sage Intelligence Blog

Like Sage Intelligence?

Follow @SageSupport

© 2018   Created by Sage Alchemex.   Powered by

Badges  |  Report an Issue  |  Terms of Service