Share
Report
2,430Hi 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:
Thanks,
Faiz
All responses (
Answers (
