You’re offline. This is a read only version of the page.
45
I need to create a excel report from inventtrans table,, i nee to get the record as month wise group by using datePhysical field,againt each item
SELECT ITEMID, DATEPART(Year,DATEPHYSICAL) as year,DATEPART(MONTH,DATEPHYSICAL) as month,SUM(QTY) FROM INVENTTRANS WHERE INVENTTRANS.ITEMID ='101' group by INVENTTRANS.ITEMID, DATEPART(Year,DATEPHYSICAL),DATEPART(MONTH,DATEPHYSICAL)
How can i achieve this in x (D365FO)
You didn't tell us how you designed your view and what you mean by "incorrect values", but the join looks wrong to me.
I would expect that your view contains year and month values for inventory transactions and than you'll join InventTrans with the view over InventTransId. Your query returns the same year and month for all transactions with the same item. regardless of the value of DatePhysical field of the given transaction.
Can you use the join in SSMS also? Because if you use join sometimes the returning data will change. So try giving same query in both places and check.
Create a view with computed columns, where you'll extract the year and month from DatePhysical.
Then use a select statement to get data from the view and use those computed columns in 'group by' clause.
[/quoteHere i need to find the sum of Quantity and sum of costvalue,i tried with this select statemnt ,but those values are in correct,can you pls help me to correct this
Share
Report
All responses (
Answers (
while select ItemId,DatePhysical,sum(Qty),sum(CostAmountPosted),sum(CostAmountAdjustment) from inventTrans
group by inventTrans.ItemId,inventview.MonthInDate,inventview.YearInDate
order by inventview.YearInDate desc
join inventview
where inventTrans.ItemId == inventview.ItemId
You can create Static or dynamic query which will solve this.
Create a view with computed columns, where you'll extract the year and month from DatePhysical.
Then use a select statement to get data from the view and use those computed columns in 'group by' clause.
#1
André Arnaud de Cal...
291,965
Super User 2025 Season 1
#2
Martin Dráb
230,836
Most Valuable Professional
#3
nmaenpaa
101,156
