Skip to Content
0

Count per line item on SAP Crystal Report

Apr 26 at 03:58 AM

42

avatar image

I have a set of data in Crystal reports that looks like below: You can see that each item, they have corresponding quantity.

However, there are no suppressed details in the report then the line-number should be equal to RecordNumber. So the following formula I got is:

WhilePrintingRecords;
NumberVar ItemNumber;


ToText(ItemNumber, 0) & "/" & ToText(Count({rpt_PackingSlip.LineNo}, {rpt_PackingSlip.WorkOrderNo}),0)

which resulted to:

Notice that it sums all the records in 1 SalesOrder.

But my desired output is:

Abbey should be 1/1

Charlotte should be 1/1

sample.jpg (13.5 kB)
correct.jpg (25.0 kB)
10 |10000 characters needed characters left characters exceeded
* Please Login or Register to Answer, Follow or Comment.

5 Answers

Best Answer
Dell Stinnett-Christy Apr 30 at 01:48 PM
0

You're never setting a value for ItemNumber. Try something like this:

WhilePrintingRecords;
ToText({rpt_PackingSlip.LineNo},0)&"/" & ToText(DistinctCount({rpt_PackingSlip.LineNo}, {rpt_PackingSlip.WorkOrderNo}),0)

This will work if LineNo is consecutive starting at 1 and you're sorting by that field.

If you're not sorting on LineNo, try this:

WhilePrintingRecords; 
NumberVar ItemNumber;
if OnFirstRecord or {rpt_PackingSlip.WorkOrderNo} <> Previous({rpt_PackingSlip.WorkOrderNo}) then
    ItemNumber := 1;
else
    ItemNumber := ItemNumber + 1;
ToText(ItemNumber,0)&"/" & ToText(DistinctCount({rpt_PackingSlip.LineNo}, {rpt_PackingSlip.WorkOrderNo}),0)

-Dell

Share
10 |10000 characters needed characters left characters exceeded
Abhilash Kumar
Apr 26 at 07:21 AM
0

Hi Trish,

If a WorkOrder has multiple Lines, then this part of the code will show > 1:

ToText(Count({rpt_PackingSlip.LineNo}, {rpt_PackingSlip.WorkOrderNo})

Could you post sample data on why it should show as 1/1 and in what situations should it show as 1/n?

-Abhilash

Show 1 Share
10 |10000 characters needed characters left characters exceeded

Thank you

Update: I've changed the code and added DistinctCount instead of Count but it doesn't still fix the issue :(

Now I got 2 Sales Orders: S018040553 and S018040850

S018040553 - has one item called Buzz Ottoman

S018040850 - has 2 items called Amelia and Charlotte

WhilePrintingRecords;NumberVar ItemNumber;ToText(ItemNumber, 0) & "/" & ToText(DistinctCount({rpt_PackingSlip.LineNo}, {rpt_PackingSlip.WorkOrderNo}),0)
I got the results below: and the desired output are in "red" font. S018040553.

Pls help

S018040850 - it has 2 items called abbey and charlotte.

0
Prasath Ram Apr 26 at 07:06 AM
0

refer VBAP Table to get sales order items

Share
10 |10000 characters needed characters left characters exceeded
Dell Stinnett-Christy Apr 26 at 01:55 PM
0

I would also use DistinctCount instead of Count to get the second number in the string.

-Dell

Show 1 Share
10 |10000 characters needed characters left characters exceeded

Thanks Dell but ...

Update: I've changed the code and added DistinctCount instead of Count but it doesn't still fix the issue :(

Now I got 2 Sales Orders: S018040553 and S018040850

S018040553 - has one item called Buzz Ottoman

S018040850 - has 2 items called Amelia and Charlotte

WhilePrintingRecords;NumberVar ItemNumber;ToText(ItemNumber, 0) & "/" & ToText(DistinctCount({rpt_PackingSlip.LineNo}, {rpt_PackingSlip.WorkOrderNo}),0)
I got the results below: and the desired output are in "red" font. S018040553.

Pls help

S018040850 - it has 2 items called abbey and charlotte.

0
Trish Fernandez Apr 29 at 09:29 PM
0

Thanks Dell but ...

Update: I've changed the code and added DistinctCount instead of Count but it doesn't still fix the issue :(

Now I got 2 Sales Orders: S018040553 and S018040850

S018040553 - has one item called Buzz Ottoman

S018040850 - has 2 items called Amelia and Charlotte

WhilePrintingRecords;NumberVar ItemNumber;ToText(ItemNumber, 0) & "/" & ToText(DistinctCount({rpt_PackingSlip.LineNo}, {rpt_PackingSlip.WorkOrderNo}),0)
I got the results below: and the desired output are in "red" font. S018040553.

Pls help

S018040850 - it has 2 items called abbey and charlotte.


buzz.jpg (25.5 kB)
abbey.jpg (26.6 kB)
charlotte.jpg (26.8 kB)
Share
10 |10000 characters needed characters left characters exceeded