Inventory Trial Balance Reporting

(0) Share

Report

Posted on by Community Member

I'm having some trouble putting together an SSRS report to show the historical layers for inventory items that make up the current on-hand quantity. I currently have a report that shows both the on-hand quantity and all of the layered transactions (sales and purchases) that make up that quantity, but going forward, I would like only to see the most recent layers associated that make up the current quantity. If we use the same item for the next 5 years, I don't want tosee 5 years worth of transactions if the last 7 transactions actually make up the current quantity. Not sure how to write that in SQL to translate to my report.

Anyone else done something like this?

*This post is locked for comments

I have the same question (0)

All responses (4)

Answers (1)

L Vail 65,271 on at

Like (0)

Report

Have you considered excluding the layers where the receipt has been completely sold?

Leslie

Was this reply helpful? Yes No
Community Member on at

Like (0)

Report

I have not tried that. To be honest, I'm not sure what that would look like script wise. Here's how I am getting the FIFO value based on the most recent transactions and the current qty on-hand:

-----------FIFO VALUE CALC----------------
,
(select SUM(QUANTITY * UNITCOST) AS 'CURRVALUE'
from x_inventory_trx_hitb
WHERE SITE = ts.SITE AND ITEMNMBR = ts.ITEMNMBR
HAVING SUM(QUANTITY) = (SELECT TOP 1 QUANTITY FROM x_inventory_trx_hitb_sum WHERE SITE = ts.SITE AND ITEMNMBR = TS.ITEMNMBR)) AS 'FIFOVALUE'
-----------------------------------------------------------------

Was this reply helpful? Yes No
Verified answer

Victoria Yudin 22,769 on at

Like (0)

Report

Hi Jason,

I think Leslie is exactly right. Please take a look at this view on my blog where I am excluding any cost layer that has no quantity left to sell: victoriayudin.com/.../sql-view-for-inventory-quantities-on-hand

That may give you a good starting point for your report.

Was this reply helpful? Yes No
Suggested answer

Community Member on at

Like (1)

Report

I was able to do exactly as you suggested. Thanks for your help! I got the exact report we needed.

Was this reply helpful? Yes No