find Physical Inventory Qty by date

i modified code originated from DAXDUDE ,
i added inventQty = inventSumDateDim.postedQty()-InventSumDateDim.deductedQty()+InventSumDateDim.postedQty() to get physical inventory

static void TIDF_getInventPhysicalbyDate(Args _args)
{
    InventDimParm inventDimParmCrit;
    InventSumDateDim inventSumDateDim;
    InventDim inventDim = InventDim::find('AllBlank');
    InventQty inventQty;
    ;

    // Set the dimensions
    inventDimParmCrit.initFromInventDim(inventDim);
    inventSumDateDim = inventSumDateDim::newParameters(today(), '010410003', inventDim, inventDimParmCrit);
    inventQty =  inventSumDateDim.receivedQty()-InventSumDateDim.deductedQty()+InventSumDateDim.postedQty();

    info(strFmt("%1",inventQty));
}

source : http://daxdude.blogspot.com/2012/11/ax-2012-find-on-hand-inventory-by-date.html

error : The DefaultValue expression for the report parameter ‘AX_CompanyName’ contains an error: Request for the permission of type System.Security.Permissions.EnvironmentPermission

this error occur when I run the SSRS report. Here are to solution :

1. on your windows server that contains SQL Server, go to C:\Program Files\Microsoft SQL Server, then do find the MRS for your environment.
Capture2
 
2. find rssrvpolicy.config
Capture
 
3. replace PermissionSetName=”Execution” on class=”UnionCodeGroup” with PermissionSetName=”FullTrust”
capture3

source : https://msdn.microsoft.com/en-us/library/ms155866.aspx

error : For views relation fields in query can not come from base or derived tables.

This error occurs at on standar AX View because i add new relation on Derived table.

Capture

example base table :
Capture

how to fix it :
1. At the example views QUERY HcmBenPolWkrPosDtlInfo, find OMOperatingUnit datasource, and find the relation on the table.
Capture

2. Set the RELATIONS properties to NO on query datasource table , then right click on the relations, add new relations and manually configure the tables relations, do this for the all base table in datasource too
Capture

3. Then restore the query views, do restore to on the views, then re-compile the view
Capture

refresh DB Sync error log

i found error log that make no sense after DB sync on AX 2012 R2 CU7. the table that the error pointed to already deleted, but the error still occurred.

here is the solution :
“If you are getting synchronization errors that do no make sense or that you think you should have already resolved there may be old records left in the SQLsyncinfo table or have old or incorrect object ID references. If you suspect this make a database backup, shutdown the AOS, in SQL run truncate table SQLSYNCINFO, start the AOS and run a new Synchronize from the AOT.”

source : http://blogs.msdn.com/b/axsupport/archive/2012/09/19/troubleshooting-aot-synchronize-errors.aspx

Kernel version and application version number are different on AX 2012 R3 CU8

i installed AX 2012 R3, then i installed R3 CU8 update with offline package.
the problem is after installed, the version number is different.
1432569397994

although it was running well on the first heavy test, i’m afraid the R2 crash issue because different build number will raise again at R3.
to make it same , install AX R3 with slipstream CU8 package update, here is the full guide from MSDN blog :
http://blogs.msdn.com/b/axsupport/archive/2015/04/21/how-to-slip-stream-ax-2012-r3-cu-8.aspx

error : Failed to create a session, confirm that the user has the proper privileges to log on to Microsoft Dynamics

I just recently installed new fresh AX 2012 R3 and importing data using Test Data Tools :
https://technet.microsoft.com/en-us/library/dn756356.aspx .

but somehow when i start sync db after full compilation and full CIL, i got following error :
“Failed to create a session; confirm that the user has the proper privileges to log on to Microsoft Dynamics”.

i logged in using domain administrator.

here is the solution :
on the user mapping at SQL Server , make sure the db owner for administrator is checked. if you are using another user, just make sure db.datareader and db.datawriter is checked.
Untitled

voila !! , the ax db sync successfully

more info :
https://community.dynamics.com/ax/f/33/t/48661

Add new custom webparts (Data Grid) to Enterprise Portal Quick Launch sidebar tutorial

This is step by step to create custom webparts and added it to Enterprise Portal (EP)
1. at Visual Studio, add new EP VS Project
Capture

2. Right click at the solution project, and click add new item, select Microsoft Dynamics Ax -> EP User Control with form and grid.
Capture

3. Go to AOT, add new data set , then add datasource, link it to your table , do compile and do INCREMENTAL CIL.
Capture

4. Go back to VS, refresh schema for the datasource, and select your newly created datasource
Capture

5. Make sure your column etc are inside axForm, fill the properties DataKeyNames,DataMember and DataSourcesID
Capture

6. at gridview, select datasource, and edit colomn, select field you want to show on the grid
Capture

7. right click on VS solution project, and choose “Add your_webParts to AOT”
Capture

8. go back to AOT, on Web -> Web Content -> Managed , find your webcontrol, right click on it, and Deploy to EP, then choose which module this webcontrol belongs to. After you deploy it , AX will create an URL Menu Item automatically at (Web -> Web Menu Items -> URLs) and Page Definition (Web -> Web Files -> Page Definition
Capture

9. If your menu is on submenu of the module, example Home\EmployeeServices\ManageTime , just change it on properties “Web Module” at Page Definition. and change the url on on web url properties
Capture
Capture

10. The next step is we want to add newly web parts to EP Quick Launch. Find the page you want to customize on “Web Modules” on AOT, on the properties, look at value of properties “Quick Launch”
Capture

10. Go to Web -> Web Menus , find the Quick Launch, and drag and drop the newly add url menu item to Quick Launch Menu, then deploy the EP from System Admin -> Setup -> Enterprise Portal -> Deployment
Capture

11. go to Web -> Web Modules . just right click and deploy on the module you customize.
Capture

12. Last, restart IIS for refreshing the EP with newest customize, with command prompt see post :
https://hellodax.com/2015/05/22/restart-iis-command-prompt/
Capture

13. Preview
Capture

Restart IIS command prompt

To restart IIS using the IISReset command-line utility
1.From the Start menu, click Run.
2.In the Open box, type cmd, and click OK.
3.At the command prompt, type iisreset /noforce computername, and press ENTER.
4.IIS attempts to stop all services before restarting. The IISReset command-line utility waits up to one minute for all services to stop. If the services cannot be stopped within one minute, all IIS services are terminated, and IIS restarts.

example :
Capture

source :
https://www.microsoft.com/technet/prodtechnol/WindowsServer2003/Library/IIS/003ed2fe-6339-4919-b577-6aa965994a9b.mspx?mfr=true

get LedgerDimension combination RecId example code

source : http://kiwiaxguy.blogspot.com/2014/07/setting-ledgerdimension-field-on.html

i modify some code from the source because the dimension name is hardcoded,

on class global :

public static container getDimensionNameTID()
{
    DimensionAttribute              dimAttr;
    DimensionAttributeSetItem       dimAttrSetItem;
    DimensionEnumeration            dimensionSetId;
    DimensionAttributeValue         dimAttributeValue;
    container                       DimensionName;

    dimensionSetId      = DimensionCache::getDimensionAttributeSetForLedger();

    while select dimAttr order by Name
                where dimAttr.Type != DimensionAttributeType::MainAccount
            join RecId from dimAttrSetItem
                where dimAttrSetItem.DimensionAttribute     == dimAttr.RecId &&
                      dimAttrSetItem.DimensionAttributeSet  == dimensionSetId
            {
                dimensionName += dimAttr.Name;

            }

    return DimensionName;
}

getLedgerDimension example code :

public static RecId getLedgerDimensionCombination(MainAccountNum _ledgerAccount, container dimValue)
{
    DimensionServiceProvider DimensionServiceProvider = new DimensionServiceProvider();
    LedgerAccountContract LedgerAccountContract = new LedgerAccountContract();
    DimensionAttributeValueContract   ValueContract;
    List ListValueContract = new List(Types::Class);
    dimensionAttributeValueCombination dimensionAttributeValueCombination;
    DimensionStorage          dimStorage;
    container dimensionName = Global::getDimensionNameTID();
    int       loopDimValue = 1;

    if(conLen(dimValue) > 0)
    {
        while(loopDimValue <= conLen(dimValue))
        {
            ValueContract = new DimensionAttributeValueContract();
            ValueContract.parmName(conPeek(dimensionName,loopDimValue)) ;
            ValueContract.parmValue(conPeek(dimValue,loopDimValue));
            ListValueContract.addEnd(ValueContract);
            loopDimValue++;
        }

        LedgerAccountContract.parmMainAccount(_ledgerAccount);
        LedgerAccountContract.parmValues(ListValueContract);
        dimStorage = DimensionServiceProvider::buildDimensionStorageForLedgerAccount(LedgerAccountContract);
        dimensionAttributeValueCombination = DimensionAttributeValueCombination::find(dimStorage.save());
        return dimensionAttributeValueCombination.RecId;
    }
    else
    {
        return 0;
    }

}

example code on how to use it :

static void TIDF_getLedgerDim(Args _args)
{
    DimensionValue       dim1,dim2;
    container conDimValue;
    MainAccountNum       mainAccountNum;

    mainAccountNum = "6100-1001";
    dim1           = "5102";
    dim2           = "OPERATIO";
    conDimValue = [dim1,dim2];

    info(strFmt("%1",getLedgerDimensionCombination(mainAccountNum,conDimValue)));


}

Init field value from datasource , show on Enterprise Portal AXBoundField

on AOT dataset method init() :
Capture

X++ code on init method example:

public void init()
{
    super();
    DNLLeaveRequest.DNLEmplRecId  = DNLEmployeeTable::findByUserId().RecId;
    DNLLeaveRequest.LeaveDateFrom = today();
    DNLLeaveRequest.LeaveDateTo   = today();
}

or you can code it on Tables\initvalue()

XML code on visual studio for EP pages :

<%@ Control Language="C#" AutoEventWireup="true" CodeFile="TIDDNLLeaveRequestEP.ascx.cs" Inherits="TIDDNLLeaveRequestEP" %>
<dynamics:AxDataSource ID="AxDataSource1" runat="server" 
    DataSetName="TIDLeaveRequestDS" ProviderView="DNLLeaveRequest" 
    oninitializeddatasetrun="AxDataSource1_InitializedDataSetRun">
</dynamics:AxDataSource>
<dynamics:AxForm ID="AxForm1" runat="server" DataSourceID="AxDataSource1" 
    DataKeyNames="RecId,Added,AdvanceUsed,BlankLeave,carryused,ContactPhone,createdBy,createdDateTime,dataAreaId,DNLEmplRecId,DNLLeaveTypeRecId,IdApproval,IdPersonSubstitute,LeaveDateFrom,LeaveDateTo,modifiedBy,modifiedDateTime,Remarks,RequestDate,State,StatusFlag,SubmittedBy,SubmittedDateTime,TableId,TotalLeaveDays,TravellingLocation,getEmplName**,GetEntitlement**,getExpiration**,getLeaveDesc**,mostRecentComment**,requiredActionDueDate**,titleFields**" 
    DataMember="DNLLeaveRequest_Current" AutoGenerateInsertButton="True" 
    DefaultMode="Insert" UpdateOnPostBack="true" oninit="AxForm1_Init"  >
    <dynamics:AxMultiColumn ID="AxMultiColumn1" runat="server">
    
 
    
 
        <dynamics:AxColumn ID="AxColumn2" runat="server">
            <dynamics:AxGroup ID="groupLeaveRequest" runat="server" FormID="AxForm1" 
                Caption="Leave Request" CaptionAlign="Top" HorizontalAlign="Left" 
                Width="300px">
                <Fields>
                    <dynamics:AxReferenceBoundField DataField="DNLEmplRecId" 
                        DataSet="TIDLeaveRequestDS" DataSetView="DNLLeaveRequest" 
                        SortExpression="DNLEmplRecId" Mandatory="True">
                    </dynamics:AxReferenceBoundField>
                    <dynamics:AxReferenceBoundField AutoPostBack="True" 
                        DataField="DNLLeaveTypeRecId" DataSet="TIDLeaveRequestDS" 
                        DataSetView="DNLLeaveRequest" SortExpression="DNLLeaveTypeRecId" 
                        Mandatory="True">
                    </dynamics:AxReferenceBoundField>
                    <dynamics:AxBoundField AutoPostBack="True" DataField="LeaveDateFrom" 
                        DataSet="TIDLeaveRequestDS" 
                        DataSetView="DNLLeaveRequest" SortExpression="LeaveDateFrom">
                    </dynamics:AxBoundField>
                    <dynamics:AxBoundField AutoPostBack="True" DataField="LeaveDateTo" 
                        DataSet="TIDLeaveRequestDS" 
                        DataSetView="DNLLeaveRequest" SortExpression="LeaveDateTo" 
                        Mandatory="True" >
                    </dynamics:AxBoundField>
                    <dynamics:AxBoundField AutoPostBack="True" DataField="Remarks" 
                        DataSet="TIDLeaveRequestDS" DataSetView="DNLLeaveRequest" 
                        SortExpression="Remarks">
                    </dynamics:AxBoundField>
                    <dynamics:AxBoundField AutoPostBack="True" DataField="TotalLeaveDays" 
                        DataSet="TIDLeaveRequestDS" DataSetView="DNLLeaveRequest" 
                        SortExpression="TotalLeaveDays">
                    </dynamics:AxBoundField>
                    <dynamics:AxBoundField DataField="GetEntitlement**" DataSet="TIDLeaveRequestDS" 
                        DataSetView="DNLLeaveRequest" ReadOnly="True">
                    </dynamics:AxBoundField>
                </Fields>
            </dynamics:AxGroup>
        </dynamics:AxColumn>
    </dynamics:AxMultiColumn>
</dynamics:AxForm>

C# code on visual studio for EP pages

using System;
using System.Collections.Generic;
using System.Linq;
using System.Web;
using System.Web.UI;
using System.Web.UI.DataVisualization.Charting;
using System.Web.UI.WebControls;
using Microsoft.Dynamics.Framework.Portal.UI.WebControls;
using Microsoft.Dynamics.Framework.Portal.UI.WebControls.WebParts;
using Microsoft.Dynamics.AX.Framework.Portal.Data;
using Microsoft.Dynamics.AX.Framework.Services.Client;
using Microsoft.Dynamics.Framework.BusinessConnector.Session;
using Microsoft.Dynamics.Framework.BusinessConnector.Adapter;
using Microsoft.Dynamics.Framework.Portal;
using Microsoft.Dynamics.Framework.BusinessConnector.Proxy;

public partial class TIDDNLLeaveRequestEP : System.Web.UI.UserControl
{
    DateTime dateFrom;
    DateTime dateTo;
    Int64 recIdLeaveType;
   
    

    #region customMethod
    private ISession AxSession
    {
        get 
        {
            AxBaseWebPart webPart = AxBaseWebPart.GetWebpart(this);
            return webPart == null ? null : webPart.Session;
        }
    }
    static AxBoundField GetField(DataControlFieldCollection fields, string name)
    {
        foreach (DataControlField field in fields)
        {
            // Is this the field being searched for?
            AxBoundField boundField = field as AxBoundField;
            if (boundField != null && String.Compare(boundField.DataField, name, true) == 0)
            {
                return boundField;
            }

        }
        // Nothing found, so return null.
        return null;
    }

    private DataSetViewRow CurrentRow
    {
        get
        {
            try
            {
                DataSetView dataSetView;
                dataSetView = this.AxDataSource1.GetDataSet().DataSetViews["DNLLeaveRequest"];
                return (dataSetView == null) ? null : dataSetView.GetCurrent();
            }
            catch (System.Exception)
            {
                return null;
            }
        }
    }

    #endregion customMethod

    protected void AxForm1_Init(object sender, EventArgs e)
    {
        //this.loadFirst();
        this.AxForm1.DefaultMode = DetailsViewMode.Insert;
        this.AxForm1.AutoGenerateInsertButton = true;

        AxBoundField axbfNoNPK;
        axbfNoNPK = GetField(groupLeaveRequest.Fields, "DNLEmplRecId");
        axbfNoNPK.ReadOnly = true; 
    }


    protected void Page_Load(object sender, EventArgs e)
    {

    }
 
   
    protected void AxDataSource1_InitializedDataSetRun(object sender, AxDataSourceDataSetEventArgs e)
    {
        e.DataSet.Init();
    }
}

preview :
Capture

error: The data source is not embedded within a (parent) data source.

This error will occur when you add new dataSource with function “addDataSource(tableNum(MyTable))” but not specify which it’s parent datasource

so example code before :

qbsInventDim = _query.addDataSource(tableNum(WMSLocation));

code after :

qbsInventDim = _query.dataSourceTable(tableNum(InventDim)).addDataSource(tableNum(WMSLocation))

Print Query-Based SSRS via X++ example

code example :

public void PrintPenarikanBarang(TECTradeInId tradeInCode)
{
    SrsReportRunController                  reportRunController;
    Map                                     queryContracts;
    MapEnumerator                           mapEnum;
    Query                                   query;
    QueryBuildRange                         range;

    SrsReportDataContract                   contract;
    TECTradeInPenarikanBarangReportContract     rdpContract;

    if(TECTradeInTable.TECTradeInCode)
    {
        reportRunController = new SrsReportRunController();
        reportRunController.parmReportName(ssrsReportStr(TECPenarikanBarangTradeIn ,Report));
        reportRunController.parmLoadFromSysLastValue(false);

        // Set printer settings (print to file, format, filename, etc).
        contract    = reportRunController.parmReportContract();

        rdpContract = contract.parmRdpContract() as TECTradeInPenarikanBarangReportContract;

        queryContracts = contract.parmQueryContracts();
        mapEnum = queryContracts.getEnumerator();
        while(mapEnum.moveNext())
        {
            query = mapEnum.currentValue();
            range = SysQuery::findOrCreateRange(query.dataSourceTable(tableNum(TECTradeInTable)),fieldNum(TECTradeInTable, TECTradeInCode));
            range.value(queryValue(tradeInCode));
        }

        reportRunController.runReport();

    }

}

Modify Query on Report DP Class example

[SysEntryPointAttribute]
public void processReport()
{
    //query class
    QueryRun            qr;
    Query               q;
    QueryBuildDataSource qbds;
    //table object
    InventSum           inventSum;
    WMSLocation         wmsLocation;
    RetailStoreTable    retailStoreTable;
    TIDLocType          tidLocType;
    //
    str                 headerName;

    contract = this.parmDataContract() as TIDStoreDisplayReportContract;
    parmShowQty = contract.parmShowQty();
    parmFilterItem = contract.parmStoreDisplayFilterItem();

    q = this.parmQuery();

    q.dataSourceTable(tableNum(WMSLocation)).addRange(fieldNum(WMSLocation,InventLocationId)).value(TECComputerNameTable::find(xGlobal::computerName()).InventLocationId);
    switch(parmFilterItem)
    {
        case TIDStoreDisplayFilterItem::OnlyDisplay :
            q.dataSourceTable(tableNum(TIDLocType)).addRange(fieldNum(TIDLocType,TIDIsDisplay)).value(SysQuery::value(NoYes::Yes));
            headerName = "Only Display Item";
            break;
        case TIDStoreDisplayFilterItem::NoDisplay :
            qbds = q.dataSourceTable(tableNum(InventSum)).addDataSource(tableNum(TIDStoreHaveDisplayItem));
            qbds.addRange(fieldNum(TIDStoreHaveDisplayItem,InventLocationId)).value(TECComputerNameTable::find(xGlobal::computerName()).InventLocationId);
            qbds.joinMode(JoinMode::NoExistsJoin);
            qbds.relations(false);
            qbds.addLink(fieldNum(InventSum,ItemId),fieldNum(TIDStoreHaveDisplayItem,ItemId));
            headerName = "No Display Item";
            break;
        case TIDStoreDisplayFilterItem::NotInDisplay :
            q.dataSourceTable(tableNum(TIDLocType)).addRange(fieldNum(TIDLocType,TIDIsDisplay)).value(SysQuery::value(NoYes::No));
            headerName = "Not in Display Item";
            break;
        case TIDStoreDisplayFilterItem::HaveDisplay :
            qbds =  q.dataSourceTable(tableNum(InventSum)).addDataSource(tableNum(TIDStoreHaveDisplayItem));
            qbds.addRange(fieldNum(TIDStoreHaveDisplayItem,InventLocationId)).value(TECComputerNameTable::find(xGlobal::computerName()).InventLocationId);
            qbds.joinMode(JoinMode::ExistsJoin);
            qbds.relations(false);
            qbds.addLink(fieldNum(InventSum,ItemId),fieldNum(TIDStoreHaveDisplayItem,ItemId));
            headerName = "Have Display Item";
            break;
        default :
            headerName = "Store Display Item";
            break;
    }

    // only display > 0
    q.dataSourceTable(tableNum(InventSum)).addRange(fieldNum(InventSum,AvailPhysical)).value(queryValue(">0"));

    qr = new QueryRun(q);

    while(qr.next())
    {
        inventSum   = qr.get(tableNum(inventSum));
        wmsLocation = qr.get(tableNum(wmsLocation));
        tidLocType  = qr.get(tableNum(TIDLocType));

        tempTable.clear();
        tempTable.headerName        = headerName;
        tempTable.ItemId            = inventSum.ItemId;
        tempTable.EcoResDescription = TIDItemDisplayCustom::getItemDescription(inventSum.ItemId);
        tempTable.EcoResProductName = TIDItemDisplayCustom::getItemName(inventSum.ItemId);
        tempTable.NameAlias         = TIDItemDisplayCustom::getItemModel(inventSum.ItemId);
        tempTable.AvailPhysical     = inventSum.AvailPhysical;
        select firstOnly retailStoreTable where retailStoreTable.inventLocation == wmsLocation.inventLocationId;
        tempTable.Store             = retailStoreTable.StoreNumber;
        tempTable.WMSLocationId     = wmsLocation.wMSLocationId;
        tempTable.IsDisplay         = tidLocType.TIDIsDisplay;
        tempTable.insert();

    }

}