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

Stacked Bar chart in Access

Recently a colleague asked how to display a stacked bar chart in Access.

Create a table where one column is used to group data e.g. Year, and additional fields containing values to be stacked e.g Revenue. For example, the columns Revenue by Item Group will be stacked by Year.

Access bar chart data

Next, create a new form and add a diagram object to the form

Add a chart to access

In the diagram wizard select your table with data. Next move the Year and the Revenue fields from the table to the diagram data box.

Add data fields to diagram

Next choose a bar chart. There is no stacked bar chart in the wizard.

Choose bar chart

Drag and drop the year to the axis drop box. Drag and drop the Revenue fields to the data box on the left. By double click on field in the left box you can choose the aggregation function e.g. Count, Sum, None

Assign fields to data and axis

Preview the diagram, it should look like this

Preview bar chart in access wizard

Finish the wizard. A diagram is placed on the form.

Bar chart in access form

Double click the diagram will open the designer view. Here right click on the diagram an open change the diagram type to stacked bar chart

Change chart type to stacked bar chart

Format the diagram as you like. Save your design and open the form

Stacked bar chart in access form

Using Bing Maps app in Excel to visualize inventory on hand data

This video shows how to use Bing Maps app in Excel to visualize on-hand data

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&#8216;,
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

Split long text in SSRS reports on two pages

By default text in tables on Dynamics AX reports like SalesQuotation are kept together. If the text does not fit on the actual page, the complete line starts on the next page. This wastes lot of space and produces unnecessary many pages.

image

To split text in the SalesQuotation follow these steps:

  1. Start Visual Studio as Admin and load the SalesQuotation report from the Application Explorer
  2. Open the report design
  3. Add a new line under the line that contains [ItemId], [Name], [DlvDate] etc.image
  4. Assign the same fields in the new line, but don’t assign the [Name] field
    image
  5. Select the new line and set the property KeepTogether to False
  6. Drag&Drop a textfield form the toolbox on an empty space in the report and name it textboxName
    image
  7. Drag&Drop the textfield in the empty column in the new line.
  8. Make sure the textfield “textboxName” is still selected and open the properties window. Set the field [Name] as value for the textfield
    image
  9. Change the Font and Size to fit the other fields, by default its Segoe UI 8pt
  10. Finally mark the old line above and set the property Hidden to True
  11. Deploy the report

image

Add carry forward sum to OpenSalesOrders SSRS report

A common requirement is to add a running totals sum and carry foward which displays the actual sum on the next page. Following the tutorials like this by André it’s easy to implement also for Dynamics AX.

  1. Start Visual Studio as Admin and open CustSalesOpenOrders_NA report from the application explorer
  2. Open the report design
  3. Add 2 new columns on the right side and set Hidden property to TRUE
  4. Open the property dialog for the first new column and change the name to RunningTotal
  5. Set the following expression as value
    =RunningValue(Fields!amountRemainingMST.Value,SUM,"CustSalesOpenOrders_NADS")
  6. Open the property dialog for the second new column and change the name to RunningTotalPrev
  7. Set the following expression as value
    =RunningValue(Fields!amountRemainingMST.Value,SUM,"CustSalesOpenOrders_NADS") – Fields!amountRemainingMST.Value
  8. In the report design create a new text field in the report header above the amountRemainingMST
  9. Set the following expression as value
    ="Running Total: " +cstr(First(ReportItems!RunningTotalPrev.Value))
  10. In the report design add a new footer and add a text field in the footer under the amountRemainingMST
  11. Set the following expression as value
    ="Running Total: " +cstr(Last(ReportItems!RunningTotal.Value))
  12. Save and deploy the report
  13. Open Dynamics AX go to Sales and Marketing > Reports > Sales Orders and start the Open Sales Orders report

Your report should look like this, including a page sum and a carry forward on the next page

image