AX QUERY: SSRS report using Ax-Query and Ranges

In this report I am going to use the Ax-Query in the SSRS business logic and report will be generated by using SSRS Business logic.

For this report…

1) Define the sample Ax Query in Dynamics Ax as follows...

2) In the above SampleCustomers query I had defined the ranges for the Name Field.

3) Now go to VS-2008 Create New Project for Dynamics Ax SSRS reports.

4) Open the report from the Solution explorer and Add the new DataMethod to the report as follows

5) Rename the DataMethod and do the code as follows

[DataMethod(), AxSessionPermission(SecurityAction.Assert)]

public static DataTable CustomerDetails(String custAccName)

{

DataTable custDataTable = new DataTable();

IDictionary<string, object> ranges = new Dictionary<string, object>();

DataRow customersRow;

DataTable dtASCAllRows = new DataTable("Customers");

DataView dvASC = new DataView();

//Defining ranges

object[] rangeName = new object[] { "CustTable.Name" };

object[] rangeValue = new object[] { custAccName };

ranges.Add(rangeName[0].ToString(), rangeValue[0]);

// execute query - "SampleCustomers" is our Ax Query

dtASCAllRows = AxQuery.ExecuteQuery((String.Format("Select * from {0}", "SampleCustomers"))

, ranges);

dvASC.Table = dtASCAllRows;

custDataTable.Columns.Add("AccountNum", typeof(string));

custDataTable.Columns.Add("AccountName", typeof(string));

custDataTable.Columns.Add("CustGroup", typeof(string));

// Loop for fetching data for every record

for (int intLoopCounter = 0;

intLoopCounter < dvASC.Count; intLoopCounter++)

{

customersRow = custDataTable.NewRow();

customersRow["AccountNum"] = Convert.ToString(dvASC[intLoopCounter]["AccountNum"]);

customersRow["AccountName"] = Convert.ToString(dvASC[intLoopCounter]["Name"]);

customersRow["CustGroup"] = Convert.ToString(dvASC[intLoopCounter]["CustGroup"]);

custDataTable.Rows.Add(customersRow);

}

return custDataTable;

}

6) Define the new Dataset for the report.

7) Assign the properties of the dataset as follows and Map our DataMethod (CustomerDetails) to the Query Property of the DataSet.

8) Save the solution. Drag and drop the Dataset to the Designs node of the report.

9) Now we can see the preview of the report by using the Customer name as filter condition.

AX QUERY