Crystal Reports - display null/zero values in a cross tab and/or chart

[tbear10]

Hello,

I'm relatively new to Crystal Reports & need help with a report I am creating. I have given an explanation of the scenario below.

Report SHOULD look like this:
Month:Jan-Feb-Mar-Apr-May ...
Type 1:-0-0-0-12-16
Type 2:-0-0-0-34-56
Total:-0-0-0-46-72

The report looks like this:
Month:Apr-May ...
Type 1:12-16
Type 2:34-56
Total:46-72

I am using a cross-tab to display this information, but I need ALL the months (including those that have a null/zero value).
I also need to create a chart based on this information.

I have the report itself working by creating a 'cross-tab look-a-like' - by that I mean, I created Type 1 & Type 2 totals for each month using formulas.

It would be great if I could get the cross-tab working & display the zero values because then my report will be cleaner & simple.
Also, if I can get the cross-tab working, creating the chart will be easy.

My question(s) is:
- how can i display zero/null values in a cross-tab?
- if that is not possible, is there any way to create the chart where the values are zero/null?

 

[hilfy]

Do you have a table that will give you all of the month values, including those that are missing (or date values and display as month in the report)? If so, use a left join from that table to your data table and then use the date fields from there as your group instead of the date fields in your data table.

-Dell

A computer only does what you actually told it to do - not what you thought you told it to do.

 

[tbear10]

Hi,

No, I do not have a table with all the month fields. I don't think I can create one either since this is an enterprise system we are using.

 

[tbear10]

Can anyone help me with this problem? I am so close to developing the report that I want, the only thing missing the chart that displays all the months.

Thanks in advance

 

[N22S]

Have you tried converting the null values to zero? For example, if isnull({field or formula}) then 0 else {field or formula}

 

[tbear10]

Yes, I tried that & it doesn't work because data for certain months is not stored anywhere.

 

[N22S]

Here's my last thought - create 12 different formulas for each month and qualify what appears for the formula based upon the month required. For example, if month({posting date field} = 4 then sum({fields used for posting}) else 0

 

[lbass]

You could try using conditional formulas as N22S suggested, except the formula should be:

//{@Apr}:
if month({table.date}) = 4 then {table.amt} //or 1 if a count

Then you would insert a sum (not a count) on this at the type group level. For charting, you would have to use sum of each month as separate summary fields, with type as the "on change of" field.

-LB

 

[tbear10]

Thanks N22S & LB. I had already created the 12 separate formulas & that was working well, but I didn't use Sum, I used Count. I'll try that & see what happens.

But I have another issue that I need to deal with before I can move ahead because it can affect my whole report. I've started a new thread:

http://www.tek-tips.com/viewthread.cfm?qid=1585226&page=1

I need to resolve this issue before I go on any further because this is the foundation of the report.

 

[tbear10]

Hi,

I got my report fixed to show all codes.
I created a mock-crosstab that displays all totals for all months, separated by type1 & type2 (even if some months do not have any data)
I am back to my original problem which is:
I need to create a chart that displays the total, separated by type1 & type 2 for all months. I cannot seem to get this to work.
So the 'side by side bar chat' should be something like this:
x-axis: display all months
y-axis: total count
2 bars within graph:
1st bar shows Type 1 total for each month
2nd bar shows Type 2 total for each month (beside 1st bar)

Can anyone please help? I have searched endlessly & cannot seem to find a suitable solution...