Missing Months in Chart when value = NULL 3

[APB1981]

I know this topic has been posted quite a few times - but I am yet to find a clearly defined solution.

I have a simple chart counting the total number of accidents per month. The month is derived from the Event table >> Month ({Event.Event_Date}).

However, if there is no event corresponding to July or August or any other month in the Event table, then Crystal Reports simply skips to the next valid month where the number of events is > 1.

I know this can be achieved with a view - but I believe there should be a much more simple solution. I have tried a variety of different formulas but still no luck. Any Ideas?

Tech specs: Crystal 8.5
Database: Oracle 9.2, MSSQL 2000, ASA Sybase 8.2
Windows XP SP2

APB

 

[lbass]

Technically, you should be able to use running totals directly in a chart and have them appear correctly, but I have never been able to get consistent accuracy regardless of where I place the chart. There are issues with 8.5 noted on the Business Objects website.

The following works in 8.0. Basically, it makes the chart available during a later pass, and thus allows the running totals to work correctly. Let's assume that you have one group, e.g., on location, and that you are charting across 12 months for each group. First, you would use the running total expert to create 12 running totals, one for each month. They would be set up to count {table.accidents}, evaluate based on a formula:

month({table.date}) = 1 //for {#Jan}; 2 for Feb, etc.

Reset on change of group (location). Place the running totals in the group footer. If you were only doing this at the report level, all running totals would have be set to reset "never".

Next create 12 formulas {@sharedJan},{@sharedFeb}, etc. (the null values are lost if you combine these in one formula), and place them in the group footer_a section also:

//{@sharedJan}:
whileprintingrecords;
shared numbervar Jan := {#Jan};

//{@sharedFeb}:
shared numbervar Feb := {#Feb}; //etc.

Also, create a reset formula:

//{@reset} to be placed in the main report group header:
whileprintingrecords;
shared numbervar Jan := 0;
shared numbervar Feb := 0;
shared numbervar Mar := 0;
|
|
V
shared numbervar Dec := 0;

Insert a second group footer, where you will place a subreport. Insert a subreport that uses your table, add one field so that you can close the subreport, and then link the subreport to the main report on {table.location} (your group field), and place the subreport in the group footer_b section. Then select the subreport->edit subreport and then create 12 formulas inside the subreport, like the following:

//{@Jan}:
shared numbervar Jan;

//{@Feb}:
shared numbervar Feb; //etc.

It is very important that you do NOT place these 12 formulas on the subreport canvas yet. First, insert a chart, and choose {table.location} for your "on change of" field. Then add the 12 formulas to the "Show value" section, and for each one, check "Don't summarize values". Place the chart in the report footer of the subreport. And only then place the 12 formulas in the subreport report header. If you place the formulas on the report first, they will not be available to add as a summary in the chart.

If you only want a chart in the report footer of the main report, change your running totals to reset never, as I mentioned earlier, eliminate the reset formula, and in your chart, instead of "On Change Of", use the dropdown to select "for all records".

In 8.0, using this method, there are no group axis labels, but you can add data labels or values, and the legend shows the months.

-LB

 

[lbass]

Actually, because every instance of the chart will have 12 months, you can add a text box with the twelve months entered as text and spaced to fit under each bar of the chart.

-LB

 

[APB1981]

Thanks Ibass. This solution sounds like it will do the trick. I haven't tried this approach yet - but I will give it a test run today.

Keep up the good work. Thanks again.

APB

 

[ITCPhil]

I don't know if it's proper to bump and old thread but I've a quick question. Is this possible without a subreport? I've the same problem but this report is already a subreport so I can't have another subreport in it...

Phil (CR8.5, Oracle9.2)

 

[lbass]

Phil,

Try creating the shared variables in the subreport and then in the main report, in a section below the one in which the subreport is located, insert a chart. Create the shared variable formulas in the main report, and without placing them on the main report first, insert them into the chart expert. This should work since the chart is dependent on values that are generated by the subreport, and therefore it forces the chart to be executed later.

Also note that in the chart expert, you should choose the "maximum" of the formula in order to get the correct value if you do not have the option of choose "don't summarize".

-LB

 

[ITCPhil]

I will try that Monday morning, have to get a few reports out before I go home, thanks for the quick reply!

 

[kenhamady]

Linda,

Maybe I misunderstand the technique, but I think you get to the same place with 12 formulas in the main report. That would eliminate the need for variables and the subreport.

If you create 12 formulas like this:

if month({table.date}) = 1
then 1
else 0

Then you could use these 12 formulas in either a group chart or a grand chart. If you stopped here the names on the chart would be "Sum of @Jan" which isn't pretty. To fix that you could write another 12 formula fields that say either:

Sum ([@month])

//or if you wanted subtotals
Sum ([@month] , [your.group])

And use these in the chart. Wouldn't that do the same thing?


Ken Hamady, On-site Custom Crystal Reports Training & Consulting
Public classes and individual training.
Guides for Formulas, Parameters, Subreports, VB, .NET, Tips and Tricks

http://www.kenhamady.com/

 

[lbass]

Ken,

Thank you for this correction. I think your method is the best approach as long as there is no row inflation--it would be simpler and faster. If running totals are required because of row inflation, then my suggestion would be an option.

-LB