Setting AxGridView index in C# and in AX 2012

Setting AxGridView index in C# and in AX 2012

 

 

 

Sometimes we face issue with AxGridView to set the selected row as current. This may be due to number of reasons like if you are doing some task or operation in that process we are somehow running “executeQuery” of the data set.

 

Here is the small example to set the AxGridView index.

 

Step1: Add an AxGridview.

Step2: Add Data set and add fields

Step3: In the properties of AxGridView write an event “Selectedindexchanging”

onselectedindexchanging="SelectedRow_Changing"

Step4: write the below code in “SelectedRow_Changing” method.

protected void SelectedRow_Changing(object sender, GridViewSelectEventArgs e)

    {

        int i;

        i = e.NewSelectedIndex; // get the new selected index

        //Set new changed index as current row in data set.

        DataSetView dataSetView;

        dataSetView = this.MkTempSelectedDS.GetDataSet().DataSetViews["TmpFrmVirtual"];

        dataSetView.SetCurrent(i);

    }

 

 

Now we will see how we can also set through AX

 

Step1: Goto Data set, Data source execute method which you want to modify.

In my example \Data Sets\MkSalesListEntry\Data Sources\SalesLine\Methods\executeQuery

Step2: write the below code in executeQuery method

public void executeQuery()

{

    SalesLine       lSalesLine;

    lSalesLine  = SalesLine.data();

    super();

    Salesline_ds.findRecord(lSalesLine);

    Salesline_ds.setCurrent();

}

 

 

Happy Learning J

How to use QueryHavingFilter in AX 2012

How to use QueryHavingFilter in AX 2012

 

 

QueryHavingFilter:

Consider the following scenario. The CUSTTABLE table has a field called CUSTGROUP, indicating the customer group the customer belongs to. We would like to get a list of all customer groups that have less than 4 customers in them.
Traditionally, in AX queries, we can group by the CUSTGROUP field, COUNT the RecIds. However, there was no way to filter on that counted RecId field. However, in SQL, the having statement gives you that ability:

SELECT CUSTGROUP, COUNT(*) FROM CUSTTABLE

        GROUP BY CUSTGROUP

        HAVING COUNT(*) < 4

In AX you can count, group by, but you'll need to loop over the results and check the counter manually if you want to filter values out. So, in AX 2012, a new query class was added: QueryHavingFilter, that lets you do just that:

static void QueryHaving(Args _args)

{

    Query                   query;

    QueryBuildDataSource    datasource;

    QueryBuildRange         range;

    QueryHavingFilter       havingFilter;

    QueryRun                queryRun;

    int                     counter = 0, totalCounter = 0;

    CustTable               custTable;

   

    query = new Query();

    datasource = query.addDataSource(tableNum(CustTable));

    datasource.addSelectionField(fieldNum(CustTable, RecId),

            SelectionField::Count);

    datasource.orderMode(OrderMode::GroupBy);

    datasource.addGroupByField(fieldNum(CustTable, CustGroup));

   

    havingFilter = query.addHavingFilter(datasource, fieldStr(custTable, RecId),

            AggregateFunction::Count);

    havingFilter.value('<4 o:p="">

   

    queryRun = new QueryRun(query);

    while (queryRun.next())

    {

        custTable = queryRun.getNo(1);

        info(strFmt("Group %1: %2", custTable.CustGroup, custTable.RecId));

    }

}

Note that in this code example, I added a selection field on RecId and used SelectionField::Count. This is not necessary for the having filter to work, the only reason it is in the code example is to be able to show it in the infolog (ie to have the count value available). So it is independent of the HavingFilter!

Difference between QueryBuildRange and QueryFilter

Difference between QueryBuildRange and QueryFilter

 

 

Let's look at the following scenario. We have a customer table, CustTable, and a sales order table, SalesTable, which has a foreign key relationship to the CustTable based on CustAccount. Let's say we want to retrieve a list of customers, and optionally any sales orders associated with each customer. To accomplish this, one would use an outer join. In SQL, this would translate as follows:

SELECT * FROM CUSTTABLE

        OUTER JOIN SALESTABLE ON SALESTABLE.CUSTACCOUNT = CUSTTABLE.ACCOUNTNUM

So far so good. Now let's say we want to show all customers, and show all sales orders associated with each customer, but ONLY the orders with currency EUR... In SQL, this gives us TWO options:

SELECT * FROM CUSTTABLE

        OUTER JOIN SALESTABLE ON SALESTABLE.CUSTACCOUNT = CUSTTABLE.ACCOUNTNUM

        AND SALESTABLE.CURRENCYCODE = 'EUR'

or

SELECT * FROM CUSTTABLE

        OUTER JOIN SALESTABLE ON SALESTABLE.CUSTACCOUNT = CUSTTABLE.ACCOUNTNUM

        WHERE SALESTABLE.CURRENCYCODE = 'EUR'

So what's the difference? In the first option, we use AND, which means the currencycode is part of the JOIN ON statement filtering the SALESTABLE. In the second option, using the WHERE keyword, the currencycode is part of the query's selection criteria... so what's the difference? If we filter the SALESTABLE using the ON clause, the CUSTTABLE will still show up, even if no SALESTABLEs with currency EUR exist, and it will just filter the SALESTABLE records. However, using a WHERE clause, we filter the complete resultset, which means no CUSTTABLE will be returned if there are no SALESTABLE records exist with EUR as the currency.

That is exactly the difference between QueryBuildRange and QueryFilter when used on an outer join. The QueryBuildRange will go in the ON clause, whereas QueryFilter will go in the WHERE clause. The following job illustrates this, feel free to uncomment the range and comment the filter, and vice versa, and test the results for yourself.

static void QueryRangeFilter(Args _args)

{

    Query                   query;

    QueryBuildDataSource    datasource;

    QueryBuildRange         range;

    QueryFilter             filter;

    QueryRun                queryRun;

    int                     counter = 0, totalCounter = 0;

    query = new Query();

    datasource = query.addDataSource(tableNum(CustTable));

    datasource = datasource.addDataSource(tableNum(SalesTable));

    datasource.joinMode(JoinMode::OuterJoin);

    datasource.relations(true);

    datasource.addLink(fieldNum(CustTable, AccountNum),

            fieldNum(SalesTable, CustAccount));

    filter = query.addQueryFilter(datasource,

            fieldStr(SalesTable, CurrencyCode));

    filter.value(SysQuery::value('EUR'));

    //range = datasource.addRange(fieldNum(SalesTable, CurrencyCode));

    //range.value(SysQuery::value('EUR'));

    queryRun = new QueryRun(query);

    while (queryRun.next())

    {

        totalCounter++;

        if (queryRun.changed(tableNum(CustTable)))

            counter++;

    }

    info(strFmt("Customer Counter: %1", counter));

    info(strFmt("Total result Counter: %1", totalCounter));

}

 

for more info: http://daxmusings.codecrib.com/2011/09/query-and-new-related-objects-in-ax.html

 

SSRS report development using Report Data Provider (RDP) Class [AX 2012]

SSRS report development using Report Data Provider (RDP) Class [AX 2012]

Overview

There are multiple methods to develop SSRS reports in Microsoft Dynamics AX 2012. This tutorial will guide you in developing Report Data Provider (RDP) based SSRS reports.

RDP based SSRS Reports are used when complex business logic cannot be achieved using AOT query.

Important Concepts
1.     Report Data Provider (RDP) Class

Report Data Provider Class is an X++ class that is used to access and process data for a SSRS report. The RDP class processes the business logic based on a specified parameter and/or query and returns a dataset to the reporting services. In order to create a RDP class in AX, you have to extend that class with SRSReportDataProviderBase. This tells AX that this class will be used by reporting services to process the data.

Two important attributes are used in RDP classes:

1.        SRSReportQueryAttribute: specifies which AOT query will be used in this report. If the RDP class uses an AOT query to process data, define this attribute at the beginning of the class.

2.        SRSReportParameterAttribute: defines the data contract class that will be used by this report to prompt for parameter values. If the RDP class contains any parameters this define this attribute at the beginning of the class.

Both the attributes are optional. If the report does not use any query or does not want any parameter to filter report data, these attributes do not need to be used.

2.     Data Contract Class

A data contract class is an X++ class which contains parm methods with the DataMemberAttribute defined at the beginning of the method. This class is used to define one or more parameters that will be used in a SSRS report.

3.     Table

An AX table is used as the dataset to store data for the report. The RDP class processes the data and stores it in the table which is then used by a SSRS report to render data.

A table can be a temporary table (InMemory or TempDB) or a regular table, but it is Microsoft best practice to use a temporary table.

The type of temporary table is based upon the performance considerations. InMemory temporary table is used when the data set is small, while TempDB is normally used for larger datasets to improve performance.

Scenario

As part of this tutorial, the report will print a list of customers and their invoiced sales order counts.

Steps

1.  First of all, create a temporary table. Open AOT à Date Dictionary à Tables.

2.  Right Click on Tables and create a new Table called CustReportRDPDemoTmp.

3.  Set the TableType property to InMemory. This will define the table as a temporary table.

4.  Expand the CustReportRDPDemoTmp table node and add the following fields in the table:
 

S. No.	Field name	Extended Data Type	Label
1	CustAccount	CustAccount
2	Name	Name
3	SalesOrderInvoiceCount	Integer	Sales order invoiced

5.  Now create a RDP class. Go to Classes and create a new class called CustReportRDPDemoDP by right clicking on Classes and selecting New Class. It is a best practice to suffix the RDP class name with DP .

6.  Open the Class declaration by right clicking on it and selecting View code.

7.  Now write the following code:

class CustReportRDPDemoDP extends SRSReportDataProviderBase { //Temporary table buffer CustReportRDPDemoTmp custReportRDPDemoTmp; }

1 2 3 4 5

class CustReportRDPDemoDP extends SRSReportDataProviderBase { //Temporary table buffer CustReportRDPDemoTmp custReportRDPDemoTmp; }

8.  Add a new method and name it getCustReportRDPDemoTmp. This method is mandatory because reporting services uses this method to get the table buffer containing the processed data. The SRSReportDataSetAttribute attribute is used to indicate the temporary table name and also tells the reporting services to use this method to retrieve the processed data.

9.     Write the following code in the method:

[SRSReportDataSetAttribute(tablestr('CustReportRDPDemoTmp'))] public CustReportRDPDemoTmp getCustReportRDPDemoTmp() { //select data from table buffer select * from custReportRDPDemoTmp; //return the buffer return custReportRDPDemoTmp; }

1 2 3 4 5 6 7 8

[SRSReportDataSetAttribute(tablestr('CustReportRDPDemoTmp'))] public CustReportRDPDemoTmp getCustReportRDPDemoTmp() { //select data from table buffer select * from custReportRDPDemoTmp; //return the buffer return custReportRDPDemoTmp; }

10.     Add a new method and name it processReport. This method contains the business logic and is called by reporting services to generate data.

11.     This method will query customer details and fill the temporary table buffer. Write the following code in the method:

///<summary> /// Processes the SQL Server Reporting Services report business logic /// </summary> /// <remarks> /// This method provides the ability to write the report business logic. This method will be called by /// SSRS at runtime. The method should compute data and populate the data tables that will be returned /// to SSRS. /// </remarks> public void processReport() { CustTable custTable; SalesTable salesTable; //select all customers while select * from custTable { //clear the temporary table custReportRDPDemoTmp.clear(); //assign customer account and name custReportRDPDemoTmp.CustAccount = custTable.AccountNum; custReportRDPDemoTmp.Name = custTable.name(); //select count of invoiced sales order of customer select count(RecId) from salesTable where salesTable.CustAccount == custTable.AccountNum && salesTable.SalesStatus == SalesStatus::Invoiced; custReportRDPDemoTmp.SalesOrderInvoiceCount = int642int(salesTable.RecId); //insert in temporary table buffer custReportRDPDemoTmp.insert(); } }

1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29

/// /// Processes the SQL Server Reporting Services report business logic ///

///

/// This method provides the ability to write the report business logic. This method will be called by

/// SSRS at runtime. The method should compute data and populate the data tables that will be returned

/// to SSRS.

///

public void processReport()

{

CustTable custTable;

SalesTable salesTable;

//select all customers

while select * from custTable

{

//clear the temporary table

custReportRDPDemoTmp.clear();

//assign customer account and name

custReportRDPDemoTmp.CustAccount = custTable.AccountNum;

custReportRDPDemoTmp.Name = custTable.name();

//select count of invoiced sales order of customer

select count(RecId) from salesTable

where salesTable.CustAccount == custTable.AccountNum

&& salesTable.SalesStatus == SalesStatus::Invoiced;

custReportRDPDemoTmp.SalesOrderInvoiceCount = int642int(salesTable.RecId);

//insert in temporary table buffer

custReportRDPDemoTmp.insert();

}

}

12.     Now create a new report. Since the development of a SSRS report is done in Visual studio, we first need to create a new project in Visual studio.

13.     Open Visual studio. Go to File à New à Project

14.     In the Installed templates section select Microsoft Dynamics AX and then select Report Model in the right pane. Name the project RDPBasedDemo and press Ok.

15.     Now add a new report in the project by right clicking on the project RDPBasedDemo à Add à Report.

16.     A report will be added to the project with the name Report1. Rename the report RDPBasedDemo.

17.     Now double click the report to open it.

18.     The description of the individual node is given below:

a.  Datasets: Datasets retrieve data from RDP class. It acts as a bridge between AX and the SSRS report. Only the fields added in the datasets can be used in a report.

b.  Designs: It defines the layout of the report.

c.  Images: It contains the images that you want to display in the SSRS report.

d.  Data Methods: It contains the business logic which can then be used in the report.

e.  Parameters: It is used to apply filtering to the data in a report. All the parameters defined in the data contract class are automatically added here when the RDP class is defined in the datasets.

19.     Now you will want to create a new Dataset by right clicking Datasets àAdd Dataset. Name it CustDetail.

20.     Select the CustDetail dataset and open the properties window. Set the Data Source Type to Report Data Provider. Then select the Query field. An ellipse button appears. Click it to open a dialog box.

21.     This dialog box lists all the RDP classes present in the AOT. Select CustReportRDPDemoDP and press Next.

22.     Select the fields to be displayed in the report and press OK. Only the fields selected in this dialog box can be shown in the report.

23     There are two types of designs that can be created in a SSRS report:

a.  Auto design: Visual studio automatically creates a design based on the dataset provided. Auto design is the preferred method because it is easy and usually fulfills the majority scenarios.

b.  Precision Design: This is used when you need custom placement of fields or the layout of the report is too complex.

24.     In this demo we will use Auto Design. Now right click the Designs nodeàAdd àAuto Design. A new design is added. Rename it Design. It is recommended that you set the name of the Design to either ‘Design‘ or ‘Report‘.

25.     Now drag the CustDetail form to the Datasets node and drop it on the Design node. A table will be created which contain all the fields present in the data set. These fields will appear in the same order as in the report. So if you want to arrange the fields, right click the field and select either ‘move up’ or ‘move down’.

26.     Now we have to define the layout of the report. Visual studio provides built in templates. Select the Design and open the properties window. Select ReportLayoutStyleTemplate in the LayoutTemplate field. Give a suitable title to the report.

27.     Select CustDetailTable under the Design node and open the properties window. Select TableStyleAlternatingRowsTemplate in the Style Template field.

28.     The report is now completed and can be viewed. To preview the report, select the Design node, right click it and select preview.

29.     Select the Report tab. To view the report.