Skip to Content

Count per line item on SAP Crystal Report

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)
Add comment
10|10000 characters needed characters exceeded

  • Get RSS Feed

5 Answers

  • Best Answer
    Apr 30 at 01:48 PM

    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

    Add comment
    10|10000 characters needed characters exceeded

  • Apr 26 at 07:21 AM

    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

    Add comment
    10|10000 characters needed 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.

  • Apr 26 at 07:06 AM

    refer VBAP Table to get sales order items

    Add comment
    10|10000 characters needed characters exceeded

  • Apr 26 at 01:55 PM

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

    -Dell

    Add comment
    10|10000 characters needed 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.

  • Apr 29 at 09:29 PM

    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.

    Add comment
    10|10000 characters needed characters exceeded