如何根据重复ID字段中的最新/更大日期配置运行总计列的余额 [英] How do I configure running total column for balance based on latest/greater date from repeating ID field
问题描述
大家好
设计贷款借贷系统。我已设法创建大多数报告,但我被困在一个报告(报告只过滤两个日期之间的记录) - 过滤代码是好的并且运作良好,问题是字段余额的最后一次运行总计。我的Crystal报表从查询中获取数据,字段分别如下:
示例在第一个分组(FERRY 1A)余额列SubTotal中ID 4444444重复8月24日被选中,8月22日下降然后添加到唯一的ID 666666,总计(49970.00 + 70400.00)= 120370.00但我的运行总配置产生错误的subTotal 122400.00,其中52,000.00的ID 444444用于DOP 22-Aug代替8月24日至8月
我在运行总编辑器中的配置如下所示:
Hi All
Am designing a loan lending system. I have managed to create most reports but am stuck at one report (Report to filter only records between two dates)- Filtering code is Ok and working well, the issue is the the last running total for field "Balance". My Crystal report fetches data from a query and the fields are as follows respectively:
Example In the first grouping (FERRY 1A) Balance Column SubTotal where ID 4444444 is repeating on 24-August is picked and 22-August dropped then added to the only ID 666666 giving total of (49970.00+70400.00)=120370.00 but my running total configuration produces wrong subTotal of 122400.00 whereby 52,000.00 of ID 444444 for DOP 22-Aug is used instead of 24- Aug.
My configuration in running total Editor are as shown:
Running total name: subBalance
Field to summarize: QryRegLoanPaymentsZonewise.Balance
Type of summary: Sum
Evaluate: On change of Field- QryRegLoanPaymentsZonewise.NatID
Reset: Use a Formula- if {QryRegLoanPaymentsZonewise.NatID}<>previous({QryRegLoanPaymentsZonewise.NatID}) and {QryRegLoanPaymentsZonewise.DOP} >previous({QryRegLoanPaymentsZonewise.DOP}) then sum({QryRegLoanPaymentsZonewise.PaybackTotAmount})
注意:1)对于特定NatID的重复余额,我只想选择最新的(更长的日期)用于总计。 2)我想在详细信息部分为每个组提供正确的小计。
任何帮助都应该受到赞赏。
NOTE: 1)For Repeating Balances of a particular NatID, I just want to pick its latest(greater date) to use for totals. 2) I want correct subtotals for each and every group in the details section.
any help shall be appreciated.
ClientZone DOP NatID PaidAmount Balance
GROUP 1: FERRY 1A
FERRY 1A 22-Aug-2018 444444 26,000.00 52,000.00
FERRY 1A 24-Aug-2018 444444 28,000.00 49,974.00
FERRY 1A 24-Aug-2018 666666 8,800.00 70,400.00
FERRY 1A Totals of this group 62,800.00 122,400.00
GROUP 2: FERRY 2A
FERRY 2A 22-Aug-2018 123456789 5,000.00 6,550.00
FERRY 2A 24-Aug-2018 123456789 5,000.00 1,550.00
FERRY 2A, Totals of this group 10,000.00 128,950.00
GROUP 3: MWANANZIA
MWANANZIA 22-Aug-2018 23538307 3,000.00 192,000.00
MWANANZIA 23-Aug-2018 23538307 5,000.00 189,997.00
MWANANZIA 24-Aug-2018 23538307 3,000.00 191,992.00
MWANANZIA 24-Aug-2018 222222 1,900.00 9,100.00
MWANANZIA 24-Aug-2018 222222 2,000.00 8,999.00
MWANANZIA Totals of this Group 14,900.00 330,050.00
Grand Total: 87,700.00 330,050.00
我是什么尝试过:
我在运行总编辑器中的配置如下所示:
运行总名称:subBalance
总结字段:QryRegLoanPaymentsZonewise.Balance
摘要类型:总和
评估:改变Field- QryRegLoanPaymentsZonewise.NatID
What I have tried:
My configuration in running total Editor are as shown:
Running total name: subBalance
Field to summarize: QryRegLoanPaymentsZonewise.Balance
Type of summary: Sum
Evaluate: On change of Field- QryRegLoanPaymentsZonewise.NatID
Reset: Use a Formula- if {QryRegLoanPaymentsZonewise.NatID}<>previous({QryRegLoanPaymentsZonewise.NatID}) and {QryRegLoanPaymentsZonewise.DOP} >previous({QryRegLoanPaymentsZonewise.DOP}) then sum({QryRegLoanPaymentsZonewise.PaybackTotAmount})
推荐答案
从标签中可以明显看出,这是一份手工报告。
你是:
1)没有正确执行控制中断
2)没有正确重置累加器
也许回去看看在一个报告作者,可以为你过滤,排序,分组和总结。
It's obvious from the "labels", that this is a "manual report".
You are:
1) Not performing "control breaks" properly
2) Not resetting accumulators properly
Maybe go back and look at a "report writer" that can "filter, sort, group and sum" for you.
这篇关于如何根据重复ID字段中的最新/更大日期配置运行总计列的余额的文章就介绍到这了,希望我们推荐的答案对大家有所帮助,也希望大家多多支持IT屋!