Skip to Content

Arranging Sales History By Month - Crystal Reports.

Hi Guys, i'm using SAP B1 In conjunction with Crystal reports. I'm wanting to create a 6 month sales history of items purchased from certain customers.

Top row will read Total/Feburary/March/April/May/June/July (Feburary will be current month)
Then running down each item code will be listed and each collumn will represent how many items have been sold in each month.

Item1 100 / 20 / 20 / 20 / 20 / 10 / 10 (100 is the running total of each value)
Item2 150 / 50 / 50 / 10 / 10 / 10 / 20 (150 is running total) each other value is a months quantity

I have managed to get the report to display item codes, amount of sales on each transaction then running total to give a total number of units sold over a parameter period. but not sure how to separate these.

Tables i am using is OCRD -> Oinv -> inv1 -> OITM

matrix.png (178.9 kB)
Add comment
10|10000 characters needed characters exceeded

  • Get RSS Feed

1 Answer

  • Best Answer
    Feb 23, 2018 at 11:11 AM

    Hi Andrew,

    1. Insert a Crosstab and place it on the Report Header.

    2. Use the date field as the "Column" of the Crosstab. Highlight the Date field > Go to Group Options > Set it to "Print for each month"

    3. Use Item Code as the Row of the Crosstab.

    4. Use Qty Sold as the "Summarized Field" and set its aggregation function to "sum".

    5. To Display the Month Name instead of a date in the column, highlight the date field > Click Group Options > Options tab > Click "Customize Group Name field", select "Use a formula" and click the formula button beside. Use this code:

    Totext({date field}, "MMM yyyy) //replace {date field} with the correct field from your database


    Add comment
    10|10000 characters needed characters exceeded