Ausway
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, HRMPartyEmployeeRelationsh ip & 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
I am creating a query in Dynamics AX 2009 - AOT. EMPLTABLE is the parent table with 3 child tables (DirPartyTable, HRMPartyEmployeeRelationsh
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
>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 HRMPartyEmployeeRelationsh ip 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.Busines sConnector Net.dll.
See the code example below.
Regards,
QK
>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 HRMPartyEmployeeRelationsh
>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.Busines
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());
}
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....
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
2) Did you reference to the BC dll
3) Can you updload the CS file
4) Can you post the errors you get
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
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.Busines sConnector Net.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(Fiel ds!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.
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.Busines
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(Fiel
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");
}
}
ASKER CERTIFIED SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
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 :-)
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 :-)
ASKER
Excellent service!
ASKER
I have a method NumberOfMaxWorkHours under HRMPartyEmployeeRelationsh
How can i call this for an employee from ReportHelper.cs?
Thanks in anticipation