Hi,

I created a crystal report to mimic the look of an excel pivot table.  I have done so in the past without any problems and now I am facing a rather strange issue.  I have a database set up with 3 tables. Items, Budget and Customers.  Right now, I am using 2 of the 3 tables.  The tables have been appropriately joined by key fields in the database expert.

The report was successfully created or so I thought.  On first glance it appears fine; however there are some discrepancies with some of the numbers that are displayed.

The main budget table consists of the product id, customer id, year and months from January to December.  In all, the table has 15 columns.  The first 3 fields are mandatory, however the fields Jan - Dec may contain null values.

The pivot table successfully displays values and aggregated totals of each product by month.  The crystal report correctly displays the totals grouped by category, however the totals per product could be a bit off for some products.

First thing I did was to go into the report options and enabled “Convert database NULL values to default” and “Convert other NULL values to default.”  The situation has not improved.

Here is an example of the results from the various tools:

Excel Pivot Table

            JAN    FEB    MAR
Category 1        60    100    40
    Product 1    15    10    10
    Product 2    35    80    20
    Product 3    10    10    10
                
Category 2        2092    3001    3287
    Product 1    175    200    200
    Product 2    50    50    50
    Product 3    50    50    50
    Product 4    250    300    350
    Product 5    200    250    350
    Product 6    100    125    150
    Product 7    120    150    200
    Product 8    800    1500    1500
    Product 9    50    50    50
    Product 10    -    -    -
    Product 11    4    4    4
    Product 12    30    30    30
    Product 13    50    50    50
    Product 14    2    1    2
    Product 15    45    75    80
    Product 16    40    25    35
    Product 17    1    1    1
    Product 18    25    25    45
    Product 19    25    25    40
    Product 20    75    90    100

Crystal Reports

            JAN    FEB    MAR
Category 1        60    100    40
    Product 1    15    10    10
    Product 2    -    -    -
    Product 3    10    10    10
                
Category 2        2,092    3,001    3,287
    Product 1    175    200    200
    Product 2    50    50    50
    Product 3    50    50    50
    Product 4    -    -    -
    Product 5    -    -    -
    Product 6    -    -    -
    Product 7    120    150    200
    Product 8    -    -    -
    Product 9    50    50    50
    Product 10    -    -    -
    Product 11    4    4    4
    Product 12    30    30    30
    Product 13    -    -    -
    Product 14    2    1    2
    Product 15    -    -    -
    Product 16    -    -    -
    Product 17    1    1    1
    Product 18    25    25    45
    Product 19    25    25    40
    Product 20    -    -    -