Dynamics Ax 2012 Reporting SSRS Reports.

List of functions used in Dynamics AX 2012 SSRS Reports Expressions

October 10, 2014 by alirazazaidi

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

Exploring Drill-down SSRS Reports in Dynamics Ax 2012 R3.

June 23, 2014 by alirazazaidi

Drill drown reports in Dynamics Ax 2012 R3 are very Easy. For this purpose I have to create a new AOT/Static Query AOT. This query is join between CustTable and SalesTable. I want to create Inner Join so Only those Customer came who have Sales Orders. Consider following Steps to create A new report
Create a new Query with Name “DyWorldCustSales”
Drop or add Data Source with CustTable

Right click on Fields and add following fields from Customer table.

Expand Data Source inside Custtable, Add or create DataSource with SalesTable and fields

Right Click on SalesTables Data Source and set its join properties as follow

Expand Relationship node of SalesTable_1 Add Following relationship

Set it properties as follow

New create a new report In existing or New DataModel Report in Visual Studio project

Add New report with DyCustSalesOrderList

Add Dataset with Name DSCustomer and Point to Query which we create in above steps

Now drag and drop the dataset on designer to create a designer

And Update rename it to DyCustSalesOrderList

Set DataTable’s Propeties visible to set false

Add a List and set its name to CustList

Create two groups there one for CustomerGroup and second for AccountNum

Now drop the fields from dataset which you want to display on report

Right click on CustList and set Data Navigation Style to DrillDown from properties window

Now right click add report to aot and then deploy to Report Server

When you run the report from meu Item you will find following

Click on Customer group this will open it

Click on Customer account it will drill down report on Detail level

Exploring Report Controller class in dynamics AX 2012 R3

June 21, 2014 by alirazazaidi

In Dynamics AX 2012 Reports can be called from Class, and it is import part of MVC pattern implementation for reports
The report created from following link is called from controller class
http://tech.alirazazaidi.com/exploring-drill-through-reports-in-dynamics-ax-2012-r3/
Create a new class and extends it with “SRSReportRunController” class.
Add New method, and update it with following code

public static void main(Args args)
{
DyWorldDrillThroughReportController _Con= new DyWorldDrillThroughReportController();
    _Con.parmReportName(ssrsReportStr(DynamicCustomerList,DesignCustomerList));
    _Con.parmArgs(args);
    _Con.startOperation();
    
}

When you run the class report will be open.

Very interestingly if you did not want to show report dialog, and just run the report form default parameter value you have to add following statement in above code

_con.parmShowDialog(false);

Exploring Drill through Reports in Dynamics Ax 2012 R3.

June 19, 2014 by alirazazaidi

Creating basic drill through reports in Dynamics Ax 2012 R3 is really simple. Consider following scenario where we have to show the list of customer and their sales Orders in particular legal Entity. In this report when User click on customer account, a new report open which contains list of all possible sales Order.

For this Purpose I created two Static/AOT queries. First for Customer, and second for Sales Order.

Customer Query is as follow.

Create a new AOT query with Name DyWorldCustomerList.
In DataSource add CustTable table.
In fields dynamic to true.

Similarly Create another Query “DyWorldSalesTable” for SalesTable. With Name DyWorldSalesTable

Now Open a Visual studio and create a new Dynamics Ax Report Project

Add a new report with name

DynamicCustomerList

Add New dataset with Name
DynamicCustomerList set its properties as follow

Select Following fields
- AccountNum
- CustGroup
- Party

Drag this DSCustomerList data set on Report Designer to create an AutoDesign.
Change the AutoDesign1 properties as follow

o Name to DesignCustomerList

o LayoutStyle to ReportLayoutStyleTemplate

Now create another report inside same Dynamics Ax Model Project, this report will call from customer Report

Create and DataSet with DSSaleOrder with query “DyWorldSalesTable”

Drag this dataset on Report Designer and update newly Designer as follow.

Now open the parameter Section of report and add new parameter as “CustId” of string type

Now expand report designer and expand Table and add filter with following Properties

Set following Properties of filter

o From Expression select “=Fields!CustAccount.Value”

o Name= Customer

o Operator =Equals

o Value “=Parameters!CustId.Value”

Save the report.

Now Open the first report “DynamicCustomerList”. And expend the reports design, “DSCustomerListTable” and then Data and select AccountNum.

Right click on AccountNum and select “Report Drill Through Action”

Now Double click and Select the report designer which will open against the click on field

Select SalesOrderReport and its Desinger form dialog

It will create, number of parameters, all belongs to DynamicSalesOrderList Report as follow

Delete all parameters except AX_CompanyName and CustId. If any other parameter remains, you definitely got following error when you call Sales Order report form drill through option.

“The Microsoft Dynamic AX parameter ID should be a RECID. The exception is System.FormatException: Input string was not in a correct format.”

Please Select following Values for these Properties AX_CompanyName

Select its value “=Parameters!AX_CompanyName.Value

Please select the following value for CustId as “=Field!AccountNum.Value”

Now saves the reports and right click on Report Project to Add to AOT and then deploy on Report Server

Now Go to AOT, and refresh the Select the Visual Studio Model Project and SSRS reports into Dynamics Ax project so it will easy to edit or locate easily

Now create a Menu Item of Display type and set its following Properties

Now run the report as follow

As click on first Customer “CNMF-000001” To its salesOrderList , The second report successfully open as follow

A call to the Microsoft Dynamics AX SRSFramework Service failed.

June 13, 2014 by alirazazaidi

Today while deploying Dynamics AX 2012 R3 SSRS reports on window 7 I got following when I run Report deployment command on publish-AXReport -ReportName *.

“A call to the Microsoft Dynamics AX SRSFramework Service service failed. An existing connection was forcibly closed by the remote host.“.

I perform following steps to solve this problem.
1. Stop reporting services,
2. Stop AOS.
3. Goto: C:\Users\\AppData\Local\

4. Take the backup of all AUC files,
5. Remove them from there
6. Start reporting services
7. Start AOS.
8. Again run publish-AXReport -ReportName *. In powershell.

Reports are successfully deploy on my machine.

References:

http://dynamics-ax.blogspot.no/2013/03/ax-2012-fatal-axrdce-exception-error.html

https://community.dynamics.com/ax/f/33/t/115327.aspx

Power script commands and Dynamics Ax 2012 SSRS reports.

January 13, 2014 by alirazazaidi

When you install dynamics Ax 2012, default reports automatically deployed. But sometimes we have to deployed the reports manually. Dynamics Ax provides build in PowerShell commands to perform various operations. For testing these commands, you have to run Power shell Management Shell as Administrator.

$reports = Get-AXReport -ReportName *
$reports

Above power shell scripts helps to get the list of Dynamics Ax 2012 SSRS reports available for deployment.

We can also filter the list of report . Dynamics Ax 2012 provide the only two filter options Report Name and changed date. You can use following statement.

$reports | Select-Object Name,ChangedDate

For example if we want all reports similar to ABC classification we can filter something like this

$reports | Select-Object Name,ChangedDate | Where { $_.Name -like "ABC*" }

If reporting Server is install and configured with Dynamics Ax 2012, we can use following statements to deploy all available reports.

If we want to deploy a single required report we have to use following statement.

Publish-AXReport –Id SSRSConfigID -ReportName CustTransList

In my case SSRSConfigID will be machine name

Publish-AXReport –Id dynamicsworld -ReportName CustTransList

If we have to deploy all reports then we can use following statement.

Publish-AXReport –Id SSRSConfigID –ReportName *

Above statement also works if we did not use SSRSConfigID.

Publish-AXReport –ReportName *

 
if you configured the multiple AOS and multiple reporting server then following command works

Publish-AXReport -Id SSRS_2712 –ReportName * -RestartReportServer -ServicesAOSName  <MACHINE NAME> -ServicesAOSWSDLPort 8101

Dynamics Ax 2012: How to duplicate the existing report for modification

November 14, 2013 by alirazazaidi

I got task to extend the existing dynamics ax SSRS report. Client also wants to sustain the existing report. They want a new report that will be replicate the original one with extended features. Data provider classes are easily duplicated by right clicking on it and select duplicate, I renamed the duplicate classes in AOT. Initially I did not change anything in renamed classes so duplicate report will work with old data set.

But I did not find any duplicate option on AOT. After R&D I found that report can duplicate in visual studio. I create a new visual studio project Report project. Open Application Explore, Expand SSRS report node and drag it into visual studio project. Visual studio automatically duplicated the existing report

You can open Application explorer in Visual Studio è Viewè Application Explorer.

. Now the duplicated report will pointed to old Data provider class. Next step I copied the names of data set fields and parameters and properties into Notepad. When I point the data set to duplicated classes, a new data set created, and parameters became duplicate. I removed the old parameters and set the new parameters with new names and their properties to as they were in deleted parameter. After this I checked that report layout is working with new data Set, Now I started modification in report and classes with requirements.

Dynamics Ax 2012: How to Edit the Existing Report.

November 10, 2013 by alirazazaidi

In Ax Dynamics usually work is pop out regarding customization of what is existing In Dynamics Ax. Usually is one of them is Report customization. If you are new in Dynamics Ax, Report locate and open mode will create more headache then modifying it. Let’s explore what steps require to open report in Edit Mode.

First step is determining the Name of report. You can identify it by menu item from it is running.

For example we want to edit the following report as per MSDN link

Menus > InventoryAndWarehouseManagement > Reports >BaseData > Quantity setup

Now Go to AOT and expend InventoryAndWareHouseManagement and right click to view the properties of Quantity Setup.

From Property you can identify the Name of MenuItem and its type. The menu item for Quantity setup is InventTableQuantity.

Now expand from AOT and Expend Menu Items and then Output Node. Because there are too many menu Items in AOT Node so you have to type when you reach on InventTableQuantity right click on it and explore the properties. You can find the Object property described the report Name of Quatity Setup is InventTableQuantity.

Now third step is go to locate the Visual Studio Projects and then Edit it.

For this purpose in the AOT, expand the node for Visual Studio Projects and then expand the node for Dynamics Ax Model Project. The project name will be similar to report Name, There are too much visual studio project so type InventTablQuantyReport. Right click on it and select edit.

Report project will be open to Visual studio, where you can modify.

Dynamics Ax 2012 : “Only integrated security is supported for AX queries.”

October 19, 2013 by alirazazaidi

Recently I update my dynamics Ax development Environment by restoring Database from Production Dynamics AX Db. After the restoring the database, when I run any SSRS report form Dynamics Ax, it starts to give error “Only integrated security is supported for AX queries.”

The reporting Services are working perfectly other than Dynamics Ax reports.

One way to apply the Windows Integrated Security on required report. By following way

Open Reporting Services Configuration Manager ( All Programs > Microsoft SQL Server 2008/2012 > Configuration Tools > Reporting Services Configuration Manager)
Go to “Report Manager URL” Tab and click on URL
Typically, URL will be in the format http://<ServerName>:80/Reports
Select the Report under Dynamics Ax Folder

In the opened page, select Data Sources on the left pane.
And apply Windows integrated security and click Apply.

But I found that error on all SSRS reports for Dynamics Ax 2012. For this, best way to delete all reports and redeploy them.
Steps are as follows:

First step to delete all reports, by selecting Dynamics Ax folder in Report server and delete it, this will all reports inside the reporting reports.

Then go to Report servers form in Dynamics Ax 2012 and click on “create report folder” button to create the Folder Dynamics Ax again

Now you can open Powershell (Administrative Tools > Microsoft Dynamics Ax 2012 Management Shell). Make sure you run Powershell as Administrator.
Deploy all the reports with the help of command:
Publish-AXReport -ReportName *
Wait for completing the report deployment after it all reports will run fine.

Dynamics Ax 2012: Very simple report in dynamics ax 2012

January 29, 2013 by alirazazaidi

There two common and mostly used ways to develop reports in dynamics Ax 2012

Query
Report data Provider.

Here we have to create a very simple report using AOT query. For this purpose I have create a custom table Student with 4 fields as

Rollnumber
First Name
Last Name
Address

The sample data in custom table is as follow.

Now we create a new Query in AOT which will be used in our Report to get data from AX to display on report.

In Query node of AoT create a new query Named it StudentsQuery.

Right click and on data source click on add new data source and on property window select or right Student table as follow.

Click on Fields and from property window dynamics to yes. Like as

Now open a new Project In Visual studio 2010. Select form Install templates select Dynamics and then Report Model

Now next step to add a new report in newly created Report Model project

I named it simple student report.

Now right click on Dataset and add a new dataset in report and Name it StudentDs and from property of data set you have set following property

Click on Query and form dialog box you have to search and select Query selected Query form Dynamics Ax. In this report we created the query with StuentsQuery. On selecting and press next a new dialog open you from here you can select fields required for report in this report we are just use 4 fields

After selecting field now create a new precision design. Add new table and drop the field on column of table as follow. Press on review pane you find result like as

« Previous Page