Technical Questions
 Crystal Reports Forum : Crystal Reports 9 through 2020 : Technical Questions
Message Icon Topic: Database with NULL values Post Reply Post New Topic
Author Message
TheITGuyAtWork
Newbie
Newbie
Avatar

Joined: 04 May 2018
Online Status: Offline
Posts: 8
Quote TheITGuyAtWork Replybullet Topic: Database with NULL values
    Posted: 08 Jun 2018 at 10:31am

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    -    -    -


Any assistance would be greatly appreciated.

Cheers.

IP IP Logged
DBlank
Moderator
Moderator


Joined: 19 Dec 2008
Online Status: Offline
Posts: 9053
Quote DBlank Replybullet Posted: 11 Jun 2018 at 3:41am
I am guessing you have a series of formulas for your summary values.
Check each of those formula fields to make sure each one is using default values for nulls

Edited by DBlank - 11 Jun 2018 at 3:41am
IP IP Logged
TheITGuyAtWork
Newbie
Newbie
Avatar

Joined: 04 May 2018
Online Status: Offline
Posts: 8
Quote TheITGuyAtWork Replybullet Posted: 11 Jun 2018 at 4:27am
Hi DBlank.

I don't have any formulas per se.  I use the group by to calculate the totals per month.

It looks like:

Group 1        Product Category        [01 - Jan]    [02 - Feb]    [03 - Mar]
Group 2            Item Description    [01 - Jan]    [02 - Feb]    [03 - Mar]

Group 1 gives me the correct  total, however there are missing some values in group 2 like I displayed in my previous post.

The interesting thing is if I add a details row, the figures that display the correct value in the group 1 are displayed.  They do not seem to be incorporated into the group 2 totals.  Am I approaching this the wrong way?  I have a single formula called Total which calculates the sum across  January through December by using simple addition.  This isn't calculated correctly either.

Am I approaching this the wrong way?

Thanks for your response.
Cheers.
IP IP Logged
DBlank
Moderator
Moderator


Joined: 19 Dec 2008
Online Status: Offline
Posts: 9053
Quote DBlank Replybullet Posted: 11 Jun 2018 at 4:43am
why are you not using a crosstab?
IP IP Logged
TheITGuyAtWork
Newbie
Newbie
Avatar

Joined: 04 May 2018
Online Status: Offline
Posts: 8
Quote TheITGuyAtWork Replybullet Posted: 11 Jun 2018 at 5:09am
Honestly, I'm not sure why.  I like to manually format the layout and spacing.  I will have to examine the crosstab.  I attempted it once a long time ago, but haven't since.
IP IP Logged
DBlank
Moderator
Moderator


Joined: 19 Dec 2008
Online Status: Offline
Posts: 9053
Quote DBlank Replybullet Posted: 11 Jun 2018 at 5:56am
what does your row level data look like and what are the group formula that you are using?
how are you breaking these into months?
IP IP Logged
TheITGuyAtWork
Newbie
Newbie
Avatar

Joined: 04 May 2018
Online Status: Offline
Posts: 8
Quote TheITGuyAtWork Replybullet Posted: 11 Jun 2018 at 7:12am
The data looks like the following:



Item        Customer    Year    01 - Jan    02 - Feb    03 - Mar
BA - Item 01    Country 10    2019    15        10        10
BA - Item 02    Country 3    2019           
BA - Item 02    Country 6    2019            60   
BA - Item 02    Country 9    2019           
BA - Item 02    Country 10    2019    35        20        20
BA - Item 02    Country 11    2019           
BA - Item 02    Country 12    2019           
BA - Item 03    Country 10    2019    10        10        10


BO - Item 2    Country 10    2019    175        200        200
BO - Item 4    Country 10    2019    50        50        50
BO - Item 6    Country 10    2019    50        50        50
BO - Item 7    Country 1    2019           
BO - Item 7    Country 2    2019           
BO - Item 7    Country 3    2019           
BO - Item 7    Country 9    2019           
BO - Item 7    Country 10    2019    250        300        350
BO - Item 8    Country 1    2019               
BO - Item 8    Country 2    2019           
BO - Item 8    Country 3    2019           
BO - Item 8    Country 9    2019           
BO - Item 8    Country 10    2019    200        250        300
BO - Item 8    Country 14    2019            50
BO - Item 9    Country 1    2019           
BO - Item 9    Country 9    2019           
BO - Item 9    Country 10    2019    100        125        150
BO - Item 10    Country 10    2019    120        150        200
BO - Item 11    Country 1    2019           
BO - Item 11    Country 2    2019           
BO - Item 11    Country 10    2019    800        1500        1500
BO - Item 13    Country 10    2019    50        50        50
BO - Item 13    Country 14    2019           
BO - Item 14    Country 3    2019           
BO - Item 14    Country 9    2019           
BO - Item 17    Country 10    2019    4        4        4
BO - Item 18    Country 10    2019    30        30        30
BO - Item 20    Country 3    2019           
BO - Item 20    Country 9    2019           
BO - Item 20    Country 10    2019    50        50        50
BO - Item 20    Country 14    2019           
BO - Item 20    Country 15    2019           
BO - Item 21    Country 10    2019    2        1        2
BO - Item 22    Country 3    2019           
BO - Item 22    Country 9    2019           
BO - Item 22    Country 10    2019    45        75        80
BO - Item 23    Country 2    2019           
BO - Item 23    Country 3    2019           
BO - Item 23    Country 9    2019           
BO - Item 23    Country 10    2019    40        25        35
BO - Item 24    Country 10    2019    1        1        1
BO - Item 25    Country 10    2019    25        25        45
BO - Item 26    Country 10    2019    25        25        40
BO - Item 27    Country 2    2019           
BO - Item 27    Country 10    2019    75        90        100


IP IP Logged
TheITGuyAtWork
Newbie
Newbie
Avatar

Joined: 04 May 2018
Online Status: Offline
Posts: 8
Quote TheITGuyAtWork Replybullet Posted: 11 Jun 2018 at 7:16am
I figured it out.  In writing the previous post, it occurred to me that I put summaries using the insert > summary > sum function in the group 1, however I put the field without summary in group 2.  I guess I completely overlooked this.

Thanks a lot for your help.  The report works perfectly now.

Cheers.
IP IP Logged
Post Reply Post New Topic
Printable version Printable version

Forum Jump
You cannot post new topics in this forum
You cannot reply to topics in this forum
You cannot delete your posts in this forum
You cannot edit your posts in this forum
You cannot create polls in this forum
You cannot vote in polls in this forum



This page was generated in 0.033 seconds.