How to create entity relationship diagram (ERD) in AX 2012

Recently I have to generate a ER-diagram for specific list of tables in Dynamics Ax.  Dynamics Ax has built in feature.

Prerequisite of this article has Microsoft Visio.

Create a project for all tables which require in ER-diagram.

For this article I  created a new project with Name customer Address and drop CustTable and DirpartyTable in it.

In AOT or development workspace. Click on tools and reverse engineer menu .

Following form will open, Select location where file with ERX will be generated. Select your private or public projects.

Click ok to generate it.

When file is generated, go on windows start up menu and open MS visio. Select database modeling Diagram Template.

When Database Model diagram is open, From database menu=> Import=> Import Erwin ERX file…

load erx file generated in previous step. This menu is only available when you select Database diagram as template in Visio.

click on Browse.. button and load erx file.

Click ok

A small dialog shows the import status

Next step you have to enable view for Tables, So you can select tables for ER-Diagram

Database=>View=> Tables and views

You will found following pane at left side of screen.

Click on required table to add it on page and it will show required tables with relationship.

You can re-size these diagrams.

Ref:- https://community.dynamics.com/ax/b/alirazatechblog/archive/2015/03/31/how-to-generate-entity-relation-diagram-for-specific-tables-in-dynamics-ax-2012

How to connect or access external database from AX 2012

Sometimes we have to communicate with outside of Dynamics Ax with in boundaries of Dynamics ax.

Consider following scenario where I have to communicate with StudentDb in Sql server and insert rows in Student Info. We can do this with odbc connection.

Now create New AX job and paste following code there

LoginProperty loginProperty;

OdbcConnection odbcConnection;

Statement statement;

ResultSet resultSet;

LoginProperty myLoginProperty;

str sql, criteria;

int output;

SqlStatementExecutePermission perm;

str myUserName="dynamicworld\\aliraza.zaidi";

str myPassword="abcd";

str myConnectionString;

 

;

 

 

 

 

myConnectionString=strfmt("UID=%1;PWD=%2",myUserName,myPassword);

myLoginProperty = new LoginProperty();

myLoginProperty.setServer("WIN-IKPOSIU2SGD");

myLoginProperty.setDatabase("studentdb");

myLoginProperty.setOther(myConnectionString);

 

//Create a connection to external database.

odbcConnection = new OdbcConnection(myLoginProperty);

 

if (odbcConnection)

{

 

sql ="INSERT INTO ..[StudentInfo]([FirstName],[LastName]) VALUES ('aliraza','zaidi')";

//Assert permission for executing the sql string.

perm = new SqlStatementExecutePermission(sql);

perm.assert();

 

//Prepare the sql statement.

statement = odbcConnection.createStatement();

output = statement.executeUpdate(sql);

 

 

statement.close();

}

else

{

error("Failed to log on to the database through ODBC.");
}

Ref:- https://community.dynamics.com/ax/b/alirazatechblog/archive/2014/10/18/connect-to-an-external-database-from-dynamics-ax-2012

How to clear the cache of all clients in AX 2012

According to that reference we have to reset the GUID, so all Clients have to reset its cache, this method required AOS restart.

static void ResetAUC(Args _args)
{
#AiF
SysSQMSettings _Settings;
;
ttsbegin;
update_recordset _Settings setting GlobalGUID = str2Guid(#EmptyGuidString);
ttscommit;
info("Now we have to Restart the AOS Service to use a new GlobalGUID.");
}

Its works for me.

Ref:- https://community.dynamics.com/ax/b/alirazatechblog/archive/2014/08/10/clear-the-cache-on-all-clients-dynamics-ax-2012

List of functions in SSRS reports in AX 2012

During working in SSRS reports I have to handle division by Zero error for calculating accumulated cost by dividing Amount by Quantity.

This expression works for me.

IIf(Sum(Fields!xyz.Value) = 0, “N/A”, Sum(Fields!abc.Value) / IIf(Sum(Fields!abc.Value) = 0, 1,Sum(Fields!xvz.Value)))

I did little research on Expressions and function used in these SSRS expressions

Expressions:

Expressions are used for manipulate or update the value, for example, cost show by dividing amount by Quantity. Rounding of decimal. Similarly Expression can be used to highlight the filed if condition meets. Style font can be changed, even you can uses switch statement, to change the display value of report.

Types of Expressions

Globals
Operators – Arithmetic, Comparison, Logical
Common Functions – Text, Date & Time, Math, Inspection, Program Flow, Aggregate, Financial, Conversion, Miscellaneous

We can see each and every one very detail in following.

Globals

Global expressions executes/works in Page Header and Footer parts only.

ExecutionTime shows date and time at when report executes
PageNumber shows page number of each and every page but allowed only in page header and footer
ReportName displays name of the active report what name we have assigned to the active report
UserId shows current user name like company/alirazazaidi
Language displays language like US-English…

Operators

Arithmetic
^ power of
* multiplication
/ divides two numbers and returns a floating point result
\ divides two numbers and returns a integer result
Mod divides two numbers and returns remainder only
+ adds two numbers and concatenation for two strings
- subtraction and indicates negative value for numeric values
Comparison
Known operators : < <= > >= <> 
Like compares two strings and return true if matched or else returns False. Ex: =Fields!Title.Value Like Fields!LoginID.Value
Is compare two object reference variables Ex: = Fields!Title.Value Is Null
Concatenation
+ and & symbols uses for concatenation
Logical
Known: And, Not, Or 
Xor SELECT * FROM users where firstname = ‘Larry’ XOR lastname = ‘Smith’
AndAlso First condition will check first and if it is true only, goes to next or else it won’t need to check. Because our execution time is saving in a logical operation in which more conditions is combined using AndAlso function.
OrElse same like above

Common Functions

Text

Asc, AscW returns an integer value represents character code corresponding to a character

Chr, chrw returns the character associated with the specified character code

Filter =Filter(Fields!Title.Value,”Pr”,true,0

Format
=Format(Fields!Price.Value, “#,##0.00″), Format(Fields!Date.Value, “yyyy-MM-dd”)

FormatCurrency =formatcurrency(Fields!SickLeaveHours.Value,3)

FormatDateTime =FormatDateTime(Fields!BirthDate.Value,Integer)
Examples:
0 returns 10/10/2014
1 returns Friday, October 10, 2014
2 returns 6/3/2014
3 returns 12:00:00AM
4 returns 00:00

FormatNumber =FormatNumber(Fields!EmployeeID.Value,2)
Examples: 4.00

FormatPercent =”Percentage : ” & formatpercent(Fields!SickLeaveHours.Value)

GetChar =GetChar(Fields!Title.Value,5)

InStr =InStr(Fields!Title.Value,”a

InStrRev =Instrrev(Fields!Title.Value,”a

LCase Change strings into lower case
=Lcase(Fields!Title.Value)

Left Returns left side characters from a string
=Left(Fields!Title.Value,4)

Len Finds length of a string
=Len(Fields!Title.Value)

LSet Returns some length of a string from left
=Lset(Fields!Title.Value,5)

LTrim Trim left side of a string
=Ltrim(” “&Fields!Title.Value)

Mid Returns characters from the mentioned starting position
=Mid(Fields!Title.Value,InSTrRev(Fields!Title.Value,”T

Replace Replaces one string with another
=Replace(Fields!Title.Value,”a”,”A

Right Returns right side characters from a string
=Right(Fields!Title.Value,10)

RSet Returns some length of a string from left
=Rset(Fields!Title.Value,5)

RTrim Trim left side of a string
=Rtrim(Fields!Title.Value & ” “)

Space Specifies some spaces within strings
=Fields!Title.Value & Space(5) & Fields!Title.Value

StrComp Returns a value indicating the result of a string comparison

vbBinaryCompare 0 Perform a binary comparison.
vbTextCompare 1 Perform a textual comparison.
string1 is less than string2 -1
string1 is equal to string2 0
string1 is greater than string2 1
string1 or string2 is Null Null

StrConv
=Strconv(Fields!Title.Value,vbProperCase)
=Strconv(Fields!Title.Value,vbLowerCase)
=Strconv(Fields!Title.Value,vbUpperCase)

StrDup Returns a string or object consisting of the specified character repeated the specified number of times.
=StrDup(3,”M”)

StrReverse =StrReverse(Fields!Title.Value)

Trim =Trim(” “& Fields!Title.Value & ” “)

UCase =Ucase(Fields!Title.Value)

Date & Time

CDate Converts a object into date format
=Format(CDate(Fields!BirthDate.Value),”MMMM”)

DateAdd Returns a datetime that is the result of adding the specified number of time interval units to the original datetime.

=dateadd(“m”,12,Fields!BirthDate.Value)

DateDiff Find number of days, months and years between two dates
=datediff(“d”,Fields!BirthDate.Value,Now)

DatePart DatePart(DateInterval.Weekday, CDate(“2009/11/13″), FirstDayOfWeek.Monday) returns 5 (Friday)

DateSerial for first day of the month
=DateSerial(Year(Now), Month(Now), 1)
for the last day of the month
=DateSerial(Year(Now), Month(Now)+1, 0)

DateString Returns string value of system date
=datestring()

DateValue Returns current date

Day Returns day value from date
=day(Fields!BirthDate.Value)

FormatDateTime =FormatDateTime(Fields!BirthDate.Value,Integer)
Examples:
0 returns 6/3/2014
1 returns Friday, June 03, 2014
2 returns 6/3/2014
3 returns 12:00:00AM
4 returns 00:00

Hour =Hour(Fields!BirthDate.Value)

Minute =Minute(Fields!BirthDate.Value)

Month =Month(Fields!BirthDate.Value)

MonthName =MonthName(Month(Fields!BirthDate.Value))

Now Indicates current month
=Now() or =Now

Second =Second(Fields!BirthDate.Value)

TimeOfDay =TimeOfDay()
Returns a date value containing the current time of day according to your system

Timer =Timer()
Returns number of seconds elapsed since midnight

TimeSerial =TimeSerial(24,60,60)
Returns a date value representing a specified hour, minute and second

TimeString =TimeString()
Returns string value representing the current time of day according to your system

TimeValue Returns a date value set to jan 1 of year 1
=TimeValue(Fields!BirthDate.Value)

Today Returns Current date

Weekday Returns an integer value representing day of week
=WeekDay(Fields!BirthDate.Value)

WeekdayName =WeekdayName(Weekday(Fields!BirthDate.Value))
Returns name of the day of week

Year =year(Fields!BirthDate.Value)
Returns year of specified date

Math

Abs Returns the absolute value
=Abs(-2.36)

BigMul Returns multiplication value of two specified numbers
=BigMul(2,3)

Ceiling Returns next highest value
=Ceiling(2.67)

Cos
=Cos(2.33)
Returns cos value for specified number

Cosh
Returns hyperbolic cos value
=Cosh(2.33)

DivRem
=DivRem(23,2,5)

Fix
=Fix(23.89)
Returns integer portion

Floor
=Floor(24.54)
Returns largest integer

Int
=Int(24.78)
Returns integer portion of a number

Log
=Log(24.78)
Returns logarithm value

Log10
=Log10(24.78)
Returns the base 10 logaritm value

Max
=Max(Fields!EmployeeID.Value)
Returns larger value in the specified values

Min
=Min(Fields!EmployeeID.Value)
Returns smaller value in the specified values

Pow
=Pow(Fields!EmployeeID.Value,2)
Returns power of value for specified number

Rnd
=Rnd()
Returns a random number

Round
=Round(43.16)
Returns rounded value to the nearest integer

Sign
=Sign(-34534543)

Sin
=Sin(Fields!EmployeeID.Value)
Returns the sin value

Sinh
=Sinh(Fields!EmployeeID.Value)
Returns the hyperbolic sin value

Sqrt
=Sqrt(Fields!EmployeeID.Value)
Returns square root value

Tan
=Tan(Fields!EmployeeID.Value)
Returns the tan value

Tanh
=Tanh(Fields!EmployeeID.Value)
Returns the hyperbolic tan value

Inspection
IsArray
=IsArray(Fields!EmployeeID.Value)
Returns a boolean value indicating whether the specified object is array or not

IsDate
=IsDate(Fields!BirthDate.Value)
Returns a boolean value indicating whether the specified object is Date or not

IsNothing
=IsNothing(Fields!EmployeeID.Value)
Returns a boolean value depends on specified object is Nothing or not

IsNumeric
=IsNumeric(Fields!EmployeeID.Value)
Returns a boolean value depends on specified object is Numeric value or not

Program Flow

Choose
=CHOOSE(3, “Red”, “Yellow”, “Green”, “White”)
Returns a specific value using index in a list of arguments

IIf
=IIF(Fields!EmployeeID.Value>10,”Yes”,”No
Returns any one value depends on condition

Switch
=Switch(Fields!EmployeeID.Value<10,”Red
Fields!EmployeeID.Value>10,”Green
Evaluates list of expressions

Aggregate

Avg
=Avg(Fields!EmployeeID.Value)
Returns average value for all specified values

Count
=Count(Fields!EmployeeID.Value)
Returns count of all specified values

CountDistinct
=CountDistinct(Fields!EmployeeID.Value)
Returns count of all distinct values

CountRows
=CountRows()
Returns count of rows

First
=First(Fields!EmployeeID.Value)
Returns first for all specified values

Last
=Last(Fields!EmployeeID.Value)
Returns last for all specified values

Max
=Max(Fields!EmployeeID.Value)
Returns max for all specified values

Min
=Min(Fields!EmployeeID.Value)
Returns min for all specified values

StDev
=StDev(Fields!EmployeeID.Value)
Returns standard deviation value

StDevP
=StDevP(Fields!EmployeeID.Value)
Returns Population standard deviation value

Sum
=Sum(Fields!EmployeeID.Value)
Returns sum of all values

Var
=Var(Fields!EmployeeID.Value)
Returns variance of all values

VarP
=Var(Fields!EmployeeID.Value)
Returns population variance of all values

RunningValue
=RunningValue(Fields!EmployeeID.Value,sum,nothing)
Returns running aggregate of the specified
expression

Financial

DDB DDB (Double Declining Balance) method computes depreciation of an asset for a specified period.
Syntax: DDB (Cost, Salvage, life, period, factor)

FV FV (Future Value) of an investment based on periodic, constant payments and a constant interest rate.
Syntax: FV (rate, nper, pmt, pv, type)

IPmt IPmt (Interest Payment) for a given period for an investment based on periodic, constant payment and a constant interest rate
IPMT (rate, per, nper, pv, fv, type)

IRR IRR (Interest Rate of Return) for a series of cash flows represented by the numbers in values.
IRR(values,guess)

MIRR MIRR ( Modified internal rate of return ) for a series of periodic cash flows
MIRR(values,finance_rate,reinvest_rate)

NPer Returns the number of periods for an investment based on periodic, constant payments and a constant interest rate.
NPER (rate, pmt, pv, fv, type)

NPV Calculates the net present value of an investment by using a discount rate and a series of future payments (negative values) and income (positive values).
Syntax: NPV(rate,value1,value2, …)

Pmt Calculates the payment for a loan based on constant payments and a constant interest rate.
PMT(rate,nper,pv,fv,type)

PPmt Returns the payment on the principal for a given period for an investment based on periodic, constant payments and a constant interest rate.
PPMT(rate,per,nper,pv,fv,type)

PV Returns the present value of an investment. The present value is the total amount that a series of future payments is worth now. For example, when you borrow money, the loan amount is the present value to the lender.
PV(rate,nper,pmt,fv,type)

Rate Returns the interest rate per period of an annuity. RATE is calculated by iteration and can have zero or more solutions.
RATE(nper,pmt,pv,fv,type,guess)

SLN Returns the straight-line depreciation of an asset for one period.
SLN(cost,salvage,life)

SYD Returns the sum-of-years’ digits depreciation of an asset for a specified period.
SYD(cost,salvage,life,per)

Conversion

CBool Convert to boolean
=CBool(fields!EmployeeID.Value)
CByte Convert to byte
CChar Convert to char
CDate Convert to date
CDbl Convert to double
CDec Convert to decimal
CInt Convert to integer
CLng Convert to long
CObj Convert to object
CShort Convert to short
CSng Convert to single
CStr Convert to string
Fix =Fix(32.342143)
Returns integer portion of a number
Hex =Hex(Fields!EmployeeID.Value)
Returns a hexadecimal value of a number
Int =Int(43.44)
Returns integer portion of a number
Oct =Oct(Fields!EmployeeID.Value)
Returns a octal value of a number
Str =Str(Fields!EmployeeID.Value)
Returns string value of a number
Val =Val(“32.43″)
Returns numeric value in string format

Miscellaneous

Previous =Previous(Fields!EmployeeID.Value)
Returns the previous value

Reference : http://krishhdax.blogspot.com/2012/10/expressions-or-functions-used-in-ax.html

change date time format in SSRS report in AX 2012

Today is another small tip,

I was developing a custom report. Queried table stored the date and time in separate fields ie. Transdate and transtime. On  mapping the transtime to report, its showed time format. Instead of time value.

For it solution, I added the string field on report temp table and then convert the time with time2str function and map to the field.

Complete statement is below.

TableTmp.StransTime  = time2Str(_Trans.transTime, TimeSeparator::Colon, TimeFormat::AMPM);

As result, report will display the trans time instead of its format.

Reference taken by:- https://community.dynamics.com/ax/b/alirazatechblog/archive/2016/10/28/time-field-on-ssrs-shows-time-format-report-dynamics-ax-2012

Full text index in AX 2012

Consider following points for Full text index on Dynamics Ax 2012

• Full text index used only on text fields on table.
• They find all records from table which contains the key words
• Queries can be used to and full text index used in range filters
• If keywords contains spaces then it will used as “Or” operator.
• X++ select statement cannot use full text index.

Let explore the full text index on our custom table “HSPatientTable”

And we want full index search of FName of above mentioned table. First of all right click on table and from property window set table group property to “Main”.

Now expand the full text Index node of table and create a new full index name it as IdxFName and drag drop the Fname in this index.

Compile and synchronize table so changes reflect at sql server level.

Now create Now Ax job and paste following code there

static void Job1(Args _args)

{

HsPatientTable patientTable;

Query query;

QueryBuildDataSource queryBDSource;

QueryBuildRange queryBRange;

QueryRun queryRun; 

delete_from patientTable;

patientTable.HsPatientId ="01";

patientTable.FName =" Ali Raza zaidi";

patientTable.insert();

patientTable.HsPatientId ="02";

patientTable.FName =" Ranjah jogi";

patientTable.insert();

patientTable.HsPatientId ="03";

patientTable.FName ="Waseem akahram";

patientTable.insert();

patientTable.HsPatientId ="04";

patientTable.FName ="Shah jii";

patientTable.insert();

patientTable.HsPatientId ="05";

patientTable.FName ="AX guru";

patientTable.insert(); 

query = new Query();

queryBDSource = query.addDataSource(tableNum(HsPatientTable));

queryBRange = queryBDSource.addRange(fieldNum(HsPatientTable, FName));

 

queryBRange.rangeType(QueryRangeType::FullText);

 

// The space character is treated as a Boolean OR.

queryBRange.value("AX jii");

 

 

queryRun = new QueryRun(query);

while (queryRun.next())

{

patientTable = queryRun.get(tableNum(HsPatientTable));

info (" Patient Id: "  + patientTable.HsPatientId + " , Patient Name: " +patientTable.FName);

}

 

}

I used the value “AX jii”. The space between two words consider as Or and in result it will return two records

Reference taken by:- https://community.dynamics.com/ax/b/alirazatechblog/archive/2015/08/23/exploring-the-full-text-index-in-dynamics-ax-2012-r3

How to create SSRS report using Report data provider (RDP Report) in AX 2012

RDP or Business logic based SSRS Reports in Dynamics Ax 2012 R3.

Logic based report in MS Dynamics Ax 2012 can be develop in following steps

1. Create a temporary table
2. Define the report parameters
3. Add business logic for the report
4. Create a reporting project
5. Bind a report to a report data provider class

In graphical shape RDP reports will be as

Image Inspiration http://dynamics-ax.blogspot.com/2011/12/ax-2012-ax-ssrs-report-design-concepts.html

Now consider a scenario, where we have to display list of Item, quantity, Price and total amount sold to customers. It is relatively simple report but have to build this report based on RDP or Report Data Provider framework.

First step to open an Ax client. When Ax client open press Ctrl + shift +W keys to open Dev environment or AOT.

For all artifacts for report development will be a single place and we did not move to node to node in AOT we have to create a AX project.

You can find projects at View=>Projects => Public project.

Create a new project at and rename it with “CustomRDBReport”

Step 1 create a temp table.

The major step in RDP report is decision the fields require in report, create a temp table and add these field in temp table. For current example what fields we required on report are as follow

• CustomerAccount
• CustomerName
• ItemId
• ItemName
• SalesPrice
• SalesQuantity
• SalesAmount

If we see these fields exists in SalesLine Table. So we drag them into our temp table, and rename them accordingly

Right click on project and create at table with Name “CustomerSalesTemp”,

From property window rename the table as “CustomerSalesTemp” and set  TableType to   tempDb

Now close all window, open AOT and opens salesLine table. From top menu click on windows => Tile and both tables comes in parallel to each other

and start drag and drop fields in temp table

Now save the table and rename the fields accordingly if required. Also add a new field with Name CustomerName with extended data Type with Name.

Right click compile and synchronize table.

Now create a AOT Query with Name QSalesLine. Add data source on SalesLine and Add following fields on Salesline table

For Date Fileter, we will use ShippingDateConfirmed on Date.

Safe this query.

Step 2 define the report parameters

For current report we required three parameters, Customer, From date and To date.

In Report Data Provider framework which is based on WCF, we have to create a data contract class.

Create a new class in, rename it, CustomerSalesDataContract.

In its declaration section create three variables

[DataContractAttribute]

class CustomerSalesDataContract

{

CustAccount CustomerAccount;

TransDate FromDate;

TransDate ToDate;

}

Now Create three data method

[

DataMemberAttribute(identifierStr(CustAccount)),

SysOperationLabelAttribute (“Customer Account”),

SysOperationHelpTextAttribute(“Customer Account”),

SysOperationDisplayOrderAttribute(“1″)

]

public CustAccount  parmCustomerAccount(CustAccount  _CustomerAccount = CustomerAccount)

{

CustomerAccount = _CustomerAccount;

return CustomerAccount;

}

[

DataMemberAttribute(identifierStr(FromDate)),

SysOperationLabelAttribute (“From Date”),

SysOperationHelpTextAttribute(“FromDate”),

SysOperationDisplayOrderAttribute(“2″)

]

public TransDate  parmFromDate(TransDate  _FromDate = FromDate)

{

FromDate = _FromDate;

return FromDate;

}

[

DataMemberAttribute(identifierStr(ToDate)),

SysOperationLabelAttribute (“To Date”),

SysOperationHelpTextAttribute(“To Date”),

SysOperationDisplayOrderAttribute(“3″)

]

public TransDate  parmToDate(TransDate  _ToDate = ToDate)

{

ToDate = _ToDate;

return ToDate;

}

Step 3 Add business logic for the report.

In Report data provider framework we have to write Data provider classes, which contain business logic to populate temp table. For this we have to add create a new class “CustomerSalesDataProvider”

Extend this class SRSReportDataProviderBase

[

SRSReportParameterAttribute(classstr(CustomerSalesDataContract))

]

class CustomerSalesDataProvider extends  SRSReportDataProviderBase

{

CustomerSaleTemp _CustomerSaleTemp;

}

This Class two method, first is return the temp table, and second one is which contains the logic to populate  temp table.

[SRSReportDataSetAttribute(“CustomerSaleTemp”)]

public CustomerSaleTemp getCustomerSaleTemp()

{

select * from _CustomerSaleTemp;

return _CustomerSaleTemp;

}

public void processReport()

{

TransDate _FromDate;

TransDate _Todate;

AccountNum _CustAccount;

CustomerSalesDataContract dataContract;

Query query;

QueryRun queryRun;

QueryBuildDataSource queryBuildDataSource;

QueryBuildRange queryBuildRange;

QueryBuildRange ShippingDateConfirmedFilter;

SalesLine querySalesLine;

query = new Query(queryStr(“QSaleLine”));

dataContract = this.parmDataContract();

_CustAccount = dataContract.parmCustomerAccount();

_FromDate = dataContract.parmFromDate();

_Todate= dataContract.parmToDate();

queryBuildDataSource = query.dataSourceTable(tablenum(SalesLine));

if (_CustAccount)

{

queryBuildRange = queryBuildDataSource.findRange(fieldnum(SalesLine, CustAccount));

if (!queryBuildRange)

{

queryBuildRange = queryBuildDataSource.addRange(fieldnum(SalesLine, CustAccount));

}

}

ShippingDateConfirmedFilter = SysQuery::findOrCreateRange(query.datasourceTable(tableNum(SalesLine)),fieldNum(SalesLine,ShippingDateConfirmed));

ShippingDateConfirmedFilter.value(SysQuery::range(_FromDate,_Todate));

queryRun = new QueryRun(query); ttsbegin;

while(queryRun.next())

{ _CustomerSaleTemp.clear();

querySalesLine = queryRun.get(tablenum(SalesLine));

_CustomerSaleTemp.SalesPrice =  querySalesLine.SalesPrice;

_CustomerSaleTemp.ItemId =  querySalesLine.ItemId;

_CustomerSaleTemp.ItemDescription =  querySalesLine.Name;

_CustomerSaleTemp.SalesQty =  querySalesLine.QtyOrdered;

_CustomerSaleTemp.CustAccount =  querySalesLine.CustAccount;

_CustomerSaleTemp.CustomerName = CustTable::find(querySalesLine.CustAccount).name();

_CustomerSaleTemp.insert();

}

ttscommit;

}

Now compile the class, generate Incremental CIL.

Step 4 create a reporting project

Now open Visual studio and create Model project say “CustomSalesLineReport”.

From solution explorer, create a new report rename it RDPSalesLineReport

Step 5 Bind a report to a report data provider class

Now double click on report and open it in

Add new DataSet and rename it “DSSalesLine”. On right click and from property window set Data Source Type to “Report Data Provider”

And click on Query and from browser window select The data provider class we created in previous step

Click ok to create fields

Now drag and drop data set to Design node in report to create AutoDesign.  Rename it “RDPSalesLine”

Expand “RDPSalesLine” design and drag and drop CustAccount field from Data Set to Group and sort nodes

Expand parameter of report and open the property of CustAccount parameter and set its allow blank to true and nullable to true, so if no customer is selected, report will run for all customer in legal entity

Save the report compile it, deploy it and add to AOT

Now switch back to AOT.  Create a new menu Item under Display node.

And set menu item Name as “mnuRDPSaleLine” and set its properties as follow

Save it and right click on menu item and open it

Set values for From Date and To date and run the report, Report will work with business logic as follow

 Reference taken by:- https://community.dynamics.com/ax/b/alirazatechblog/archive/2015/07/08/rdp-or-business-logic-based-ssrs-reports-in-dynamics-ax-2012-r3