How do I translate SSRS query logic into a Dynamics 365 X++ query

Finance and Operations/AX Developer Forum
1

I am currently rebuilding an SSRS report that was external to D365 and hosted in SQL Server Reporting Services.
I want to know the best way of translating the SQL logic below to build a query in X++

****** SQL Logic ******

select a.WareHouseID, a.itemid, a.[Description],a.jbgmaingroup

, COUNT(case when a.DaysToExpiration >= 0 AND a.DaysToExpiration <= 5 then 1 end) as [0 to 5 cases]

, COUNT(case when a.DaysToExpiration >= 6 AND a.DaysToExpiration <= 10 then 1 end) as [6 to 10 cases]

, COUNT(case when a.DaysToExpiration >= 11 AND a.DaysToExpiration <= 16 then 1 end) as [11 to 16 cases]

, COUNT(case when a.DaysToExpiration >= 17 AND a.DaysToExpiration <= 20 then 1 end) as [17 to 20 cases]

, COUNT(case when a.DaysToExpiration >= 21 AND a.DaysToExpiration <= 30 then 1 end) as [21 to 30 cases]

, COUNT(case when a.DaysToExpiration >= 31 then 1 end) as [31 and over cases]

, SUM(case when a.DaysToExpiration >= 0 AND a.DaysToExpiration <= 5 then [Weight] end) as [0 to 5 Kgs]

, SUM(case when a.DaysToExpiration >= 6 AND a.DaysToExpiration <= 10 then [Weight] end) as [6 to 10 Kgs]

, SUM(case when a.DaysToExpiration >= 11 AND a.DaysToExpiration <= 16 then [Weight] end) as [11 to 16 Kgs]

, SUM(case when a.DaysToExpiration >= 17 AND a.DaysToExpiration <= 20 then [Weight] end) as [17 to 20 Kgs]

, SUM(case when a.DaysToExpiration >= 21 AND a.DaysToExpiration <= 30 then [Weight] end) as [21 to 30 Kgs]

, sum(case when a.DaysToExpiration >= 31 then [Weight] end) as [31 and over Kgs]

FROM (

SELECT dbo.Inventory.WareHouseID, dbo.Inventory.ItemID, it.ItemName as [Description], it.jbgmaingroup, dbo.Inventory.Weight,

DATEDIFF(DD, convert(date, getdate()),dbo.Inventory.ExpirationDate) as DaysToExpiration

FROM dbo.Inventory

inner join [edc-clst07sql01].[AX_50_Prod].[dbo].[INVENTTABLE] it on dbo.Inventory.itemid = it.itemid

WHERE (RIGHT(LEFT(dbo.Inventory.SerialID, 14), 3) NOT LIKE ‘%[^0-9]%’) AND (RIGHT(LEFT(dbo.Inventory.SerialID, 11), 1) NOT LIKE ‘%[^0-9]%’)

AND (dbo.Inventory.SerialID LIKE ‘___[0-9][0-9][0-9][0-9][0-9][0-9][A-Z][0-9][0-9][0-9][0-9][0-9]%’)

AND dbo.Inventory.itemid in (@itemID) AND dbo.Inventory.warehouseid = @warehouseID

AND it.dataareaid = ‘bdc’) a

group by a.WareHouseID, a.itemid, a.[Description], a.jbgmaingroup

order by a.itemid, a.WareHouseID, a.jbgmaingroup

Do you have any ideas on how to translate the SQL expression “case when a.DaysToExpiration >= 0 AND a.DaysToExpiration <= 5 then 1 end) as [0 to 5 cases]” in X++

Note: Screenshot below.

2

I would create a computed column for each case.

Each of the method can call the common logic, of course, just with different parameters.

By the way, please use Insert > Insert Code to paste source code. It prevents the double line spacing, it preserves line indentation and you can even use syntax highlighting for SQL.

3

Thank you very much for your response and suggestion Martin.
I truly appreciate it.

I am new to D365, could you please explain where I am supposed to create the calculated column. I am guessing on the TempTable that I have created to store the reports data.

Please I am asking for your guidance.

I currently have the Contract, Data Provider and Controller classes along with the TempTable I created to store the data for the report.

Please see Screenshot(s) and code snippet(s) below:

– TempTable –

– Data Provider Class –

[
//SRSReportQueryAttribute('JBGInventoryAgingByItemCategoryReportQuery'),
SRSReportParameterAttribute(classstr(JBGInventoryAgingByItemCategoryReportContract))
]
public class JBGInventoryAgingByItemCategoryReportDP extends SRSReportDataProviderBase
{

    InventLocationId warehouse;
    ItemGroupId productGroupId;
    ItemId itemId;
    //str agingBuckets;

    InventWarehouseInventoryStatusOnHandIncludingCatchWeightItemEntity inventWarehouseInventory;
    InventTable inventTable;


    Query                   query;
    QueryBuildDataSource    datasource;
    QueryBuildRange         rangeInventWarehouse;
    QueryBuildRange         rangeInventTable;
    QueryFilter             filter;
    QueryRun                queryRun;

    JBGInventoryAgingByItemCategoryReport_TempTable inventoryAgingByItemCategoryReport_TempTable;

    [SRSReportDataSetAttribute('JBGInventoryAgingByItemCategoryReport_TempTable')]
    public JBGInventoryAgingByItemCategoryReport_TempTable GetInventoryAgingByItemCategoryReport_TempTable()
    {
        select * from inventoryAgingByItemCategoryReport_TempTable;
        return inventoryAgingByItemCategoryReport_TempTable;
    }

    public void processReport()
    {
        JBGInventoryAgingByItemCategoryReportContract dataContract;

        dataContract = this.parmDataContract() as JBGInventoryAgingByItemCategoryReportContract;

        warehouse        = dataContract.paramWarehouse();
        productGroupId   = dataContract.paramProductGroupId();
        itemId           = dataContract.paramItemId();

        query = new Query();
        datasource = query.addDataSource(tableNum(InventWarehouseInventoryStatusOnHandIncludingCatchWeightItemEntity));
        datasource = datasource.addDataSource(tableNum(InventTable));
        datasource.joinMode(JoinMode::InnerJoin);
        datasource.relations(true);
        datasource.addLink(fieldNum(InventWarehouseInventoryStatusOnHandIncludingCatchWeightItemEntity, ItemNumber),
            fieldNum(InventTable, ItemId));

        rangeInventWarehouse = datasource.findRange(fieldnum(InventWarehouseInventoryStatusOnHandIncludingCatchWeightItemEntity, InventoryWarehouseId));
        rangeInventTable = datasource.findRange(fieldnum(InventTable, ItemId));

        if (!rangeInventWarehouse)
        {
            rangeInventWarehouse = datasource.addRange(fieldnum(InventWarehouseInventoryStatusOnHandIncludingCatchWeightItemEntity, InventoryWarehouseId));
        }
        if(!rangeInventWarehouse.value()) // If the Warehouse has not been set, then use the parameter value to set it.
        {
            rangeInventWarehouse.value(warehouse);
        }

        if (!rangeInventTable)
        {
            rangeInventTable = datasource.addRange(fieldnum(InventTable, ItemId));
        }
        if(!rangeInventTable.value()) // If the ItemId has not been set, then use the parameter value to set it.
        {
            rangeInventTable.value(itemId);
        }

        
        // Run the query with modified ranges.
        queryRun = new QueryRun(query);

        ttsbegin;
        while(queryRun.next())
        {
            
            
            inventoryAgingByItemCategoryReport_TempTable.clear();
            
            inventWarehouseInventory = queryRun.get(tablenum(InventWarehouseInventoryStatusOnHandIncludingCatchWeightItemEntity));
            inventTable = queryRun.get(tablenum(InventTable));
            
            
            inventoryAgingByItemCategoryReport_TempTable.WarehouseID = inventWarehouseInventory.InventoryWarehouseId;
            inventoryAgingByItemCategoryReport_TempTable.ItemID = inventTable.ItemId;
            inventoryAgingByItemCategoryReport_TempTable.Description = "";
            inventoryAgingByItemCategoryReport_TempTable.ID_0_to_5_Cases = 0;
            inventoryAgingByItemCategoryReport_TempTable.ID_6_to_10_Cases =  0;
            inventoryAgingByItemCategoryReport_TempTable.ID_11_to_16_Cases = 0;
            inventoryAgingByItemCategoryReport_TempTable.ID_17_to_20_Cases = 0;
            inventoryAgingByItemCategoryReport_TempTable.ID_21_to_30_Cases = 0;
            inventoryAgingByItemCategoryReport_TempTable.ID_31_and_over_Cases = 0;
            inventoryAgingByItemCategoryReport_TempTable.ID_0_to_5_Kgs = 0;
            inventoryAgingByItemCategoryReport_TempTable.ID_6_to_10_Kgs =  0;
            inventoryAgingByItemCategoryReport_TempTable.ID_11_to_16_Kgs = 0;
            inventoryAgingByItemCategoryReport_TempTable.ID_17_to_20_Kgs = 0;
            inventoryAgingByItemCategoryReport_TempTable.ID_21_to_30_Kgs = 0;
            inventoryAgingByItemCategoryReport_TempTable.ID_31_and_over_Kgs = 0;
            
            inventoryAgingByItemCategoryReport_TempTable.insert();

                                    
        }
        ttscommit;
        
        
    }

}

– Controller –

public class JBGInventoryAgingByItemCategoryReportController extends SrsReportRunController
{
    public static void main(Args _args)
    {
        JBGInventoryAgingByItemCategoryReportController controller = new JBGInventoryAgingByItemCategoryReportController();
        controller.parmReportName(ssrsReportStr(JBGInventoryAgingByItemCategoryReport_Report, JBGInventoryAgingByItemCategoryReport_ReportDesign));
        controller.parmArgs(_args);
        controller.parmDialogCaption("Inventory Aging By Item Category Report");
        //controller.parmShowDialog(false);
        controller.startOperation();
    }

}
4

Hello Martin.
As it relates to my reply to your post yesterday, can you say if the information I posted states that I have to good foundation ?
Please let me know what are your thoughts pertaining to my response to your reply.

Thank you.

5

You need fields in the temporary table to hold the values, but that’s not the topic we’re discussing right now. You asked about fetching the data from database and I’m talking about the same. You would create a view with computed columns and each computed column would contain a subquery for one of the cases. Then you would query this view to fetch the data.