Report not getting correct data

(0) Share

Report

Posted on by Dineshkarlekar

1,457

I have made report which gets the project invoice amount of each project id which is getting inserted correctly , but i need to insert the billable and non billable amount from ProjUnpostedTransView so when i apply the join with the view the its only fetching the project id rest of the values get blank . not getting how to solve the issue ,can plz any one help me on this .i am posting my code below in comments .

Thanks ,

regards,

Dinesh

All responses (10)

Answers (1)

Verified answer

Layan Jwei 7,264 Super User 2024 Season 2 on at

Like (0)

Report

Report not getting correct data

Hi Dinesh,

I'm glad your issue is fixed. Please don't forget to verify the answers that helped for ProjTable. Thank you.
Dineshkarlekar 1,457 on at

Like (0)

Report

Report not getting correct data

hi ,

thanks for reply guys ,

actually the non billable amount is mention as sum on report design and also on the dp class thats why it was getting incorrect value but as i removed the sum on report design it is showing correct as of now , still need to test report with multiple cases , i will let you know .

thanks,

Regards ,

Dinesh.
Bharani Preetham Pe... 3,587 Super User 2024 Season 1 on at

Like (1)

Report

Report not getting correct data

When you say that non billable value is showing double, does it mean the value is coming twice? If yes, i couldn't find the same in the screenshot which you have provided.

If not, do you mean that the value is getting doubled and showing up in report like if you have 4 value and in report it is coming as 8? If yes, can you check if some grouping or some code is written at report design level?
Layan Jwei 7,264 Super User 2024 Season 2 on at

Like (1)

Report

Report not getting correct data

Hi Dinesh,

I'm not sure:

1. does it happen for all project Ids? or just certain ones?

2. do you have any logic in the report design? like grouping or sth?

3. Also when you debugged, how many times did the while loop get called for the projId where the double happen? are you sure it's correct when you debug?

4. what does SQL give you?

Thanks,
Layan Jweihan
Dineshkarlekar 1,457 on at

Like (0)

Report
Report not getting correct data

hi , thanks for reply ,

code is working fine data is getting inserted in report as required but at last reocrd the non billable value is getting double on report can you help me on this .the nonbillable value of project id is getting double on report but on debugger its showing correct can you plz help me on this .

class DTProjectContractReportDP extends SRSReportDataProviderBase { DTProjectContractTableTmp DTProjectContractTableTmp; PSAProjProposalProj pSAProjProposalProj; ProjInvoiceJour projInvoiceJour; ProjTable projTable, projTable1; projUnpostedTransView projUnpostedTransView; str name; ProjId projId; [SrsReportDataSetAttribute(tableStr('DTProjectContractTableTmp'))] public DTProjectContractTableTmp GetDTProjectContractTableTmp() { select * from DTProjectContractTableTmp; return DTProjectContractTableTmp; } Public void processReport() { Query q; QueryRun qr; QueryBuildDataSource qbdsobjProjTable; QueryBuildDataSource qbdsobjDirPartyTable; QueryBuildDataSource qbdsobjLogisticsPostalAddress; QueryBuildRange qbr; DTProjectContractTableTmp.clear(); q= new Query(); qbdsobjProjTable = q.addDataSource(tableNum(ProjTable)); qr=new QueryRun(q); while(qr.next()) { projTable = qr.get(tableNum(ProjTable)); while select * from projTable group by PROJID join pSAProjProposalProj where pSAProjProposalProj.PROJID == projTable.PROJID join projInvoiceJour group by PROPOSALID ,INVOICEAMOUNT where projInvoiceJour.PROPOSALID == pSAProjProposalProj.PROJPROPOSALID { name = projTable::find(projTable.ProjId).Name; projId = projTable.ProjId; DTProjectContractTableTmp.clear(); DTProjectContractTableTmp.ProjId = projId; DTProjectContractTableTmp.Name = name; DTProjectContractTableTmp.DTProjectValue = projTable.DTProjectValue; DTProjectContractTableTmp.InvoiceAmount = projInvoiceJour.InvoiceAmount; select sum(Amount) from projUnpostedTransView where projUnpostedTransView.LinePropertyId == 'Billable' && projUnpostedTransView.ProjId == projId; DTProjectContractTableTmp.BillableAmt = projUnpostedTransView.Amount; select sum(Amount) from projUnpostedTransView where projUnpostedTransView.LinePropertyId == 'Non-Bill' && projUnpostedTransView.ProjId == projId; DTProjectContractTableTmp.NonBillAmt = projUnpostedTransView.Amount; DTProjectContractTableTmp.insert(); } } } }
Layan Jwei 7,264 Super User 2024 Season 2 on at

Like (1)

Report

Report not getting correct data

Hi Dinesh,

In your first screenshot for the code, you weren't inserting multiple times, so you please remove that. So did removing the join with projTable fix the issue? if yes, then please verify the answer that helped

Thanks,
Layan Jweihan
Dineshkarlekar 1,457 on at

Like (0)

Report

Report not getting correct data

hi ,

thanks for reply , i was getting error because of the insert statement that i mentioned multiple times .now the amount is getting inserted correctly need to test it and let you know .

thanks ,

regards,

Dinesh.
Dineshkarlekar 1,457 on at

Like (0)

Report
Report not getting correct data

I have tried without the join the values are getting inserted but the amount is getting inserted wrong and also the invoice amount is getting wrong before uncommenting the select statement it was correct .can you plz suggest where i am getting wrong .but the sql is showing the right data .

while select * from projTable group by PROJID join pSAProjProposalProj where pSAProjProposalProj.PROJID == projTable.PROJID join projInvoiceJour group by PROPOSALID ,INVOICEAMOUNT where projInvoiceJour.PROPOSALID == pSAProjProposalProj.PROJPROPOSALID { name = projTable::find(projTable.ProjId).Name; projId = projTable.ProjId; DTProjectContractTableTmp.ProjId = projId; DTProjectContractTableTmp.Name = name; DTProjectContractTableTmp.DTProjectValue = projTable.DTProjectValue; DTProjectContractTableTmp.InvoiceAmount = projInvoiceJour.InvoiceAmount; select sum(Amount) from projUnpostedTransView where projUnpostedTransView.LinePropertyId == 'Billable' && projUnpostedTransView.ProjId == projId; DTProjectContractTableTmp.BillableAmt = projUnpostedTransView.Amount; DTProjectContractTableTmp.insert(); select sum(Amount) from projUnpostedTransView where projUnpostedTransView.LinePropertyId == 'Non-Bill' && projUnpostedTransView.ProjId == projId; DTProjectContractTableTmp.NonBillAmt = projUnpostedTransView.Amount; DTProjectContractTableTmp.insert(); DTProjectContractTableTmp.insert(); }
Suggested answer

Layan Jwei 7,264 Super User 2024 Season 2 on at

Like (1)

Report

Report not getting correct data

Hi Dinesh,

Looking at it quickly, I can see you joined the view with projTable but you didn't specify the relation between the two tables in the where condition. Can you try that and let us know if it works? If not we can look further. Or do you really need the join with ProjTable? If it's not needed then remove it and try again, but if you will keep it, then put proper relation.

In general when you put for example:

Select sum(LineAmount) from SalesLine where SalesLine.SalesId = 'xx';
Then you will only be able to access SalesLine.Amount and all other fields will be empty
Because you specified that you are only interested in amount

Thanks,

Layan Jweihan

Please mark this answer as "Verified" if it solved your issue. In order to help others who will face a similar ise in the future
Dineshkarlekar 1,457 on at

Like (0)

Report
Report not getting correct data

Here is the code for the dp class

class DTProjectContractReportDP extends SRSReportDataProviderBase { DTProjectContractTableTmp DTProjectContractTableTmp; PSAProjProposalProj pSAProjProposalProj; ProjInvoiceJour projInvoiceJour; ProjTable projTable, projTable1; projUnpostedTransView projUnpostedTransView; str name; ProjId projId; [SrsReportDataSetAttribute(tableStr('DTProjectContractTableTmp'))] public DTProjectContractTableTmp GetDTProjectContractTableTmp() { select * from DTProjectContractTableTmp; return DTProjectContractTableTmp; } Public void processReport() { Query q; QueryRun qr; QueryBuildDataSource qbdsobjProjTable; QueryBuildDataSource qbdsobjDirPartyTable; QueryBuildDataSource qbdsobjLogisticsPostalAddress; QueryBuildRange qbr; DTProjectContractTableTmp.clear(); q= new Query(); qbdsobjProjTable = q.addDataSource(tableNum(ProjTable)); qr=new QueryRun(q); while(qr.next()) { projTable = qr.get(tableNum(ProjTable)); while select * from projTable group by PROJID join pSAProjProposalProj where pSAProjProposalProj.PROJID == projTable.PROJID join projInvoiceJour group by PROPOSALID ,INVOICEAMOUNT where projInvoiceJour.PROPOSALID == pSAProjProposalProj.PROJPROPOSALID { name = projTable::find(projTable.ProjId).Name; projId = projTable.ProjId; DTProjectContractTableTmp.ProjId = projId; DTProjectContractTableTmp.Name = name; DTProjectContractTableTmp.DTProjectValue = projTable.DTProjectValue; DTProjectContractTableTmp.InvoiceAmount = projInvoiceJour.InvoiceAmount; //select sum(Amount) from projUnpostedTransView //join projTable // where projUnpostedTransView.LinePropertyId == 'Billable' // && projUnpostedTransView.ProjId == projId ; // DTProjectContractTableTmp.BillableAmount = projUnpostedTransView.Amount; // select sum(Amount) from projUnpostedTransView // join projTable // where projUnpostedTransView.LinePropertyId == 'Non-Bill' // && projUnpostedTransView.ProjId == projId; // DTProjectContractTableTmp.NonBillableAmount = projUnpostedTransView.Amount; DTProjectContractTableTmp.insert(); } } } }

i have comment out the lines which gets data for billable and non billable amount for report but i need to get the both amount is it possible to get the billable and non billable amount in report if there is transaction in projUnpostedTransView .

here is output of my report .