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

Advertisements

One thought on “AX 2012: Chart in SSRS Report

    neophytism said:
    July 9, 2016 at 5:04 am

    How can we add PERCENT % in above pie chart? Also I want to put a footer like “Total Count = 350”.Please tell me.Thanks in Advance!

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s