I would like to be able to report on contracts that have different values for the same date range and account no range on the GL and JL.
I have managed to do this for a complete contract list (takes over 30 mins to run, not feasible) or selection
I would like to produce a filter that returns only the jobs that have difference, then filter and return the GL account no's that are different.
Can anyone suggest a more efficient way of doing this?
Sample of what i currently use and then hide the row when the difference = 0
Column 1 =NL("Sum","G/L Entry","Amount","G/L Account No.","20005..29999","Posting Date","301010..261110","Job No.","1234")
Column 2 =(NL("Sum","Job Ledger Entry","Total Cost","No.","20005..29999","Posting Date","301010..261110","Job No.","1234")+NL("Sum","Job Ledger Entry","Total Price","No.","20005..29999","Posting Date","301010..261110","Job No.","1234"))
Sample structure
Column headings
Job No…..
1234
…….GL Account No……Difference…..GL Value…..JL Value
…….2000……………..£100…………£200……….£100
…….2001……………..£5……………£1000……..£995
=====================================================
………………………..£105…………………………………
=====================================================
1235 etc
Date
Votes
2 comments
-
Jet Reports Historic Posts This is goimng to be simple answer to complex issue and not quite right ( need to look up the nf part but it should get you started )
tricky part starts here as the entire set to <> 0 is one filter
Filter ( Job ledger entry , Job no, (NL("Sum","G/L Entry","Amount","G/L Account No.","20005..29999","Posting Date","301010..261110",NF("Job No.")-=(NL("Sum","Job Ledger Entry","Total Cost","No.","20005..29999","Posting Date","301010..261110",NF"Job No.")+NL("Sum","Job Ledger Entry","Total Price","No.","20005..29999","Posting Date","301010..261110",NF"Job No."")),"<>0")
Dont have tiem to mess with it right now but the general idea is to create a filter for the jobs that fit yor spec ( don't match ) and then generate your report using only those Jobs -
Jet Reports Historic Posts Unfortunately my system doesn't have job ledger so I couldn't build filter but the basic idea is to
create a filter that brings back job number where the sum of amount in GL for that job for time period is not a match to job ledger for same job for same time period
Actually I'm trying to figure out how your job ledger is out of balance with GL unless job is not complete and your comparing an expected total to an actual ?
Please sign in to leave a comment.