Moving Average in XL reporter

[Former Member]

Hi everybody!

Could you tell me how to get Item cost which set cost by moving average in XL reporter.Because i only get Standard cost

Thank you

[wilma_wang · Accepted solution]

Hello Tine,

The item cost depends on the method for managing the cost. If it is set

to manager globally, it can be get from the item attribute 'Item->Item

Cost' (ITM.AvgPrice).

If it is manager by warehouse, the item cost in dimension item will

always be zero, as the cost in stored with every warehouse. In this case

the item cost is obtained from 'Inventory -> Average price'

(OITW.AvgPrice)'.

This field is the real item cost and is the same as you can observe from

the item master data. You can open Business one, coose 'View -> System

Information' from the menu bar to let B1 display DB fields of each Ui

element. Then you open item master data, go to 'Inventory' tab and point

to the 'Item Cost' column on the grid. From the status bar you can see

the field related to this column is OITW.AvgPrice. Go to XLR -> Report

Composer -> Inventory tab, and point to light dimension Average Price,

and you can see from the pop-up tool tips that it is linked to

OITW.AvgPrice. So these two elements are actually linked to the same

field in the DB, therefore they cannot have different values.

However if they are working with marketing documents, the Item Cost onthe 'Sales - A/R' tab should be used. We get a problem here as there is

something wrong in the meta data that the item cost field will always

return the unit price instead of the cost. This is because the dimension

'Item Cost' points to field INV1.INMPrice, but it should point to

INV1.StockPrice. This is a bug exists in all the XLR versions. To solve

this, a walkaround can be applied as following:

1) Go to the customer DB in the SQL Server ManageMent Studio (Enterprise

Manager if in SQL2000), browse to views and find the view ixvF_INV1 and

ixvF_INV4 right click it and select 'Edit'.

2) In the auto generated query, search for term 'INMPrice', and you will

find the folowing line:

Row1.INMPrice as ARP_INMPrice,

3) Change this line to

Row1.StockPrice as ARP_INMPrice,

then run the query.

Now the 'Item Cost' dimension on the Sales - A/R tab will reflect the

real cost of the item, but ONLY for invoices. If you also want the

deliveries, sales orders, quotations or credit memos, chang the views

ixvF_DLN1, ixvF_RDR1, ixvF_QUT1, and ixvF_RIN1

in the same way.

Note: this walkaround will not take effect if the XLR is upgraded or

re-installed.

Hope this helps.

Wilma Wang

SAP Business One Forums team

[Former Member · Accepted solution]

Thank you very much