minimum date in graph (Crystal Reports XI)

[SchwabTerex]

I am trying to use Crystal Reports XI to recreate reports and graphs that were originally done in Access/Excel. I am pretty familiar with designing reports in this software but have never used the graphs before now. What I would like to do is graph the minimum start date per production category and location. I have groups set up for both the location (group 1) and the production category (group 2). I am able to insert a summary in the report to find the minimum start date in each production category. This is then displayed in the group 2 footer. I can see that I have the correct info that I want but I am unable to display it on a graph. I believe the root of my problem is that when I go into the data tab in the chart expert, I am unable to summarize my "show value" in any way other than count. Therefore I am obviously getting a value of 1 for every category instead of the date itself. Does any one have a suggestion on what I may be doing incorrectly? Is the functionality of Crystal this limited or am I just missing something?

Thanks for the help!

 

[lbass]

I'm having trouble imagining how you would want to see the date graphed. What would that look like if not a count?

-LB

 

[SchwabTerex]

The original graph that I am trying to duplicate showed the production categories along the y-axis and dates (current date forward in week increments) on the x-axis. The actual date was then plotted on this chart.

The purpose was to visually see where the minimum starting date would appear in the coming weeks. Each production category has a set leadtime for parts ordering. The background of the chart gets colored in to display this. For exmaple catogory 65 has a leadtime of 4 weeks, category 40 has a leadtime of 8 wks. If the plots on the chart fall within that colored section for lead time drafting knows they are behind. Ideally all the minimum start dates should be plotted outside of the leadtime area.

That is why I want to plot the dates, not a count of 1 minimum date for each category. If there is a better way of displaying the above info, I am open to suggestion. I hope my discription makes sense.

~S

 

[lbass]

I was able to create a chart on date by converting the minimum date to a number by doing the following. First create a SQL expression {%mindt} like this:

(
select min(`date`)
from table A
where A.`location` = table.`location` and
A.`category` = table.`category`
)

Then create a formula {%dttonum} like this to convert the minimum to a number:

{%mindt}-date(1899,12,30)

Use the location and category as on change of fields, and add the formula {%dttonum} as your summary field and check "Don't summarize." Then observe the range of values that appear on the chart and narrow the displayed range by going to the axes tab in the chart expert and uncheck auto range and add in the range of values that will allow the correct chart display.

Then select the datenumber on the y axis->right click->format axis label->number tab->category: date->select the date display.

-LB

 

[SchwabTerex]

That works. Thank you! That was getting very frustrating.