SSRS Reports

AX 2012: Create SSRS Line Chart Report

Posted on Updated on

MakItemSalesHistory

 

Purpose:

The purpose of this document is to demonstrate how we can leverage the power of business intelligence through a simple SSRS line chart report.

Prerequisites:

  1. Visual Studio 2013
  2. Visual Studio 2013 tools.
  3. Reporting extension.
  4. Report Builder.
  5. Should be well-versed in Report Programming Model.

Business requirement:

Leverage business intelligence by analyzing sales trends for top item groups over the period of time.

Project overview:

MakItemSalesHistoryProject

Development:

We will be following the Report Programming Model to develop a precision design report.

1. Create an InMemory table, MakItemSalesHistoryTmp. This table will be used as a data set for the report in Visual Studio.

MakItemSalesHistory

2. Create an AOT query, MakItemSalesHistory. This query will be used in the Data Provider class to fetch records for the report.

MakItemSalesHistory

3. Create a data provider class, MakItemSalesHistoryDP. Bind the AOT query with it.

[
    SRSReportQueryAttribute(queryStr(MakItemSalesHistory))
]
class MakItemSalesHistoryDP extends SRSReportDataProviderBase
{
    MakItemSalesHistoryTmp  makItemSalesHistoryTmp;
}
[
    SRSReportDataSetAttribute(tableStr(MakItemSalesHistoryTmp))
]
public MakItemSalesHistoryTmp getItemSalesHistoryTmp()
{
    select makItemSalesHistoryTmp;
    return makItemSalesHistoryTmp;
}
private void insertTmpTable(SalesLine _salesLine)
{
    Qty                 qty;
    Date                shipDate;
    InventItemGroupItem groupItem;

    qty = _salesLine.QtyOrdered;
    shipDate = _salesLine.ShippingDateConfirmed;
    groupItem = InventItemGroupItem::findByItemIdLegalEntity(
        _salesLine.ItemId,
        _salesLine.DataAreaId);

    ttsBegin;
    makItemSalesHistoryTmp.clear();
    makItemSalesHistoryTmp.ItemId = _salesLine.ItemId;
    makItemSalesHistoryTmp.ItemGroupId = groupItem.ItemGroupId;
    makItemSalesHistoryTmp.Price = _salesLine.salesPrice;
    makItemSalesHistoryTmp.Amount = _salesLine.SalesPrice * Qty;
    makItemSalesHistoryTmp.Qty = qty;
    makItemSalesHistoryTmp.Year = year(shipDate);
    makItemSalesHistoryTmp.MonthOfYearId = mthOfYr(shipDate);
    makItemSalesHistoryTmp.Days = dayOfMth(shipDate);
    makItemSalesHistoryTmp.insert();
    ttsCommit;
}
[SysEntryPointAttribute(false)]
public void processReport()
{
    Query                   query;
    QueryRun                queryRun;
    QueryBuildDataSource    qbds;
    QueryBuildRange         qbr;
    SalesLine               salesLine;
    InventItemGroup         ItemitemGroup;

    query = this.parmQuery();
    qbds = query.dataSourceTable(tableNum(InventItemGroupItem));
    qbds.name("InventItemGroupItem");

    qbr = qbds.addRange(fieldNum(InventItemGroupItem, ItemGroupId));
    qbr.value(strFmt('((%1.%2 == "Speakers") || (%1.%2 == "Television"))',
        qbds.name(),
        fieldStr(InventItemGroupItem, ItemGroupId)));

    queryRun = new queryRun(query);

    while (queryRun.next())
    {
        salesLine = queryRun.get(tableNum(salesLine));
        this.insertTmpTable(salesLine);
    }
}

4. Create a controller class, MakItemSalesHistoryController. You can observe, how the report dialog has been suppressed in the controller.

class MakItemSalesHistoryController extends SrsReportRunController
{
}
static void main(Args _args)
{
    MakItemSalesHistoryController controller;

    controller = new MakItemSalesHistoryController();
    controller.parmReportName(ssrsReportStr(MakItemSalesHistory, LineChart));
    controller.parmArgs(_args);
    controller.parmShowDialog(false);
    controller.startOperation();
}

5. In Visual Studio 2013

  • Create a new Project
  • Add new Report to the solution.
  • Add new Dataset, MakItemSalesHistoryTmp, of type Report Data Provider.
  • Choose MakItemSalesHistoryDP class in the Query.
  • Add new precision design, LineChart, to the report Designs node.

MakItemSalesHistory

6. Edit LineChart using Designer.
7. Insert a new Chart report item from the Toolbox.

MakItemSalesHistory

8. Double click on the chart area to open the field editor, add the following fields as specified.

Field Drop area
Year, MonthOfYearId Category
ItemGroupId Series
Qty Data

9. Next, you can improve the aesthetics of the chart, as it pleases, by updating different chart properties available out of the box in Visual Studio.
10. Save, build and deploy the report.
11. Create an output menu item and bind it with the SSRS report.

MakItemSalesHistory

12. Add menu item to Sales and marketing > Reports > Sales orders > Item sales history.
13. You should be able to see the report in the Report viewer.

MakItemSalesHistory

AX 2012: Print SSRS Report in PDF to Disk

Posted on Updated on

Purpose:

The purpose of this document is to illustrate how we can print an SSRS report in PDF format to disk or file-system in X++.

Business requirement:

Ability to print an SSRS report in PDF format to disk or file-system automatically.

Assumptions:

SSRS report is successfully deployed to the report server.

Development:

Please find below the job to write SSRS report in PDF to disk or file-system.

// Developed on 01 Feb 2016 by Muhammad Anas Khan
// Blog: dynamicsaxinsight.wordpress.com
// LinkedIn: pk.linkedin.com/in/muhammadanaskhan
// Description: Write SSRS report in PDF to disk
static void makPrintSSRSReportToDisk(Args _args)
{
    //Instantiate custom controller
    SRSPrintDestinationSettings printSettings;
    SrsReportRunController 	controller = new PurchPurchaseOrderController();
        
    controller.parmReportName(ssrsReportStr(PurchPurchaseOrder, Report));

    //Get print settings from contract
    printSettings = controller.parmReportContract().parmPrintSettings();

    // set print medium
    printSettings.printMediumType(SRSPrintMediumType::File);
    printSettings.fileFormat(SRSReportFileFormat::PDF);
    printSettings.overwriteFile(true);
    printSettings.fileName(@"C:\Temp\PurchPurchaseOrder.pdf");

    // suppress the parameter dialog
    controller.parmShowDialog(false);
    controller.startOperation();
}

AX 2012: Create SSRS Column Chart Report

Posted on Updated on

Untitled

Purpose:

The purpose of this document is to demonstrate how to develop SSRS column chart report in Dynamics AX 2012.

Prerequisites:

  1. Visual Studio 2010
  2. Visual Studio tools.
  3. Reporting extension.
  4. Report Builder.
  5. Should be well-versed in Report Programming Model.

Business Requirement:

Create a column chart report to show top products sold in the year 2012.

Project Overview:

Untitled

Data Analysis:

Untitled

Use the above SQL query result to validate the report which shows the product IDs sorted on the quantity sold in descending order.

Development Steps:

1. Create an RDP class MAKTopProductsSoldDP. Give the definition of processReport method as follows:

[SysEntryPointAttribute]
public void processReport()
{
    MAKSalesTable   makSalesTable;

    while select ProductSold, sum(SalesQty) from makSalesTable
        group by makSalesTable.ProductSold
            order by makSalesTable.SalesQty desc
    {
        this.setMAKSalesTableTmp(makSalesTable);
    }
}

2. Create a new method setMAKSalesTableTmp and give the following definition:

private void setMAKSalesTableTmp(MAKSalesTable _makSalesTable)
{
    makSalesTableTmp.clear();

    makSalesTableTmp.ProductSold = _makSalesTable.ProductSold;
    makSalesTableTmp.SalesQty = _makSalesTable.SalesQty;

    makSalesTableTmp.insert();
}

3. Create a new method getMAKSalesTableTmp and give the following definition:

[SRSReportDataSetAttribute(tableStr(MAKSalesTableTmp))]
public MAKSalesTableTmp getMAKSalesTableTmp()
{
    select makSalesTableTmp
        order by SalesQty desc;
    return makSalesTableTmp;
}

4. Open Visual Studio 2010 and create a new project of type Report Model, MAKTopProductsSold choosing Microsoft Dynamics AX from installed templates.

Untitled

5. Add new Report MAKTopProductsSold to the Project node in the Solution Explorer.
6. Add new Precision Design to the Designs node of the report.
7. Right click the precision design and click Edit Using Designer.
8. Drag Chart from the Toolbox.
9. Set Chart axes properties appropriately to show Products on the X-Axis and Quantity on the Y-Axis.

Untitled

10. Improve formatting of the chart using some professional intellect 🙂
11. Deploy the report to the Report Server using Microsoft Dynamics AX 2012 Management Shell.

Untitled

12. Create a new controller class MAKTopProductsSoldController and give the following definition for the main method:

static void main(Args _args)
{
    MAKTopProductsSoldController    controller;

    controller = new MAKTopProductsSoldController();
    controller.parmReportName(ssrsReportStr(MAKTopProductsSold, PrecisionDesign));
    controller.parmArgs(_args);
    controller.parmShowDialog(false);
    controller.startOperation();
}

13. Create a new output menu item MAKTopProductsSold to point it to the controller class. Sets its properties as shown below:

Untitled

14. Run the report using the output menu item. Notice that the report dialog has been suppressed by the controller.
15. You should be able to see the report in action 🙂

AX 2012: SSRS Report Drill Through Action URL

Posted on Updated on

In this post we’ll learn how to add a drill through action URL to a field in an SSRS report. The focus will be more towards exploring drill through action URL rather than developing SSRS report. We’ll be using an RDP report with precision design to demonstrate the development.

Let’s say we have a requirement to open HcmWorker form when user clicks on the Name of the worker in the SSRS report. Before adding the drill through URL our simple report looks like below showing list of workers:

Untitled

To add the drill through URL to open HcmWorker form on clicking Name field on the report, please follow the development steps below:

1. Open the Visual Studio project of the report.
2. Go to Application Explorer > Visual Studio Projects > C Sharp Projects > SRSDrillThroughCommon.
3. Right click and Edit the project. A project node for SRSDrillThroughCommon will be added in the Solution Explorer.

Untitled

4. Open DrillThroughCommonHelper.cs and search for the pre-built drill through method to open HcmWorker form.

Untitled

5. Expand HRM Helpers region. You’ll find a method ToHcmWorker(). We’ll be referring to this method in a while.

Untitled

6. Now add a data method to the report drillHcmWorker. This will add a BusinessLogic project to the Solution Explorer.

Untitled

Untitled

7. Add reference of SRSDrillThroughCommon project to MAKHcmWorker.BusinessLogic project.

Untitled

8. Open MAKHcmWorker.cs and add namespace using Microsoft.Dynamics.AX.Application.Reports.
9. Rewrite the drillHcmWorker() method to have the following definition:

using System;
using System.Collections.Generic;
using System.Security.Permissions;
using System.Data;
using Microsoft.Dynamics.Framework.Reports;
using Microsoft.Dynamics.AX.Application.Reports;

public partial class MAKHcmWorker
{
    [DataMethod(), PermissionSet(SecurityAction.Assert, Name = "FullTrust")]
    public static string drillHcmWorker(string reportContext, string personnelNumber)
    {
        return DrillThroughCommonHelper.ToHcmWorker(reportContext, personnelNumber);
    }
}

10. Now edit the precision design using Designer. Select Name field to access its Action property.

Untitled

11. Click on the ellipsis icon. In the hyperlink options, choose Go to URL.

Untitled

12. Give the following expression:

=drillHcmWorker(Parameters!AX_ReportContext.Value, Fields!PersonnelNumber.Value)

13. Change the font color to Blue.
14. Access properties of MAKHcmWorker.BusinessLogic project. Set Deploy to Client to Yes.
15. Access properties of SRSDrillThroughCommon project. Set Deploy to Client to Yes.
16. Build and Deploy the report.

We are good to run the report to see the drill through URL in action 🙂

Untitled

AX 2012: Multi-Select Lookup for SSRS Report Dialog

Posted on Updated on

Multi-Select Lookup

Overview:

In this post we’ll learn how to build multi-select lookup for SSRS report dialog. We’ll create an RDP report with an AutoDesign layout. Controller will be used to run the report. An output menu item will be created to point to the controller class. Each AOT element involved will be described in detail. You can guess the complexity of this development task by looking at the AOT elements required for it:

Multi-Select Lookup

Let’s develop them piece by piece…

1. MAKCustTable (Query):

Multi-Select Lookup

1. Create MAKCustTable query.
2. Drag CustTable table to the data sources node.
3. Select the fields as shown in the picture.

 

 

 

 

 

 

 

 

2. TmpMAKParameters (InMemory Table):

Multi-Select Lookup

1. Create InMemory table TmpMAKParameters.
2. Add the fields in the table as shown in the picture.

 

 

 

 

 

 

3. MAKParametersUIBuilder (UI Builder Class):

  • build() method overridden to add dialog field.
  • postBuild() method overridden to register custom lookup method with lookup event.
  • custTableLookup() method gives custom lookup implementation.
  • postRun() method overridden to comment out super() method call.
  • super() is commented to avoid the following exception:
    • RegisterOverrideMethod was called twice for the same object for method ‘lookup’…
class MAKParametersUIBuilder extends SysOperationAutomaticUIBuilder
{
    DialogField     dialogCust;
}

public void build()
{
    MAKParametersContract   contract;

    contract = this.dataContractObject() as MAKParametersContract;
    dialogCust = this.addDialogField(
	methodStr(MAKParametersContract, parmCustAccountList),
	contract);
}

public void postBuild()
{
    MAKParametersContract   contract;

    super();

    contract = this.dataContractObject() as MAKParametersContract;

    dialogCust = this.bindInfo().getDialogField(
        contract,
        methodStr(MAKParametersContract, parmCustAccountList));

    dialogCust.registerOverrideMethod(
        methodStr(FormStringControl, lookup),
        methodStr(MAKParametersUIBuilder, custTableLookup),
        this);

    if (dialogCust)
    {
        dialogCust.lookupButton(2);
    }
}

private void custTableLookup(FormStringControl _control)
{
    Query       query;
    container   conCustTable;

    query = new Query(queryStr(MAKCustTable));

    SysLookupMultiSelectGrid::lookup(
	query,
	_control,
	_control,
	conCustTable);
}

public void postRun()
{
    //super();
}

 

4. MAKParametersContract (Contract Class):

[
    DataContractAttribute,
    SysOperationContractProcessingAttribute(classstr(MAKParametersUIBuilder))
]
class MAKParametersContract
{
    List  custAccountList;
}

[
    DataMemberAttribute("custAccountList"),
    AifCollectionTypeAttribute("custAccountList", Types::String),
    SysOperationLabelAttribute(literalStr("Customers"))
]
public List parmCustAccountList(List _custAccountList = custAccountList)
{
    custAccountList = _custAccountList;

    return custAccountList;
}

 

5. MAKParametersController (Controller Class):

  • showPrintSettings() method overridden to return false to turn off Print Settings field group.
  • showQueryValues() method overridden to return false to turn off default query parameters fields.
  • main() method runs the report.
class MAKParametersController extends SrsReportRunController
{
}

public boolean showPrintSettings()
{
    return false;
}

public boolean showQueryValues(str parameterName)
{
    return false;
}

public static void main(Args _args)
{
    MAKParametersController controller;

    controller = new MAKParametersController();
    controller.parmReportName(ssrsReportStr(MAKParametersReport, AutoDesign));
    controller.parmArgs(_args);
    controller.startOperation();
}

 

6. MAKParametersDP (Data Provider Class):

  • processReport() method adds multiple ranges to the query for multiple customers selected in lookup
[
    SRSReportParameterAttribute(classStr(MAKParametersContract))
]
class MAKParametersDP extends SRSReportDataProviderBase
{
    MAKParametersContract   contract;
    TmpMAKParameters        tmpMAKParameters;
}

[
    SRSReportDataSetAttribute("TmpMAKParameters")
]
public TmpMAKParameters getTmpMAKParameters()
{
    select * from tmpMAKParameters;

    return tmpMAKParameters;
}

public void populateTmpTable(AccountNum _accountNum)
{
    CustTable custTable;

    while select custTable where custTable.AccountNum == _accountNum
    {
        tmpMAKParameters.AccountNum = custTable.AccountNum;
        tmpMAKParameters.CustGroup = custTable.CustGroup;
        tmpMAKParameters.insert();
    }
}

[SysEntryPointAttribute]
public void processReport()
{
    Query                   query;
    QueryRun                queryRun;
    CustTable               custTable;
    QueryBuildDataSource    qbdsCustTable;
    ListIterator            custListIterator;

    contract = this.parmDataContract() as MAKParametersContract;
    custListIterator = new ListIterator(contract.parmCustAccountList());
    query = new Query(queryStr(MAKCustTable));
    qbdsCustTable = query.dataSourceTable(tableNum(CustTable));
    
    while(custListIterator.more())
    {    
        qbdsCustTable.addRange(
            fieldNum(CustTable, AccountNum)).value(custListIterator.value());
        
        custListIterator.next();
    }

    queryRun = new QueryRun(query);

    while(queryRun.next())
    {
        custTable = queryRun.get(tableNum(custTable));
        this.populateTmpTable(custTable.AccountNum);
    }
}

 

7. MAKParametersReport (SSRS Report):

Multi-Select Lookup

1. Create MAKParametersReport SSRS Report.
2. Create a new RDP dataset.
3. Select MAKParametersDP
4. Drag the dataset to the Designs node.
5. Save, build and deploy the report.

 

 

 

 

 

 

8. MAKParametersReport (Output Menu Item):

1. Create an output menu item MAKParametersReport.
2. Set ObjectType to Class.
3. Set Object to MAKParametersController


We are now good to run the report by clicking the menu item 🙂

Untitled

Untitled

AX 2012: Add Report Parameter to SSRS Query Report

Posted on

In this post we’ll learn how to add report parameter and show it in the report dialog for a Query based SSRS report. Please follow the development steps below to achieve it:

1. Create an AOT Query MAKCustTable.
2. Drag CustTable table to the Data Sources node of the query.
3. Set Dynamic property to Yes on the Fields node to add all the fields available in the table quickly.
4. Keep the following fields only and remove others. Then set Dynamic property to No.

Untitled

5. Open Visual Studio to create a new Project of type Report Model using Microsoft Dynamics AX installed template.
6. Add report to the project MAKParametersReport.
7. Add Dataset to the report and choose the query MAKCustTable we have just created.

Untitled

8. Drag the dataset MAKCustTable to the Designs node. This will create an AutoDesign layout and Table control with fields added.

Untitled

9. Now Build and Deploy the report from Visual Studio.
10. After successful deployment, you should find MAKParametersReport in the AOT > SSRS Reports
11. Create an output menu item for the newly created SSRS report.
12. Open the report using menu item.

Untitled

13. By default, Customer account is the only report parameter in the dialog.
14. Now to add more report parameters, update the AOT query MAKCustTable to have the following ranges added:

Untitled

15. Back in Visual Studio, refresh the dataset.

Untitled

16. Save, Build and Deploy the report again from Visual Studio.
17. To see the changes, open the report using the existing output menu item.

Untitled


For your information, the MAKCustTable_DynamicParameter collectively holds all the ranges defined in the Dataset’s query. Make sure its properties are set as shown here:

Untitled

AX 2012: Chart in SSRS Report

Posted on

Prerequisites:

1. Reporting services extensions must be installed
2. Visual Studio tools must be installed
3. Dynamics AX should be connected to Reporting services instance


In this post we’ll learn how to create data visualization in SSRS report using Pie or Doughnut chart. We’ll be creating a query based AutoDesign report. This is how our report will look like at the end after following the mentioned development steps:

Untitled

Steps:

1. Create a new Query in the AOT. Name it as PKTCustomerDetails.
2. Add CustTable table to the Data sources node of query.
3. On the Fields node of CustTable, set the Dynamic property to Yes. This will add all the table fields to Fields node.
4. Set the Dynamic property to No and then remove unwanted fields to make the query light and report efficient. This way we can add fields quickly 🙂

Untitled

5. We are done with the query now. Let’s design report in Visual Studio.
6. Open Visual Studio and create a new project using Microsoft Dynamics AX installed template. Name it as PKTCustomerDetails.

Untitled

7. Right click the project node in the Solution Explorer, click Add > Report. Rename the report to PKTCustomerDetails.
8. Add new Dataset to the report. Rename the dataset to PKTCustomerDetails.
9. Set the Query property of the dataset to the newly created AOT Query PKTCustomerDetails.

Untitled

10. Select all the fields of the query.
11. Drag the newly added dataset to the Designs node. This will automatically an AutoDesign to the report. Moreover a table will also be added for the dataset having all the fields in the Data node.

Untitled

12. Drag the CustGroup field from dataset to the Groupings node of the table to group data by CustGroup field.
13. Preview the report. Your report should look like below:

Untitled

14. Now we’ll add Pie chart to our report to show total Customers per Customer Group.
15. Right click on the AutoDesign to add a Pie chart

Untitled

16. Rearrange Pie chart so that it comes before table in the AutoDesign.
17. Drag AccountNum field from dataset to the Data node. Set its properties as shown below.

Untitled

19. Drag CustGroup field to the Series node.
20. By now your report structure should look like as shown below.

Untitled

21. You may preview the report now to see multiple data regions in action – Pie chart and table.
22. Now Build and Deploy the report from Visual Studio.

Untitled

23. Go back to AX development workspace. PKTCustomerDetails report should be coming in AOT > SSRS Reports.
24. Create an Output menu item for the report PKTCustomerDetails.
25. We are good to view our report from within AX now by clicking the newly created output menu 🙂

Untitled