≡ Menu




Query Based SSRS report from Scratch Dynamics Ax 2012 R3.

In Microsoft Dynamics Ax 2012, we can create less complex reports with AOT Query or Static Query.

Simple or model based report is as follow.

QueryBased Report

http://dynamics-ax.blogspot.com/2011/12/ax-2012-ax-ssrs-report-design-concepts.html

 

The steps are as follow  We can create simple or model based report with following steps

  • Step 1: Create a Microsoft Dynamics AX query
  • Step 2: Create a new report in Visual Studio
  • Step 3: Apply Layout template
  • Step 4: Add Column sorting
  • Step 5: Group report data
  • Step 6: Filter report data
  • Step 7: Add a dynamic parameter
  • Step 8: Save and deploy the report
  • Step 9: View the report in Microsoft Dynamics AX

 

We can see these steps below

 

Now consider a scenario, where we have to display sales item detail with respect to its customer. As this report is not much complex and on exploring Dynamics Ax 2012 default tables we found that Sales order detail at Item level can be found on “SalesLine” table. Complexity is less so we decide to create this report with Static Query or AOT query based Report.

This Article is based on Contoso demo data, and Dynamics Ax 2012 R3 on demo licences

 

 

Open MS Dynamics Ax 2012 Client And press Ctrl+Shift+W to open dev environment or open the AOT.

When AOT open expand it and at AOT node add new query

CreatingAOTQuery

Rename Query to simple “QSalesLine”.

Expand its data Source Node, right click and add new data Source

Add DataSource

Rename the Data Source to SalesLine and select SalesLine table

SalesLineDataSource

Now expand the field Node and set its dynamic property to no

DynamicPropertyToNo

Right Click on Field and add new field and select salesId

 

SelectField

 

SalesId

Similarly you can add following fields

  • CustAccount
  • SalesId
  • OrderQty
  • SalePrice
  • LineAmount
  • ItemId,
  • Name,
  • CustGroup

QSales

 

Now save the Query.

 

Open Visual studio and create a new report project

ModelReport

 

 

Open Visual studio environment and create a new Report and Rename It to SalesLineReport

 

From solution Explorer add new report

SolutionExplorer

Now expand report and right click on data Source to create a new data source Rename It to DSSaleLine

SalesLineDS

 

Right click on “DSSaleLine” it Edit it or click on properties and open property window

propertyWindow

From Property window select

 

Click on Query and from Brower window select Required Query

 

Click on next window

SelectionOfQSales

 

Select all fields and click on save

FieldSelection

 

 

ReportDetails

 

Now drag the data set and drop on designs node, It will create auto Design

AutoDesign

Right click on report designer and do the following

Rename AutoDesign to as “SalesLineDesign”

Select ReportLayoutTemple to ReportLayoutTemplate

Title to “CustomSalesLineReport”

and click on save.

ReportTitle

Now expand “SalesLineDesign” design and drop and drop CustAccount in Group and Sort node. This will create the records group and line according to CustAccount

SalesLineReport

 

Now Compile the report, Deploy it and At to AOT.

 

 

Now open the AOT. Change the legal entity to “USMF” expand the menuItem node, and create a new display menu item

NewMenuItem

 

 

 

Change its Name to mnuCustomSalesLineReport

Change object Type to SSRS report and select the object is SalesLinereport and Report Design to SalesLineDesign

MenuSalesLine

 

 

Now save the menu and let’s run the report

ReportDailog

Click on ok to run the preview

Reports

Adding Dynamic Parameter

Create another query in AOT name it QCustomer, and add AccountNum is in field. You have to follow the same step which we used to create QSalesLine Query. Save the Query

DynamicQuery

Now switch back to model project in Visual studio and add new Dataset and Name it DSCustomer and pointed to QCustomer query the same way we select the Query.

DsCustomer

Now expand parameter node of report

And add new Parameter with Name “CustomerParm”

Expand Values and set

Customer

DynamicParameterparamet

Now expand Report designer and under filter node add new node

AccountFilter

Right Click on AccountFilter and from property window

Click on expression  To select = Fields!CustAccount

Operator select “Like”

And Value select =Parameters!CustomerParm.Value

FilterProperties

 

 

 

 

 

Save the report and deploy it, and then Add to AOT.

 

Open Aot and from item display menu to run report

 

You will found a new drop down for parameter in

CustomerSelection

 

 

Form this parameter You can select and run the report for specific customer

CustomerSelectionReport

{ 0 comments… add one }

Leave a Comment