Dynamics Community Forum Thread Details

Hi Experts,

Somehow SSRS report figure not matching with SQL query figure. In SSRS report Active customer is 555 and in SQL is 688.

I have run set date range from 1-Mar-2022 to 1-Mar-2023. Please help me I tried even extracting in excel sheet and it is showing exact 688.

Below is SQL result:

ProcessReport method is below:

  public void processReport()
    {
       
       
        contract = this.parmDataContract() as SFA_RetentionContract;
        TransDate transDate = contract.parmReportDate();
        
        CustTable custTable, custTableNew;
        CustInvoiceJour custInvoiceJour, custInvoiceJourNew;
        CustTrans custTrans;
        CustInvoiceTrans custInvoiceTrans, custInvoiceTransNew;
        CustInvoiceLine custInvoiceLine;
    
        Map customerInvoiceCount = new Map(Types::String, Types::Int64);
        Map customerFirstInvoiceDate = new Map(Types::String, Types::Date);
        integer                    customerCount = 0;
        integer activeCust = 0;
        integer newCondition = 0;
        Set firstPeriodCustomers = new Set(Types::String);
        Set secondPeriodCustomers = new Set(Types::String);


        while select minof(InvoiceDate), InvoiceAccount from custInvoiceJour
group by InvoiceAccount
            where custInvoiceJour.InvoiceDate >= str2Date('3/1/2022',213)
                    && custInvoiceJour.InvoiceDate <= str2Date('3/1/2023',213)
          exists join custTable
            where custTable.AccountNum==custInvoiceJour.InvoiceAccount 
&& 
(custTable.CustGroup != "SCP" 
|| custTable.CustGroup != "RMS" 
|| custTable.CustGroup != "FAS")
               
         exists join custInvoiceTrans
              where custInvoiceTrans.InvoiceId == custInvoiceJour.InvoiceId
                    && 
(custInvoiceTrans.ItemId like '101*' 
|| custInvoiceTrans.ItemId like '201*' 
|| custInvoiceTrans.ItemId like '301*' 
|| custInvoiceTrans.ItemId like '251*')

            {

                customerFirstInvoiceDate.insert(custInvoiceJour.InvoiceAccount, custInvoiceJour.InvoiceDate);

            }



          
        

        

      


        while select InvoiceAccount,count(RecId) from custInvoiceJour 
group by InvoiceAccount
            where custInvoiceJour.InvoiceDate >= str2Date('3/1/2022',213)
                    && custInvoiceJour.InvoiceDate <= str2Date('3/1/2023',213)
          exists join custTable
            where custTable.AccountNum==custInvoiceJour.InvoiceAccount 
&& (custTable.CustGroup != "SCP" 
|| custTable.CustGroup != "RMS" 
|| custTable.CustGroup != "FAS")
               
                exists join custInvoiceTrans
                where custInvoiceTrans.InvoiceId == custInvoiceJour.InvoiceId
                    && (custInvoiceTrans.ItemId like '101*' 
|| custInvoiceTrans.ItemId like '201*' 
|| custInvoiceTrans.ItemId like '301*' 
|| custInvoiceTrans.ItemId like '251*')
        {

            customerInvoiceCount.insert(custInvoiceJour.InvoiceAccount,custInvoiceJour.RecId);

        }

        MapEnumerator mapEnumerator = customerInvoiceCount.getEnumerator();

        while (mapEnumerator.moveNext())
        {
            str accountNum = mapEnumerator.currentKey();
            Integer invoiceCount = mapEnumerator.currentValue();
            TransDate firstInvoiceDate = customerFirstInvoiceDate.lookup(accountNum);

            if(firstInvoiceDate != dateNull())

            {
                Integer numberOfMonths = (year(oneYear) - year(firstInvoiceDate)) * 12 
+ (mthOfYr(oneYear) - mthOfYr(firstInvoiceDate)) ; 
                real invoiceRatio = (invoiceCount / numberOfMonths);

                if (invoiceRatio >= 0.5)
                {
                    firstPeriodCustomers.add(accountNum);
                }
            }
        }

        activeCust = firstPeriodCustomers.elements();
        tmpTable.ActiveCustomer = activeCust;
    
        tmpTable.insert();
}

and in SSRS report output is below: