Reporting Capabilities of MS Dynamics Ax 2009 – Part 2 (SSRS Report Builder)

This article is from a series of articles describing Dynamics Ax reporting capabilities. In this article we will see how easily end user can create a Dynamics Ax SQL Server Reporting Services report using Report Builder (MorphX development tools) for Dynamics Ax.

Here developer also plays a role in the setup of ad hoc report. Using Report Builder, Microsoft SQL Server Reporting Service component, end user can create a transactional ad hoc report based on the transactional data entered in the Dynamics Ax.

In this article we will see how to create a simple report using report builder. In this scenario Dynamics Ax end user creates the report, and setup of report information is done by a developer and an administrator by creating perspectives and generating report models respectively.

Creating Dynamics Ax Report using Report Builder.

Step 1: Perspective

A perspective is a collection of tables that provides organized information for a report model. You create perspectives in Dynamics Ax AOT.
You can create a new perspective or use standard prospectives that came with Dynamics Ax. Check AOT–DataDictionary–Prospectives.
This is a Dynamics Ax developer task.

Step 2: Generate Report Model

Report Model is used to create reports. A report model provides a view to transactional business data. A view is not complete Dynamics Ax data, it is always relevant to specific end user. End users select a report model according to their information need and create reports based on this report model.
This is a Dynamics Ax administrator’s task.

Step 3: Create Report with Report Builder

  A) Select fields to display in the report

  • From the Dynamics Ax menu, open Tools | Business Intelligence (BI) tools | Report Builder.
  • In the Report Builder options window, from the Report data list, select Accounts receivable (Cust perspective in AOT), and then click OK.
  • Report builder will open, click the title box and type Sales Orders.
  • Select Customers from the Entities list. Drag Customer group, Customer account, and Name into the report design where it says Drag and drop column fields.
  • Select Sales orders (as detail) from the Entities list. Drag Sales order and Status into the report design after the fields that you already added to the report design.
    If you followed me correctly then your report builder screen should look like this.

  B) Specify filter, sorting, and grouping options

  • From the toolbar, click Filter.
  • From the Fields list, double-click Status.
  • In the filter clause, click “equals”, and then click “In a List”.
  • Click (no values selected), and then select Delivered and Invoiced.
  • Click OK.
  • From the toolbar, click Sort and Group.
  • Select Customer group. In the Sort by list, select Customer group, and then select Descending.
  • Click OK.
  • Click the Run Report icon in the toolbar to view the report data.

          Check out the report layout, its not just nice and clear but also has some interesting things inbuilt. It has sorting / grouping, try clicking on the column header. The most important thing, it has automatically created drilldown reports for you :), try to click on column values you will understand what I am talking about. Check the footer, it provides useful information about number of records fetched and what filters are applied to this report.

  C) You can save the report to the report server or save it to a file that you can open later.
    Save the report to the report server:

  • From the File menu, click Save As.
  • Type the name of the report in the Name box and then click Save.
    Note: By default, the location of the report folder located on the report server is provided. For example, http://localhost/reportserver/Dynamics.
  • From the Dynamics AX menu, point to Tools, point to Business Intelligence (BI) tools, and then click Report manager.
    Note: Click the report to run it from the report manager.

    Save the report to a file:

  • From the File menu, click Save to File.
  • Select the location to which to save the report, type the name of the report in the Name box, and then click Save.
I hope this article briefed you well with the power of Dynamics Ax reporting. In my next article I will discuss how to create more complex Dynamics Ax SSRS reports using Visual Studio 2008.

Feel free to post your feedback / comments / queries here.

Open SSRS reports designed through Report Builder in Dynamics Ax
Reporting Capabilities of MS Dynamics Ax 2009 – Part 1

If you liked the story and want to see more of this kind of content, please follow our twitter handle @CursorRun or FB page @CursorRun
Default image
Rahul Sharma
Rahul is a technology enthusiast, solutions architect, trainer, and blogger, working on various Microsoft and open source solutions with more than 18 years of industry experience. He specially takes interest in designing enterprise applications, cloud integrations, IoT, and other architecture rich business solutions.

Leave a Reply

%d bloggers like this: