Inventory Movements Report


Introduction

We have a business requirement to report on the overall on hand figure at the start of each calendar month, the end of each calendar month and all of the material transactions that have effected that item in between these two dates.

My first issue was I cannot find any system is Syteline that snapshots the inventory figures, so I have had to create a table, a stored procedure and a sql job to do this as follows :-

Creating the snapshot

The first script is quite straight forward, it creates a table containing 3 fields to hold the data required for my report. I’ve used a foreign key constraint to ensure the item exists in the item table, and a default constraing to automatically populate the create date. here is the script :-

CREATE TABLE [dbo].[pbti_ih_OnHandSnapshot](
[item] [dbo].[ItemType] NOT NULL,
[qoh] [decimal](10, 2) NOT NULL,
[Createdate] [datetime] NOT NULL
) ON [PRIMARY]

GO

ALTER TABLE [dbo].[pbti_ih_OnHandSnapshot]  WITH CHECK ADD  CONSTRAINT [fk_ITEM] FOREIGN KEY([item])
REFERENCES [dbo].[item] ([item])
GO

ALTER TABLE [dbo].[pbti_ih_OnHandSnapshot] CHECK CONSTRAINT [fk_ITEM]
GO

ALTER TABLE [dbo].[pbti_ih_OnHandSnapshot] ADD  DEFAULT (getdate()) FOR [Createdate]
GO

The next challenge was to create a stored procedure to populate the table with data. Note – I am not concerned by the warehouse the items are in hence this is not being collected :-

CREATE procedure [dbo].[PBTI_IH_SP_CreateQOHSnapShot] as

insert into pbti_ih_OnHandSnapShot (item, qoh)
select
item
, sum(qty_on_hand) QOH
from itemloc
group by item
order by item

GO

To populate the data on an ad-hoc basis you simply need to execute the procedure. (“EXEC PBTI_IH_SP_CreateQOHSnapShot”), however we want to run this automatically every night, so I have used a SQL job for this as shown here :-

SQL Create OnHand Snapshot Job

SQL Create OnHand Snapshot Job

Collating the snapshot information

We want to be able to keep all of the records for each day in the snapshot table – we have often found it useful to be able to look back and see what we have on hand on a particular day, we also want to be able to show the data in at the current time, if we are part way through the month, therefore for the purposes of the report I have devised a view which obtaint the first and last record in a month for each item. In this view I use CTEs (common table expressions) and the Row_Number function to achieve my goal as shown here :-

create view PBTI_IH_VW_QOHSnapShotSummary as

with fst as (
select
item
, qoh
, createdate
, right(’00’ +cast(DATEPART(month,Createdate) as nvarchar(2)),2) + ‘/’ + cast(DATEPART(year,Createdate) as nvarchar(4)) as createmonth
, ROW_NUMBER() over (partition by item, right(’00’ +cast(DATEPART(month,Createdate) as nvarchar(2)),2) + ‘/’ + cast(DATEPART(year,Createdate) as nvarchar(4))   order by   item, right(’00’ +cast(DATEPART(month,Createdate) as nvarchar(2)),2) + ‘/’ + cast(DATEPART(year,Createdate) as nvarchar(4)), createdate asc)   as rn
from pbti_ih_onhandsnapshot
)
,lst as (
select
item
, qoh
, createdate
, right(’00’ +cast(DATEPART(month,Createdate) as nvarchar(2)),2) + ‘/’ + cast(DATEPART(year,Createdate) as nvarchar(4)) as createmonth
, ROW_NUMBER() over (partition by item, right(’00’ +cast(DATEPART(month,Createdate) as nvarchar(2)),2) + ‘/’ + cast(DATEPART(year,Createdate) as nvarchar(4))   order by   item, right(’00’ +cast(DATEPART(month,Createdate) as nvarchar(2)),2) + ‘/’ + cast(DATEPART(year,Createdate) as nvarchar(4)), createdate desc)   as rn
from pbti_ih_onhandsnapshot
)
select
fst.item
, fst.createmonth
, fst.qoh as QOH_StartofMonth
, fst.createdate CreateDate_StartofMonth
, lst.qoh as QOH_EndOfMonth
, lst.createdate CreateDate_EndOfMonth
from fst
left join lst on fst.item = lst.item and fst.createmonth = lst.createmonth and lst.rn = 1
where fst.rn = 1

 Collating the Material Transaction Information

Collating the material transaction information by month is a fairly straight forward process, the Execution Plan identified some indexes I needed to add in order to make the query efficient, again I’ve created it as a view as we will probably require this data in different places, and I view will allow the sql server to cache the execution plan for future use, and I am not sure if a query within an SSRS report will do the same.

create view pbti_ih_vw_MatltranSummary as

select
right(’00’ +cast(DATEPART(month,trans_date) as nvarchar(2)),2) + ‘/’ + cast(DATEPART(year,trans_date) as nvarchar(4)) as TransactionMonth
, item
, sum(qty) Total
, case
when trans_type = ‘r’ then ‘Reciept’
when trans_type = ‘M’ then ‘Stock Move’
when trans_type = ‘S’ then ‘Shipment’
when trans_type = ‘W’ then ‘Return’
when trans_type = ‘G’ then ‘Misc. Issue’
when trans_type = ‘H’ then ‘Misc. Receipt’
when trans_type = ‘C’ then ‘Create / Split / Merge’
when trans_type = ‘N’ then ‘Labour / Next Operation’
when trans_type = ‘F’ then ‘Finish’
when trans_type = ‘I’ then ‘Issue’
else trans_type
end as trans_type
from matltran
group by
right(’00’ +cast(DATEPART(month,trans_date) as nvarchar(2)),2) + ‘/’ + cast(DATEPART(year,trans_date) as nvarchar(4))
, item
,trans_type

 Pulling the dataset together

Within the ssrs report itself I have used the following query for the dataset :-

select
i.item
, i.Uf_TCode
, i.Uf_TegCode
, ss.QOH_StartofMonth
, ss.CreateDate_StartofMonth
, ss.QOH_EndofMonth
, ss.CreateDate_EndofMonth
, mts.trans_type
,mts.total
from item as i
left join PBTI_IH_VW_QOHSnapShotSummary as ss /*snap shot*/ on ss.item = i.item
left join pbti_ih_vw_MatltranSummary as mts /*Material Transaction Summary*/ on i.item = mts.item and ss.createmonth = mts.TransactionMonth
order by i.item, ss.createmonth

The report

This report is probably the most complex single-source report I have written to date. Using a Matrix, and some grouping, the end result appears as follows :-

 

SSRS Inventory Movement Report

SSRS Inventory Movement Report

The intended audience is non-technical. They require the data for auditing purposes and will probably only look at it once a month. As such I have attempted to make it as intuitive as possible by including a notes page explaining each transaction type to the best of my ability and then highlighting the cells they need to add up to validate the calculation as shown above. The formula for the background colour of the cells is as follows :-

=iif(Fields!trans_type.Value=”Create / Split / Merge”,”LightCoral”,
iif(Fields!trans_type.Value=”Finish”,”PaleGreen”,
iif(Fields!trans_type.Value=”Issue”,”PaleGreen”,
iif(Fields!trans_type.Value=”Labour / Next Operation”,”LightCoral”,
iif(Fields!trans_type.Value=”Misc. Issue”,”PaleGreen”,
iif(Fields!trans_type.Value=”Misc. Receipt”,”PaleGreen”,
iif(Fields!trans_type.Value=”Reciept”,”PaleGreen”,
iif(Fields!trans_type.Value=”Return”,”PaleGreen”,
iif(Fields!trans_type.Value=”Shipment”,”PaleGreen”,
iif(Fields!trans_type.Value=”Stock Move”,”LightCoral”,
“White”))))))))))

Giving the users as much information as possible is important, so I have included a notes page as shown here :-

Inventory Movement Report Notes

Inventory Movement Report Notes

And finally making navigation of the document is key, so labelling the sheets is always important as shown here :-

SSRS Inventory Movement Report Tabs

SSRS Inventory Movement Report Tabs

Leave a comment

This site uses Akismet to reduce spam. Learn how your comment data is processed.