Home > Dynamics Ax > Dynamics Ax – Simple SSRS report example by using Ax-Query and Ranges

Dynamics Ax – Simple SSRS report example by 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.

Smile…..Gift with a bow

Advertisements
Categories: Dynamics Ax
  1. Hani
    January 18, 2010 at 7:01 am

    Very nice article Santosh, I never thought that is simply done like this.

  1. No trackbacks yet.

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

%d bloggers like this: