How can I design inventory movement report.

(0) Share

Report

Posted on by Community Member

Please I want to design inventory movement report. This report will show in and out of stock and also show the balance as at a particular date. It will also show beginning quantity /start quantity balance. Please I want to design this report on SSRS. How do I go about getting the beginning quantity /start quantity balance? Thank you

*This post is locked for comments

I have the same question (0)

All responses (11)

Answers (0)

soma 24,410 on at

Like (0)

Report

I have done this for one of my previous customer. If you want the inventory movement report please send me the mail, I will create and send you the same.

Please send your requirement with some example to the mail id: soma.moorthy@gmail.com

Was this reply helpful? Yes No
Suggested answer

soma 24,410 on at

Like (0)

Report

If you wants to create the query on yourself, have a look on the Mahmoud Saadi blog and also follow him suggestion from below link for calculating Beginning and ending QTY.

community.dynamics.com/.../112183.aspx

Please let me know if you need any information regarding the same.

Hope this help!!!

Was this reply helpful? Yes No
Suggested answer

Mahmoud Saadi 32,738 on at

Like (0)

Report

Nothing more valuable to add up. Although, the requirements specified by your business set the factors of the report design. The Historical Inventory Trial Balance per period provides a summarized view for your inventory transactions.

In case you are looking for an item movement report, the one mentioned above could be a starting point for a Historical Inventory Trial Balance per day, I will be publishing this report soon on GP Essentials blog, and it should meet your requirements.

Your feedback is highly appreciated,

Was this reply helpful? Yes No
Community Member on at

Like (0)

Report

@Mahmoud

After creating the view In sql, in the data set I selected the view and table IV30300.

I created a relation between them which is ITEMNBR to ITEM but the result was that it replicate one transaction 24 times.

I enquired further and found out that it replicate one transaction for the whole period and years in the view 24 times.

Please how do I resolve this issue.

Thank you.

Was this reply helpful? Yes No
soma 24,410 on at

Like (0)

Report

Can you post your code?

Was this reply helpful? Yes No
Mahmoud Saadi 32,738 on at

Like (0)

Report

The historical inventory trial balance report provided on the link above retrieves data primarily from SEE30303, not IV30300.

Let me check what you've done.

Looking forward to hearing from you

Was this reply helpful? Yes No
Community Member on at

Like (0)

Report

@ Mahmoud & Soma

Even when I used SEE30303

It's the same result I got.

Find below the script

SELECT

IV00101.ITEMDESC

,V_HITB_Per_Period.Trx_Year

,V_HITB_Per_Period.Period

,V_HITB_Per_Period.QuantityBalance

,SEE30303.DOCNUMBR

,SEE30303.TRXQTY

,SEE30303.UNITCOST

,SEE30303.EXTDCOST

,SEE30303.DOCDATE

,SEE30303.ITEMNMBR

FROM

IV00101

INNER JOIN SEE30303

ON IV00101.ITEMNMBR = SEE30303.ITEMNMBR

INNER JOIN V_HITB_Per_Period

ON SEE30303.ITEMNMBR = V_HITB_Per_Period.Item

Thank you I await your response.

Was this reply helpful? Yes No
Suggested answer

Mahmoud Saadi 32,738 on at

Like (0)

Report

That would never work. You are joining the view which retrieves summary balances with a transactional table (SEE30303).

Please check the data set below, this is the cumulative HITB report.

The requirements to build the report is illustrated on the following link: HITB Per Period (Cumulative) – Inventory Balance on a Prior Date

Your feedback is highly appreciated.

Was this reply helpful? Yes No
soma 24,410 on at

Like (0)

Report

Mahmoud,

I saw your post. This is excellent and will helps FUNMILAYO and some other who having the same case.

Was this reply helpful? Yes No
Community Member on at

Like (0)

Report

@ Mamoud

Does it mean I cant get my inventory movement with balances?

Was this reply helpful? Yes No