How to fix following issue to show Date Wise inventory records in SSRS from Microsoft Dynamics AX

Question

I am new Microsoft Dynamics AX Business Analyst. I am writing an SQL Query to show Date Wise Inventory Records Like Opening Stock, Net Dispatch, Sold Stock, Transferred Out Stock, Stock Adjustment and Closing Stock. I wrote a query which displaying following records between @FromDate=2013-11-01 and @ToDate=2014-01-01. There are 518 Records fetching. I have no concern to show upto first 398 records in Report because these are Before Date records to calculate opening Stock. Problems are : 1. I don't want to show these (Before @FromDate)records on report what condition can be used to hide them. 2. Closing stock of Previous Date must be equivalent to Opening Stock of Next Day.But u can see in Figure 3 it is not so that.

NOTE: I sum up all the Quantities(Like Sum(OpeningStock)) one by one. It shows exact number of units. enter image description here Figure-1 Figure-2 Figure-3

Here Is the SQL Query.

SELECT TempTable.Dated, TempTable.BusinessGroup,
       ISNULL(SUM(Opening),0) OpeningStock, ISNULL(SUM(Dispatched),0) NetDispatched,
       (ISNULL(SUM(Opening),0) + ISNULL(SUM(Dispatched),0)) TotalStock,
       ISNULL(SUM(Sold),0) SoldStock, ISNULL(SUM(TransferredOut),0) TransferOutStock,
       ISNULL(SUM(StockAdjustment),0) StockAdjustment,
       (ISNULL(SUM(Opening),0)+ISNULL(SUM(Dispatched),0)-ISNULL(SUM(Sold),0)-ISNULL(SUM(TransferredOut),0)+ISNULL(SUM(StockAdjustment),0)) ClosingStock   
FROM
(
    /*------------------Opening---------------------------*/
    SELECT CSM.Dated,RIT.BUSINESSGROUP,RIT.DIVISIONGROUP,RIT.ITEMDEPARTMENT,CSM.InventLocationID,CSM.ITEMID,IT.ITEMNAME,RIT.SEASONCODE,CSM.LabelId,CSM.LabelName,SUM(CSM.Qty) Opening,
      0 Dispatched, 0 Sold, 0 TransferredOut, 0 StockAdjustment,CSM.OtherLocation
    FROM dbo.Mtq_CompleteStockMovement AS CSM 
      LEFT OUTER JOIN dbo.INVENTTABLE AS IT ON CSM.ITEMID = IT.ITEMID AND CSM.DataAreaID = IT.DATAAREAID
      LEFT OUTER JOIN dbo.RBOINVENTTABLE AS RIT ON RIT.ITEMID=IT.ITEMID AND RIT.DATAAREAID=IT.DATAAREAID
    WHERE CSM.DataAreaID='tcl' AND CSM.LabelId <>3 AND CSM.InventLocationID='SW143' AND CSM.Dated < '2013-11-01' 
    GROUP BY CSM.Dated,RIT.BUSINESSGROUP,RIT.DIVISIONGROUP,RIT.ITEMDEPARTMENT,CSM.InventLocationID,CSM.ITEMID,IT.ITEMNAME,RIT.SEASONCODE,CSM.LabelId,CSM.LabelName,CSM.OtherLocation

    UNION ALL

    SELECT CSM.Dated,RIT.BUSINESSGROUP,RIT.DIVISIONGROUP,RIT.ITEMDEPARTMENT,CSM.InventLocationID,CSM.ITEMID,IT.ITEMNAME,RIT.SEASONCODE,CSM.LabelId,CSM.LabelName,SUM(CSM.Qty) Opening,
      0 Dispatched, 0 Sold, 0 TransferredOut, 0 StockAdjustment,CSM.OtherLocation
    FROM dbo.Mtq_CompleteStockMovement AS CSM 
      LEFT OUTER JOIN dbo.INVENTTABLE AS IT ON CSM.ITEMID = IT.ITEMID AND CSM.DataAreaID = IT.DATAAREAID
      LEFT OUTER JOIN dbo.RBOINVENTTABLE AS RIT ON RIT.ITEMID=IT.ITEMID AND RIT.DATAAREAID=IT.DATAAREAID
    WHERE CSM.DataAreaID='tcl' AND CSM.LabelId =4 AND CSM.OtherLocation='SW143' AND CSM.Dated < '2013-11-01' 
    GROUP BY CSM.Dated,RIT.BUSINESSGROUP,RIT.DIVISIONGROUP,RIT.ITEMDEPARTMENT,CSM.InventLocationID,CSM.ITEMID,IT.ITEMNAME,RIT.SEASONCODE,CSM.LabelId,CSM.LabelName,CSM.OtherLocation

    UNION ALL

    /*---------------------Dispatch----------------------*/
    /****Transfer in****/
    SELECT CSM.Dated,RIT.BUSINESSGROUP,RIT.DIVISIONGROUP,RIT.ITEMDEPARTMENT,CSM.InventLocationID,CSM.ITEMID,IT.ITEMNAME,RIT.SEASONCODE,CSM.LabelId,CSM.LabelName, 0 Opening,
      SUM(CSM.Qty) Dispatched, 0 Sold, 0 TransferredOut, 0 StockAdjustment, CSM.OtherLocation
    FROM dbo.Mtq_CompleteStockMovement AS CSM 
      LEFT OUTER JOIN dbo.INVENTTABLE AS IT ON CSM.ITEMID = IT.ITEMID AND CSM.DataAreaID = IT.DATAAREAID
      LEFT OUTER JOIN dbo.RBOINVENTTABLE AS RIT ON RIT.ITEMID=IT.ITEMID AND RIT.DATAAREAID=IT.DATAAREAID
    WHERE CSM.DataAreaID='tcl' AND CSM.LabelId =4 AND CSM.InventLocationID='SW143' AND CSM.Dated BETWEEN '2013-11-01' AND '2014-01-01'

    GROUP BY CSM.Dated,RIT.BUSINESSGROUP,RIT.DIVISIONGROUP,RIT.ITEMDEPARTMENT,CSM.InventLocationID,CSM.ITEMID,IT.ITEMNAME,RIT.SEASONCODE,CSM.LabelId,CSM.LabelName,CSM.OtherLocation

    UNION ALL
    /***Purchase Order***/
    SELECT CSM.Dated,RIT.BUSINESSGROUP,RIT.DIVISIONGROUP,RIT.ITEMDEPARTMENT,CSM.InventLocationID,CSM.ITEMID,IT.ITEMNAME,RIT.SEASONCODE,CSM.LabelId,CSM.LabelName, 0 Opening,
      SUM(CSM.Qty) Dispatched, 0 Sold, 0 TransferredOut, 0 StockAdjustment, CSM.OtherLocation
    FROM dbo.Mtq_CompleteStockMovement AS CSM 
      LEFT OUTER JOIN dbo.INVENTTABLE AS IT ON CSM.ITEMID = IT.ITEMID AND CSM.DataAreaID = IT.DATAAREAID
      LEFT OUTER JOIN dbo.RBOINVENTTABLE AS RIT ON RIT.ITEMID=IT.ITEMID AND RIT.DATAAREAID=IT.DATAAREAID
    WHERE CSM.DataAreaID='tcl' AND CSM.LabelId =1 AND CSM.InventLocationID='SW143' AND CSM.Dated BETWEEN '2013-11-01' AND '2014-01-01'

    GROUP BY CSM.Dated,RIT.BUSINESSGROUP,RIT.DIVISIONGROUP,RIT.ITEMDEPARTMENT,CSM.InventLocationID,CSM.ITEMID,IT.ITEMNAME,RIT.SEASONCODE,CSM.LabelId,CSM.LabelName,CSM.OtherLocation

    UNION ALL

    /***------------------Sales---------------------***/
    SELECT CSM.Dated,RIT.BUSINESSGROUP,RIT.DIVISIONGROUP,RIT.ITEMDEPARTMENT,CSM.InventLocationID,CSM.ITEMID,IT.ITEMNAME,RIT.SEASONCODE,CSM.LabelId,CSM.LabelName, 0 Opening,
      0 Dispatched, SUM(CSM.Qty * -1)Sold, 0 TransferredOut, 0 StockAdjustment, CSM.OtherLocation
    FROM dbo.Mtq_CompleteStockMovement AS CSM 
      LEFT OUTER JOIN dbo.INVENTTABLE AS IT ON CSM.ITEMID = IT.ITEMID AND CSM.DataAreaID = IT.DATAAREAID
      LEFT OUTER JOIN dbo.RBOINVENTTABLE AS RIT ON RIT.ITEMID=IT.ITEMID AND RIT.DATAAREAID=IT.DATAAREAID
    WHERE CSM.DataAreaID='tcl' AND CSM.LabelId =6 AND CSM.InventLocationID='SW143' AND CSM.Dated BETWEEN '2013-11-01' AND '2014-01-01'

    GROUP BY CSM.Dated,RIT.BUSINESSGROUP,RIT.DIVISIONGROUP,RIT.ITEMDEPARTMENT,CSM.InventLocationID,CSM.ITEMID,IT.ITEMNAME,RIT.SEASONCODE,CSM.LabelId,CSM.LabelName,CSM.OtherLocation

    UNION ALL
    /***---------------Transfer out------------------***/
    SELECT CSM.Dated,RIT.BUSINESSGROUP,RIT.DIVISIONGROUP,RIT.ITEMDEPARTMENT,CSM.InventLocationID,CSM.ITEMID,IT.ITEMNAME,RIT.SEASONCODE,CSM.LabelId,CSM.LabelName, 0 Opening,
     0 Dispatched, 0 Sold, SUM(CSM.Qty) TransferredOut, 0 StockAdjustment, CSM.OtherLocation
    FROM dbo.Mtq_CompleteStockMovement AS CSM 
      LEFT OUTER JOIN dbo.INVENTTABLE AS IT ON CSM.ITEMID = IT.ITEMID AND CSM.DataAreaID = IT.DATAAREAID
      LEFT OUTER JOIN dbo.RBOINVENTTABLE AS RIT ON RIT.ITEMID=IT.ITEMID AND RIT.DATAAREAID=IT.DATAAREAID
    WHERE CSM.DataAreaID='tcl' AND CSM.LabelId =3 AND CSM.InventLocationID='SW143' AND CSM.Dated BETWEEN '2013-11-01' AND '2014-01-01'

    GROUP BY CSM.Dated,RIT.BUSINESSGROUP,RIT.DIVISIONGROUP,RIT.ITEMDEPARTMENT,CSM.InventLocationID,CSM.ITEMID,IT.ITEMNAME,RIT.SEASONCODE,CSM.LabelId,CSM.LabelName,CSM.OtherLocation

    UNION ALL
    /***------------Stock Adjustment--------------***/
    SELECT CSM.Dated,RIT.BUSINESSGROUP,RIT.DIVISIONGROUP,RIT.ITEMDEPARTMENT,CSM.InventLocationID,CSM.ITEMID,IT.ITEMNAME,RIT.SEASONCODE,CSM.LabelId,CSM.LabelName, 0 Opening,
      0 Dispatched, 0 Sold, 0 TransferredOut, SUM(CSM.Qty) StockAdjustment, CSM.OtherLocation
    FROM dbo.Mtq_CompleteStockMovement AS CSM 
      LEFT OUTER JOIN dbo.INVENTTABLE AS IT ON CSM.ITEMID = IT.ITEMID AND CSM.DataAreaID = IT.DATAAREAID
      LEFT OUTER JOIN dbo.RBOINVENTTABLE AS RIT ON RIT.ITEMID=IT.ITEMID AND RIT.DATAAREAID=IT.DATAAREAID
    WHERE CSM.DataAreaID='tcl' AND CSM.LabelId =5 AND CSM.InventLocationID='SW143' AND CSM.Dated BETWEEN '2013-11-01' AND '2014-01-01'

    GROUP BY CSM.Dated,RIT.BUSINESSGROUP,RIT.DIVISIONGROUP,RIT.ITEMDEPARTMENT,CSM.InventLocationID,CSM.ITEMID,IT.ITEMNAME,RIT.SEASONCODE,CSM.LabelId,CSM.LabelName,CSM.OtherLocation
) AS TempTable

GROUP BY TempTable.Dated, TempTable.BusinessGroup
ORDER BY TempTable.Dated, TempTable.BusinessGroup

Thanks for Help in Advance.

How to fix following issue to show Date Wise inventory records in SSRS from Microsoft Dynamics AX

0 Answers0