CALCSUMS Problem in Report 129

te6

Hi all,

I am running this report , using the Date Filter in the range '01-04-08D' to '31-03-09D'

But , for one customer the Ending BalAnce is not coming..

I filtered Detailed Cust. Ledg. Entry table ,by using the same filters which were used in the report Funnction.

i could able to see that is there in the table..

I could not able to understand , what is the problem.. :-k

Please suggest me.

garak

SQL or native.
If SQL, are this entries created through a normal posting process or through a, sorry that i ask this but often seen, dataport or a direct writing like with sql into the table? If you make a form with a message box where you output the calcsum of these both fields, shows the message also zero as value? if yes, and it's sql it could be that

1. u insert these datas direct per SQL or 3rd-app into the table but the SIFTS are not created
2. u doesn't have maint. jobs like rebuild index and sp_updatestats / sp_createstats, so nav can't calculate the correct values in some cases.

te6

Hi garak thanks for the reply.

Sorry,

it is Sql DB.

Exactly, when it reached calcsums the 'Debit Amount(LCY)' and 'Credit Amount(LCY)' is showing Zero values..

But , when run through CU by using the Same Code , i could able to see the matching values which were in table through Msg Box..

If u don't mind ,Can u Xplain me again.. how to solve this Issue..

kine

1) Which NAV version?

Exactly, when it reached calcsums the 'Debit Amount(LCY)' and 'Credit Amount(LCY)' is showing Zero values..

2) On which table?

te6

hi kine,

1) Version 4.03 sp3

2) table no. 379 (Detailed Cust. Ledg. Entry table )

it is in report 129 , CalcAmounts funtion is called in OnAfterGetRecord()

The Code in the funtion is

WITH DtlCustLedgEntry DO BEGIN
SETCURRENTKEY("Customer No.","Posting Date","Entry Type","Currency Code");
SETRANGE("Customer No.",Customer."No.");
SETRANGE("Posting Date",DateFrom,DateTo);
SETFILTER("Entry Type",'<> %1',"Entry Type"::Application);
CALCSUMS("Debit Amount (LCY)","Credit Amount (LCY)");
DebitAmt := "Debit Amount (LCY)";
CreditAmt := "Credit Amount (LCY)";
TotalBalance := BeginBalance + DebitAmt - CreditAmt;
END;

The same code i run through CU, I could able to see data in "Debit Amount (LCY)" and "Credit Amount (LCY)" through msg box.

But, if kept a msg box after calcsums in the above code it is showing zero values..

kine

Check that there are no other filters set somewher before, because there is no RESET thus setting some filters somewhere in the code in another trigger could have side effect (just to be sre that you are really calculating on the set you want...)

te6

Hi kine,

thanks for the reply,

i checked the code as u suggested me, but i could not find any filters.

and we didn't modify the report .

kine

I suggest to use different NAV client version, e.g. with latest hotfix. Some versions (hotfixes) of NAV 4.00SP3 have problems with filters etc.