Args

If the caller is activating the called object by a menu item the Args object is automatically initialized and sent as a parameter to the object called. AOT properties of the menu item will be used.

If the called object is a form or a report, it can automatically apply to the information send by the Args object. Forms will automatically try to make a delayed synchronize with the args.record().

Query -- Exists Join

Combine records from one table whenever a value exists in a common field in another table.

Display inventory items the providing vendor who has been blocked.
    InventTable inventTable;
    VendTable vendTable;
    ;

    ttsbegin;

    while select forupdate inventTable
     exists join vendTable
      where vendTable.AccountNum == inventTable.PrimaryVendorId &&
vendTable.Blocked == CustVendorBlocked::All
    {
        inventTable.PrimaryVendorId ="";
        inventTable.update();
    }
    ttscommit;

Display only those customers which have at least one sales order in sales table. 

Query                   query;

QueryBuildDatasource    datasource,SalesDataSource,CustomerDataSource;

QueryRun   queryrun;

CustTable custtable;

SalesTable salesTable; 

query = new Query();

CustomerDataSource  = query.addDataSource(tableNum(CustTable ));

SalesDataSource  = CustomerDataSource.addDataSource(tableNum(SalesTable ));

SalesDataSource.joinMode(JoinMode::ExistsJoin   );

SalesDataSource.relations(false);

SalesDataSource.addLink(fieldNum(CustTable, AccountNum),

fieldNum(SalesTable  , CustAccount ));

queryrun = new QueryRun(query);

while(queryRun.next())

{

custtable = queryRun.get(tablenum(CustTable ));

Info(custtable.AccountNum);

}

Query

The queryBuildRange object contains a limitation of the query on a single field.

The queryFilter object is used to filter the result set of an outer join. It filters the data at a later stage than 

the queryBuildRange object and filters the parent table based on the child table results.

The queryBuildDynaLink objects can only exist on the outer data source of a query. The objects contain information about a relation to an external record. When the query is run, this information is converted to additional entries in the "where" section of the SQL statement. The function is used by forms when two data sources are synchronized. The subordinate data source contains DynaLink(s) to the master data source. The function is used even if the two data sources are placed in two different forms, but are synchronized.

The queryBuildLink objects can only exist on inner data sources. The objects specify the relation between the two tables in the join.

2 Data Region with Query Datasource

Query Based Reporting with 2 tables

1.       Generate Query – Add CustTable first, and CustTrans to the datasource of CustTable

2.       Create a Visual Report Model Project, add a new dataset, in the dataset property, selected the query created.

3.       Create Design, drag and drop the dataset to the design,

4.       Create Group, first drag and drop the CustGroup field, then AccountNum, in the header node of AccountNum group, add currency, party to the row. This grouping is first grouped by CustGroup, then by AccountNum, in the AccountNum row, the report lists Currency, Party …

5.       Add a second data region, right click on the “TransActionDetail” design, add Pie.

6.       In the Pie – Data, add Field AmountMST, add CustGroup to the Series

7.       Deploy the report.

SSRS Report from Query

Query based Reporting

1.       Generate Query – Add table to the query, add fields

2.       Create a JOB to run the query to test if the data was displayed correctly

3.       Create a Visual Studio Project, select “Report Model” as project type

4.       Add a new dataset to the “Datasets”, set dataset property to Query

5.       Right click on the design, select auto-design. Fields are automatically created.

6.       Add a table to the auto-design (Dataset)

7.       Preview the report by right clicking on the auto-design – preview.

8.       To group the report by a certain field, drag and drop that field from the dataset to the “Groupings” under the table.

9.       Add Range, From AOT, drag and drop a field to the “Range”, refresh the dataset on the Visual Studio, the parameter Products_DynamicParameter adds the select button to the report dialog.

10.   Deploy the report, in AOT, right click the report “Deploy Element”.

11.   Create a menu for the report, create a menu item in the output folder. The menu item links to the newly created report.

Find top layer objects

static void Job13(Args _args)
{

    #AOT
    #File
    TextIo textIo;
    TreeNode treeNodeTables = TreeNode::findNode(#ClassesPath);
    TreeNode treeNode;
    int myCount, valueID;
    TreeNode treeNode2 = TreeNode::findNode(#TablesPath + '\\' + tableStr(Address));
    UtilEntryLevel layer;
    EnumId  enumId;
    DictEnum dictEnum;
    ;
 
    textIo = new TextIo(@"C:\temp\textlayerchanged_classes.txt", #IO_WRITE);
 
    treeNode = treeNodeTables.AOTfirstChild();
    while (treeNode != null)
    {
        layer = treeNode.applObjectLayer();
        valueID  = enum2int(layer);
        if( valueID ==  10)
        {
//            info(treeNode.AOTname());
            textIo.write(treeNode.AOTname());
        }
        treeNode = treeNode.AOTnextSibling();
    }

}

To find a property from the object

static void findAOTObjectByProperty(Args _args)
{
#AOT
TreeNode treeNodeTables = TreeNode::findNode(#TablesPath);
TreeNode treeNode;
str strPropertyName = 'SaveDataPerCompany';
str strPropertyValue = 'No';
;
// first table
treeNode = treeNodeTables.AOTfirstChild();
while (treeNode != null)
{
if (treeNode.AOTgetProperty(strPropertyName)== strPropertyValue)
{
info(treeNode.AOTname());
}
// next table
treeNode = treeNode.AOTnextSibling();
}
}

PowerPivot Data Source for Excel, Power View

Data in Dynamics AX can be brought into Excel/PowerPivot in the SharePoint, so that the end user can open Excel to view the AX data and use PowerPivot to create report.

First needs to create PowerPivot Gallary once created the site collection out of template "PowerPivot Site".

From the start menu, open an excel, make sure the powerpivot has been installed on your local Excel. Then use the Excel to connect to the Dynamics AX SQL Database to retrieve the data, save the Excel data sheet into the SharePoint.

Now the data sheet in SharePoint is connected to the data in the AX. From there, you can view AX data from Excel.