Data：
|
Estate
|
Block
|
Customer
|
Status
|
|
ALC
|
1
|
AAA
|
|
|
ALC
|
1
|
BBB
|
Settle
|
|
ALC
|
1
|
CCC
|
|
|
ALC
|
2
|
DDD
|
Settle
|
|
ALC
|
2
|
EEE
|
|
Requirement：Count records
by estate, block, hide customer with status = ‘Settle’ when display.
|
Estate
|
Block
|
Customer
|
Status
|
|
ALC
|
1
|
AAA
|
|
|
ALC
|
1
|
CCC
|
|
|
Settled case:
|
1
|
|
|
|
Total case:
|
3
|
|
|
|
ALC
|
2
|
EEE
|
|
|
Settled case:
|
1
|
|
|
|
Total case:
|
2
|
|
|
It is a piece of cake that implementing this report
in Crystal report, but it spent me much time in Cognos ReportNet—I can’t find
out how to hide a whole row.
At first, I have tried to control visibility of the
fields with conditional variable. But, there still existed a blank row though
all the fields were hidden.
|
Estate
|
Block
|
Customer
|
Status
|
|
ALC
|
1
|
AAA
|
|
|
ALC
|
1
|
CCC
|
|
|
|
|
|
|
|
Settled case:
|
1
|
|
|
|
Total case:
|
3
|
|
|
|
ALC
|
2
|
EEE
|
|
|
|
|
|
|
|
Settled case:
|
1
|
|
|
|
Total case:
|
2
|
|
|
Another way I have tried is wrapping all fields
with conditional block. The blank rows were still there, just with smaller
height.
Finally, I gave up finding out which option could
be used to hide the whole row. I have amended the SQL with COUNT(…) OVER
(PARTITION BY …):
SELECT estate, block, customer, status,
count(customer) over (partition by estate, block) as totalCase FROM table ORDER
BY estate, block.
The retrieved data with the amended SQL:
|
Estate
|
Block
|
Customer
|
Status
|
totalCase
|
|
ALC
|
1
|
AAA
|
|
3
|
|
ALC
|
1
|
BBB
|
Settle
|
3
|
|
ALC
|
1
|
CCC
|
|
3
|
|
ALC
|
2
|
DDD
|
Settle
|
2
|
|
ALC
|
2
|
EEE
|
|
2
|
In the last step, A filter was added to the details
in the report: status <> ‘Settle’, and the calculation of grouping area ‘Total
Case’ was set to Average.