You’re offline. This is a read only version of the page.
390
Hey team,
I have an SSRS Report where if the person selects Customer level report without giving the CustAccount number then I have to show the sum of all the estimates of all the projects of all the customers under the selected Division and Profit Center.
My code:
Share
ReportProjectEstimatesView projview;
if(!contract.parmCustomerCode())
{
int x=0;
while select projview where projview.Division == divisions && projview.ProfitCentre == profitcentre
{
select count(RecId) from projview1 where projview1.Division == projview.Division && projview1.ProfitCentre == projview.ProfitCentre && projview1.CustAccount == projview.CustAccount;
numestimates = projview1.RecId;
ecost = estimatetmpclass.getTotalEngineFees(projview.EstimateRefRecId,startenginedate,fromdate,todate,pass);
thirdpartycost = estimatetmpclass.getthirdpartycost(projview.EstimateRefRecId,startthirdpartydate,fromdate,todate,pass2);
prodinsurance = estimatetmpclass.getproductionInsurance(thirdpartycost,projview.ProdInsurance);
csslevy = estimatetmpclass.getCSSLevy(ecost,thirdpartycost,prodinsurance,projview.CCSLevy);
projcalculationtmp.ProjId = projview.ProjId;
select firstonly projtable where projTable.ProjId == projview.ProjId;
projcalculationtmp.CustName = projTable.custName();
projcalculationtmp.CustAccountNumber = projview.CustAccount;
projcalculationtmp.EstimateCode = projview.EstimateCode;
projcalculationtmp.PotentialFee = ecost;
projcalculationtmp.thirdPartyCostTotal = thirdpartycost;
projcalculationtmp.IsProdInsurance = projview.IsProdInsurance;
if( projcalculationtmp.IsProdInsurance)
{
projcalculationtmp.ProdInsurance = prodinsurance;
}
else
{
projcalculationtmp.ProdInsurance = 0;
}
projcalculationtmp.IsCCSLevy = projview.IsCCSLevy;
if(projcalculationtmp.IsCCSLevy)
{
projcalculationtmp.CCSLevy = csslevy;
}
else
{
projcalculationtmp.CCSLevy = 0;
}
projcalculationtmp.Total = e thirdpartycost prodinsurance csslevy;
projcalculationtmp.insert();
x ;
if(x==numestimates)
{
select sum(PotentialFee),sum(thirdPartyCostTotal),sum(CCSLevy),sum(ProdInsurance),sum(InvestmentEstimate),sum(Total),IsProdInsurance,IsCCSLevy
from projcalculationtmp where projcalculationtmp.CustAccountNumber == projview.CustAccount;
select firstonly projtable2 where projTable2.ProjId == projview.ProjId;
projectEstimatesTmp.CustName = projtable2.custName();
projectEstimatesTmp.CustAccountNumber = projview.CustAccount;
projectEstimatesTmp.ProjId =projcalculationtmp.ProjId;
projectEstimatesTmp.PotentialFee = projcalculationtmp.PotentialFee;
projectEstimatesTmp.thirdPartyCostTotal = projcalculationtmp.thirdPartyCostTotal;
projectEstimatesTmp.IsProdInsurance = projcalculationtmp.IsProdInsurance;
projectEstimatesTmp.ProdInsurance = projcalculationtmp.ProdInsurance;
projectEstimatesTmp.IsCCSLevy = projcalculationtmp.IsCCSLevy;
projectEstimatesTmp.CCSLevy = projcalculationtmp.CCSLevy;
projectEstimatesTmp.Total = projcalculationtmp.Total;
projectEstimatesTmp.insert();
x = 0;
}
}
}
}
but the problem is since there is no group by on the while loop so when an estimate of different custaccount is selected by the while loop my logic for if(x==numestimate) is going wrong and some projects(and their cost) are not getting displayed. If I add group by it doesn't give me a value for "numestimate". Please help me resolve this issue. How can I get sum of all projects for each customer under a a particular Division and ProfitCentre.
Hi skd,
Do you really need to code such a report?
If you have the customer, division, profit center and the project setup as findims then you can use the trial balance online form to analyze those data.
Of course you could also use the finance reports if you like.
Best regards,
Ludwig
All responses (
Answers (
