Geeks With Blogs
Satya Srikant Mantha Reflecting DAX NET and SQL Server

In this post I’ll discuss, creation of a simple Customer Report, as and when it is time to introduce new concepts I will add more to this report.

Creating a Reporting Project

1) Open Visual Studio

2) Click on File –> New –> Project –> <Select your preferred language C# or VB.NET> –> Dynamics –> Dynamics AX Reporting Project

image

Note: You should have Reporting Extensions installed on your system. Reporting Extensions will install a Visual Studio plug-in which enables you to create a Reporting Project in AX.

3) You will see a default Report Model Report1.moxl and you solution explorer will look like:

 image

4) Click on the Report Designer and Change the following properties:

  • In the Solution Explorer change the name of the Report File to CustReport.moxl
  • Open the Report model and Click on the top Most node called as Report1, change its Name and Title properties to CustReport and Customer Report respectively.

At the end your report model will look like:

image

Creating Data Source for your Report

A Report Data Source represents a source of data for your report. It can be a pre-defined data source from Microsoft Dynamics AX, or you can have your own custom data source. Depending upon your choice these data sources can be one of the following:

Data Source Type Options for Data Retrieval
Dynamics AX (predefined data source)
  • Queries that are defined in AOT
  • Data Methods that are defined within a reporting project in Microsoft Visual Studio
SQL
  • TSQL Query
  • Stored Procedure
OLAP
  • MDX query

Important: It is recommended that you use the predefined Dynamics AX data source to ensure that appropriate security is enforced. When you use the predefined Dynamics AX data source, you can use queries that are defined in AOT, and all data access requests go through the MorphX security system. When you use SQL or OLAP data source, the MorphX security system is bypassed.

In this case, we will use CustEPQuery, which is a simple Query in Microsoft Dynamics AX the data source of this query is CustTable.

In the designer of the Report, right click on the DataSets node and click Add Dataset. Change the properties of this newly created dataset as follows:

1) Edit the Data Source property to point to the Database used by Microsoft Dynamics AX instance you are pointing to.  (This is basically an ODC file called as Dynamics AX in the present DynamicsAx folder of Report Server.)

2) Rename the DataSet as CustDataset

3) Data Source Type as Query

4) Select the ellipsis button ahead on Query property, which will show the following popup dialog box:

image

Select the following fields:

  • AccountNum
  • Address
  • BankAccount
  • CustGroup
  • Name
  • Phone

Now the report dataset is ready for use. In case you want more fields just select the fields in the Select a Dynamics AX Query dialog and check the required fields. I will discuss the some more advanced options for the report data source in upcoming posts.

Designing the Report

There are two types of designs in the Reports:

1) Auto Design: It is a report design in which a layout is automatically generated based on the data for the report.

2) Precision Design: It is a report design with a very precise layout. SQL Report Designer is used to edit the precision design for a report. Before editing the report design, it must have a model and dataset must be added to it so that the data is available in SQL Report Designer.

In this post, I am going to demonstrate how to work with the Auto Design Reports. Follow the steps to create an auto design for a report:

1) In the Model Editor, expand the node for the report.

2) Add appropriate Dataset .

3) Drag the dataset to the Designs Node of the Report. In our example, drag the dataset CustDataset to Design node of the CustReport, by default the name of the design will be AutoDesign1, the resultant model will look as shown in the following figure:

 image

Before previewing the design we need to set some templates to the report for look and feel.  Microsoft Dynamics AX has a report library called as SharedLibrary which contains some of the basic design templates which can be used inside the reports.

Add the SharedLibrary Project to your solution by following the steps below:

1) In Microsoft Visual Studio, click on File –> Add –> Existing Project from Dynamics AX.

2) In the resultant dialog box select the SharedLibrary project and click on OK.

By doing so, you will add SharedLibrary report project into your solution. Select the reporting project you are working on and click on Add Reference.  Add the reference for SharedLibrary into your project. SharedLibrary consists of various Layout Templates which can be used in your report design.

Add the following design templates to your report:

  • Select AutoDesign1 node in CustReport report model and set the LayoutTemplate property to ReportLayoutStyleTemplate.
  • Select the CustDataSetTable node inside the AutoDesign1 node and set Style Template property to TableStyleTemplate.

Save your report. Right click on the AutoDesign1 node and click on Preview. Your report will look like:

image

In the subsequent posts we will examine the Precision Reports and Parameterized reports.

Posted on Tuesday, March 10, 2009 5:47 AM Enterprise Portal Reporting | Back to top


Comments on this post: Reporting Services for Microsoft Dynamics 2009 Enterprise Portal – Part 3 Designing a Basic Report

# re: Reporting Services for Microsoft Dynamics 2009 Enterprise Portal – Part 3 Designing a Basic Report
Requesting Gravatar...
Good post dude !!
Left by SAM on Mar 11, 2009 11:33 AM

# re: Reporting Services for Microsoft Dynamics 2009 Enterprise Portal – Part 3 Designing a Basic Report
Requesting Gravatar...
Hi! I was just wondering what are its advantages over those done in Excel. I could really use this in my business.
Left by Boiler Parts on Jun 02, 2010 6:43 PM

# Report in Enterprise Portal
Requesting Gravatar...

Hi,
Is there a way I can call can standard ax reports(eg. sales Invoice) from the enterprise portal ,I am able to run reports of ax from ep , but am not able to do the same with AX SalesConfirm.
Left by vikas on Jun 05, 2011 9:35 PM

Your comment:
 (will show your gravatar)


Copyright © ssmantha | Powered by: GeeksWithBlogs.net