Create a Power BI Dashboard for Dynamics AX 2012 Sales

This is an update to the previous published articles on Data Visualization, OData Feeds, Power Map, Power Pivot in Office 2013 and Power Pivot in Office 2010. It shows how to use Power BI for Desktop to create a Sales Dashboard for Dynamics AX 2012 (R2).

Power BI Dashboard

Get Data

Start Power BI for Desktop and start with an empty report. From the ribbon on top click “Get Data”, choose SQ Server and provide your server and database. In this example I’m using a single server installation. However, in a production environment you might need to provide <SERVERNAME> \ <INSTANCENAME> , <PORT> e.g. SRVSQL\PROD,2303.

Get data into Power BI

In the next step you have to provide credentials. In my case I’m allowed to access the server with my domain account. In a production environment it is recommended to create a separate Login which is only used for BI Purpose. Don’t get confused if you get a warning that your SQL does not support encryption. If the connection was established successfully, the data wizard presents you a list of tables. Select the following tables:

  • CustInvoiceJour
  • CustInvoiceTrans
  • CustTable
  • InventTable
  • LogisticsPostalAddress

Select tables for Power BI

Click Load, and choose “Import” to load the data in Power BI for Desktop.

Transform Data

In Power BI for Desktop, at the Ribbon click “Edit Queries”. This will open the query editor. We don’t need all columns for this Demo. For each table click the “Choose Columns” button and the select only the following columns:

Choose columns for Power BI

CustInvoiceJour CustInvoiceTrans CustTable
  • InvoiceAccount
  • InvoiceDate
  • InvoiceId
  • NumberSequenceGroup
  • SalesId
  • InvoicePostalAddress
  • DataAreaId
  • InvoiceId
  • InvoiceDate
  • NumberSequenceGroup
  • ItemId
  • LineAmountMST
  • SumLineDiscMST
  • DataAreaId
  • AccountNum
  • CustGroup
  • DataAreaId
InventTable LogisticsPostalAddress
  • ItemId
  • ItemType
  • DataAreaId
  • Address
  • CountryRegionId
  • ZipCode
  • City
  • RecId

Click “Close & Apply” to finish this task.

Choose columns for Power BI

 

Create Relations

In Power BI for Desktop switch to the Data View (with the table symbol on the left pane). It is required to create primary keys and foreign keys before linking the tables. From the list of tables (on the right) select the CustTable and at the Ribbon click “New Column”. Type the following definition:

PK_Cust = CUSTTABLE[ACCOUNTNUM] & “-” & CUSTTABLE[DATAAREAID]

Create Foreign Keys in Power BI

This will create a new column with a customer account which is unique for all company accounts. Repeat this step for the following tables and columns:

InventTable:

PK_Item = INVENTTABLE[ITEMID] & “-” & INVENTTABLE[DATAAREAID]

CustTable:

PK_Cust = CUSTTABLE[ACCOUNTNUM] & “-” & CUSTTABLE[DATAAREAID]

CustInvoiceJour:

FK_Cust = CUSTINVOICEJOUR[INVOICEACCOUNT] & “-” & CUSTINVOICEJOUR[DATAAREAID]

PK_Invoice = CUSTINVOICEJOUR[INVOICEID] & “-” & CUSTINVOICEJOUR[SALESID] & “-” & CUSTINVOICEJOUR[NUMBERSEQUENCEGROUP] & “-” & CUSTINVOICEJOUR[DATAAREAID]

CustInvoiceTrans:

FK_Item = CUSTINVOICETRANS[ITEMID] & “-” & CUSTINVOICETRANS[DATAAREAID]

FK_Invoice = CUSTINVOICETRANS[INVOICEID] & “-” & CUSTINVOICETRANS[SALESID] & “-” & CUSTINVOICETRANS[NUMBERSEQUENCEGROUP] & “-” & CUSTINVOICETRANS[DATAAREAID]

Save, and open the relations by clicking on the relations item in the navigation pane on the left. You can drag&drop columns from one table to another table to create relations. Link the following columns:

  • CustInvoiceTrans,FK_Item > InventTable.PK_Item
  • CustInvoiceTrans.FK_Invoice > CustInvoiceJour.PK_Invoice
  • CustInvoiceJour.FK_Cust > CustTable.PK_Cust
  • CustInvoiceJour.InvoicePostalAddress > LogisticsPostalAddress.RecId

Your data model should look like this:

Create relations in Power BI data model

Name the ItemType

In the data view, select the InventTable. From the ribbon create a new column and name it “TypeName”. Add the following code to translate the Enum based ItemType Integer Value to a meaningful name.

TypeName = IF(INVENTTABLE[ITEMTYPE] = 0; “Item”; IF(INVENTTABLE[ITEMTYPE] = 2; “Service”; “Not an Item”))

The InventTable should look like this:

Name item type column

Create a Discount measure

Next we will create a measure which calculates the given discounts as the percentage of the total price. Open the data view using the second button on the left navigation pane. Select the CustInvoiceTrans. From the ribbon, click “New Measure” button in the “Modelling” tab. Provide the following code:

M_DiscPerc = sum(CUSTINVOICETRANS[SUMLINEDISCMST]) * 100 / ( sum(CUSTINVOICETRANS[LINEAMOUNTMST]) + sum(CUSTINVOICETRANS[SUMLINEDISCMST]))

For example:

Qty = 1 € , Unit Price = 1000 €     –> Price = 1000 €
Discount = 100 €                    –> Price = 900
Discount Percentage 3 %             –> Price = 873 €

CustInvoiceTrans.LineAmountMST = 873
CustInvoiceTrans.Discount = 100
CustInvoiceTrans.LinePercent = 3.0
CustInvoiceTrans.SumLineDiscMST = 127

M_DiscPerc = 127 * 100 / (873 + 127) = 12,7

Visualize

Switch to the empty report view using the first button on the left navigation pane. From the Visualization toolbox click the “Card”. This will place an empty card on the report. Drag&Drop the LineAmountMst from the CustInvoiceTrans on the empty card. It should look like this:

Power BI card chart

Next, place a map from the toolbox on the report. Drag&Drop the fields CountryRegionId, City and ZipCode from the LogisticsPostalAddress table on the Location. Drag&Drop the LineAmountMST from the CustInvoiceTrans on the Values Field. The map should look like this:

Power BI map chart

Add a new Gauge to the report and use the Measure M_DiscPerc as value. You cannot set a hardcoded Min. and Max. value in the data properties. Switch to the Format view using the pencil icon.  In the group Gauge Axis, set the Min. Value 0, the target value to 3 and Max. Value 100. Depending on your data, the gauge may look like this:

Power BI gauge control

Next a donut chart to visualize the revenue per item type. Drag&drop the LineAmountMST from the CustInvoiceJour on the value property of the donut chart and drag the TypeName from the InventTable.

Power BI donut chart

Place a column chart on the report to visualize the revenue per customer group. Place the LineAmountMST in the Value field. Use the CustGroup from the CustTable as Axis. Change the sort order to LineAmountMST by using the […] Dropdown Menu in the upper right corner of the chart.

Power BI column chart

Finally, add a line chart on the report to visualize the revenue per year. Place the LineAmountMST from the CustInvoiceTrans on the charts value field and put the InvoiceDate from the CustInvoiceTrans on the Axis field.

Power BI line chart

Fine-tuning

Give each chart a meaningful name. Change the size for the text to fit your report style. Switch to the data view. Change the column names into something more meaningful for an end user e.g. LineAmountMST to Amount. Change the columns formats e.g. Currency for LineAmountMST, date format for the InvoiceDate.

Lookup Financial Dimension in AX 2012

Financial Dimensions have changed in AX 2012. In contrast to earlier versions of Dynamics AX, financial dimensions now are somehow similar to inventory dimensions where a certain combination of dimension values are stored as dimension set. I faced a requirement that an external application (SharePoint based DMS) sends a certain combination of financial dimensions and AX has to assign this combination to a certain record. Here is a (simplified) code how to check the existence of such a dimension.

// Example:
// Business Unit 005 Electronics
// Department 025 IT
// ItemGroup Services

//Step 1: Find Dimension Attributes

DimensionAttribute  attributeBU = DimensionAttribute::findByLocalizedName("BusinessUnit");
DimensionAttribute  attributeDP = DimensionAttribute::findByLocalizedName("Department");
DimensionAttribute  attributeIG = DimensionAttribute::findByLocalizedName("ItemGroup");

//Step 2: Find Dimension Attributes Values

DimensionAttributeValue valueBU = DimensionAttributeValue::findByDimensionAttributeAndValue(attributeBU,"005");
DimensionAttributeValue valueDP = DimensionAttributeValue::findByDimensionAttributeAndValue(attributeDP,"025");
DimensionAttributeValue valueIG = DimensionAttributeValue::findByDimensionAttributeAndValue(attributeIG,"Services");

//Step 3: Create an Array of Hash Keys from the Attribute Values 

DimensionSHA1Hash hashKey;
DimensionAttributeValueSet valueSet;

HashKey dimAttrHashKeyArray[];
dimAttrHashKeyArray[1] = valueBU.HashKey;
dimAttrHashKeyArray[2] = valueDP.HashKey;
dimAttrHashKeyArray[2] = valueIG.HashKey;

hashKey = DimensionAttributeValueSetStorage::getHashFromArray(dimAttrHashKeyArray,3);

//Step 4: Lookup Hash Key

valueSet = DimensionAttributeValueSet::findByHash(hashKey);

if(valueSet.RecId != 0)
{
    info("Dimension found!");
}

Publish Dynamics Ax documents to SharePoint

A typical requirement is to publish documents like Invoice, Purchase Order, etc. to an DMS system like SharePoint. However, in Dynamics AX 2012 and lower there is no built-in functionality to publish these documents to SharePoint. But this requirement can easily be achieved without coding.

Prerequisite

  1. Install (at least) SharePoint 2013 Foundation incl. SP1. Foundation 2013 SP1 includes all the filters required to process PDF files and a basic search center. By default the configuration wizard creates a new team website collection.
  2. Make sure the WebClient window service is installed and running. If not, you may have to install the User Interface and Desktop Experience Feature on your server.
    WebClient Windows Service

Document Library and Content Type

In SharePoint  open the Settings image and open the Site Settings. From the User and Permissions group, open “People and Groups”. Select the Group “Owners”, Click “New” and add the Dynamics AX AOS Service account.

Provide AOS user access to SharePoint document library

In SharePoint create a new Document Library. On the team website open Settings image (right, on top) > Add an app > Name the document library “Invoice”.

Go to the newly created document library. Open the Library ribbon, and edit library with SharePoint Designer. SharePoint Designer is not installed by default, and you may have to download and install it first.

Open with SharePoint Designer

After SharePoint Designer was installed, click again on “Edit Library” with SharePoint Designer. Within SharePoint designer, select Content Types and create a new Content Type. Call it “Invoice” and make sure to select the parent content type form “Document Content Types” and parent content type “Document”. Save your modifications.

New content type for Invoice

In the navigation, select Lists and Libraries and open the Invoices library. Make sure the option “Allow management of content types” is enabled. At the “Content Types” group at the bottom, open “Add” and select your newly created Invoice content type. This will add the content type to the document library.

Add Invoice content type to library's managed content types

Select the Invoice content type at the content types group. At the ribbon select “Set as default”. Now every new document uploaded to the library will be an Invoice type. Save your modifications and go back go SharePoint.

Set invoice as default content type

At the Invoices document library in SharePoint, open the Library Ribbon and choose “Open in Explorer”. This will open a new instance of windows explorer, showing the content of the document library.

Open Library with Windows Explorer

Click in the address bar and copy the address.

image

 

Post and Publish Sales Invoice

Open Dynamics AX 2012 and go to “Accounts Receivable” > Periodic > Sales Update > Invoice. Change the Quantity to Packing Slip. Make sure the options “Posting”, “Late Selection” and “Post Invoice” are enabled.

Post Sales Invoice

Open “Select” and make sure the selection is configured to identify all Delivered sales orders. Click OK and save the selection.

Select only delivered sales orders

Open Printer Setup > Invoice. Select File as Target and choose File Format PDF. Open the File Name Dialog and provide the Document Library Address as Target. Repeat this step for the Invoice (Copy).

Print to SharePoint

Back at the Posting Invoice Dialog, open Batch processing (at the bottom). Enable batch processing and modify the recurrence to “No End” and repeat all 5 minutes.

Batch processing

 

Test

In Dynamics AX create one or more new sales order and post the packing slip. Wait until the Post Invoice batch job runs. The sales orders should be invoiced. Go to SharePoint and open the Invoices document library. There you should see the uploaded PDFs.

Dynamics Ax Invoice Document Library

Open the SharePoint Central Administration and go to Application > Manage service application > Search Service Application.

SharPoint Search Service

Select Content Sources and start the crawl manually. This will force SharePoint to index the PDF files in the Invoices document library. By default the crawler indices the documents on SharePoint periodically without manual interaction.

Manually start crawl

Open SharePoint Team Website. In the Search Box, type the Name of Customer. SharePoint will present a list of found documents related to the customer.

Find Dynamics AX documents on SharePoint

 

(Optional) Confirm and Publish Purchase Orders to SharePoint

Create a new document library with an purchase order content type as default:

Go to SharePoint, open the settings image menu and choose to add an app. Select document library and name it Purchases. Navigate to the new document library, and from the Library Ribbon, select “Open with SharePoint Designer”. In SharePoint designer create a new content type, based on a document content type, and name it Purchase. In SharePoint Designer navigate to the libraries, select the newly created Purchases library and make sure the option “Allow Management of Content Types” is enabled. Below, at the content types group add the Purchase content type you’ve created. Finally select the content type, and from the ribbon in SharePoint designer select “Set as Default”.

In Dynamics AX go to the Procurement and Sourcing module > Settings > Procurement and Sourcing Parameters > and activate Change Management. This option is required to run the purchase order approval workflow.

Activate change management

Next go to Procurement and Sourcing > Settings > Procurement and Sourcing Workflows. Create a new workflow based on the Purchase Order Workflow template. In the Workflow Configuration, add the Approve Purchase Order task to the workflow, between start and end.

Configure Purchase Order Workflow

Provide meaningful texts for the Workflow Submission instruction. Double click on the Approve Purchase Order taks to open the details. There assign Step 1 to a user or a user group. For testing purpose, you may assign the approval to yourself. Save and activate the workflow configuration.

Configure Purchase Order Workflow

Create a new purchase order. The purchase order starts in the approval state Draft. Submit it to the workflow for approval. This will change the state to “In Review”. Go back to the Purchase Order List Page and refresh the data. It may take a few minutes, depending on your workflow batch jobs configuration, until the purchase order is assigned to you for approval. When the purchase order is assigned for approval, approve the purchase and wait a few minutes until the purchase order has reached the state approved.

Purchase Order Approval State

Go to Procurement and Sourcing > Periodic > Purchase orders > Confirmation. Make Sure the Options Posting, Late Selection and Print purchase orders are active. From the Select button on the right, make sure only Approved and Open purchase orders will be selected.

Confirm Purchase Order

Open the Printer Setup > Purchase Order > Choose print to file as PDF and provide the path to the Purchases document library on your SharePoint. Repeat this for Printer Setup > Purchase Order (copy).

Print to SharePoint

Finally, open the Batch dialog  using the button on the lower right. Make sure to activate batch processing and set the recurrence to no end and recurring pattern to 10 minutes.

Batch processing

After a few minutes the purchase order state has change from Approved to Confirmed, and you should see the generated PDFs on SharePoint. These documents will be included within the next search crawl and can be found using the search box at your SharePoint website.

Purchase order confirmation

(Optional) Enhance Search Experience

Open SharePoint Central Administration and click the group Application Management. Go to “Create Site Collection” . Make sure your are using the same Web Application as the Team Website. Call the new site collection “Search” and provide an URL including like http://YOUR_SERVER/site/search. Make sure to select the “Basic Search Center” template from the Enterprise template group. Click OK, this will create a new basic search center.

Create Basic Search Center

Open the URL you have provided in a web browser. You should see an almost empty web page with a search box.

SharePoint Basic Search Center

In SharePoint Central Administration go to “Application Management” > “Manage Service Applications” > “Search Service Application”. Provide the Search Center URL at the property “Global Search Center URL”. This will instruct SharePoint to redirect all search to the Search Center.

Global Search Center URL

Open the Search Center URL in a browser and start a search e.g. for a Customer. This will open the result page. On the top right open the settings  image and click on “Edit page”. This will switch the page to edit mode. On the left side, click the drop down of the refiner web part and click “Edit Web Part”. This will open the Edit Dialog on the right.

Customize Search Center Refiner

Click on “Choose Refiners” and make sure SPContentType, File Type and Last Modified Time are selected. Remove all the other refiners from the select. Click OK in the dialog, Apply in the Web Part Configuration Dialog on the right and Stop Edition in the ribbon on the top left.

SharePoint Search Refiner

When you use the search center the next time, the result page will allow you to refine by Content Type like Invoice, Purchase Order, etc., File Type like PDF, Word, etc., and Date.

Dynamics AX Document Search

Ax 2012 Workflow Exception: Token StartElement in state Epilog would result in an invalid XML document.

At work we developed a quite simple Sales Order Approval workflow for a customer running Dynamics Ax 2012 R2. The workflow artifacts compiled without error and the workflow was configured. However, every time a sales order was submitted to the workflow we were facing an error:

Ax 2012 Workflow Error

A detailed look at the artifacts in the AOT reviled the problem. The Workflow Document class referred to a Query.

Workflow Document Class

The query had a SalesTable Datasource, but the SalesLine was not added as Sub Datasource within the SalesTable. Therefor the system could not generate a valid XML document.

Query with 2 data sources

We moved the SalesLine Datasource to the Sub Datasources of the SalesTable. Finally we compiled the project in X++ and IL and restarted the AOS.

Corrected workflow query

Finally the workflow completed successfully

Dynamics Ax 2012 Workflow completed

How to create a Word App for Address Lookup in Dynamics Ax

Office Apps provide additional functionality within an Office document like Excel and Word. This is an example how to create an App for Word 2013 which can be used to lookup an Address of a customer in Dynamics Ax.

Dynamics Ax Word App

 

Architecture

Word App Architecture

  1. Dynamics Ax will provide a web service which takes the (partial) name of a customer as input parameter, and returns a list of matching customers and their addresses.
  2. However, apps prefer a more lightweight form of communication.Therefore another web service will provide a simple JSON interface.
  3. Finally the Word App will be used to select a partial text within the document eg.”Contoso”, send it to AX and present the result of matching Customer / Addresses.

Dynamics Ax Service

Create a new data contract class and call it ERPCustomer. These will be the result objects for a customer lookup.

[DataContractAttribute]
class ERPCustomer
{
    CustName    custName;
    LogisticsAddressing address;
}

[DataMemberAttribute]
public LogisticsAddressing parmAddress(LogisticsAddressing _address = 
                                                              address)
{
    address = _address;

    return address;
}

[DataMemberAttribute]
public CustName parmCustName(CustName _custName = custName)
{
    custName = _custName;

    return custName;
}

Create a second class for the lookup logic and call it ERPAppHelper and add this static method:

public static List lookup(Name _name)
{
    CustTable custTable;
    DirPartyTable dirParty;
    List customers = new List(Types::Class);
    ERPCustomer customer;

    _name = strFmt("*%1*",_name);

    while select dirParty
        exists join custTable
        where dirParty.Name like _name && dirParty.RecId == custTable.Party
    {
        customer = new ERPCustomer();
        customer.parmAddress(dirParty.primaryAddress());
        customer.parmCustName(dirParty.Name);

        customers.addEnd(customer);
    }

    return customers;
}

Create a third class for the Lookup Service and call it ERPAppService

[SysEntryPointAttribute(true),
AifCollectionTypeAttribute(‘return’, Types::Class, classStr(ERPCustomer))]
public List lookup(Name _name)
{
    return ERPAppHelper::lookup(_name);
}

Create a new Service in the AOT, assign the ERPAppService class to the service and add the lookup method. Next create new privilege or use an existing one. Add the service lookup method to the privileges’ entry points. Right click on the service, from the context menu choose “Register”

Dynamics Ax App Service

Finally, to to System Administration > Setup > Services and AIF > Inbound Ports and create a new service. Use net.tcp port and add the lookup method as service operation.

Dynamics Ax App Service AIF

[Optional] Test the Dynamics Ax Service

Open Visual Studio, create a new Console Project and call it TestAifService. Add the Service reference (URI from the Screenshot above) to your project and call it ERPAppService. Call the lookup method and print the results. Use code below, and replace USR, PW, DMN with a valid User, Password, Domain combination in your environment.

using System;
using System.Collections.Generic;
using System.Linq;
using System.Text;
using System.Threading.Tasks;
using TestAifService.ERPAppService;

namespace TestAifService
{
    class Program
    {
        static void Main(string[] args)
        {
            var cred = new System.Net.NetworkCredential("USR","PW","DMN");
            var client = new ERPAppServiceClient();
            client.ClientCredentials.Windows.ClientCredential = cred;

            var context = new CallContext();
            context.MessageId = Guid.NewGuid().ToString();

            var result = client.lookup(context, "Contoso");
            foreach (var customer in result)
            {
                Console.WriteLine(customer.parmCustName);
                Console.WriteLine(customer.parmAddress);
            }
            Console.ReadKey();
        }
    }
}

If you are using the Contoso Demo Data, the result should look like this:

Test Dynamics Ax AIF Service

JSON Frontend Service

In order to host the JSON Frontend Service, you need to install the Application Server Role and HTTP Activation. You will also need the Webserver role installed, including Application Development with .NET and ASP.NET.

In Visual Studio 2013 create a new Website Project and choose a new WCF Service. Use .NET Version 4.5 and select the file system as host. Select a C:\inetpub\wwwroot\appservice as target directory. Name it ERPAppService. This will create a WCF service project in Visual Studio including a working Hello World Service. Add the service reference to the Dynamics Ax net.tcp service to your project and call it DynamicsAx. Your Visual Studio Project should look like this:

Add Dynamics Ax AIF Service as Service Reference

Next, modify the IService1.cs Interface:

namespace ERPAppService
{
    [ServiceContract]
    public interface IService1
    {
        [OperationContract]
        [WebInvoke(Method="GET",ResponseFormat=WebMessageFormat.Json,
            BodyStyle=WebMessageBodyStyle.Wrapped,
            UriTemplate="lookup/{name}")]
        ERPAppService.DynamicsAx.ERPCustomer[] lookup(string name);
    }
}

Go to the Service1.svc.cs object and implement the lookup method. Replace USR, PW and DOMAIN with a valid user to access Dynamics Ax.

namespace ERPAppService
{
    public class Service1 : IService1
    {

        public ERPAppService.DynamicsAx.ERPCustomer[] lookup(string name)
        {
            var cred = new System.Net.NetworkCredential("USR","PW","DOMAIN");
            var client = new DynamicsAx.ERPAppServiceClient();
            client.ClientCredentials.Windows.ClientCredential = cred;

            var context = new DynamicsAx.CallContext();
            context.MessageId = Guid.NewGuid().ToString();

            return client.lookup(context, name);
        }
    }
}

Open the web.config and go to the bindings element and add the webHttpBinding element. Within the webHttpBinding element add a binding element and set the security element to mode=Transport. This is required to host the service with SSL.

<bindings>
  <netTcpBinding>
    <binding name="NetTcpBinding_ERPAppService" />
  </netTcpBinding>
  <webHttpBinding>
    <!– SSL !!!  –>
    <binding>
      <security mode="Transport"></security>
    </binding>
  </webHttpBinding>
</bindings>

 

Save, Compile and publish the service to the local directory. In the IIS Manager, right click on the Default Website and from the context menu create a new application and name it AppService. Select as directory the directory where your service is deployed.

Host JSON App Service

Go to IIS Manager, select the server running IIS and from the feature pane open certificates. Create a new, self-signed SSL certificate. Next go to the Default Web Site, from the right pane open the Binding Dialog. Select the HTTPS:443 Binding and assign the newly created SSL certificate.

SSL Certificate for JSON App Service

Go to the AppService application and from the feature pane open SSL Settings. Set the “Requires SSL” active.

[Optional] Test the JSON Service

Open a web browser and navigate to the service address. In this example the server name is SRV-Demo within the Domain Demo.Local. Therefore the serivce URL is https://srv-demo.demo.local/AppService/Service1.svc. The result should look like this.

Test JSON App Service

Next call the service lookup method with parameter “Contoso”. The call should look like this https://srv-demo.demo.local/AppService/Service1.svc/lookup/Contoso. The result should like this:

Test JSON App Service

Develop a Word App

In order to create Office Apps, you need at least the professional edition. Community Edition and Express edition do not include the Office / SharePoint App Development projects:

Create new App Project in Visual Studio

Create a new Office App Project. In the wizard choose to develop a side pane app for Word. This will create a new project which already contains an app that can be used to process the selected text within word. The Home.js contains the getDataFromSelection function, which will be used to call the JSON Service.

JSON Call from Word App

Modify the code in the getDataFromSelection() method. Change the URL of the JSON Service to the URL of your service. Use HTTPS and the server name for which the SSL certificate was created. The following code will send the selected text to the JSON service, parse the result display the result as list of links within the word app. By clicking on the link, the text will be pasted into word.

function getDataFromSelection() {
       

Office.context.document.getSelectedDataAsync(Office.CoercionType.Text,
    function (result) {
        if (result.status === Office.AsyncResultStatus.Succeeded) {
            var lookupUrl = https://srv-demo.demo.local/AppService/
                            Service1.svc/lookup/
+ result.value;

            $.getJSON(lookupUrl, function (data) {
                var result = data.lookupResult;
                $.each(result, function (key, val) {
                    var custname = val.parmCustName;
                    var address = val.parmAddress;
                    address = address.replace(/\n/g, ",");

                    $(‘#result_list’).append("<h3>" + custname + "</h3>");
                    $(‘#result_list’).append("<a onclick=
           \"Office.context.document.setSelectedDataAsync(‘"+address+"’)\">"
                                              + address + "</a>");

                     });
                 });

             } else {
                app.showNotification(‘Fehler:’, result.error.message);
             }
        }
    );
}

 

Run and test your app Smiley

Update: Send SSRS Report as Email (with meaningful PDF File name)

A while ago I’ve described how to add a “Send E-Mail Button” to the Report Viewer in Dynamics AX 2012: https://erpcoder.wordpress.com/2013/01/07/send-ssrs-report-as-email/. In this initial script all reports were generated as Report.PDF file. However, a visitor recently asked how to generate a PDF file, named according to the documents ID, e.g. To create an Invoice PDF named as the Invoice Number in AX.

This can be achieved by reading the SSRS Report Parameters and create a meaningful document name based on this information.  For example, the SalesInvoice report holds a parameter RecordId, which is the RecId for the corresponding CustInvoiceJour record in Dynamics Ax.

Import the ReportViewer.WinForms DLL

The DLL Microsoft.ReportViewer.WinForms is required. The DLL can be found at C:\Program Files (x86)\Microsoft Visual Studio 12.0\ReportViewer . Add the DLL as Reference in the AOT.

Parameter Table

Create a table that contains 3 fields and stores the combination of Report Name, Report Design and Report Parameter. Create an index for the combination of Report Name and Report Design.

Report Mailing Parameter
A sample configuration for SalesInvoice looks like this: The Report Name is SalesInvoice, the Report Design is Report and the Parameter used to generate the document name is RecordId.
Report Mailing Parameter for SalesInvoice

find() and exist()

Add a find and exist method to the table.

public static ERPReportMailParameter find(SrsReportName _name,
SrsReportDesignName _design,
boolean _forupdate = false)
{
ERPReportMailParameter parm;
parm.selectForUpdate(_forupdate);
select firstOnly parm
where parm.ReportName == _name &&
parm.ReportDesignName == _design;
return parm;
}

 

public static boolean exist(SrsReportName _name,
SrsReportDesignName _design)
{
return ERPReportMailParameter::find(_name,_design).RecId > 0;
}

Business Logic

Create the following documentName* methods to generate the name of the PDF file. These methods are called by the SrsReportViewer Form to get a meaningful name for the PDF file in the mail.

public static str documentName(SRSReportName _name,
SRSReportDesignName _design,
Microsoft.Reporting.WinForms.LocalReport _report)
{
Microsoft.Reporting.WinForms.ReportParameterInfoCollection parms;
Microsoft.Reporting.WinForms.ReportParameterInfo reportParm;
System.Collections.IList list;
System.Object value;
str documentName = “Report”;    // default value
str parameterName = “”;
str parameterValue = “”;

   // Is a parameter configured
if(ERPReportMailParameter::exist(_name,_design))
{
parameterName = ERPReportMailParameter::find(_name,_design)
.ReportParameter;

      parms = _report.GetParameters();
reportParm = parms.get_Item(parameterName);
list = reportParm.get_Values();
value = list.get_Item(0);
parameterValue = value.ToString();

      // Call document specific logic to determine the document name
switch(_name)
{
case “SalesInvoice”:
documentName = ERPReportMailParameter::documentNameSalesInvoice
(parameterValue);
break;

         default:
break;
}
}
return documentName;
}

public static str documentNameSalesInvoice(str _parameterValue)
{
RecId recId = str2int64(_parameterValue);
return CustInvoiceJour::findRecId(recId).InvoiceId;
}

SrsReportViewer Form

At the SrsReportViewer Form, modify the init() method so the report parameters are always visible.

public void init()
{
boolean showReportViewerParameters;
    super();
    if(this.controller())
{
        // showReportViewerParameters = this.controller()
.parmShowReportViewerParameters();
        this.setFormCaption();
        // set form help context.
this.setFormHelpContext();
}
else
{
//showReportViewerParameters = false;
    }
    showReportViewerParameters = true;

Add a send button to the button group
image

Modify the clicked() method

void clicked()
{
Microsoft.Dynamics.AX.Frameworks.Controls.ReportViewer.AxReportViewer
axviewer;
Microsoft.Reporting.WinForms.ReportViewer nviewer;
Microsoft.Reporting.WinForms.LocalReport report;
ERPReportMailParameter mailParameter;
str parameterName;
str reportName;
str reportPath;
int dot;
int slash;
str documentName = “Report”;
SRSReportName srsReportName;
SRSReportDesignName srsReportDesignName;
System.Exception ex;
System.Array bytear;
System.Object no;
System.IO.File file;
str tmpPath;
System.IO.FileStream fs;
SmmOutlookEmail smmOutlookEmail = new SmmOutlookEmail();

   super();

   axviewer = AxReportViewer.control();
nviewer = axviewer.get_ReportViewerControl();

   try
{
report = nviewer.get_ServerReport();
reportPath = report.get_ReportPath();

 

      // cut the report server path
slash = strFind(reportPath,”/”,strLen(reportPath),
strLen(reportPath)*-1);
reportName = subStr(reportPath,slash+1,strLen(reportPath)-slash);

      // split name and design
dot = strFind(reportName,”.”,strLen(reportName),
strLen(reportName)*-1);
srsReportName = subStr(reportName,1,dot-1);
srsReportDesignName = subStr(reportName,dot+1,
strLen(reportName)-dot);
documentName = ERPReportMailParameter::documentName
(srsReportName,srsReportDesignName,report);

      //render as PDF
bytear = report.Render(“PDF”);
no = bytear;

      //path to temp. files
tmpPath = System.IO.Path::GetTempPath();
tmpPath = tmpPath + documentName + “.pdf”;

      //save to file
fs = System.IO.File::Create(tmpPath);
fs.Write(no,0,bytear.get_Length());
fs.Flush();
fs.Close(); 

      //open outlook email
if (smmOutlookEmail.createMailItem())
{
smmOutlookEmail.isHTML(true);
smmOutlookEmail.addFileAsAttachment(tmpPath);
smmOutlookEmail.sendEMail(smmSaveCopyofEmail::No);
}
}
catch(Exception::CLRError)
{
ex = CLRInterop::getLastException();
info(ex.ToString());
}
}

Test it

Compile your code in X++ and IL and test it.

Send SSRS Report per Email

Multiple Aggregations in X++ Join

At work we’ve recently discussed how to calculate a customers invoice amount for multiple years. One idea was to loop over the customer accounts (while select)  and individually calculate the sum for each year with individual statements (select sum). However, this can be done by a single statement.

CustInvoiceJour     custInvoiceJour;
CustInvoiceTrans    custInvoiceTrans1;
CustInvoiceTrans    custInvoiceTrans2;

while select InvoiceAccount,InvoiceId from custInvoiceJour
group by InvoiceAccount
outer join InvoiceId, sum(LineAmount) from custInvoiceTrans1
where custInvoiceJour.InvoiceId == custInvoiceTrans1.InvoiceId &&
custInvoiceTrans1.InvoiceDate >= str2DateDMY(‘1.1.2011’) &&
custInvoiceTrans1.InvoiceDate <= str2DateDMY(‘31.12.2011’)
outer join InvoiceId, sum(LineAmount) from custInvoiceTrans2
where custInvoiceJour.InvoiceId == custInvoiceTrans2.InvoiceId &&
custInvoiceTrans2.InvoiceDate >= str2DateDMY(‘1.1.2012’) &&
custInvoiceTrans2.InvoiceDate <= str2DateDMY(‘31.12.2012’)
{
info( strFmt(“Customer:%1 2011:%2 2012:%3”,
custInvoiceJour.InvoiceAccount,
custInvoiceTrans1.LineAmount,
custInvoiceTrans2.LineAmount));
}
//Customer:DE-001 2011:3.814.145,50 2012:3.842.223,00

This will result in the following output

Mutliple sum() in X++ Join

Check values for customer DE-001 with excel

Check Mutliple sum() in X++ Join

Use Aggregation Function in Document Data Source

Dynamics AX 2012 supports to publish a query as document data source e.g. to load data in excel. However, document data sources are very limited. For example aggregation like sum() is not supported. Here is an example

  • Create a new query and use the CustInvoiceJour as datasource.
  • Set the Dynamic Fields property to No, but add a SUM field for the InvoiceAmoutMST
  • Append the LogisticsPostalAddress as datasource to the CustInvoiveJour
  • Add a relation and use the predefined InvoicePostalAddress_FK relation
  • Set the Dynamic Fields property to No, but add the CoutryRegionId and City
  • In the query add CountryRegionId and City as Group By Fields

Dynamics AX Query with SUM aggregation

 

Go to System Administration > Setup > Document Management > Document data sources > Add a query reference for the newly create query.

Publish Query with aggregation function as document data source

When you try to load data from this query using Excel Addins, you will face an error message.

Query with aggregation functions fails in Dynamics AX Excel Addin

When you open the document data source in a browser, your will see that there are no useful entries in the data feed. By default the URL is http://localhost:8101/DynamicsAx/Services/ODataQueryService

Dynamics AX OData XML feed has no data

However, one simple workaround is to create a view based on the query.

  • Create a new view
  • Add the previous create query as data source
  • Add the SUM field, CountryRegionId and City to the view fields

Create a view for the query

Open the view. The result should look like this

Dynamics AX view displays query data

Finally, create a second view and use the view as data source. Add a query reference to the second query at the document data sources. The data feed now contains valid data.

Dynamics AX OData XML feed with aggregated data

Now you can load your data using Excel Addins.

Dynamics AX Excel Addin with aggregated SUM data

Show SSRS Report in AX 2012 R3 Mobile Device Portal

This post shows how to provide an SSRS report via Mobile Device Portal in Dynamics Ax 2012 R3. The user will be able to scan an Item ID and the MDP page will show the BOMConsistOf Report for this item. Therefore the report will be rendered as HTML.

First, create a file share on the web server where Dynamics AX can save the HTML File. In my case I’ve created an additional folder in C:\Program Files (x86)\Microsoft Dynamics AX\60\Warehouse Mobile Devices Portal0\ , called it “Report” and set the folder properties in windows explorer to be a file share.

Report folder in Mobile Device Portal

In Dynamics AX Development Workspace, extend the base enums WHSWorkActivity and WHSWorkExecuteMode by adding an additional enumeration value called “ItemInformation”. I’m using the label @SYS113931 for “Item Information”.

Next, create a new class that extends WHSWorkExecuteDisplay

class ERPWHSWorkExecuteDisplayItemInfo extends WHSWorkExecuteDisplay
{
}

The MDP page will include 2 steps, first to scan an ItemId and second to create the report and provide a link. Therefore add 3 methods to the class

// Label and text field for an ItemID
container buildStep0(container _con)
{
xSession    session = new xSession();
container   ret = _con;

    ret += [this.buildControl(#RFLabel,’Lbl_ItemId’,’Scan Item ID’,1,”,
#WHSRFUndefinedDataType,”,0)];

ret += [this.buildControl(#RFText,’ItemId’,”@SYS7407″,1,”,
extendedTypeNum(ItemId),”, 0)];

ret += [this.buildControl(#RFButton,#RFOK,”@SYS5473″,1,”,
#WHSRFUndefinedDataType,”,1)];

    return ret;
}

// Disabled text field with ItemID and Label with URL to HTML report
container buildStep1(container _con)
{
xSession    session = new xSession();
container   ret = _con;

    ret += [this.buildControl(#RFText,’ItemId’,”@SYS7407″,1,
pass.lookup(‘Item’),extendedTypeNum(ItemId),”,0,false)];

ret += [this.buildControl(#RFLabel,’A_REPORT’,
http://localhost:8080/Report/bom.html&#8217;,
1,”,#WHSRFUndefinedDataType,”,0)];

ret += [this.buildControl(#RFButton,#RFOK,”@SYS5473″,1,”,
#WHSRFUndefinedDataType,”,1)];

    return ret;
}

// Set ItemID range and save report to File
private void createReport()
{
ItemId itemId = pass.lookup(‘Item’);
Query query;
BOMConsistOfController ctrl = new BOMConsistOfController();

    ctrl.parmReportName(ssrsReportStr(BOMConsistOf,Report));
query = ctrl.parmReportContract().parmQueryContracts().
.lookup(‘BOMConsistOfDP_DynamicParameter’);

SysQuery::findOrCreateRange(query.dataSourceNo(1),
fieldNum(InventTable,ItemId)).value(itemId);

ctrl.parmReportContract().parmPrintSettings()
.printMediumType(SRSPrintMediumType::File);

ctrl.parmReportContract().parmPrintSettings()
.fileFormat(SRSReportFileFormat::HTML4_0);

ctrl.parmReportContract().parmPrintSettings()
.parmFileName(@”\\localhost\Report\bom.html”);

ctrl.parmReportContract().parmPrintSettings().overwriteFile(true);

    ctrl.run();
}

Add this code the displayForm method

public container displayForm(container _con, str _buttonClicked = ”)
{
WHSWorkExecute  workExecute = new WHSWorkExecute();
container       ret = connull();
container       con = _con;
int             hasError = 0;
int             startInfologLine;

    pass = WHSRFPassthrough::create(conPeek(_con, 2));
hasError = this.hasError(_con);

    if (pass.exists(#UserId))
{
userId = pass.lookup(#UserId);
}

    startInfologLine = infologLine() + 1;

    switch (step)
{
        case 0:
            ret = this.buildStep0(ret);
step = 1;
break;

        case 1:
pass.insert(‘Item’, conPeek(conPeek(con, 4 + hasError), #data));
this.createReport();
ret = this.buildStep1(ret);
step = 2;
break;

        case 2:
pass = this.resetPassthrough(ret, false);
ret = this.buildStep0(ret);
step = 0;
break;

        default:
break;

    }

    ret = this.updateModeStepPass(ret,WHSWorkExecuteMode::ERPItemInfo,
step,pass);
ret = this.addCancelButton(ret, 1, true);

    return ret;
}

With this code in place the Mobile Device Portal page will show a simple text with the Reports URL. However, to provide a link, some JavaScript code is require. Start Visual Studio (or any other HTML editor) as Admin and open C:\Program Files (x86)\Microsoft Dynamics AX\60\Warehouse Mobile Devices Portal0\Views\Execute\ DisplayIEOS.aspx file. At the init() method add this code to hide the label and add a link.

function init() {

    “use strict”;

    focusFirstEnabledInput();

    var label = document.getElementById(“A_REPORTLbl”);
if(label != null)
{
label.style.display = ‘none’;
var link = document.createElement(‘A’);
link.setAttribute(‘HREF’,label.outerText);
link.appendChild(document.createTextNode(“Report”));
label.parentNode.appendChild(link);
}
}

Make sure to compile your work in X++ and IL and synchronize the Data Dictionary. Restarting the AOS might also be a good idea at this stage.

Finally, configure the new activity to be available in Mobile Device Portal menu. In Dynamics AX 2012 R3 > Warehouse Management > Setup > Mobile Device > Mobile Device Menu Item > Add a new item with Mode:Indirect and Activity Code:Item Information. In Warehouse Management > Setup > Mobile Device > Mobile Device Menu > select inventory and add the newly create menu item.

Open the Mobile Device Portal page, logon, select Inventory Management and Item Information. Provide a valid ItemId which has BOM lines. If you are using Contoso Demo data, item D0007 from company USMF is an example. Next, the SSRS report is create and placed as HTML file. The mobile device portal page will show the link to the HTML report.

SSRS Report as HTML in Mobile Device Portal

BTW: you may use Visual Studio Addins to debug the application

Related Work

Data Upgrade Problem: Failed to create session

I was recently upgrading an application to Dynamics AX 2012 R3 Cumulative Update 8. However, the data upgrade scripts didn’t load properly. AX presented an error “Failed to create session” and there were no batch jobs scheduled.

Dynamics AX Failed to Create Session

There ware 3 partitions in this installation

      • initial Initial Partition
  • ps Public Sector
  • ext Extended
    Three Partitions in Dynamics AX 2012 R3

A detailed look in the dbo.UserInfo table revealed that the User Admin was only linked to the initial partition. However, the user requires access to all three partitions in order to create update scripts for these partitions.

dbo.UserInfo User Admin is missing Partition

The solution was to manually add 2 additional rows to the dbo.UserInfo table for the User Admin and link each row to one of the missing partitions. You may alter 2 existing users in order to get a valid RECID, or just increment the last RecID by 1 and delete the records after the upgrade scripts are finished.