Ali Raza Zaidi

A practitioner’s musings on Dynamics 365 Finance and Operations

Dynamics Ax 2012 Reporting SSRS Reports.

TempDB, SrsReportDataProviderPreProcessTempDB And long Running Report Dynamics 365 Finance and operations

by

Recently I got interesting issue. I developed the report based on RDP Class. I have to populate temp table. And based on this temp table I have to aggregate some calculation like sum, Average on Group by.

But when I populate another table based on this aggregation. Report starts to go long awaiting state. I dont know the reason. Later I found that I did not give active connection to temp table variables.

I added following line of code in reports and report processing reduce to less then 2 minutes

this.takeOwnershipOfTempTable(_BEGroupWiseSalarySheetTmp);
this.takeOwnershipOfTempTable(_BEBranchWiseSalarySheetTmp);

float value conversion in string SSRS expression and decimal points SSRS dynamics ax 2012 R3.

by

 

 

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

by

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.

by

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

by

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

by

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.

2016-12-06_9-58-01

 

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

by

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.

error

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.

by

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.

2016-08-04_11-38-16

From property window, select fill, select file the image source “embedded” and import file.

Import

After import file will be shown as report explorer

 

Duplicate

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.

by

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.”

2016-07-12_9-33-00

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.

Reporting Server Extension

 

 

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.

AoS problem

 

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.