Dynamics AX 2009 - AOT query object

>The tables link but I get duplicates when I run the query in a Report.
>I get EMPLID listed 3 times, it was 4 but I place a group by on Parent Table hence now 3.

In AX troubleshooting reports could be difficult. My advise would be to check the datasources for each section group in your report. BTW the purpose of the *GROUP BY* option in the report query is there to allow you to apply aggregate functions on the sected datasource.

>I use an INNERJOIN. Does anyone know how to do a select distinct (sort of speak)?

In X++ there is no DISTINCT, but you can use group by instead.
I guess you wouldn't need/miss DISTINCT most of the time.

>I have a method NumberOfMaxWorkHours under HRMPartyEmployeeRelationship table in AOT.
>How can i call this for an employee from ReportHelper.cs?

You need the Dynamics AX .Net Business Connector for this.
If you already have it, you need to add a reference to the Microsoft.Dynamics.BusinessConnectorNet.dll.
See the code example below.

Regards,
QK

var ax = new Microsoft.Dynamics.BusinessConnectorNet.Axapta();
 
try
{
    ax.Logon(null, null, null, null);
    var tbl = ax.CreateAxaptaRecord("TableName");
    tbl.Call("MethodName", parm1);
    ax.Logoff();
}
catch (Exception xcpn)
{
    MessageBox.Show(xcpn.ToString());
}            
                                              

1:
2:
3:
4:
5:
6:
7:
8:
9:
10:
11:
12:
13:

Hi,

thanks for your feedback. I have tried pasting the code into ReportHelper.cs under

public static string getMaxHours()

but build failed due to a whole heap of errors. 7 in total.

The syntax looks different to what is already in the ReportHelper.cs file ... Pretty much a standard ReportHelper.cs that come with AX 2009....

1) Do you have the .Net Business Connector installed
2) Did you reference to the BC dll
3) Can you updload the CS file
4) Can you post the errors you get

Hi,

Standard ReportHelper file attached. Please rename extension to cs.

Not sure about

1) Do you have the .Net Business Connector installed
2) Did you reference to the BC dll

New to AX 2009.

Previously developed reports in Business Objects XIR2 and MS VS2005 for SSRS 2005. AX 2009 is different with the concept of 'business logic'...

Hope that all makes sense and thanks for your help.

OK, I'll get back to you when I find a quiet moment to take a look at it.

Sorry for the delay but I had a busy weekend. As I was unable to determine the exact cause
of your problem, I decided write some step by step instructions for you:

1) Create new "Dynamics AX reporting project" in Visual Studio
2) Add new datasource
4) In the properties pane for the datasource, set the "Data Source Type" to "Query" and add the AX query
5) Create new data method
6) In the "Solution Explorer" expand the "Busines Logic" node and right-click on "Reference"
in order to add a reference to Microsoft.Dynamics.BusinessConnectorNet.dll.
The dll is usually located in   C:\Program Files\Microsoft Dynamics AX\50\Client\Bin
7) Rename and edit the method (see the code below)
8) Add new fields to the report
9) Use the Expression builder to assign the DataMethod to a field.
The syntax is like    =NumberOfMaxWorkHours(Fields!EmplId.Value)
10) Now you can add the new fields to the design of the report. In order to access the return values  of the X++ methods you may have to refresh the datasource and/or rebuild the entire solution.
After that you  can use the return value from the data-method just like the fields from the AX query.

Please note that in your AX Query you need to select only records with unique EmplIds.

using System;
using System.Collections.Generic;
using System.Security.Permissions;
using System.Data;
using Microsoft.Dynamics.Framework.Reports;
 
using Microsoft.Dynamics.BusinessConnectorNet;
 
public partial class Report1
{
    [DataMethod(), AxSessionPermission(SecurityAction.Assert)]
    public static double NumberOfMaxWorkHours(string EmplId)
    {
        AxaptaWrapper ax = SessionManager.GetSession();
        AxaptaRecord HRMPartyEmployeeRelationship = 
            (AxaptaRecord)ax.CreateAxaptaRecord("HRMPartyEmployeeRelationship").AxaptaRecord;
        
        String [] parms = {""};
 
        parms[0] = EmplId;
        HRMPartyEmployeeRelationship = 
            (AxaptaRecord)ax.CallStaticRecordMethod("HRMPartyEmployeeRelationship", "find", parms);
 
        return (double)HRMPartyEmployeeRelationship.Call("numberOfMaxWorkHours");         
    }
}
                                              

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:

That is brilliant!!!

Thank you so much for taking time out and describing the steps! Works a treat!!!

Like i said, this is new to me and these examples are a great building block!

Thanks again!

PS There will be more questions to come :-)