Getting Duplicate records in SSRS reports

Finance and Operations/AX Developer Forum
,
1

Hi Community,

I am getting duplicate records while running a SSRS report
I used the following code :

[
SrsReportParameterAttribute(classStr(MC_StageSalesErrorContract)),
SRSReportQueryAttribute(queryStr(MC_StageSalesErrorQuery))
]
class MC_StageSalesErrorDp extends SRSReportDataProviderBase
{
MC_StageSalesErrorContract contract;
FromDate _fromDate;
ToDate _toDate;

Query                                       query;
QueryBuildDataSource                        qbdsStageSalesTable, qbdsStageSalesLine;
QueryBuildDataSource                        qbdsDMFExecution, qbdsDMFDefinitionGroup, qbdsDMFStagingValidationLog, qbdsDMFDefinitionGroupExecution;
QueryBuildRange                             queryBuildRange;
QueryRun                                    queryRun;

MC_StageSalesTableStagingTmp                stageSalesTableTmp;
MC_HIMSIntegrationSalesStageTableStaging    stageSalesTable;
MC_HIMSIntegrationSalesStageLineStaging     stageSalesLine;

DMFExecution                                dMFExecution;
DMFDefinitionGroup                          dMFDefinitionGroup;
DMFStagingValidationLog                     dMFStagingValidationLog;
DMFDefinitionGroupExecution                 dMFDefinitionGroupExecution;

[SrsReportDataSetAttribute(tableStr(MC_StageSalesTableStagingTmp))]
public MC_StageSalesTableStagingTmp getData()
{
    select stageSalesTableTmp;
    return stageSalesTableTmp;
}

public void processReport()
{
    super();
    utcdatetime         fromDateTime;
    utcdatetime         toDateTime;

    query       =   this.parmQuery();

    contract    =   this.parmDataContract() as MC_StageSalesErrorContract;
    _fromDate   =   contract.parmFromDate();
    _toDate     =   contract.parmToDate();

    if(_fromDate && _toDate)
    {
        fromDateTime = DateTimeUtil::newDateTime(_fromDate, 0*0*0, DateTimeUtil::getUserPreferredTimeZone());
        toDateTime   = DateTimeUtil::newDateTime(_toDate, 24*60*60, DateTimeUtil::getUserPreferredTimeZone());

        qbdsStageSalesTable = query.dataSourceTable(tableNum(MC_HIMSIntegrationSalesStageTableStaging));
        qbdsStageSalesTable.addRange(fieldNum(MC_HIMSIntegrationSalesStageTableStaging, TransferStatus)).value(SysQuery::valueNot(DMFTransferStatus::Completed));

        qbdsDMFExecution    =   qbdsStageSalesTable.addDataSource(tableNum(DMFExecution));
        qbdsDMFExecution.addLink(fieldNum(MC_HIMSIntegrationSalesStageTableStaging, ExecutionId), fieldNum(DMFExecution, ExecutionId));
        qbdsDMFExecution.addRange(fieldNum(DMFExecution, CreatedDateTime)).value(queryRange(fromDateTime, toDateTime));

        qbdsDMFDefinitionGroupExecution = qbdsStageSalesTable.addDataSource(tableNum(DMFDefinitionGroupExecution));
        qbdsDMFDefinitionGroupExecution.addLink(fieldNum(DMFExecution, ExecutionId), fieldNum(DMFDefinitionGroupExecution, ExecutionId));

        qbdsDMFDefinitionGroup = qbdsStageSalesTable.addDataSource(tableNum(DMFDefinitionGroup));
        qbdsDMFDefinitionGroup.addLink(fieldNum(DMFDefinitionGroupExecution, DefinitionGroup), fieldNum(DMFDefinitionGroup, DefinitionGroupName));
        qbdsDMFDefinitionGroup.addRange(fieldNum(DMFDefinitionGroup, ProjectCategory)).value(queryValue(DMFProjectCategory::Integration));
        qbdsDMFDefinitionGroup.addRange(fieldNum(DMFDefinitionGroup, OperationType)).value(queryValue(DMFOperationType::Import));

        qbdsDMFStagingValidationLog = qbdsStageSalesTable.addDataSource(tableNum(DMFStagingValidationLog));
        qbdsDMFStagingValidationLog.addLink(fieldNum(MC_HIMSIntegrationSalesStageTableStaging, ExecutionId), fieldNum(DMFStagingValidationLog, ExecutionId));

    }

        queryRun = new QueryRun(query);

        ttsbegin;
        while(queryRun.next())
        {
            stageSalesTable         = queryRun.get(tableNum(MC_HIMSIntegrationSalesStageTableStaging));
            dMFExecution            = queryRun.get(tableNum(DMFExecution));
            dMFDefinitionGroup      = queryRun.get(tableNum(DMFDefinitionGroup));
            dMFStagingValidationLog = queryRun.get(tableNum(DMFStagingValidationLog));

            stageSalesTableTmp.clear();

            stageSalesTableTmp.DefinitionGroup      =   dMFExecution.DefinitionGroup;
            stageSalesTableTmp.Description          =   dMFExecution.Description;
            stageSalesTableTmp.Company              =   dMFExecution.Company;
            stageSalesTableTmp.ExecutionId          =   stageSalesTable.ExecutionId;
            stageSalesTableTmp.TransferStatus       =   stageSalesTable.TransferStatus;
            stageSalesTableTmp.ErrorMessage         =   dMFStagingValidationLog.ErrorMessage;
            stageSalesTableTmp.ExecutionTime        =   dMFExecution.CreatedDateTime;
            stageSalesTableTmp.OperationType        =   dMFDefinitionGroup.OperationType;
            stageSalesTableTmp.ProjectCategory      =   dMFDefinitionGroup.ProjectCategory;
            stageSalesTableTmp.DocumentType         =   stageSalesTable.DocumentType;
            stageSalesTableTmp.HIMSTransType        =   stageSalesTable.HIMSTransType;
            stageSalesTableTmp.AccountNum           =   stageSalesTable.AccountNum;
            stageSalesTableTmp.PartyCategory        =   stageSalesTable.PartyCategory;
            stageSalesTableTmp.IPNumber             =   stageSalesTable.IPNumber;
            stageSalesTableTmp.InvoiceId            =   stageSalesTable.InvoiceId;
            stageSalesTableTmp.InvoiceDate          =   stageSalesTable.InvoiceDate;
            stageSalesTableTmp.HIMSCostCenter       =   stageSalesTable.HIMSCostCenter;
            stageSalesTableTmp.HIMSCostCenterName   =   stageSalesTable.HIMSCostCenterName;
            stageSalesTableTmp.HIMSDepartment       =   stageSalesTable.HIMSDepartment;
            stageSalesTableTmp.HIMSDepartmentName   =   stageSalesTable.HIMSDepartmentName;
            stageSalesTableTmp.NetAmount            =   stageSalesTable.NetAmount;
            stageSalesTableTmp.GrossAmount          =   stageSalesTable.GrossAmount;
            stageSalesTableTmp.SponsorCategory      =   stageSalesTable.SponsorCategory;
            stageSalesTableTmp.SponsorDueAmount     =   stageSalesTable.SponsorDueAmount;
            stageSalesTableTmp.SponsorDiscount      =   stageSalesTable.SponsorDiscount;
            stageSalesTableTmp.PartyName            =   stageSalesTable.PartyName;
            stageSalesTableTmp.FirstName            =   stageSalesTable.FirstName;
            stageSalesTableTmp.MiddleName           =   stageSalesTable.MiddleName;
            stageSalesTableTmp.LastName             =   stageSalesTable.LastName;
            stageSalesTableTmp.PartyType            =   stageSalesTable.PartyType;                
            stageSalesTableTmp.CountryCode          =   stageSalesTable.CountryCode;
            
            stageSalesTableTmp.insert();
        }
        ttscommit;
}

}

My requirement is that to get the list of records of errors and its message, and the records which haven’t posted (I.e. - Other that Completed) . I need these records while we do Import in Integration.

I need to know the reason for, why I am getting duplicate records. Are the relations between the Tables (which I used in above Code) are Valid ? And also is there any possibilities to code in DP Class ( Other that using those While Statements using JOIN).

-Thanks in Advance

2

All right, you’re saying that your query returns duplicate records in some tables, while you believe that they shouldn’t. This is likely caused by joining a table that returns more than a single related record. Which are the tables returning unexpected data?

Can you share the query string with us, please?

3

I didn’t used any Query string in the code and all the tables needs to be called once for one record. If a record has more that one line than that tables need to loop for all the lines in a record.

5

I don’t mean that you used a query string in code, but that you can see what query is generated by your code.

Your statement “If a record has more that one line” doesn’t make sense. A record is a single row in a table; several lines means several records.

I think you mean related records in multiple table. For example, let’s say that we have a sales order with three lines. If you create a query joining the order header with lines, you’ll get three records, because there are three lines. If you want to get just the order header, don’t join it with lines. If you want to return just the header but apply some conditions for lines (e.g. returning an order with at least one line with a given item), use an exists join instead of the inner join.