Dynamic grouping for inventory dimension on SSRS Report in AX12 using query builder

We usually see the inventory dimensions selection criteria while running several inventory reports. I just went through the requirement where we needed to group on dimensions dynamically based on the selection. Different approaches can be used. I used query builder classes. Let’s say a user wants to split data on site, warehouse and batch number for the item

Below is the code which is getting a sum of quantity from the inventory transaction table for the items.

     Query                 inventTransQuery = new Query();
    QueryBuildRange       dynRange;
    QueryBuildDataSource  qbdsInvent, qbdsDim;
   
    inventTransQuery.clearAllFields();
 
    qbdsInvent  = inventTransQuery.addDataSource(tableNum(InventTrans));
    qbdsDim     = qbdsInvent.addDataSource(tableNum(InventDim));
 
    qbdsInvent.addSelectionField(fieldNum(InventTrans,Qty), SelectionField::Sum);
    qbdsInvent.addGroupByField(fieldNum(InventTrans,ItemId));
    qbdsInvent.addSelectionField(fieldNum(InventTrans, ItemId));
    qbdsDim.relations(true);
 
 
Now, adding dynamic grouping for inventory dimensions.
 
    inventdimviewcontract = contract.parmInventDimViewContract();
 
    if (inventdimviewcontract.parmViewInventSiteId())
    {
        _qbdsDim.addSelectionField(fieldNum(inventDim, InventSiteId));
        _qbdsDim.addOrderByField(fieldNum(inventDim, InventSiteId));
    }
 
    if (inventdimviewcontract.parmViewInventLocationId())
    {
        _qbdsDim.addSelectionField(fieldNum(inventDim, InventLocationId));
        _qbdsDim.addOrderByField(fieldNum(inventDim, InventLocationId));
    }
 
    if ( inventdimviewcontract.parmViewInventBatchId())
    {
        _qbdsDim.addSelectionField(fieldNum(inventDim, InventBatchId));
        _qbdsDim.addOrderByField(fieldNum(inventDim, InventBatchId));
    }
 
    if (inventdimviewcontract.parmViewInventSerialId())
    {
        _qbdsDim.addSelectionField(fieldNum(inventDim, InventSerialId));
        _qbdsDim.addOrderByField(fieldNum(inventDim, InventSerialId));
    }
 
    if (inventdimviewcontract.parmViewInventSizeId())
    {
        _qbdsDim.addSelectionField(fieldNum(inventDim, InventSizeId));
        _qbdsDim.addOrderByField(fieldNum(inventDim, InventSizeId));
    }
 
    if (inventdimviewcontract.parmViewInventColorId())
    {
        _qbdsDim.addSelectionField(fieldNum(inventDim, InventColorId));
        _qbdsDim.addOrderByField(fieldNum(inventDim, InventColorId));
    }
 
    if (inventdimviewcontract.parmViewConfigId())
    {
        _qbdsDim.addSelectionField(fieldNum(inventDim, ConfigId));
        _qbdsDim.addOrderByField(fieldNum(inventDim, ConfigId));
    }
 
    if (inventdimviewcontract.parmViewWMSLocationId())
    {
        _qbdsDim.addSelectionField(fieldNum(inventDim, wMSLocationId));
        _qbdsDim.addOrderByField(fieldNum(inventDim, wMSLocationId));
    }
 
    if (inventdimviewcontract.parmViewInventStyleId())
    {
        _qbdsDim.addSelectionField(fieldNum(inventDim, InventStyleId));
        _qbdsDim.addOrderByField(fieldNum(inventDim, InventStyleId));
    }
 
    if (inventdimviewcontract.parmViewWMSPalletId())
    {
        _qbdsDim.addSelectionField(fieldNum(inventDim, WMSPalletId));
        _qbdsDim.addOrderByField(fieldNum(inventDim, WMSPalletId));
    }
 
    _qbdsDim.orderMode(OrderMode::GroupBy);

AX Query:

 SELECT SUM(Qty), ItemId FROM InventTrans
GROUP BY InventTrans.ItemId, InventDim.InventSiteId, InventDim.InventLocationId, InventDim.inventBatchId
JOIN InventSiteId, InventLocationId, inventBatchId
FROM InventDim
ON InventTrans.inventDimId = InventDim.inventDimId

 SQL Query:
SELECT SUM(Qty), ItemId, InventSiteId, InventLocationId, inventBatchId
FROM InventTrans
JOIN InventDim
ON InventTrans.inventDimId = InventDim.inventDimId
GROUP BY InventTrans.ItemId, InventDim.InventSiteId, InventDim.InventLocationId, InventDim.inventBatchId


 

 

 

 

 


Comments

Post a Comment

Popular posts from this blog

Electronic Reporting For Beginners

Image
  Basics of Electronic Reporting & Business Documents in D365 F&O Introduction: Electronic reporting is a no-code reporting tool that is completely integrated into D365.  It allows the creation of report structures directly within the platform without the need for extra software like Visual Studio.  Customization of business documents using Excel is also feasible, this is a great alternative for non-developers.  C onfigurations from the Life Cycle Services (LCS) asset library can be easily downloaded  and loaded into D365FO for use in reports and print management. Visual editors are used to create all components of the ER configuration. The language that is used for data transformation resembles the language that is used in Microsoft Excel. Data flow: Component ER supports the following types of components: Data model Model mapping Format Metadata Designing: Go to the path:  Organization administration > Workspace >Electronic Rep...
Read more

Computed column and virtual field in D365

Image
  Computed column and virtual field: Requirement: Display methods needed in entity/view Solution: There are two different approaches for adding additional  unmapped  fields beyond those that are directly mapped to fields of the data sources. 1.        Computed column (SQL query executed by Microsoft SQL Server) 2.        Virtual Field (Custom X++ code)   ·       Computed Column: The computed column method returns a T-SQL query. It hits during database synchronization and is written to the database. In runtime no x++ code is executed, instead, your T-SQL query is executed inside the database. It is mostly used for reads/exports. Performance wise it is better than virtual methods. Equivalent X++ query: select sum (amount) from consignmentProductReceiptJournalLine           where consignmentProductReceiptJournalLine.ReplenishmentOrderLine =...
Read more

Complex or Advance Query Ranges - D365

Image
Complex or Advance Query Ranges  using null, enum, expressions  in D365 I came across the requirement to add custom ranges on query which cannot be handled by using the simple query add range method. So, I used query expressions to add range on query according to the requirement. Below are some ways to use advance or complex query ranges. We can pass values in query ranges by using strfmt so that %1 gets the value of X and %2 gets the value of Y. To get complete compile-time stability, use intrinsic functions to return the correct field names, as shown in the following code example. strFmt('((%1 == %2) || (%3 == %4))', fieldStr(MyTable, A), x, fieldStr(MyTable, B), y)   Comparing with null: We can use strfmt (%1 = = ‘ ‘) but instead it is better to use empty string function of SYSQUERY ; qbrDate.value(strFmt('(%1 != %2)’ ), fieldStr(InventTrans, DateInvent), SysQuery::valueEmptyString()); Comparing with somevalue: We there are many functions of  ...
Read more