Creating a Matrix Report [AX 2012]

In this walkthrough, you will create a report to display customer transaction data in a matrix data region. A matrix data region displays data in a two-dimensional grid of columns and rows that intersect at specific data points. When defining a matrix data region, you can specify column and row groupings. The data that appears in the detail cells are aggregates based on the intersections of columns and rows.
This walkthrough illustrates the following tasks:

• Defining a query
  
• Creating a reporting project
  
• Creating a report with a matrix data region
  

Prerequisites

To complete this walkthrough, you will need:

• Microsoft Dynamics AX with sample data
  

  Note
  This walkthrough uses the CustTable, CustTrans, and CustGroup tables. In order to view data in the report, these tables must contain data.

• Microsoft Visual Studio 2010
  
• Microsoft Dynamics AX tools for Microsoft Visual Studio
  

Defining a Query

There are several ways to retrieve data for reports. In this walkthrough, you will use a query that is defined in the Microsoft Dynamics AX development workspace. The following procedure explains how to define the query that will be used to retrieve customer transaction data.

To define a query

1. In the Microsoft Dynamics AX development workspace, open the AOT.
   
2. In the AOT, right-click the Queries node, and then click New Query.
   
3. Right-click the node for the query, click Rename, and then type CustTransactions.
   
4. Expand the CustTransactions query.
   
5. In the AOT, right-click the Data Dictionary node, and then click Open New Window.
   
6. In the new window, expand the Tables node.
   
7. Locate the CustTable table and drag it onto the Data Sources node for the query.
   
8. Expand the node for the CustTable_1 data source.
   
9. Select the Fields node, in the Properties window, set the Dynamic property to Yes.
   
10. Locate the CustTrans table and drag it onto the Data Sources node located within the CustTable_1 data source.
    
11. Expand the node for the CustTrans_1 data source.
    
12. Select the Fields node, in the Properties window, set the Dynamic property to Yes.
    
13. Locate the CustGroup table and drag it onto the Data Sources node located within the CustTrans_1 data source. This will be three levels down the hierarchy.
    
14. Expand the node for the CustGroup_1 data source.
    
15. Select the Fields node, in the Properties window, set the Dynamic property to Yes.
    
16. Right-click the Relations node for the CustTrans_1 data source, and then click New Relation.
    
17. Select the node for the relation and specify the following values in the Properties window.
    

    Property	Value
    JoinDataSource	CustTable_1
    Field	AccountNum
    Related Field	AccountNum

18. Expand the node for the CustGroup_1 data source.
    
19. Right-click the Relations node for the CustGroup data source, and then click New Relation.
    
20. Select the node for the relation and specify the following values in the Properties window.
    

    Property	Value
    JoinDataSource	CustTable_1
    Field	CustGroup
    Related Field	CustGroup

21. Save the query.
    

Creating a Reporting Project

Next, you will create a reporting project. In this walkthrough, you will use the Report Model template. This will give you access to several predefined layout and style templates that you will use to define the layout of your reports. These templates are the standard templates for Microsoft Dynamics AX reports.

To create a reporting project

1. Open Microsoft Visual Studio.
   
2. On the File menu, point to New and then click Project. The New Project dialog box is displayed.
   
3. In the Installed Templates pane, click the Microsoft Dynamics AX node. In the Templates pane, click Report Model.
   
4. In the Name box, type SampleMatrixReport, and in the Location box, type a location.
   
5. Click OK.
   

Creating a Report with a Matrix Data Region

Next, in the reporting project, you will create a report that displays customer transaction data in a matrix data region. You will define a column grouping based on the TransDate field and two row groupings based on the Name and AccountNum fields. The following procedure explains how to create the report.

To create a report with a matrix data region

1. In Solution Explorer, right-click the project, point to Add, and then click Report.
   
2. In Model Editor, right-click the Report1 node and then click Rename.
   
3. Type SampleMatrixReport as the name.
   
4. Right-click the Datasets node for the report, and then click Add Dataset.
   
5. Select the node for the dataset.
   
6. In the Properties window, specify the following property values.
   

   Property	Value
   Data Source	Dynamics AX
   Data Source Type	Query
   Default Layout	Matrix
   Name	CustTransactions
   Query	Click the ellipsis button (…). A dialog box displays where you can select a query that is defined in the AOT and identify the fields that you want to use. Select the CustTransactions query and then click Next. Expand the All Fields node for CustTable. Select the AccountNum field from the CustTable table. Expand the CustTrans > All Fields node, select the AmountMST and TransDate fields from the CustTrans table. Expand the CustGroup > All Fields node, select the Name field from the CustGroup table. Click OK to save the field selection. The Query field is now SELECT CustTable.1.AccountNum,CustTrans.1.TransDate,CustTrans.1.AmountMST,CustGroup.1.Name FROM CustTransactions

7. In Model Editor, select the Datasets > CustTransactions > Fields > AmountMST node.
   
8. In the Properties window, set the Aggregate Function property to Sum and set the Format String property to Currency.
   
9. In Model Editor, drag the CustTransactions node onto the Designs node. An auto design named AutoDesign1 is created for the report.
   
10. Expand the AutoDesign1 node, expand the node for the matrix data region, and then expand the Data node.
    
11. Drag the TransDate field from the Data node onto the Column Groupings node.
    
12. Select the TransDate node.
    
13. In the Properties window, type the expression =Year(Fields!TransDate.Value) for the Label property.
    
14. In Model Editor, expand the TransDate node, expand the Group on node, and then select the TransDate node that is located below the Group on node.
    
15. In the Properties window, type the expression =Year(Fields!TransDate.Value) for the Expression property.
    
16. In Model Editor, drag the Name field from the Data node onto the Row Groupings node.
    
17. Select the Name node.
    
18. In the Properties window, set the Display Width property to 1in and set the Display Subtotals property to False.
    
19. In Model Editor, drag the AccountNum field from the Data node onto the Row Groupings node.
    
20. Select the AccountNum node.
    
21. In the Properties window, set the Display Width property to 1in and set the Display Subtotals property to False.
    
22. In Model Editor, select the AmountMST node that is located below the Data node.
    
23. In the Properties window, set the Display Width property to 1in, and set the Text Align property to Right.
    

To apply layout and style templates

1. In Model Editor, select the AutoDesign1 node.
   
2. In the Properties window, set the Layout Template property to ReportLayoutStyleTemplate. Also, type Customer transactions for the Title property.
   
3. In Model Editor, expand the AutoDesign1 node, and then select the CustTransactionMatrix node.
   
4. In the Properties window, set the Style Template property to MatrixStyleTemplate. Also, delete the default text for the Title property so that it does not display a title for the data region.
   
5. In Model Editor, right-click the AutoDesign1 node, and then click Preview. Click the Report tab to see the report. The report displays a list of customer transactions with customer groups down the left side and years across the top.
   
6. Close the Preview window.
   

Read more...

This virtual machine appears to be in use .vmdk - how to resolve?

Issue :-

This virtual machine appears to be in use.  If this virtual machine is already in use, press the "Cancel" button to avoid damaging it. If this virtual machine is not in use, press the "Take Ownership" button to obtain ownership of it.

Solution :-

you will have .lck folders in the directory where you will have the .vmdk file. Delete them and try again. 

 

real work....!!!!

Read more...

Tips & Tricks around debugging X++ code in Dynamics AX

When you are developing X++ code for Dynamics AX over the time you will for sure run into a situation where the below Tips & Tricks can make your debugging live easier. The information are no secrets it is just hard finding them the very first time you need them. Since I was also once in the situation needing to find them, I thought it is maybe a good idea to collect the most useful ones I'm using day to day...

The most oblivious one: Interrupting the code execution
When you have written some code that is causing an endless loop (or a very long execution) and you want to know what code is currently keeping the client busy the easiest way is pressing [Ctrl] + [Break] on the keyboard.
This will stop the code execution and show up a message box: Are you sure that you want to cancel this operation?
If you hold down the [Shift] button on the keyboard while you click at the No button of the message box, the AX Debugger will be launched exactly at the line of code that was about to be executed. From here you can continue to step over / into line by line and try to identify the cause of your endless loop.

The breakpoint is sometimes not hit?
Sometimes it happens that a breakpoint is not hit although you are sure the respective code was executed. For example if you want to debug code behind User Controls like Form buttons this can happen.
Here instead of putting a breakpoint at a line of code insert the breakpoint keyword.

public void executeQuery()
{
   ;
   breakpoint;   super();
}

Please note that using breakpoint has a serious side effect:
The breakpoint will be hit by all user sessions! This is definitely something you don't want to do in a live environment.

How can I see the call stack that resulted in a X++ exception?
When you run into an exception that is showing up in the InfoLog usually double clicking at the error brings you to the line of code where the error was raised.
Sometimes knowing the line of code where the error was raised is however not good enough as the individual call stack is what you need to know. Putting a breakpoint at the line of code where the exception was raised is helpful, what however if you are in a loop and you don't know what loop iteration results in the error especially if the error is random?
Instead of putting the breakpoint at the line of code where the exception might be raised, put a breakpoint in the add method of the Info class, e. g. at the line switch (logLevel).

Exception add(
   // ...
   ;
   switch (logLevel)
   // ...

Info::add is always called when something is written to the InfoLog and since an exception shows up in the InfoLog as soon as the exception was raised the Info::add method is called and here you will hit your breakpoint.

How can I get the current X++ call stack?
The static method xppCallStack() of the xSession Kernel class returns the current X++ call stack:

   //...
   Info(con2str( xSession::xppCallStack() ));
   //...

The function funcname() returns the name of the method where it has been called.

public void executeQuery()
{
   ;
   Info( funcname() ); //In this case "executeQuery" will show up in the InfoLog

   super();
}

How can I see the SQL query that the Dynamics AX Kernel is generating for my Form?
When you are debugging your solution you sometimes might want to see the SQL Query the Dynamics AX Kernel has generated for your Form. While you can of course trace this directly at your Database Server, you also can do this in X++ using the QueryBuildDataSource class.
Let's imagine you have a Form that has PurchTable as Data Source. You have to override the executeQuery method of the PurchTable Data Source and add the following code:

public void executeQuery()
{
   Query q;
   QueryBuildDataSource qbds;
   ;
   q = this.query();
   qbds = q.dataSourceName("PurchTable"); //Replace with the current Table
   info( qbds.toString() );

   super();
}

Read more...

Create Primary Key at AX 2012

At The Properties Index > AllowDuplicates change NO.

Read more...