Wednesday, August 22, 2018

Drill Down Reports in AX 2012





Drill Down Reports:
Here we are going to do Drill down reports in ssrs - AX 2012
1.Create new query in AOT Named as Custtransaction.
2.Add CustTrans as datasource.
3.Set Dynamic  = yes in field property.
4.Open Visual studio 2010
5.Create new report > In property change the name to CustTrans
6.Add new dataset with below properties
 Datasource = Dynamics AX
 Datasource Type = Query   
        In query property select the query (CustList)
7.Create Precision Design.
8.Select Table in report tools.
9.Select Voucher,Transdate,Invoiceid,Amount currency

Now we need to group the data based on the account number.

Follow the below steps:

Adding Group



Select the row which we want to hide and show > Right click > Row visibility.


Select hide button to hide the row details.

Now we can expand and we can see the Customer details.


Same way we can group coloumn wise.

Tuesday, August 14, 2018

AOT and X++ queries and ranges in AX 2012


Let's say we have a query in AOT \Queries\ProjTable














And now we want to add more datasources into this query's parent datasource (ProjTable) and some ranges etc.

Let's assume we have a class (SRS report data provider class or a dialog class) which is using above query and during processing in this class we want to add more datasources and so on.

I created a SRS report data provider class for this example;

Class declaration

[
    SRSReportQueryAttribute (querystr(MarginAnalysisReportV2Sw)),
    SRSReportParameterAttribute(classstr(MarginAnalysisReportContractV2Sw))
]
public class MarginAnalysisReportDPV2Sw extends SRSReportDataProviderBase
{
    Query                       query;
    TempTable               tempTable;
    RecordInsertList       recordInsertListTmpTable;
    ProjTable                  projTable;
    TransDate                 AsOfDate;

}

ProcessReport
[SysEntryPointAttribute]
public void processReport()
{
    // Query variable declaration
    QueryRun                            queryRun;

    // Contract class declaration
    MarginAnalysisReportContractV2Sw    dataContract;

    // Contract class parameters
    ProjDateCriteriaSw                  dateType;

    // Views declaration
    ProjTableDateViewSw                 projTableDateViewSw;

    // This will retrieve the above ProjTable query
    // Select * from ProjTable
    query = this.parmQuery();

    dataContract  = this.parmDataContract();
    dateType        = dataContract.parmDateType();
    asOfDate        = dataContract.parmAsOfDate();

    this.addParameterRanges();

    queryRun = new QueryRun(query);

    while(queryRun.next())
    {
        projTable = queryRun.get(tablenum(ProjTable));
        this.insertTmpTable(ProjTable);
    }
}

addParameterRanges() Method
private void addParameterRanges()
{
    QueryBuildDataSource    qbds1;
    QueryBuildRange            qbr1;

// add ProjTableDate table into query's parent datasource projtable
qbds1= query.dataSourceTable(tableNum(projTable)).addDataSource(tableNum(ProjTableDate));
// defining relation based on ProjId
qbds1.addLink(fieldNum(ProjTable, ProjId), fieldNum(ProjTableDate, ProjId));
// defining join mode
qbds1.joinMode(JoinMode::ExistsJoin);
// adding range on ActualEndDate field of ProjTableDate table
// setting value <= asOfDate which is considered as per date
qbds1.addRange(fieldNum(ProjTableDate,ActualEndDate)).value(queryRange(dateNull(), asOfDate));
}

Resultant X++ Query
SELECT * FROM ProjTable(ProjTable) 
EXISTS JOIN * FROM ProjTableDateViewSw(ProjTableDateViewSw_1) 
WHERE ProjTable.ProjId = ProjTableDateViewSw.ProjId AND 

      ((ActualEndDate<={ts '2015-05-14 00:00:00.000'}))


Adding OR and AND in query
QueryBuildRange                 qbr;
qbr = query.dataSourceTable(tableNum(Table)).addRange(fieldNum(Table, TransDate));
qbr.value(strFmt('((%1 != %2) || (%3 == %4))',
                      fieldStr(Table, ModelId),
                      queryvalue("Latest"),
                      fieldStr(Table, TransDate),
                      asOfDate
                      ));

qbr.value(strFmt('((%1 != %2) && (%3 == %4))',
                      fieldStr(Table, ModelId),
                      queryvalue("Latest"),
                      fieldStr(Table, TransDate),
                      asOfDate
                      ));
Using enum value in query as a range
qbr.value(strFmt('(Status == %1)', any2int(ProjStatus::active)));

Check modifiedDate value of the table
qbr.value(strFmt('(ModifiedDate > ProjTable.ModifiedDate)'));

Using wildcards in query
You can use LIKE keyword for wildcards in ranges
qbr.value(strFmt('(ProjName LIKE "*Builder*")'));

Creating a Custom Inbound AIF Service in Microsoft Dynamics AX 2012



Overview

Application Integration Framework (AIF) services are used to communicate with external systems or applications. Microsoft Dynamics AX 2012 has the following types of services:
  1. Document services
  2. Custom services
  3. System services
An AIF service can be Inbound or Outbound. An Inbound service is used when you want to send data to an external system and Outbound services are used when you want to retrieve data. This tutorial will guide you in creating Custom Inbound AIF services in Microsoft Dynamics AX 2012. Custom services are used when:
  1. The complexity of the entities is relatively low.
  2. When you want to have full control of the service contract.
  3. When data contracts needs to be shared with different entities.

Pre-requisites

  1. Microsoft Dynamics AX 2012
  2. AIF services must be installed and configured on IIS

Important Concepts

  1. Service operation

    Service operations are class methods that expose any business logic in a service. To define a service operation, add the SysEntryPointAttribute attribute at the top of a method. Any existing method can be converted to a service operation by adding this attribute at the beginning of the method.
    &absp;
    Note:
    The service operation name cannot include Create, Find, Update or Delete. These names are reserved to be used by AIF Document services. AX will make an exception when they are called from a client.
  2. SysEntryPointAttribute

    SysEntryPointAttribute defines the authorization checks that will be performed when a method is called from the server. This attribute must be set on all the service operations. If the value is “true”, it means that authorization checks will be performed for all the tables accessed in the method and if set to “false”, no authorization checks will be performed.
  3. AifCollectionTypeAttribute

    AifCollectionTypeAttribute is used when you want to use a collection as a return type or in a parameter of a service operation. This attribute defines the type of data a collection contains.

Scenario

As part of this tutorial the service will return the list of customer names in Dynamics AX.

Steps

  1. First we will create a new class which will contain Service operations.

  2. Open AOT. Go to Classes and create a new class. Let’s name it CustomerServiceDemo.

  3. Set the RunOn property of the class to Server. This will make sure the class always executes on the server.
  4.  
     
  5. Add a new method and name it as getCustomerNameList. This method will query customers’ names and return a Type list string. Write the following code in the method:
  6. [SysEntryPointAttribute(true),
    AifCollectionTypeAttribute('return', Types::String)]
    //return denotes the parameter type and String specifies the type of data the collection will hold
    public List getCustomerNameList()
    {      
        CustTable       custTable;    
        List list = new List(Types::String);    
        
        //select all customers
        while select * from custTable        
        {
            //add customer name in the list
            list.addEnd(custTable.name());
        }
        
        return list;
    }
  7. The SysEntryPointAttribute tells AX that this method is a service operation method.

  8. Now we will create a new service and add the above created service operation to it.

  9. Go to Services, right click and select New Service.

  10. Name it as CustomerServiceDemo.

  11. Open the properties of the newly created service and select the CustomerServiceDemo class in the Class field.

  12. Now expand the CustomerServiceDemo service node and add a new Operation by right clicking on Operations and selecting Add Operation.

  13. All service operations present in the class will be listed. Select the getCustomerNameList service operation by checking the Add field in the grid and press OK.

  14. The next step is to create a service group and deploy the service to the Inbound Port.

  15. Go to Service Groups, right click and select New Service group.

  16. Name it as CustomerServiceDemoGroup.

  17. Set the AutoDeploy to Yes (Service will start automatically when AOS is started) and set the Description as Customer name service.

  18. Right click the newly created service group and select New Service Node Reference.

  19. In the newly created service node, set the Service property to CustomerServiceDemo. The Name property will automatically default to the Service name.

  20. Now right click the service group and select Deploy Service Group.

  21. A success message will appear if the service group is successfully deployed.

  22. To verify it, go to System administration à Setup à Services and Application Integration Framework à Inbound ports.

  23. The Service group name CustomerServiceDemoGroup will appear here as Port name with a green check mark. This shows that service group is deployed and active. If a red ‘x’ is appearing, select Activate from the action pane to activate the port.

  24. The WSDL URI is the URL of the service which can be used by external systems to access the service.

  25. To test whether the service is running properly, open the Visual Studio Command Prompt by going to All Programs à Microsoft Visual Studio 2010 à Visual Studio Tools à Visual Studio Command Prompt.

  26. Write wcftestclient and press enter. WCF Test Client will open.

  27. Now, go to File àAdd Service.

  28. In the Add Service dialog box, paste the WSDL URI of the port and press OK.

  29. The WCF Test Client will open the service with the list of operations available.

  30. Double click the getCustomerNameList. It will open the operation details in the right hand side pane.

  31. Click the Invoke button. The result of the service will appear in the Response pane.

  32. Pat yourself on the back. You are finished!

Virtual Company & Table Collections in Dynamics Ax




This article describes how Dynamics Ax Virtual Company and Table Collection work. We will also discuss how to move data from normal company to virtual company when you introduce virtual company in existing Ax implementation.

Virtual Company:

Dynamics Ax stores data as per company in tables. But there might be occasions when you want to share data across companies, like country, state, zip codes data. This sharing of data is achieved by creating a virtual company and storing data in this virtual company. Normal companies are then configured to read/write data from this virtual company. The only purpose of virtual company is to share data across companies, you cannot log into this virtual company.

Before seeing how to do virtual company setup, I would like you to show another trick that can be used to share data across Ax. There is a property on Ax tables called "SaveDataPerCompany", you can use this property to save data globally in Ax. To share data set this property to "No".


Note: This data is shared by all the companies in Ax. This option will delete DataAreaId field and default (DataAreaId, RecId) index from the table. If you want more control on shared data, like which companies can share and which can not then use virtual company. 

Virtual Company setup:

Step 1: Create Table Collection

Decide which tables you want to share and create a table collection for these functionally related tables. For example; if you want to share Global Address Book across companies then you can utilize the existing table collection "DirPartyCollection".

To create a table collection, go to AOT\Data Dictionary\Table Collections and on right click select "New Table Collection", then just drag your required tables in this collection.

Step 2: Create Virtual Company, configure/attach normal companies and table collection

Create a virtual company that will hold the shared data for normal companies.
Note: Before doing the below steps, make sure you are the Ax administrator and the only user online.
  1. Go to Administration -- Setup -- Virtual company accounts, and create a virtual company.
  2. Decide which companies needs to share data and attach those normal companies with this virtual company.
  3. Attach the table collection with this virtual company.

Your Ax client will re-start and you are done with setting up the virtual company account.

Now, when you have virtual company in place, all new data will be saved in this virtual company. Only companies attached to the virtual company can use this shared data. All other companies which are not attached will work normally, these companies will continue to read/write data as per company bases.

How to move existing data to virtual company?
When you setup a new virtual company, Ax does not move data automatically from normal company to virtual company. This is done by system administrator manually.

There are many ways to do this data move, but I will discuss only two approaches here.

Ax Import / Export:
This is standard Ax approach.

  1. Manually export existing normal company data from Ax.
  2. Remove duplicate records from this exported data set. 
  3. Delete exported data from normal companies.
  4. Import the exported data back in Ax, while logged into one of the participating companies. 
  5. Create records deleted in point 2 again in Ax using your logic. How you want to handle duplicate? For example, if you have customer 'Rah' in more than one normal company, what you want to do with this?
Direct SQL:
Use this approach if you have good knowledge about SQL queries and Ax table structures/relationships. Below are few points that will help you understand what to do and how to do.

  • All Ax tables store data as per company unless otherwise specified. For this, Ax uses a special field called DataAreaId. In case of virtual company, it does not matter from which normal company you log-in, it is always the virtual company id which is stored in DataAreaId field of shared tables. 
  • Ax also assigns a unique 64bit number to each record in table. For this, Ax uses a special field called RecId. This RecId is unique in the table and is generated by Ax when you insert a new record in Ax. It is not related to DataAreaId / Company.
  • For unique records between all participating normal companies, update the DataAreaId to the virtual company id.
  • For duplicate records, create them again in Ax using some Ax job or Ax import/export technique.