Hi all, small tip. I faced this issue, when I extend the custom report in D365 for finance and operations. During development on onebox
Solution was simple,
- restart IIS services
- Restart Reporting Services.
Happy Daxing.
Dynamics Ax 2012 Reporting SSRS Reports.
float value conversion in string SSRS expression and decimal points SSRS dynamics ax 2012 R3.
Today I have to record very small tip. During development of on SSRS report for Dynamics Ax 2012 R3, End user wants a row at the report footer for analysis. he wants some kind of percentage calculation with ‘%’. A For this I have to use CStr ssrs expression function like
Ctr(((sum(DataSet!field.Value) / sum(DataSet!field.Value)))*100)+”%”.
Now it is string and shows number of decimal as it result from division. For example 5.4356333. But end user wants formatting like 5.43 .
It is string or text value and SSRS textbox formatting is not apply on it.
Following SSRS expression works for me.
Left(CStr(5.4356333),instr(CStr(5.4356333),”.”)+2)
My Updated expression is something like this
Left(CStr(IIF(Parameters!ProductionReportBM2DS_ItemName.Value = “ITEM-00000420”,((sum(Fields!Scrap.Value)/sum(Fields!TotalConsumption.Value))*100),((sum(Fields!CutLength.Value)/sum(Fields!TotalConsumption.Value))*100))),instr(CStr(IIF(Parameters!ProductionReportBM2DS_ItemName.Value = “ITEM-00000420″,((sum(Fields!Scrap.Value)/sum(Fields!TotalConsumption.Value))*100),((sum(Fields!CutLength.Value)/sum(Fields!TotalConsumption.Value))*100))),”.”)+2) +”%”
Skip certain values Report group sum SSRS Dynamics Ax 2012 R3. Dynamics 365 for operations
There is again small tips. Let me share you again scenario. Suppose you have to show customer Sale With customer group level. And group footer want to sum of Sales of that group. Now requirement is that customer with certain sub classification will skip in group sum. For example those customer who are on hold or blocked will not shown in report group sum, but shown in report detail
I was developing RDP based reports. So I add a int field in table. So I can mark specific record need to skip at group level sum.
I update the following SSRS expression to skip certain records at group level.
=Sum(iif(Fields!Flag.Value= 0, Cdbl(Fields!Value.Value), 0.0),”Group Name”)
Serial Number in Report Group SSRS Dynamics Ax 2012 R3.
Today I have small tip. Let me share a scenario. Suppose you have to display Customer group as report level group and customer at detail level who did purchased products from your organization in given period of time.
Serial number on detail level can be achieved by RowNumber(“Scope Name”)
But serial number shown on Group level is tricky because RowNumber not works there. For example in above mention scenario, Client wants serial number on Customer Group instead of Customer at detail level.
I used following single line SSRS expression helps me to achieve this.
=Runningvalue(Fields!FieldName.Value,countdistinct,”Dataset1″)
Advance SSRS Report Drill Through Action URL Dynamics ax 2012 R3
Let discuss real time scenario. In one of my custom SSRS report, Client wants to open Saleorder form by clicking on Customer Account, By default it leads to customer detail form. I used following link to build this functionality.
https://technet.microsoft.com/en-us/library/cc582049.aspx
https://dynamicsaxinsight.wordpress.com/2014/12/23/ax-2012-ssrs-report-drill-through-action-url/ to complete the re
If you read the Microsoft link on TechNet in one of above. It also described the way to call the form, whose reference method is not include in existing Drill through Common Helper class of SRSDrillThroughtCommon project.
But one thing is missing. What if the targeted form did not have code to catch coming record and did not show the desired result. For this we have to add some customization or add code to read upcoming record.
With following line of code you can catch SSRS passed record with Element.args().lookupRecord().
Common commonLookup;
commonLookup= element.args().lookupRecord();
.
The formatter threw an exception while trying to deserialize the message Dynamics Ax 2012 R3 SSRS
On running report, following error message appear in info box.
The formatter threw an exception while trying to deserialize the message: There was an error while trying to deserialize parameter http://tempuri.org/:queryBuilderArgs. The InnerException message was ‘Element ‘http://tempuri.org/:queryBuilderArgs’ contains data from a type that maps to the name ‘http://schemas.datacontract.org/2004/07/XppClasses:SrsReportProviderQueryBuilderArgs’. The deserializer has no knowledge of any type that maps to this name. Consider using a DataContractResolver if you are using DataContractSerializer or add the type corresponding to ‘SrsReportProviderQueryBuilderArgs’ to the list of known types – for example, by using the KnownTypeAttribute attribute or by adding it to the list of known types passed to the serializer.’. Please see InnerException for more details.
Solution:
- Stop AOS service.
- Stop Sql Server Service.
- Start SQL server Service.
- Restart SQL Server reporting services.
RegisterOverrideMethod was called twice for the same object for method Dynamics Ax 2012
I was developing the report by adding UI Class and building the drop down list to select multiple customer.
When I try to bind, DataSet of SSRS report to data provider class, it shows following error.
RegisterOverrideMethod was called twice for the same object for method ‘textChange’. You can only override a method once per instance.
On Exploring I found that I used the Same dialog field in two lookup methods.
SysLookupMultiSelectCtrl::constructWithQuery(this.dialog().dialogForm().formRun(), dlgCustAccount.control(), query, false, selectedFields);
water mark in SSRS report Dynamics Ax 2012.
Hi, all very small tip today, Recently I have to add water mark in one of custom report.
In SSRS for Dynamics Ax 2012, we can achieve water mark by adding background image in report body.
Water mark image should be small in size and used as background Image. If you use high in size image, Report will threw errors on deployed at server.
This water mark can hide and show with ssrs expression.
So complete real word requirement will be Print report with water mark if certain criteria meets, other wise print report normal report.
At SSRS side right right click on report Body and click on Body Properties.
From property window, select fill, select file the image source “embedded” and import file.
After import file will be shown as report explorer
Now add following expression to show report display water mark or not.
=IIf(Fields! DuplicateCopy.Value=1,”Duplicate3″,””)
No such host is known and AOS not reached SSRS Dynamics AX R3.
Yesterday I deployed the new SSRS customization to staging, so QA / Functional guy can test.
Client configured the AOS to Database server and Application server was moved for backup services.
Due to change in AOS server I have to face two issues.
“No Such Host is known.”
Whenever we run the report on staging server during report processing Error pop up appear with message “ Host not found “.
Modification in Windows Register, did not solve this problem. I have to reinstall the Reporting services extension form Dynamics Ax 2012 R3.
Second issue I faced that During installation of Reporting Extensions. Setup did complete and shows message about it did not locate the Required AOS. Interesting setup pointing to old server.
I solve this error by removing the credential save for Business connecter form Administration module for Dynamics AX. After rerun the reporting extension form AX Setup, Installation will complete successfully. Later deploy all reports and reports runs successfully.
Display methods in Report SSRS Dynamics Ax 2012 .
Hi, All, today I have to modify the report. Interestingly this report was built on AOT Query, instead of Data provider class. Current Scenario I was working on Worker Report. The requirement was to show’s primary position and department. HCMWorker table has two display method returns these values.
We can add these display methods into reports by following way.
For example I am modify the out of the box HCMWorkersHiredInPeriod Report.
Now Problem appears, we need different values then provided by out of the box display methods. For example we need Position Title instead of Primary Position Id. for this Add new display method that will return the Position Title instead of Position Id, compile table synchronize table, restore the AOT Query and refresh the report data Set. If there is no error in your working environment you will find required methods in report Data set.
An item with the same key has already been added. SSRS Dynamics Ax 2012
Today I got this strange error “An item with the same key has already been added “, Very interestingly I got no error during compilation or deployment of report
when I run the report I got this error
On investigate I found this error is due to meta data of report. Report is based on Query. On Exploring Query I found that I added the same field two times, At report run time I got error due to duplication of same field. If you see the below screen shoot you will found that “CustGroup” appears two times in Query, I removed the Duplicate field. compile the Query, Refresh the report data source, compile, deploy and add to AOT. Report run successfully.
Query Based SSRS report from Scratch Dynamics Ax 2012 R3.
In Microsoft Dynamics Ax 2012, we can create less complex reports with AOT Query or Static Query.
Simple or model based report is as follow.
http://dynamics-ax.blogspot.com/2011/12/ax-2012-ax-ssrs-report-design-concepts.html
The steps are as follow We can create simple or model based report with following steps
- Step 1: Create a Microsoft Dynamics AX query
- Step 2: Create a new report in Visual Studio
- Step 3: Apply Layout template
- Step 4: Add Column sorting
- Step 5: Group report data
- Step 6: Filter report data
- Step 7: Add a dynamic parameter
- Step 8: Save and deploy the report
- Step 9: View the report in Microsoft Dynamics AX
We can see these steps below
Now consider a scenario, where we have to display sales item detail with respect to its customer. As this report is not much complex and on exploring Dynamics Ax 2012 default tables we found that Sales order detail at Item level can be found on “SalesLine” table. Complexity is less so we decide to create this report with Static Query or AOT query based Report.
This Article is based on Contoso demo data, and Dynamics Ax 2012 R3 on demo licences
Open MS Dynamics Ax 2012 Client And press Ctrl+Shift+W to open dev environment or open the AOT.
When AOT open expand it and at AOT node add new query
Rename Query to simple “QSalesLine”.
Expand its data Source Node, right click and add new data Source
Rename the Data Source to SalesLine and select SalesLine table
Now expand the field Node and set its dynamic property to no
Right Click on Field and add new field and select salesId
Similarly you can add following fields
- CustAccount
- SalesId
- OrderQty
- SalePrice
- LineAmount
- ItemId,
- Name,
- CustGroup
Now save the Query.
Open Visual studio and create a new report project
Open Visual studio environment and create a new Report and Rename It to SalesLineReport
From solution Explorer add new report
Now expand report and right click on data Source to create a new data source Rename It to DSSaleLine
Right click on “DSSaleLine” it Edit it or click on properties and open property window
From Property window select
Click on Query and from Brower window select Required Query
Click on next window
Select all fields and click on save
Now drag the data set and drop on designs node, It will create auto Design
Right click on report designer and do the following
Rename AutoDesign to as “SalesLineDesign”
Select ReportLayoutTemple to ReportLayoutTemplate
Title to “CustomSalesLineReport”
and click on save.
Now expand “SalesLineDesign” design and drop and drop CustAccount in Group and Sort node. This will create the records group and line according to CustAccount
Now Compile the report, Deploy it and At to AOT.
Now open the AOT. Change the legal entity to “USMF” expand the menuItem node, and create a new display menu item
Change its Name to mnuCustomSalesLineReport
Change object Type to SSRS report and select the object is SalesLinereport and Report Design to SalesLineDesign
Now save the menu and let’s run the report
Click on ok to run the preview
Adding Dynamic Parameter
Create another query in AOT name it QCustomer, and add AccountNum is in field. You have to follow the same step which we used to create QSalesLine Query. Save the Query
Now switch back to model project in Visual studio and add new Dataset and Name it DSCustomer and pointed to QCustomer query the same way we select the Query.
Now expand parameter node of report
And add new Parameter with Name “CustomerParm”
Expand Values and set
Now expand Report designer and under filter node add new node
Right Click on AccountFilter and from property window
Click on expression To select = Fields!CustAccount
Operator select “Like”
And Value select =Parameters!CustomerParm.Value
Save the report and deploy it, and then Add to AOT.
Open Aot and from item display menu to run report
You will found a new drop down for parameter in
Form this parameter You can select and run the report for specific customer
Microsoft.Dynamics.Framework.Metadata.AX, Version=6.2.0.0 SSRS Report Error
Today I got very this error on deployment of customized SSRS report. The customization code was written in Dynamics AX 2012 R2 and it was successfully migrated to Dynamics Ax 2012 R3
System.Web.Services.Protocols.SoapException: Error while loading code module: ‘Microsoft.Dynamics.Framework.Metadata.AX, Version=6.2.0.0, Culture=neutral, PublicKeyToken=31bf3856ad364e35’. Details: Could not load file or assembly ‘Microsoft.Dynamics.Framework.Metadata.AX, Version=6.2.0.0, Culture=neutral, PublicKeyToken=31bf3856ad364e35’ or one of its dependencies. The system cannot find the file specified.
at Microsoft.ReportingServices.Library.ReportingService2005Impl.CreateReport(String Report, String Parent, Boolean Overwrite, Byte[] Definition, Property[] Properties, Guid batchId, Warning[]& Warnings)
at Microsoft.ReportingServices.WebServer.ReportingService2005.CreateReport(String Report, String Parent, Boolean Overwrite, Byte[] Definition, Property[] Properties, Warning[]& Warnings)
Solution is open the report In Visual studio, compile it, deployed it and add to AOT.
Unable to find appropriate service endpoint information in the configuration object Dynamics Ax 2012.
Recently installed the SSRS report extension on my VM Dynamics Ax 2012 R2 CU7. During installation, I continuously found following error message In log files.
“Unable to find appropriate service endpoint information in the configuration object”
Later I understand that SSRS reports communicate with AOS through WCF services and WCF service for Dynamics Ax was not running on AOS.
When I opened the Application Integration Framework è Inbound port, I found that BI service was deactivated.
I activate the service, and retry SSRS reporting extension installation, which installed successfully.
List of functions used in Dynamics AX 2012 SSRS Reports Expressions
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.
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
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.
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.
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
SQL Server 2012 Reporting Services UAC (User Access Control)
While configuring Reporting server with Dynamics Ax 2012 R23, I got error that Dynamics Ax 2012 R3 did not validate the Reporting Server. On Opening reporting server in Browser I got following error.
“User ‘Domain\User’ does not have required permissions. Verify that sufficient permissions have been granted and Windows User Account Control (UAC) restrictions have been addressed.”
After searching and trouble shooting, I found that nor Dynamics Ax Client or IE run with administrator rights.
First I run the IE with Administrator rights
From Start > All Programs > Internet Explorer, Right Click and choose Run as administrator
Enter the Reporting Server Address.
This open the Reporting server in IE.
After That I open the Dynamics Ax client By right click and open with Administrator
On Administrator section, on validate the configuration settings, and SSRS successfully configured on My Machine.
