SSRS Contract class parameters not refreshed

When we design the report the parameters are copied from the contract class and the details for them are stored in the report parameters. 

In case that we later change the parameters in the contract class and the same are not refreshed in the SSRS Report parameters, there is a possibility that we will continue to see the parameters even though they are deleted from the contract class. This is due to the fact that the parameters are being fetched from the SSRS report. 

To get rid of the issue, delete all the parameters in the report design, and restore the dataset again. When the dataset is refreshed the required parameters are automatically added to the report. 

X++ check for null value

X++ was designed in a way that the complications around null value are not required and hence X++ manages a default value always to ensure that null exceptions do not occur. The null can however occur when there is a view defined with outer joins. 

The obvious option it to handle this using SQL and a computed column in views. However in my case the columns were far too many to created a computed column for each and hence i wanted to handle null in the x++ code itself. I wanted to check if the value of a field in the view in null and accordingly take an action to either copy or ignore it. 

the easiest way to do it, let x++ evaluate it on its own by placing it in round brackets. If the field is not null it would evaluate to true and in case of null it would evaluate to false.  

 employeeNewJoinedTmp.Grade   = ( workerDetails.Grade) ?  "" : workerDetails.Grade;

Sequence contains no matching element at System.Linq.Enumerable.Single[TSource](IEnumerable`1 source, Func`2 predicate)

This error occurs when there is a parameter at the subnode (Dataset) level, which is  supposed to be mapped to report parameter; however, it does not map to one. 

This can happen when we selected to have the Dynamics Filters = True for the dataset (which would create corresponding elements at the report level) and later deleted them from the report level, thus leaving the dataset level filters as orphan and without a matching filter. 

In the screen above we can see that the HeaderFooter dataset refers to a parameter called HeaderFooter_parmBankAccountId and this does not exist at the report level. We should map this to a valid report parameter if it exists, or we can set the dynamic filters to false and then true again so that these filters are created again if missing. 

The number of defined parameters is not equal to the number of cell definitions in the parameter panel

 This issue can occur when a developer copies a report from an existing design and tries to make the changes to the dataset. 

There are parameters passed between the AX system and SSRS framework and these need to be in Sync. The parameters are stored at 3 levels and it needs to be in Sync. Sometimes it can happen that due to the edits one makes these move out of Sync in such cases we have to manually fix it. 

Its important that for every parameter at the report level, there is a corresponding parameter at the dataset level. 

The other odd place where the parameters are saved is in the XML file and it can sometimes not be seen/referred on the screen. Hence to fix these we should open the report using an xml editor. Right click the report and choose to open with XML editor.

Once the xml editor is opened , one can see that there parameters referred in the XML file which do not exist on the dataset. These are from a previous dataset PromotionSalaryDS which now is probably removed and hence the mismatch. 

We should manually delete these parameters to match the list as per the report parameters. 

You will also have to delete the parameters if they are referred in the Parameter cell definition. Please ensure that you delete each xml block as a unit. One unit is highlighted below it starts with CellDefinition and ends with /CellDefinition

   

X++ get T-SQL Statement

Something that is frequently required to debug SQL statement is the select query that is generated. To get the SQL statement associated with the x++ select 

Use the generate only and forceliterals clause in the statement. 

        select generateonly forceliterals disbursementJournal
            join disbursementDetails
            where disbursementJournal.DisbursementDetailsRecId == disbursementDetails.RecId
                && disbursementJournal.OffsetBankAccount == _bankAccountId
                && disbursementDetails.PayrollPeriodLine == _payrollPeriodLine
            join hcmWorker
            where hcmWorker.RecId == disbursementJournal.Worker
            join workerBankAccount
            where workerBankAccount.Worker == disbursementDetails.Worker
                && workerBankAccount.RecId == disbursementJournal.WorkerBankAccount
            join companyBankAccount
            where companyBankAccount.AccountID == disbursementJournal.OffsetBankAccount
            join payrollPeriodLine
            where payrollPeriodLine.RecId == disbursementDetails.PayrollPeriodLine;

        info( disbursementJournal.getSQLStatement()); 

DateTime in Dynamics

DateTime fields have some complexity added to them because these fields are stored in SQL server and are displayed in Dynamics as per the user timezone. 

Because timezone is not stored along with the DateTime filed in SQL; Dynamics always assumes that the date is in UTC date time format and if it needs to be displayed to the user then it is convereted from that timezone to the user timezone by the dynamics framework.

This understanding is important when working with DateTime fields as the data that one might see in the SQL database might be different and that is because the framework is converting the time from the user timezone to UTC.

The different timeZones that the system supports are defined as a TimeZone enum

•    Timezone::GMTMINUS0600CENTRALTIME;
•    Timezone::GMTMINUS0500EASTERNTIME;
•    Timezone::GMTMINUS0800PACIFICTIME;

There are a lot of functions in the DateTimeUtil class to hanlde the different time conversion scenarios the important ones are : 

• Convert to the logged in users TimeZone
  DateTimeUtil::applyTimeZoneOffset(myDateTime,DateTimeUtil::getUserPreferredTimeZone());
• Convert to a specific TimeZone
  DateTimeUtil::applyTimeZoneOffset(myDateTime,myTimeZone);