Posted By: TheITGuyAtWork — 8 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.
Posted By: DBlank — 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
Posted By: TheITGuyAtWork — 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.
Posted By: DBlank — 11 Jun 2018 at 4:43am
why are you not using a crosstab?
Posted By: TheITGuyAtWork — 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.
Posted By: DBlank — 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?
Posted By: TheITGuyAtWork — 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
Posted By: TheITGuyAtWork — 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.
