Link to home
Create AccountLog in
Avatar of Ausway
AuswayFlag for Australia

asked on

Dynamics AX 2009 - AOT query object

Hi Experts,

I am creating a query in Dynamics AX 2009 - AOT. EMPLTABLE is the parent table with 3 child tables (DirPartyTable, HRMPartyEmployeeRelationship & HRMEmplHiringTerms).

I have linked these tables with Relations EmplID = EmplID.

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.

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

Many thanks in anticipation

Avatar of Ausway
Ausway
Flag of Australia image

ASKER

Hello,


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

Thanks in anticipation


>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());
}            

Open in new window

Avatar of Ausway

ASKER

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
Avatar of Ausway

ASKER

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.
ReportHelper.txt
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");         
    }
}

Open in new window

ASKER CERTIFIED SOLUTION
Avatar of quale-kaizen
quale-kaizen
Flag of Bulgaria image

Link to home
membership
Create an account to see this answer
Signing up is free. No credit card required.
Create Account
Avatar of Ausway

ASKER

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 :-)

Avatar of Ausway

ASKER

Excellent service!