Developing a SSRS report using the Report Data Provider in Microsoft Dynamics AX 2012

Overview

There are multiple methods to develop SSRS reports in Microsoft Dynamics AX 2012. This tutorial will guide you in developing Report Data Provider (RDP) based SSRS reports.
RDP based SSRS Reports are used when complex business logic cannot be achieved using AOT query.

Pre-requisites

1. Microsoft Dynamics AX 2012
2. Visual studio 2012
3. SQL Server Reporting Services (SSRS) must be configured
4. Reporting services extensions must be installed in Dynamics AX

Important Concepts

1. Report Data Provider (RDP) Class

Report Data Provider Class is an X++ class that is used to access and process data for a SSRS report. The RDP class processes the business logic based on a specified parameter and/or query and returns a dataset to the reporting services. In order to create a RDP class in AX, you have to extend that class with SRSReportDataProviderBase. This tells AX that this class will be used by reporting services to process the data.

Two important attributes are used in RDP classes:
1. SRSReportQueryAttribute: specifies which AOT query will be used in this report. If the RDP class uses an AOT query to process data, define this attribute at the beginning of the class.
2. SRSReportParameterAttribute: defines the data contract class that will be used by this report to prompt for parameter values. If the RDP class contains any parameters this define this attribute at the beginning of the class.
Both the attributes are optional. If the report does not use any query or does not want any parameter to filter report data, these attributes do not need to be used.


2. Data Contract Class

A data contract class is an X++ class which contains parm methods with the DataMemberAttribute defined at the beginning of the method. This class is used to define one or more parameters that will be used in a SSRS report.


3. Table

An AX table is used as the dataset to store data for the report. The RDP class processes the data and stores it in the table which is then used by a SSRS report to render data.

A table can be a temporary table (InMemory or TempDB) or a regular table, but it is Microsoft best practice to use a temporary table.

The type of temporary table is based upon the performance considerations. InMemory temporary table is used when the data set is small, while TempDB is normally used for larger datasets to improve performance.

Scenario

As part of this tutorial, the report will print a list of customers and their invoiced sales order counts.

Steps

1. First of all, create a temporary table. Open AOT à Date Dictionary à Tables.
2. Right Click on Tables and create a new Table called CustReportRDPDemoTmp.

3. Set the TableType property to InMemory. This will define the table as a temporary table.





4. Expand the CustReportRDPDemoTmp table node and add the following fields in the table:



S. No.	Field name	Extended Data Type	Label
1	CustAccount	CustAccount
2	Name	Name
3	SalesOrderInvoiceCount	Integer	Sales order invoiced



5. The final table should look like the following:





6. Now create a RDP class. Go to Classes and create a new class called CustReportRDPDemoDP by right clicking on Classes and selecting New Class. It is a best practice to suffix the RDP class name with DP .





7. Open the Class declaration by right clicking on it and selecting View code.





8. Now write the following code:
class CustReportRDPDemoDP extends SRSReportDataProviderBase { //Temporary table buffer CustReportRDPDemoTmp custReportRDPDemoTmp; }

1 2 3 4 5

class CustReportRDPDemoDP extends SRSReportDataProviderBase { //Temporary table buffer CustReportRDPDemoTmp custReportRDPDemoTmp; }

9. Add a new method and name it getCustReportRDPDemoTmp. This method is mandatory because reporting services uses this method to get the table buffer containing the processed data. The SRSReportDataSetAttribute attribute is used to indicate the temporary table name and also tells the reporting services to use this method to retrieve the processed data.
10. Write the following code in the method:
[SRSReportDataSetAttribute(tablestr('CustReportRDPDemoTmp'))] public CustReportRDPDemoTmp getCustReportRDPDemoTmp() { //select data from table buffer select * from custReportRDPDemoTmp; //return the buffer return custReportRDPDemoTmp; }

1 2 3 4 5 6 7 8

[SRSReportDataSetAttribute(tablestr('CustReportRDPDemoTmp'))] public CustReportRDPDemoTmp getCustReportRDPDemoTmp() { //select data from table buffer select * from custReportRDPDemoTmp; //return the buffer return custReportRDPDemoTmp; }

11. Add a new method and name it processReport. This method contains the business logic and is called by reporting services to generate data.
12. This method will query customer details and fill the temporary table buffer. Write the following code in the method:
///<summary> /// Processes the SQL Server Reporting Services report business logic /// </summary> /// <remarks> /// This method provides the ability to write the report business logic. This method will be called by /// SSRS at runtime. The method should compute data and populate the data tables that will be returned /// to SSRS. /// </remarks> public void processReport() { CustTable custTable; SalesTable salesTable; //select all customers while select * from custTable { //clear the temporary table custReportRDPDemoTmp.clear(); //assign customer account and name custReportRDPDemoTmp.CustAccount = custTable.AccountNum; custReportRDPDemoTmp.Name = custTable.name(); //select count of invoiced sales order of customer select count(RecId) from salesTable where salesTable.CustAccount == custTable.AccountNum && salesTable.SalesStatus == SalesStatus::Invoiced; custReportRDPDemoTmp.SalesOrderInvoiceCount = int642int(salesTable.RecId); //insert in temporary table buffer custReportRDPDemoTmp.insert(); } }

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 27 28 29

///<summary> /// Processes the SQL Server Reporting Services report business logic /// </summary> /// <remarks> /// This method provides the ability to write the report business logic. This method will be called by /// SSRS at runtime. The method should compute data and populate the data tables that will be returned /// to SSRS. /// </remarks> public void processReport() { CustTable custTable; SalesTable salesTable; //select all customers while select * from custTable { //clear the temporary table custReportRDPDemoTmp.clear(); //assign customer account and name custReportRDPDemoTmp.CustAccount = custTable.AccountNum; custReportRDPDemoTmp.Name = custTable.name(); //select count of invoiced sales order of customer select count(RecId) from salesTable where salesTable.CustAccount == custTable.AccountNum &amp;&amp; salesTable.SalesStatus == SalesStatus::Invoiced; custReportRDPDemoTmp.SalesOrderInvoiceCount = int642int(salesTable.RecId); //insert in temporary table buffer custReportRDPDemoTmp.insert(); } }

13. Now create a new report. Since the development of a SSRS report is done in Visual studio, we first need to create a new project in Visual studio.
14. Open Visual studio. Go to File à New à Project
15. In the Installed templates section select Microsoft Dynamics AX and then select Report Model in the right pane. Name the project RDPBasedDemo and press Ok.





16. A new project will be created as shown below.





17. Now add a new report in the project by right clicking on the project RDPBasedDemo à Add à Report.



18. A report will be added to the project with the name Report1. Rename the report RDPBasedDemo.
19. Now double click the report to open it.



20. The description of the individual node is given below:
1. Datasets: Datasets retrieve data from RDP class. It acts as a bridge between AX and the SSRS report. Only the fields added in the datasets can be used in a report.
2. Designs: It defines the layout of the report.
3. Images: It contains the images that you want to display in the SSRS report.
4. Data Methods: It contains the business logic which can then be used in the report.
5. Parameters: It is used to apply filtering to the data in a report. All the parameters defined in the data contract class are automatically added here when the RDP class is defined in the datasets.
21. Now you will want to create a new Dataset by right clicking Datasets àAdd Dataset. Name it CustDetail.





22. Select the CustDetail dataset and open the properties window. Set the Data Source Type to Report Data Provider. Then select the Query field. An ellipse button appears. Click it to open a dialog box.





23. This dialog box lists all the RDP classes present in the AOT. Select CustReportRDPDemoDP and press Next.





24. Select the fields to be displayed in the report and press OK. Only the fields selected in this dialog box can be shown in the report.





25. There are two types of designs that can be created in a SSRS report:
1. Auto design: Visual studio automatically creates a design based on the dataset provided. Auto design is the preferred method because it is easy and usually fulfills the majority scenarios.
2. Precision Design: This is used when you need custom placement of fields or the layout of the report is too complex.
26. In this demo we will use Auto Design. Now right click the Designs nodeàAdd àAuto Design. A new design is added. Rename it Design. It is recommended that you set the name of the Design to either ‘Design‘ or ‘Report‘.





27. Now drag the CustDetail form to the Datasets node and drop it on the Design node. A table will be created which contain all the fields present in the data set. These fields will appear in the same order as in the report. So if you want to arrange the fields, right click the field and select either ‘move up’ or ‘move down’.
28. The final design should look like the following:





29. Now we have to define the layout of the report. Visual studio provides built in templates. Select the Design and open the properties window. Select ReportLayoutStyleTemplate in the LayoutTemplate field. Give a suitable title to the report.





30. Select CustDetailTable under the Design node and open the properties window. Select TableStyleAlternatingRowsTemplate in the Style Template field.





31. The report is now completed and can be viewed. To preview the report, select the Design node, right click it and select preview.





32. Select the Report tab. The report will appear as shown below:



33. To view this report from AX, Add the report to AOT and create an Output menu item and set the appropriate Properties. For further details on creating SSRS reporting, refer to our previous article ‘Developing SSRS report using Query‘.

Building a Simple Report–Precision Design

In this post, we will see how we can build a report using precision design. A precision design is like a pre-printed format where the placing of report controls, the design and the spacing matters a lot.
A precision design is the standard SSRS report designer following the same principles, rules and requirements as are found in a SQL Server Business Intelligence Studio SSRS designer.
We will again use the same example of printing Customer Id, Customer name and Balance but this time instead of creating an Auto Design we will use Precision Design.
I will also show how we can use some inbuilt parameters to display some generic information like page number, execution date and time etc.
Here we will use the same AOT query created in previous example Build and Deploy Simple Report–Queries: SKL_SampleCustomer
Create the Required Dataset

• Open the previously created Report Model

• Right click on report model, select Add –> Report. Name the report SKL_SampleReportPrecision
• Set the report title to “Customer balance”

• Now in the report, go to the Datasets section and create new Dataset using the name Customer
• In the query property, click the button
• This opens up the query selection box to select a Microsoft Dynamics AX Query
• From the list select “SKL_SampleCustomer” and click Next


• Here you can select required fields. Either you can select all fields or a subset
• Expand All Fields nodes under the CustTable node to select “AccountNum”
• Expand Node “All Display Methods” and select “name” and “openBalanceMST”
• Click “Ok”
• Now Right click on the designs node, select Add and then “Precision Design”. This creates a new Precision Design and name it as PrecisionReport

• Now right click on new Design and select “Edit Using Designer”

• This opens up the SSRS Designer (same as standard SSRS)
• You can see the standard Report Item tool bar from where you can drag and drop the controls onto the designer surface

• Now from the controls tool bar, drag and drop the Table control onto the Report Designer
• Select the Tablix and then set the DataSetName property = “Customer”

• Now let us specify headers, Select one text box in the header section and right click, then select “Expression…”

• In the expression editor, you can see lot of sections for Variables, Parameters, Fields, Datasets etc.
• Here write the following in the space “Set expression for: Value” -  =Labels!@SYS316441
• This displays the header text using the labels in AX. This label is “Customer”

• Now again specify the values in Second and third text boxes in header row
• 2nd Column Header(Name) : =Labels!@SYS117778
• 3rd Column Header (Amount): =Labels!@SYS62867

Specifying the data for the table

• Now in the data row, move the mouse to first text box and you should see a button on the right side that looks as shown below

• Click the button and it displays the list of fields from the dataset, select required fields

• Alternatively, you can right and use “Expression…” box to specify the data fields. The expression box looks as shown below:

Now that the data is ready, we will go ahead and change the look and feel of the report
Formatting the table
You can specify the lots of formats using the properties windows like Background color, Font type color etc. You can also use the formatting tool bar above to format the data

Go ahead and specify the borders for the table for each row. Then change the background of the Header row. Make the text in the header row as center aligned and make the font as bold
Select the table rows one by one and change the font to “Segoe UI” and Font Size to “8pt”. Now your table looks as shown below:

Now we will go ahead and a Page Header and Page Footer. In Page Header, we will add Report name, Report Title and Execution Date and Time. In the Page Footer, we will add the Page Numbers in the format (Page of Total Pages)
To enable Page Header and Footer, in the Report menu select “Add Page Header” and “Add Page Footer” options


Now drag three text boxes from tool box and drop onto the Page Header area. Place two text boxes on the left hand corner and one on the right hand corner as shown below. Drag and drop one text box in the center of the Page footer as shown below.

Select the first text box, Open the expression box and in the Category section, select “Built-in Fields. Now double click on “ReportName” field and it should add the report name to value section

In the same way select “ExecutionTime” in third text box. For report title, Type in “Customer balances” in the second text box
In the footer section, select the 4th text box and type the expression ‘=Globals!PageNumber & ” of ” & Globals!TotalPages’
Apply formatting as required. Now you should see the design as follows

Now close the designer, add the report back to AOT (along with the model) and deploy the report from within AOT.
Create a new menu item
Go to AOT –> Menu Items –> Output, Right click and select “New Menu Item”. Set following properties

Now run the report and the report will be seen as shown below:

How to Install DAX on Stand alone Windows XP system:

Hello,


Unfortunately setup of AX on the computer outside a domain is prohibited by installer. But there is a trick for doing such thing and works good.
This trick is tested under Windows XP SP2, it does not work on Vista.
For Ax 4.0 Windows XP sp2 is required , for DAX 2009 Windows XP sp3 is required.
Here are steps of the trick (borrowed from AxForum):
1. Change the value of there registry key HKEY_LOCAL_MACHINE\SYSTEM\ControlSet001\Control\ComputerName\ActiveComputerName to any value which does not match the name of your computer
2. Set value of environment variable named “UserDnsDomain” to any other value
3. Run setup.exe
4. Restore values, changed on the steps 1, 2
5. Go to client and server setup in control panel and change computer name to the real name of your computer
In order to install EP,Workflow for DAX 2009 on Windows XP we need to install share point services on Windows XP . Please visit the below link
Install SharePoint on WindowsXP

AX2012 – Assigning permissions to users through Roles,Privileges and duties

All of us know that Security frame work is completely changed in AX2012. We are used with AX2009 way of assigning permissions to user groups and linking users to user groups.
Let’s get conversant with AX2012’s way of assigning permissions and restricting the forms and activities as required by our clients.
Whoever tried assigning permissions might have already noticed that user groups are no more in AX2012. So, to have the users assigned with needed permissions, user must be assigned with a Role that he/she performs. In a case where the user performs multiple roles, one can be assigned with multiple roles.
By default a good number of “Security roles” are available in AX. These roles have been created by keeping industry requirements in mind and also to achieve the standardization across the multiple business organizations. However, it is quite usual that clients’ requirements on permissions will be varying from one client to another client.
Below is the image that helps in understanding how the permissions are structured in AX2012.

Each role will have the Set of duties that are relevant for the specific role. Duties comprises of set of privileges. Through Privileges, we give the permissions to the relevant forms, menu items and tables.
(I do not want to stress on definitions and terminology as loads of data available over Technet).
Let’s analyze and understand this completely using the role “Purchasing agent” as an example.
To see the different security roles available, Go to System Administration>setup >Security > Security roles.

Focus on “Purchasing agent” then right side list box “Role content” shows the list of duties that will be applicable for the “Purchasing agent role’.
It gives the precise information on what the activities that the user with the specific role can perform. By seeing the content obviously we can’t understand what all that will be covered under each duty. For instance, let’s consider the duty “Maintain Vendor Master”. We do not know what all that comes if it says just “Maintain vendor master”.
Let’s see what all it covers, go to System administration>setup>Security>Security roles> Security privileges
Search for the needed duty “Maintain vendor Master”, it shows the list of “Privileges” that are covered.

We can notice the privileges give little detailed information that what all comes in “Maintain vendor master”. Of course, we should know how the access is being given to forms. For which lets get into AOT, with the AOT name of duty.
In the image you can see the AOT name of “Maintain vendor master” as “VendVendorMasterMaintain”.
Now, Go to AOT (by pressing Ctrl+D)>Security>Duties, find duty “VendVendorMasterMaintain”, and see the privileges(AOT names) to proceed further.

To find the forms that are been give the permission,
Go to AOT>Security>Privileges>“VendBankAccountsMaintaing” and expand the entry points node to know which forms are been given the access.

Hope it is clear now on how the access to forms is controlled using the roles, duties and privileges. In next post, we will see how to stop access to some forms in the already defined duty and also how to add a completely new privileges in duties.

Adding Dialog Box on AX 2009 Report

Declare the dialogue filed on the class declaration


public class ReportRun extends ObjectRun
{
    LedgerJournalTrans   _ledgerJournalTrans;

    DialogField         dialogFromDate;
    DialogField         dialogToDate;
    DialogField         dialogLedger;
    DialogField         dialogBankAccount;
    DialogField         dialogBankAccount1;
    DialogField         dialogVoucher;


    TransDate           fDate;
    TransDate           tDate;
    TransDate           oldDate;
    TransDate           finStartDate;
    TransDate           finEndDate;
    Real                drOpenBalance;
    Real                crOpenBalance;
    Real                openBalance;
    str                 tempDate,date1,date2;   //madhav_abc
}

Override the Dialog method on the report.



public Object dialog(Object _dialog)
{
   DialogRunbase dialog = _dialog;
    ;
    dialog.addGroup('Date');
    dialogFromDate = dialog.addFieldValue(typeId(TransDate),fDate,'From ');
    dialogToDate = dialog.addFieldValue(typeId(TransDate),tDate,'To ');

    dialog.addGroup('Voucher');
    dialogVoucher      = dialog.addFieldValue(typeId(Voucher),_voucher,'Voucher:');

    return dialog;
}

Expressions or functions used in AX SSRS Reports

Expressions are usually used for appearance of the data in a report, change properties of the fields, calculate some values and display them in a proper way, compare values between data of fields and then display them.
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/prabu.thangavelu
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 6/3/1977
1 returns Friday, June 03, 1977
2 returns 6/3/1977
3 returns 12:00:00AM
4 returns 00:00
FormatNumber =FormatNumber(Fields!EmployeeID.Value,2)
Examples: 2.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 yyyy”)
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/1977
1 returns Friday, June 03, 1977
2 returns 6/3/1977
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.Value10,”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

Difference between this and element

this & element: this can be used in any objects to reference the current object and member methods.

In forms the collection of objects is contained within a FormRun object. You can reference members in the outer formrun object by using the element reference.

If your code is placed at the top level there are no functional difference between this and element.

If your code is placed in a formdatasource but this will reference the datasource but element will reference the formrun.

You cannot use element to refer table or class object.

Failed to create a session; confirm that the user has the proper privileges to log on to Microsoft Dynamics.

Hi;
Today i faced an issue while creating a PO in AX2012.

To solve this error,I tried following and the problem was fixed:
Tools > Options > Development > uncheck “Execute business operations in CIL”
As you can see the below screen.

Purpose of this checkbox(“Execute business operations in CIL” or not?)
In AX 2012, steps are taken by the development team at Microsoft to align AX IDE with .NET. That’s why you regenerate the CIL after X++ code changes.
CIL obviously has its benefits like faster execution in cases of heavy logic processing.
But sometimes while testing or debugging, developers would prefer NOT to avail this option. Debugging can be cumbersome in AX 2012 as you will need to configure Visual Studio for code running in CIL. The solution is to uncheck a checkbox in Options at
Tools > Options > Development > General > Execute business operations in CIL
Now even the code which was running in CIL will now run in X++ interpreted mode, the old way.
Word of caution, this option should only be used by developers while debugging. It should ideally be checked again after work is finished.

Execute Report section

Hello
Today i would like to share how to write a code which will enable you control the execution a particular report section in ax 2009 and ax 2012 standard reports;
The below code will enable you achieve;


Override the executesection method in your report design  ;

void  executesection()
{
 if( yourtable. fieldname = condition)
{
super()
}
}

suppose you want to execute the section only if the value in a particular field is not equal to zero;

void  executesection()
{
if( inventtable.onhandqty != 0)
{
super()
}
}

Copying Data from excel sheet to AX Tables

Hi friends,today i have written a simple code which can help you import data from excel sheet into ax table
The below code will help you achieve the task:
I have created a simple table named mynames in the AOT with fields Fname,Lname and salary.My excel sheet is also named mynames.xlsx. The below code helped me to achieve the task.


static void mynamescopyfromexcel(Args _args)
{
SysExcelApplication application;
SysExcelWorkbooks workbooks;

SysExcelWorkbook workbook;
SysExcelWorksheets worksheets;
SysExcelWorksheet worksheet;
SysExcelCells cells;
COMVariantType type;
Name name;
FileName filename;
myNames  mynames;

int row = 1 ;

str lastname;
str firstname;
    int salary;
;

application = SysExcelApplication::construct();
workbooks = application.workbooks();
//specify the file path that you want to read
filename = "F:\\mynames.xlsx";
try
{
workbooks.open(filename);

}
catch (Exception::Error)
{
throw error("File cannot be opened.");
}


    workbook = workbooks.item(1);
worksheets = workbook.worksheets();
worksheet = worksheets.itemFromNum(1);//.itemFromName("Sales target");
cells = worksheet.cells();

do
{
row++;


firstname = cells.item(row, 1).value().bStr();
lastname = cells.item(row, 2).value().bStr();
    salary = any2int(cells.item(row,3).value().toString());
    mynames.clear();
    mynames.Fname = firstname;
    mynames.Lname = lastname;
    mynames.Salary = salary;
    mynames.insert();

    info("Data inserted succesfully");




type = cells.item(row+1, 1).value().variantType();
}

while (type != COMVariantType::VT_EMPTY);
application.quit();
}

How to Read/Write an Excel file through X++ code

How to write an Excel file through X++ code

In This Post you will found two code sample
1.Write data in excel through X++ code.
2. Read from an Excel through X++ code

1.Write data in excel through X++ code.

static void thaAxapta_Write2Excel(Args _args)
{
InventTable inventTable;
SysExcelApplication application;
SysExcelWorkbooks workbooks;
SysExcelWorkbook workbook;
SysExcelWorksheets worksheets;
SysExcelWorksheet worksheet;
SysExcelCells cells;
SysExcelCell cell;
int row;
;
application = SysExcelApplication::construct();
workbooks = application.workbooks();
workbook = workbooks.add();
worksheets = workbook.worksheets();
worksheet = worksheets.itemFromNum(1);
cells = worksheet.cells();
cells.range('A:A').numberFormat('@');
cell = cells.item(1,1);
cell.value("Item");
cell = cells.item(1,2);
cell.value("Name");
row = 1;
while select inventTable
{
    row++;
    cell = cells.item(row, 1);
    cell.value(inventTable.ItemId);
    cell = cells.item(row, 2);
    cell.value(inventTable.ItemName);
}
application.visible(true);
}

2. Read from an Excel through X++ code

static void theAxapta_ReadExcel(Args _args)
{
SysExcelApplication application;
SysExcelWorkbooks workbooks;
SysExcelWorkbook workbook;
SysExcelWorksheets worksheets;
SysExcelWorksheet worksheet;
SysExcelCells cells;
COMVariantType type;
int row;
ItemId itemid;
Name name;
FileName filename;
;
application = SysExcelApplication::construct();
workbooks = application.workbooks();
//specify the file path that you want to read
filename = "C:\\item.xls";
try
{
    workbooks.open(filename);
}
catch (Exception::Error)
{
    throw error("File cannot be opened.");
}
workbook = workbooks.item(1);
worksheets = workbook.worksheets();
worksheet = worksheets.itemFromNum(1);
cells = worksheet.cells();
do
{
    row++;
    itemId = cells.item(row, 1).value().bStr();
    name = cells.item(row, 2).value().bStr();
    info(strfmt('%1 - %2', itemId, name));
    type = cells.item(row+1, 1).value().variantType();
}
while (type != COMVariantType::VT_EMPTY);
application.quit();
}