Crystal XI Dynamic Chart X axis labels

[James1963]

I am using CRXI and have developed a sick leave absences report that is grouped by a formula that checks the date differences and produces a number as below.
if datediff("m",{?Pm-@f1_StartDate},{hrpay_lve_union_avw.TIMESHEET_DATEI}) = 0
then
datediff("m",{?Pm-@f1_StartDate},{hrpay_lve_union_avw.TIMESHEET_DATEI})+1
else
datediff("m",{?Pm-@f1_StartDate},{hrpay_lve_union_avw.TIMESHEET_DATEI})+1

This produces the result I was expecting to get the data in namely 1 - 12 and the array of results matches to sequence in the subreport for the graph but any attempt to use the abbreviated month name and year and the graph sorts alphabetically or numerically if you use the month numbers.

The data is sorted in the subreport for the graph into any 12 month sequence from the date entered. The only problem is that when I try to graph the data I get 1,2,3,4,5,6,7,8,9,10,11,12 for the X axis which is correct but I have an an array that shows the month name abbreviated but the graph keeps sorting alphabetically when I use that formula. What I would like to do is have the chart show the data as follows
Feb 08 Mar 08 Apr 08 May 08 etc

Is there anyway that Crystal can be made to dynamically use a specified sequence (there is only twelve sequences thankfully)and not sort alpabetically or numerically. I have tried the cross tab solution but it seems to do exactly the same thing so I am at my wits end.

 

[lbass]

I'm unclear on how you are converting the number to a date for the chart. What exactly are you using for the x-axis? A number or date?

You might want to take a look at the numeric axis type of chart, which might address the order issue. There are three date type charts within the numeric axis option.

-LB

 

[James1963]

I have used the datediff function to check the starting date entered by the user and the timesheet date and found out if the difference is 0 or a result then added 1 to the result and used that for my sequence to run the graph out in the subreport. But what I'm trying to do is graph data out of an array of months and another array of the values but the only way I could get it to work was to do the formula above that created a numeric value based on the date selected to start from. this then allowed for the data to be displayed and graphed in the subreport. This is ok but I would like it to be month & year eg Jan 09 and because the starting date could be any date the X axis needs to dynamically create to suit. If I use the
Monthname(Monthno({hrpay_lve_union_avw.TIMESHEET_DATEI}),true)
the sort order is either ascending or descending as the specified order changes everytime it is run. Any suggestions are greatly appreciated as I am at my wits end with this one.

 

[lbass]

Please try the numeric axis chart option.

-LB

 

[James1963]

Hi LB
Thanks for your reply it helped a some but not quite what I am after.

I tried the numeric axis chart option no luck I think it is because I am dragging data from three distinct reports. I ahve one report capturing the available hours by month and passing the value by month to the main report as a shared variable. Then the main report is capturing the sick leave data and doing counts and evaluations of the data in groups of empid, date(weekly) and date(monthly) and then creating an array to pass the data down to the chart subreport to graph it and the subreport for the chart is where the problem is as it will not display the correct months.

Formulas Subrpt1
Ahours - Shared Numbervar Hrs;
Hrs:= Sum ({HRPAY_PAYITEMS_DTL.NUMBER_UNITS}, {@MonthName})
MonthNames - MonthName (Month({HRPAY_PAYITEMS_DTL.PAY_PERIOD_ENDDATE}),true)

Formulas Main Rpt
Availablehours - Shared Numbervar Hrs
percent - ({#MonthSickSum}/{@AvailableHrs})

Formulas - Chartsubrpt
Showmth - shared stringvar array MthArray [{@ChooseMth}]
ShowValue - if isnumeric(split({?Pm-@StrShowValue},"^")[{@ChooseMth}])
then tonumber(split({?Pm-@StrShowValue},"^")[{@ChooseMth}])
Choosemth - if datediff("m",{?Pm-@f1_StartDate},{hrpay_lve_union_avw.TIMESHEET_DATEI}) = 0
then
datediff("m",{?Pm-@f1_StartDate},{hrpay_lve_union_avw.TIMESHEET_DATEI})+1
else
datediff("m",{?Pm-@f1_StartDate},{hrpay_lve_union_avw.TIMESHEET_DATEI})+1

To split my arrays it wants a numeric value so I had to do the number based on the date and the difference between the months. I get the right values but the X axis still looks like 1,2,3,etc the way I need it to look is Jan 09 but I don't know if its possible the way I am doing it. :-(

-JA

 

[lbass]

Still not sure I'm following, because I can't see how the number in choosemonth would necessarily show the correct month. But what if you changed choosemonth to display a date, by using something like the following:

dateadd("m", datediff("m",{?Pm-@f1_StartDate},{hrpay_lve_union_avw.TIMESHEET_DATEI})+1, {?Pm-@f1_StartDate})

I don't think you need the "if" statement since whether the result is the same whether or not the "if" criterion is met.

-LB

 

[James1963]

Fantastic Thanks LB this works perfectly for the dates but the percentages are just not pulling out correctly. It is only pulling the one figure which is the last in the array. I will see what I can do to extract the correct values.

1/02/2008 Jan 0.0260
1/03/2008 Jan 0.0260
1/04/2008 Jan 0.0260
1/05/2008 Jan 0.0260
1/06/2008 Jan 0.0260
1/07/2008 Jan 0.0260
1/08/2008 Jan 0.0260
1/09/2008 Jan 0.0260
1/10/2008 Jan 0.0260
1/11/2008 Jan 0.0260
1/12/2008 Jan 0.0260
1/01/2009 Jan 0.0260

Thankyou for your help it has been great.

-JA