Incomplete Data for double "On change of" Bar graph 2

[MikeSawt]

I am using Crystal 11 and a SQL database.
My data is for On-time starts in an Hospital Operating Room.
Data includes: DateofSurgery, MDName, SurgeonType, PercentOnTimeStarts.

I am doing a six-month report. I am grouping the graph "On Change of" by Surgeontype first and then MDName.

The issue is that the order of the dates is following the first surgeon in the group. If the first surgeon has no cases in a month, the second and third surgeon's graph will be out of order. This works OK when the first surgeon has complete data. Meaning that the performed at least one case in every month. The first surgeon with incomplete data bar graph will show JUL, AUG, OCT, NOV, DEC. This surgeon did not do a surgery in SEP. The next surggeon has a case in every month (complete data). The graph show the months out of order. The Graph will show JUL,AUG,OCT,NOV,DEC,SEP.

Is there anyway to control this without adding data to the table eliminating the NULL months for each surgeon?

 

[lbass]

What datatype is your dateofsurgery field? And what time frame did you choose for the report--is it crossing calendar years? How did you build the date field into your chart? You didn't identify it as an On Change Of field.

-LB

 

[MikeSawt]

The dateofsurgery is a DateTime Field. In a process of trying to correct the issue, I convert the datetime field to a text field with a formual using if statements. I then used a specific order on the text field Jan Feb Mar..... It did not matter. The Month would still be out of order. I also try it with a formual converted to a number feild using month(dateofsurgery) it did not matter. The sort order was still based on the first MDname. If there were NULL months, the order would incorrect on the second and third MD.
If I filtered the surgeon with the null months out, the order is corrected. But as soon as unfilter that surgeon with the null months the next suregons order is out of order.

 

[lbass]

Please answer all questions.

-LB

 

[MikeSawt]

DateTime
6 months Jun - Dec
The date is entered using an Access front end, as a date format "yyyy-mm-dd-hh-mm-ss". Although it only displays the "mm-dd-yyyy". The user enters "12/1/2009" but it is stored in the table as 2009.12.1.00.00.00.

I used on change of MDName and then change of DateofSurgery.

I have created a group "surgeonType" in the report and placed the graph in the header of this group.
This will display the 15 different graph for each surgeontype, first sorted by Surgeontype and then each gragh has the MDname and then sorted on the Dateofsurgery which is out of order, when there is null months for the first surgeon. The next surgeon in the group has the dates miss sorted.

 

[lbass]

The problem is your conversion to a string. Try adding the datetime itself, and then manually edit the label if you want the monthname to show instead of the date.

-LB

 

[MikeSawt]

I tried three different ways. I tried it as a Datetime field, a String field and a Number field. It did not matter. The sort for the date would come out wrong on the second MDname if the first MDname had missing data.

 

[lbass]

Okay, I just tested this, with a normal bar chart and the dates don't even show up on the axis--what chart type are you using in the chart expert->type tab?

-LB

 

[MikeSawt]

Chart Specific Options
Type) Bar: side by side bar chart
Data) 1)On change of MDname ascending order
2)On change of DateofSurgery ascending order
Show Values) Avg of @percentdetail
Options) Show label
The lable for the month will show in the graph NOT on the axis. The Axis displays the MDname.

The percentdetail is an if statement with the following logic, if the case is more than 10 minutes late it is assigned a score of 0, if less than 10 minutes late it is assigned a score of 100.

If casedelay > 10 then 0 else 100

The "show values" is the avg of this fomula

 

[lbass]

Okay, I see the issue. The only way I could force the dates to order correctly was to use MDName as the first on change of field, and then for the second one, to use this formula:

{table.MDName} & " " & date(year({table.date}),month({table.date}),1)

This does cause the MD Name to repeat in the legend, but the chart display is ordered correctly anyway.

-LB

 

[MikeSawt]

This is the formula with my table name

{FirstCaseLateReport.SFN} & " " & date(year({FirstCaseLateReport.SDate}),month({FirstCaseLateReport.SDate}),1)

but with my data, it still was out of order.

I had to removed the labels. I know it is out of order because I have a Pivot Table below the bar graph with the same variable, so I can easily see the order. They don't match. The PT works fine with the missing data, PT show a 0 for the Null months.

 

[lbass]

Hmm. I double checked and this worked for me for ordering, with this formula as the second on change of field, and the name field as first on change of field. When I changed it back to just the date field, the order was mixed again. A quick way of telling is through the legend. This worked across over 70 groups. You used ascending (not specified) order, right?

-LB

 

[MikeSawt]

Yes ascending order.
The legend was supressed. I showed the legend and it was colored by group. Only the MD names showed. I changed it to colored by series. This show all the MDnames followed by the months. They are out of order. Smith 11/1/09, Smith 7/1/09, Smith 8/1/09.

I think the order is now based on the month part of the formula as a string, so November is before July. "Smith_11" is before "Smith_7". If I could change the date to be "Smith_07" then I think it will work. Do you know how I can do that?

 

[lbass]

The difference is that my default setup for dates in file->options is 07/01/2009, so mine sorted correctly. If you change that, yours should sort correctly, too. If the report can cross fiscal year, it should be set to: YMD in the customize tab, with the formatting for month and day set to two digits.

However, it would make more sense (since Options apply to ALL reports), to change your formula to:

{FirstCaseLateReport.SFN} & " " & totext(date(year({FirstCaseLateReport.SDate}),month({FirstCaseLateReport.SDate}),1),"yyyy-MM-dd")

-LB

 

[MikeSawt]

Got it. That's great
Thanks for your help
LB

 

[lbass]

Sorry I didn't come up with that sooner.

-LB