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/)