OLAP

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: Creating cubes

Posted on Updated on

Image

Prerequisites

  • Dynamics AX 2012
  • Dynamics AX 2012 Analysis Services
  • SQL Server 2012

Once you have identified the tables and views which contain the relevant data for your BI report, you can begin creating perspectives (cubes) in the AOT. Let’s suppose we have to develop a sales report for which the following tables/views contain the relevant data:

CUSTTRANSTOTALSALES view

CUSTTABLECUBE view

CUSTPAYMMODETABLE table

Creating Cube

Follow these steps to create a new perspective in the AOT:    Perspective Node

1. Expand Data Dictionary/Perspectives node

2. Create a new perspective and give it a suitable name

3. Set the USAGE property to OLAP

4. Drag the relevant tables and views

Defining Measures and Dimensions

Next step is to define measures and dimensions. Expand CUSTTRANSTOTALSALES view and set the field properties in the AOT as follows:

Untitled

Repeat the above step to specify measures and dimensions for other tables/views in your perspective as per your report requirements.

Generate Project

Click Tools > Business Intelligence (BI) tools > SQL Server Analysis Services project wizard. Create a new project and give a suitable name as shown below:

Untitled

On the next screen, select your newly created perspective:

Untitled

The next couple of screens offer optional features which include adding financial, date dimensions, language selection and currency conversion. The wizard will generate the project:

Untitled

Its up to you whether you save the project on disk or in the AOT.

Deploy cube

Next, the wizard will prompt you to choose the partitions to deploy the cube in if multiple partitions exist in the database environment. You can either to choose to process the cube using this wizard by ticking the checkbox or process the cube later in the SQL Server Management Studio.

Untitled

Process cube

Open SQL Server Management Studio, connect to Analysis Services. In the Object Explorer, you should be seeing now your newly created cube:

Right click your cube and click Process. Make sure the user has sufficient rights to process the cube otherwise you would have to provide Impersonation information in the Security settings of the database properties of your cube. The wizard will successfully process the cube.

Untitled

This ends the process of creating cubes! Now you can make MDX queries, build reports on top of your cubes. One such report that I created using SQL Server 2012 Power View plugin looks like below in the role center:

Untitled