AX 2012 R3 - Retail POS - CDX Server/Client/Real-time service certificate has expired.

When dealing with the reail pos system in AX you need to create self-signed certs in order for the cdx server/client and real-time service calls to work. However these self-signed certs expire and when they do you notice the entire system come to grinding halt and will see something like the following in windows event viewer

Unable to communicate with server for upload. Please check username/password, server and database connections. Error Details: System.ServiceModel.Security.SecurityNegotiationException: Could not establish trust relationship for the SSL/TLS secure channel with authority '<server name>:8304'. ---> System.Net.WebException: The underlying connection was closed: Could not establish trust relationship for the SSL/TLS secure channel. ---> System.Security.Authentication.AuthenticationException: The remote certificate is invalid according to the validation procedure.

In order to resolve the expired certificate we will need to do the following:

• Generate new FQDN self-signed certs via iis on the machines that you need it (depending on the environment you are working with will determine which certs need to be created)
• Add new certs to the servers that host cdx server, cdx client, real-time service and pos client via MMC under the Personal and Trusted root certification authorities
• After the cert is generated you will need to grab the thumb from the cert.
  *Note paste the cert thumbprint into notepad++ and change encoding to ansi and to show special characters so you can remove any special characters within the thumbprint. You will also need to remove the spaces in the thumbprint
• Update C:\Program Files (x86)\Microsoft Dynamics AX\60\CDX\Async Server\Package\web.config to include new cert thumbprint
• Update C:\Program Files (x86)\Microsoft Dynamics AX\60\CDX\Real-time Services\6.3\web.config to include new cert thumbprint

• Update C:\inetpub\AsyncServerSite\CDX_AsyncServer\web.config to include new cert thumbprint (if iis instance for cdx server created a new folder and not utilizing the folder above)

• The setting with the thumbprint should be listed under the tag <serviceCertificate findValue=

• Run issreset + restart the cdx client service

• Verify in event viewer that the connection is now made successfully. on the cdx server, client, real-time service 

• Run a sync in AX via /Retail/Periodic/Data distribution/Distribution schedule or /Retail/Setup/Retail scheduler/Channel integration 

• Once this has been ran make sure the jobs are processing successfully via /Retail/Inquiries/Commerce Data Exchange/Download sessions if they are processing then the async server connection status (heartbeat) should have been updated as well that is located @ /Retail/Inquiries/Commerce Data Exchange/Async Server connection status

Everything should now be good to go and you can start processing POS transactions once again.

AX 2012 R3 - Retail POS - Clear all existing transactions from the POS DB/CDX Server/CDX Client

When reloading environments sometimes it is necessary to reset the retail pos, cdx server, cdx client and back office databases to show none of the previous transactions that once existed within the system.

In order to clear it out we need to do the following (with CDX server + client services offline)

POSDB - AXRetailPOS

--current transactions within the pos system

truncate table ax.RetailPosBatchAccountTrans

truncate table ax.RetailPosBatchTable

truncate table ax.RetailPosBatchTenderTrans

truncate table ax.RetailTransactionBankedTenderTrans

truncate table ax.RetailTransactionTenderDeclarationTrans

truncate table ax.RetailTransactionDiscountTrans

truncate table ax.RetailTransactionIncomeExpenseTrans

truncate table ax.RetailTransactionInfocodeTrans

truncate table ax.RetailTransactionLoyaltyRewardPointTrans

truncate table ax.RetailTransactionOrderInvoiceTrans

truncate table ax.RetailTransactionPaymentTrans

truncate table ax.RetailTransactionSafeTenderTrans

truncate table ax.RetailTransactionSalesTrans

truncate table ax.RetailTransactionTable

truncate table ax.RetailTransactionTaxTrans

CDX_AsyncClient DB

--clear previous syncs

truncate table dbo.datastore

truncate table dbo.downloadsession

truncate table dbo.downloadsessionLog

truncate table dbo.offlinedataexportlog

CDX_AsyncServer DB

--clear previous syncs

truncate table dbo.UPLOADSESSIONLOG

truncate table dbo.UPLOADSESSION

truncate table dbo.DOWNLOADSESSIONDATASTORELOG

truncate table dbo.DOWNLOADSESSIONDATASTORE

truncate table dbo.DOWNLOADSESSION

--clear base info (optional)

truncate table dbo.FILESTORAGEPROVIDER

truncate table dbo.JOB

truncate table dbo.SCHEDULERINTERVAL

truncate table dbo.SCHEDULEPARAMETER

truncate table dbo.SCHEDULEJOB

truncate table dbo.SCHEDULEDATAGROUP

truncate table dbo.DATASTOREHEARTBEATLOG

truncate table dbo.DATASTORE

truncate table dbo.DATAGROUP

truncate table dbo.AUTHENTICATIONLOG

If there are transaction with the back office AX then you may need to do the following

Company/Retail/Journals/Open statements > remove all statements that pertain to the pos system

Then run the following job

static void ClearRetailTransactions(Args _args)

{

    RETAILTRANSACTIONBANKEDTENDERTRANS retailTable1;

    RETAILTRANSACTIONTENDERDECLARATIONTRANS retailTable2;

    RETAILTRANSACTIONDISCOUNTTRANS retailTable3;

    RETAILTRANSACTIONINCOMEEXPENSETRANS retailTable4;

    RETAILTRANSACTIONINFOCODETRANS retailTable5;

    RETAILTRANSACTIONLOYALTYREWARDPOINTTRANS retailTable6;

    RETAILTRANSACTIONORDERINVOICETRANS retailTable7;

    RETAILTRANSACTIONPAYMENTTRANS retailTable8;

    RETAILTRANSACTIONSALESTRANS retailTable9;

    RETAILTRANSACTIONTABLE retailTable10;

    RETAILTRANSACTIONTAXTRANS retailTable11;

    

    ttsbegin;

    delete_from retailTable1;

    delete_from retailTable2;

    delete_from retailTable3;

    delete_from retailTable4;

    delete_from retailTable5;

    delete_from retailTable6;

    delete_from retailTable7;

    delete_from retailTable8;

    delete_from retailTable9;

    delete_from retailTable10;

    delete_from retailTable11;

    ttscommit;

    info('done');

}

Finally you will want to remove all of the previous cdx sync files located in 

Company/Retail/Setup/Retail scheduler/Channel integration/working folders

Once this is completed you should follow the normal store setup if it is not already done.

Note: if you run the sql under --clear base info (optional) you will need to run the sync metadata function (button) that is located @

Company/Retail/Setup/Parameters/Retail scheduler parameters

Update: depending on if you are transferring data from one environment to the next  you may run into an issue where the data will download but wont upload you may need to clear out/update the following tables depending on what you are trying to do.

Clearing these tables will force a complete refresh of all pos transactions into the back office.

POSDB - AXRetailPOS:
truncate table [crt].[TABLEREPLICATIONLOG]
truncate table [crt].[UPLOADSESSION]

R3 Retail Async Client + Server Error: Unable to communicate with server for upload. Please check username/password, server and database connections.

Recently I came across a strange error when dealing with the new Async Server + Client feature within AX 2012 R3 to support a channel database for a retail store/pos setup.

This environment had been working just fine with no errors for the past couple of months but I just noticed that none of the download/upload files were being processed by the server and client for the past couple of days. Nothing had changed in the AX, SQL or account access that pertained to this environment.

When looking in the event login I saw the following


CDX Client: Unable to communicate with server for upload. Please check username/password, server and database connections. Error Details: System.ServiceModel.Security.MessageSecurityException: An unsecured or incorrectly secured fault was received from the other party. See the inner FaultException for the fault code and detail. ---> System.ServiceModel.FaultException: Invalid username or password
   --- End of inner exception stack trace ---

CDX Server: Invalid username or password

However I knew that my username/password combination was valid and my certs for the aos + cdx server were still valid.

After doing some basic troubleshooting I found that the following fixed the error on both the server + client.

Retail > Setup > Parameters > Retail scheduler parameters > Sync metadata

I then restarted the async client service + did an issreset on the async server. I am not sure the last part was necessary but restarting them forces it to try to reconnect to AX.

AX - SSRS - SrsReportDataProviderPreProcess - Warning the user of a long run time

When running a preprocessing type of report it may cause the users AX client to freeze. So in order to let them know that it is actually running we may want to alert the user and give them the ability to choose not to run the report.

We can do this by overriding the preRunValidate() method on the controller class.

protected container preRunValidate()
{
    //let the user know that it will take a while to run
    return [SrsReportPreRunState::Warning, "This report may take a long time to run are you sure you want to run it?"];
}

SrsReportPreRunState (ENUM) is the type of alert. You can choose the following types

SrsReportPreRunState::Warning - will allow the user to choose if they should run the report or not
SrsReportPreRunState::Error - will stop the report from running
SrsReportPreRunState::Run - will start the report

Given these 3 types of commands available we can create logic based on current information and what course of action we can take.

By default the container only expects the enum SrsReportPreRunState but if we add a 2nd element then we can override the default text as well thus giving the user a more descriptive alert message.

example

[SrsReportPreRunState::Error, "The report has been canceled because it should only be run during the weekend"];

AX SSRS - Regular Processing (SRSReportDataProviderBase) VS PreProcessing (SrsReportDataProviderPreProcess) (fixes timeout issues on reporting)

Sometimes in AX we need to create reports that exceed the default run-time limits defined in AX (default is 10minutes) because of the amount of data we are either calculating or returning. As discussed in one of my other posts (AX SSRS Report times out after 10 minutes) one of the ways to overcome this is to change the config files of the client/ssrs server but another way to overcome this limit and improve the speed in which a report can be ran is changing your data provider to extend the class 'SrsReportDataProviderPreProcess' instead of the regular type of 'SRSReportDataProviderBase'

I found this great blog entry over at msdn (click here) with this nice graphic shown below that explains the differences between the two. But long story short the difference is we are utilizing the aos to generate the data then handing the results to ssrs rather than handing the logic to ssrs and letting it generate the data. By doing this we can bypass the time out that is defined in ssrs because the data is already generated before we hand it off and we can improve runtime because it is now generating the data on the aos server.


Regular processing:



Pre-processing:

In order to switch from the regular process we need to make the following changes:

1. On the table that is returned as a dataset to the report make the following changes
1. Created by - Yes
2. CreatedTransactionId - Yes
3. TableType - Regular
2. Change the DP class so it extends SrsReportDataProviderPreProcess
3. Within the DP class.processReport() method add the following code reportData.setConnection(this.parmUserConnection()); where reportData is the table that is returned as a dataset within your report
4. Execute a incremental cil
5. Open the SSRS report and hit refresh on the dataset. At this point you should see the field 'createdTransactionId' added to the available field.
6. Deploy report
7. Security Note: For your security object it is good to note that when using this preprocessing method you need to add the DPClass.processReport() method to the Server methods node in the privilege associated with the report

Check to see if a file is locked by another process. Error: The process cannot access the file 'filelocation' because it is being used by another process.

Sometimes when dealing with scenarios where you need to generate a file and then do something else with said file the code base can tell you the file is created and ready for use but the OS has not released the file lock. This can also occur when trying to grab or do something with a file that is located on a network share that other users may be modifying at any given time. Because of these scenarios AX can give you an error

"The process cannot access the file 'filelocation' because it is being used by another process."


The following code will allow you to check to see if the os has a file in a 'locked' status. I ended up adding a special wait 4 second clause after retrying 10x because I was finding all of the retries were finishing within under 1 second so the system didn't have a chance to finish what it was doing. You can remove the 2nd retry if this clause does not suite your needs.


/// <summary>
///  Check to see if a file a locked
/// </summary>
/// <param name="fileName">
/// File Name + Path
/// </param>
/// <returns>
/// If the file is locked
/// </returns>
/// <remarks>
/// The first 10 trys were executing under 1 sec so I added a sleep if its still locked after 10 trys to be safe.
/// </remarks>
public boolean isFileLocked(FileName fileName)
{
    Counter retryCount;
    System.Exception clrException;
    System.Type exceptionType;
    boolean validRetry;
    boolean isLocked;
   
    new InteropPermission(InteropKind::ClrInterop);
    new FileIOPermission (Filename, 'rw').assert();
    try
    {
        //check to see if we can open the file
        System.IO.File::Open(fileName, System.IO.FileMode::Open);
        CodeAccessPermission::revertAssert();
    }
    catch(Exception::CLRError)
    {
        validRetry = false;
       
        //get the last CLR error and display it to the user
        clrException = CLRInterop::getLastException();
        if(clrException.get_InnerException() != null)
        {
            clrException = clrException.get_InnerException();
            exceptionType = clrException.GetType();
            //check to see if the exception type is a valid one we need to retry
            switch(CLRInterop::getAnyTypeForObject(exceptionType.get_FullName()))
            {
                case 'System.UnauthorizedAccessException':
                    validRetry = true;
                    break;
                case 'System.IO.IOException':
                    validRetry = true;
                    break;
                default:
                    throw error(CLRInterop::getAnyTypeForObject(clrException.get_Message()));
            }
        }
        //check to see if we have a valid file to retry
        if(validRetry)
        {
            ++retryCount;
            if(retryCount <= 10)
            {
                //execute a retry the first 10x right away
                retry;
            }
            else if(retryCount == 11)
            {
                //on the last try sleep for 4 seconds then try again
                sleep(4000);
                retry;
            }
            else
            {
                isLocked = true;
                CodeAccessPermission::revertAssert();
            }
        }
    }
    return isLocked;
}


I am currently using the tool Easy File Locker located here in order to test this code out.

CostSheetCache table is too large. How to clear it out

The CostSheetCache table which is used as a temp location for cost sheets to help generate prices is not cleared automatically in AX unless you tell it to. So it can become very large inside of your DB (sometimes 10-30GB+) There are two ways you can clear out this information properly to keep this table small in size.

1.) Manually trigger the clearing inside of AX (this is the only way I know how to do it via default AX)
Go to  Production Control / Setup / Routes / Cost Groups​  Modify any record on this form and it will trigger the cleanup whenever you close the form.

2.) Create a job with the following code.

static void CostSheetCacheClear(Args _args)
{
    //this will clear the table CostSheetCache
    CostSheetFactory::construct().clearCachedData();
    info("Cleared the table CostSheetCache");
}

This is the same code that gets triggered in step 1 and can be found on

\Tables\BOMCostGroup\insert(), update(), delete()

AX SSRS - Repeat Row/Column Headers on every page

If you need to repeat a row header on every page within a report that is being generated from AX you will have noticed that the majority of the times the default settings of "repeat header" under row/column properties does not work. The method below will always work.


1.) Create a new group on the report that is at the top most level. Lets give it a name of "RepeatHeader" (or whatever you like) When it asks what to group by you need to input 0. Yes the number 0, no spaces, no quotes, just 0.

 

2.) On the grouping "frame" there is a down arrow on the top right. Click it and choose "Advanced Mode" You will now see "static" rows within your grouping list.

3.) On the static row above your new group go to properties and change the following

KeepWithGroup = After
RepeatOnNewPage = true

You should now have a row that contains headers that repeat on every page. If they are not repeating then your existing header row may not be within the correct group and you may need to change it according to how your data's grouping structure is set.

CostSheets - Finding the costing header that is associated with a cost group id

If you take a look at costing sheets within AX 2012 (Company/Inventory and warehouse management/Setup/Costing/Costing Sheets) you will find that this is where the item price costing sheet structure is setup (Cost group id <-> Headers) This will allow you to break down/sum up an items price by costgroupid and then summary/costing headers.

However whenever you try to view the data that will populate your cost sheet via the table BOMCalcTrans you will find that everything is related to the cost group id which is good for displaying the "details" of the costsheet but we sometimes need to summarizes the cost group ids by which header it is under. By using the method bellow you can create a "summary" of your bomcalctrans table.


(note CostGroupHeaderDescription EDT is just a generic str edt I created to store a header group id + description)

This method will only work if you use the header checkbox shown on the Costing sheet setup form listed above under Edit>Identification>Setup>Header

(you can easily can this logic to include the total check box as well or use both fields)

/// <summary>
/// Find the costing header that a cost group id is associated with
/// </summary>
/// <param name="costGroupId">
/// Cost Group Id
/// </param>
/// <returns>
/// Costing Header Code + Description
/// </returns>
/// <remarks>
/// The will pull the header node that a cost group id is under
/// </remarks>
public CostGroupHeaderDescription getCostCategoryByCostGroupId(CostGroupId costGroupId)
{
    CostSheetNodeTable costSheetNodesBase, costSheetNodesParent;
    boolean foundTotalNode;
    CostSheetNodeId currentNodeIdToSearch;
    CostGroupCodeAndDescription description;

    //find the base instance to find the parent node to see where we need to start the search
    select NodeId, ParentNodeId, Code, Description, Total, Header from costSheetNodesBase where costSheetNodesBase.CostGroupId == costGroupId;

    //check to see if we are already at the header level when we are at the base
    if(costSheetNodesBase.Header == NoYes::Yes)
    {
        foundTotalNode = true;
        description = strFmt("%1 (%2)", costSheetNodesBase.Code, costSheetNodesBase.Description);
    }
    currentNodeIdToSearch = costSheetNodesBase.ParentNodeId;
    //execute this logic until we find a node flagged as header
    while(!foundTotalNode)
    {
        //find the parent nodes record to see if it is a header record
        select NodeId, ParentNodeId, Code, Description, Total, Header from costSheetNodesParent where costSheetNodesParent.NodeId == currentNodeIdToSearch;
        if(costSheetNodesParent)
        {
            //check to see if the current node is a header which is the topmost bucket that we need to log
            if(costSheetNodesParent.Header == NoYes::Yes)
            {
                foundTotalNode = true;
                description = strFmt("%1 (%2)", costSheetNodesParent.Code, costSheetNodesParent.Description);
            }
            else
            {
                //the current node is not a header so we need to get the parent node of the parent
                currentNodeIdToSearch = costSheetNodesParent.ParentNodeId;
                if(currentNodeIdToSearch == 0)
                {
                    //if we have reached the top most level there has to be a misconfig issue
                    throw error(strFmt("No header cost sheet node was found for cost group %1 (%2). Please check the configuration of the costsheet", costSheetNodesBase.Code, costSheetNodesBase.Description));
                }
            }
        }
        else
        {
            throw error("There was an issue finding the parent node cost sheet record.");
        }
    }
    return description;
}

AX SSRS Report times out after 10 minutes (Error: A connection attempt failed because the connected party did not properly respond after a period of time, or established connection failed because connected host has failed to respond)

Sometimes when running a report that is going to return a large data set you may get the following error message after 10 minutes:

"A connection attempt failed because the connected party did not properly respond after a period of time, or established connection failed because connected host has failed to respond"

Whenever you receive this error the first thing you should look at is your DP class and can performance be improved by rewriting some of your code. If it cant be or this is a default ax report sometimes your only option is to increase the default timeout period.

In order to increase the timeout for an AX SSRS report you need to do the following:

1.) Create a new client config(or copy an existing one.) This is the same config that could be used as your .axc file that is used to access different environments/aos. However this will have larger timeout values in it so only use this on the server mentioned in step 4. You do not need your user's applications config to have these settings.

2.) Update the binding node of "QueryServiceEndPoint" and change the sendTimeout property from "00:10:00" (10 minutes) to whatever timeout value you like in hh:mm:ss format. You can also update any other timeout setting as well if you like. (Just search for 00:10:00 within the document and replace it with your new time)

3.) Save the new config as  "Microsoft.Dynamics.AX.ReportConfiguration.axc" (https://technet.microsoft.com/en-us/library/hh389774.aspx)

4.) Copy the new file Microsoft.Dynamics.AX.ReportConfiguration.axc into the following folder on your sql/ssrs server (inside of the os/windows file system)

C:\Program Files\Microsoft SQL Server\MSRS11.<AOS>\Reporting Services\ReportServer\bin

Note: This is for a sql2012 instance. If you are running a different version of ms sql please refer to the following TechNet article to find the location of your report server bin folder https://technet.microsoft.com/en-us/library/hh389774.aspx if you need help locating it.

5.) Run the report and the report should no longer time out. You do not need to restart the aos, sql or ssrs services in order for the changes to take place once the file is in this folder it should now respect your new timeout settings


If this does not fix your issue please refer to the following sites which can also help you on time out issues with ssrs reports

TechNet: Tips to help prevent long-running reports from timing out [AX 2012]

You can also change the timeout settings on ssrs via the following method:

Global timeout setting: Go to site settings > General > Report Timeout > do not timeout report.
Report timeout setting:  Go to the report > Manage > Processing options > Report time out> do not timeout report.


There are also some timeout settings if you connect to the reporting service via sql management studio. Right click on your server node and go to properties > Advanced.


Update: Also see AX SSRS - Regular Processing (SRSReportDataProviderBase) VS PreProcessing (SrsReportDataProviderPreProcess) (fixes timeout issues on reporting)


Update 2: After applying the Microsoft.axc file to the ssrs server you may run into a security issue (Microsoft Dynamics AX 2012 Reporting extensions: Error System.Security.Permissions.EnvironmentPermission while running report)

To fix this go to the SSRS installation folder and modify rssrvpolicy.config Report_Expressions_Default_Permissions node from "Execution" to "FullTrust" (https://blogs.msdn.microsoft.com/axsupport/2012/02/02/microsoft-dynamics-ax-2012-reporting-extensions-error-system-security-permissions-environmentpermission-while-running-report/)