I would like a report that shows ending monthly inventory levels for a given period of time.  Essentially this would be the same information that is presented in the Valuation Report by Period in the canned reports of Sage 100, but the ability to show multiple periods.

Views: 237

Reply to This

Replies to This Discussion

Matthew

The attached is what I think you need.

I have not used it in about 2 years and did not test before attaching but should work and hope it meets your need.

Looks like this.......

Attachments:

Dear Craig,

Getting the following error, I am using Sage Evolution

Description : Invalid sql query near SE WHEN "IM_PeriodPostingHistory"."FiscalCalYear" = CAST( AS CHAR) THEN (IM_PeriodPostingHistory.BeginningBalExtendedStdCost + IM_PeriodPostingHistory.PeriodChangeExtendedStdCost) ELSE 0 END

,
CASE WHEN IM_PeriodPostingHistory.FiscalCalYear = CAST( AS CHAR) AND (IM_PeriodPostingHistory.FiscalCalPeriod = '01') THEN IM_PeriodPostingHistory.BeginningBalExtendedStdCost + IM_PeriodPostingHistory.PeriodChangeExtendedStdCost ELSE 0 END


,
CASE WHEN IM_PeriodPostingHistory.FiscalCalYear = CAST( AS CHAR) AND (IM_PeriodPostingHistory.FiscalCalPeriod = '01' OR IM_PeriodPostingHistory.FiscalCalPeriod = '02') THEN IM_PeriodPostingHistory.BeginningBalExtendedStdCost + IM_PeriodPostingHistory.PeriodChangeExtendedStdCost ELSE 0 END


,
CASE WHEN IM_PeriodPostingHistory.FiscalCalYear = CAST( AS CHAR) AND (IM_PeriodPostingHistory.FiscalCalPeriod = '01' OR IM_PeriodPostingHistory.FiscalCalPeriod = '02' OR IM_PeriodPostingHistory.FiscalCalPeriod = '03') THEN IM_PeriodPostingHistory.BeginningBalExtendedStdCost + IM_PeriodPostingHistory.PeriodChangeExtendedStdCost ELSE 0 END


,
CASE WHEN IM_PeriodPostingHistory.FiscalCalYear = CAST( AS CHAR) AND (IM_PeriodPostingHistory.FiscalCalPeriod = '01' OR IM_PeriodPostingHistory.FiscalCalPeriod = '02' OR IM_PeriodPostingHistory.FiscalCalPeriod = '03' OR IM_PeriodPostingHistory.FiscalCalPeriod = '04') THEN IM_PeriodPostingHistory.BeginningBalExtendedStdCost + IM_PeriodPostingHistory.PeriodChangeExtendedStdCost ELSE 0 END


,
CASE WHEN IM_PeriodPostingHistory.FiscalCalYear = CAST( AS CHAR) AND (IM_PeriodPostingHistory.FiscalCalPeriod = '01' OR IM_PeriodPostingHistory.FiscalCalPeriod = '02' OR IM_PeriodPostingHistory.FiscalCalPeriod = '03' OR IM_PeriodPostingHistory.FiscalCalPeriod = '04' OR IM_PeriodPostingHistory.FiscalCalPeriod = '05') THEN IM_PeriodPostingHistory.BeginningBalExtendedStdCost + IM_PeriodPostingHistory.PeriodChangeExtendedStdCost ELSE 0 END


,
CASE WHEN IM_PeriodPostingHistory.FiscalCalYear = CAST( AS CHAR) AND (IM_PeriodPostingHistory.FiscalCalPeriod = '01' OR IM_PeriodPostingHistory.FiscalCalPeriod = '02' OR IM_PeriodPostingHistory.FiscalCalPeriod = '03' OR IM_PeriodPostingHistory.FiscalCalPeriod = '04' OR IM_PeriodPostingHistory.FiscalCalPeriod = '05' OR IM_PeriodPostingHistory.FiscalCalPeriod = '06') THEN IM_PeriodPostingHistory.BeginningBalExtendedStdCost + IM_PeriodPostingHistory.PeriodChangeExtendedStdCost ELSE 0 END


,
CASE WHEN IM_PeriodPostingHistory.FiscalCalYear = CAST( AS CHAR) AND (IM_PeriodPostingHistory.FiscalCalPeriod = '01' OR IM_PeriodPostingHistory.FiscalCalPeriod = '02' OR IM_PeriodPostingHistory.FiscalCalPeriod = '03' OR IM_PeriodPostingHistory.FiscalCalPeriod = '04' OR IM_PeriodPostingHistory.FiscalCalPeriod = '05' OR IM_PeriodPostingHistory.FiscalCalPeriod = '06' OR IM_PeriodPostingHistory.FiscalCalPeriod = '07') THEN IM_PeriodPostingHistory.BeginningBalExtendedStdCost + IM_PeriodPostingHistory.PeriodChangeExtendedStdCost ELSE 0 END


,
CASE WHEN IM_PeriodPostingHistory.FiscalCalYear = CAST( AS CHAR) AND (IM_PeriodPostingHistory.FiscalCalPeriod = '01' OR IM_PeriodPostingHistory.FiscalCalPeriod = '02' OR IM_PeriodPostingHistory.FiscalCalPeriod = '03' OR IM_PeriodPostingHistory.FiscalCalPeriod = '04' OR IM_PeriodPostingHistory.FiscalCalPeriod = '05' OR IM_PeriodPostingHistory.FiscalCalPeriod = '06' OR IM_PeriodPostingHistory.FiscalCalPeriod = '07' OR IM_PeriodPostingHistory.FiscalCalPeriod = '08') THEN IM_PeriodPostingHistory.BeginningBalExtendedStdCost + IM_PeriodPostingHistory.PeriodChangeExtendedStdCost ELSE 0 END


,
CASE WHEN IM_PeriodPostingHistory.FiscalCalYear = CAST( AS CHAR) AND (IM_PeriodPostingHistory.FiscalCalPeriod = '01' OR IM_PeriodPostingHistory.FiscalCalPeriod = '02' OR IM_PeriodPostingHistory.FiscalCalPeriod = '03' OR IM_PeriodPostingHistory.FiscalCalPeriod = '04' OR IM_PeriodPostingHistory.FiscalCalPeriod = '05' OR IM_PeriodPostingHistory.FiscalCalPeriod = '06' OR IM_PeriodPostingHistory.FiscalCalPeriod = '07' OR IM_PeriodPostingHistory.FiscalCalPeriod = '08' OR IM_PeriodPostingHistory.FiscalCalPeriod = '09') THEN IM_PeriodPostingHistory.BeginningBalExtendedStdCost + IM_PeriodPostingHistory.PeriodChangeExtendedStdCost ELSE 0 END


,
CASE WHEN IM_PeriodPostingHistory.FiscalCalYear = CAST( AS CHAR) AND (IM_PeriodPostingHistory.FiscalCalPeriod = '01' OR IM_PeriodPostingHistory.FiscalCalPeriod = '02' OR IM_PeriodPostingHistory.FiscalCalPeriod = '03' OR IM_PeriodPostingHistory.FiscalCalPeriod = '04' OR IM_PeriodPostingHistory.FiscalCalPeriod = '05' OR IM_PeriodPostingHistory.FiscalCalPeriod = '06' OR IM_PeriodPostingHistory.FiscalCalPeriod = '07' OR IM_PeriodPostingHistory.FiscalCalPeriod = '08' OR IM_PeriodPostingHistory.FiscalCalPeriod = '09' OR IM_PeriodPostingHistory.FiscalCalPeriod = '10') THEN IM_PeriodPostingHistory.BeginningBalExtendedStdCost + IM_PeriodPostingHistory.PeriodChangeExtendedStdCost ELSE 0 END


,
CASE WHEN IM_PeriodPostingHistory.FiscalCalYear = CAST( AS CHAR) AND (IM_PeriodPostingHistory.FiscalCalPeriod = '01' OR IM_PeriodPostingHistory.FiscalCalPeriod = '02' OR IM_PeriodPostingHistory.FiscalCalPeriod = '03' OR IM_PeriodPostingHistory.FiscalCalPeriod = '04' OR IM_PeriodPostingHistory.FiscalCalPeriod = '05' OR IM_PeriodPostingHistory.FiscalCalPeriod = '06' OR IM_PeriodPostingHistory.FiscalCalPeriod = '07' OR IM_PeriodPostingHistory.FiscalCalPeriod = '08' OR IM_PeriodPostingHistory.FiscalCalPeriod = '09' OR IM_PeriodPostingHistory.FiscalCalPeriod = '10' OR IM_PeriodPostingHistory.FiscalCalPeriod = '11') THEN IM_PeriodPostingHistory.BeginningBalExtendedStdCost + IM_PeriodPostingHistory.PeriodChangeExtendedStdCost ELSE 0 END

,
CASE WHEN IM_PeriodPostingHistory.FiscalCalYear = CAST( AS CHAR) AND (IM_PeriodPostingHistory.FiscalCalPeriod = '01' OR IM_PeriodPostingHistory.FiscalCalPeriod = '02' OR IM_PeriodPostingHistory.FiscalCalPeriod = '03' OR IM_PeriodPostingHistory.FiscalCalPeriod = '04' OR IM_PeriodPostingHistory.FiscalCalPeriod = '05' OR IM_PeriodPostingHistory.FiscalCalPeriod = '06' OR IM_PeriodPostingHistory.FiscalCalPeriod = '07' OR IM_PeriodPostingHistory.FiscalCalPeriod = '08' OR IM_PeriodPostingHistory.FiscalCalPeriod = '09' OR IM_PeriodPostingHistory.FiscalCalPeriod = '10' OR IM_PeriodPostingHistory.FiscalCalPeriod = '11' OR IM_PeriodPostingHistory.FiscalCalPeriod = '12') THEN IM_PeriodPostingHistory.BeginningBalExtendedStdCost + IM_PeriodPostingHistory.PeriodChangeExtendedStdCost ELSE 0 END

FROM IM_PeriodPostingHistory INNER JOIN CI_Item ON IM_PeriodPostingHistory.ItemCode = CI_Item.ItemCode

WHERE
(IM_PeriodPostingHistory.FiscalCalYear = '2018'
)

Number : -2146233088
Source :
Driver Error : Unknown
SQL State : Unknown

I believe Craig's report was built for Sage 100.

You could have look at this report too. 

Inventory History Trend Report

RSS

The Sage Intelligence Blog

Like Sage Intelligence?

Follow @SageSupport

© 2019   Created by Sage Alchemex.   Powered by

Badges  |  Report an Issue  |  Terms of Service