Dates in charts

[strom99]

How do you allow the user to enter a date range in a parameter field and have the graph display either by week, month or quarter. Using CR8.5.

Example, user would be prompted to see the graph either by week, month or quarter. Using one date field (CUST_CONTACT_DATE).

 

[Moonmouth]

Hi storm99...as soon as I figure out my "little problem" I'm doing a similar report...I will let you know soon how I done it

Peace

 

[rhinok]

As a report developer, I have a few questions regarding the report requirements:

1) Does the date range truly use only the field indicated or is it something like: {tablename.datefield} in {?FromDate} to CurrentDate.

2) Is there a set limit to the Date Range the user can enter? For example, will the user always select a year's worth of data, 6-month's worth of data, or is there an unlimited range? Does the date range rely upon the "Week, Month, Quarter" parameter. For example, if the user selects Quarter, should they only select a year's worth of data? If they select "WeeK" then should they only select a Month's worth of data?

This will greatly affect how you code your report around the required parameter.

3) How do you want to qualify and define your display parameter? For example, if the user selects "Week" as the display value then what does the user expect to see? If the user selected a year's worth of data then do they expect to see Week 1 - Week 52? If the user selected 1-month's worth of data do they expect to see Week 1 - Week 4 or do they expect to see Current Week, Week 1 (From Current), Week 2, Week 3(etc... in decending order from the Current or most recent Week)?

Once again, this is greatly reliant upon the date range allowed. How you define a "Week" depends on both the expected result and the date range allowed. If you determine week by calculating days then you need to take Leap Years into consideration, also.

 

[strom99]

1) Date range only uses the (CUSTOMER_COMPLAINT_DATE) FIELD.
2)If the user selects 'Week' they should see the last full week. If they select 'Qtr' should see last completed quarter. If they select 'Last 4 Qtrs' then they should see the last 4 completed quarters.
3) Weeks are determined by the last completed week M-F.

Thanks for the help

 

[rhinok]

Ok, I've written a sample report (CR7)and run it against my own database and gotten it to work well. This type of report could be best accomplished with a Stored Procedure, but if that isn't an option, this should work...

1) Created a String parameter {?GroupDisplay} with Default Values of:

'Week'
'Qtr'
'Last 4 Qtrs'

2) Created a formula called {@GroupDisplay}:

//Defines the Values by which to Group the Data
StringVar DayVar;
StringVar MonthVar;
StringVar QtrVar;

If DayOfWeek({tablename.cust_contact_date}) = 1 Then DayVar := 'Sun' Else
If DayOfWeek({tablename.cust_contact_date}) = 2 Then DayVar := 'Mon' Else
If DayOfWeek({tablename.cust_contact_date}) = 3 Then DayVar := 'Tue' Else
If DayOfWeek({tablename.cust_contact_date}) = 4 Then DayVar := 'Wed' Else
If DayOfWeek({tablename.cust_contact_date}) = 5 Then DayVar := 'Thu' Else
If DayOfWeek({tablename.cust_contact_date}) = 6 Then DayVar := 'Fri' Else
If DayOfWeek({tablename.cust_contact_date}) = 7 Then DayVar := 'Sat';

If Month({tablename.cust_contact_date}) = 1 Then MonthVar := 'Jan' Else
If Month({tablename.cust_contact_date}) = 2 Then MonthVar := 'Feb' Else
If Month({tablename.cust_contact_date}) = 3 Then MonthVar := 'Mar' Else
If Month({tablename.cust_contact_date}) = 4 Then MonthVar := 'Apr' Else
If Month({tablename.cust_contact_date}) = 5 Then MonthVar := 'May' Else
If Month({tablename.cust_contact_date}) = 6 Then MonthVar := 'Jun' Else
If Month({tablename.cust_contact_date}) = 7 Then MonthVar := 'Jul' Else
If Month({tablename.cust_contact_date}) = 8 Then MonthVar := 'Aug' Else
If Month({tablename.cust_contact_date}) = 9 Then MonthVar := 'Sep' Else
If Month({tablename.cust_contact_date}) = 10 Then MonthVar := 'Oct' Else
If Month({tablename.cust_contact_date}) = 11 Then MonthVar := 'Nov' Else
If Month({tablename.cust_contact_date}) = 12 Then MonthVar := 'Dec';

If Month (CurrentDate) In [1 to 3] and Month({tablename.cust_contact_date}) In [1 to 3] Then QtrVar := 'Qtr1' Else
If Month (CurrentDate) In [1 to 3] and Month({tablename.cust_contact_date}) In [4 to 6] Then QtrVar := 'Qtr2' Else
If Month (CurrentDate) In [1 to 3] and Month({tablename.cust_contact_date}) In [7 to 9] Then QtrVar := 'Qtr3' Else
If Month (CurrentDate) In [1 to 3] and Month({tablename.cust_contact_date}) In [10 to 12] Then QtrVar := 'Qtr4' Else
If Month (CurrentDate) In [4 to 6] and Month({tablename.cust_contact_date}) In [4 to 6] Then QtrVar := 'Qtr1' Else
If Month (CurrentDate) In [4 to 6] and Month({tablename.cust_contact_date}) In [7 to 9] Then QtrVar := 'Qtr2' Else
If Month (CurrentDate) In [4 to 6] and Month({tablename.cust_contact_date}) In [10 to 12] Then QtrVar := 'Qtr3' Else
If Month (CurrentDate) In [4 to 6] and Month({tablename.cust_contact_date}) In [1 to 3] Then QtrVar := 'Qtr4' Else
If Month (CurrentDate) In [7 to 9] and Month({tablename.cust_contact_date}) In [7 to 9] Then QtrVar := 'Qtr1' Else
If Month (CurrentDate) In [7 to 9] and Month({tablename.cust_contact_date}) In [10 to 12] Then QtrVar := 'Qtr2' Else
If Month (CurrentDate) In [7 to 9] and Month({tablename.cust_contact_date}) In [1 to 3] Then QtrVar := 'Qtr3' Else
If Month (CurrentDate) In [7 to 9] and Month({tablename.cust_contact_date}) In [4 to 6] Then QtrVar := 'Qtr4' Else
If Month (CurrentDate) In [10 to 12] and Month({tablename.cust_contact_date}) In [10 to 12] Then QtrVar := 'Qtr1' Else
If Month (CurrentDate) In [10 to 12] and Month({tablename.cust_contact_date}) In [1 to 3] Then QtrVar := 'Qtr2' Else
If Month (CurrentDate) In [10 to 12] and Month({tablename.cust_contact_date}) In [4 to 6] Then QtrVar := 'Qtr3' Else
If Month (CurrentDate) In [10 to 12] and Month({tablename.cust_contact_date}) In [7 to 9] Then QtrVar := 'Qtr4';

//Determines the Type of Group based on the Parameter selected
If {?GroupDisplay} = 'Week' Then DayVar Else
If {?GroupDisplay} = 'Qtr' Then MonthVar Else
If {?GroupDisplay} = 'Last 4 Qtrs' Then QtrVar;

//I assumed that you wanted to break the Last Full Week Chart into Days of the week.
//Since the Last 4 Full Quarters can span different years, QTR1 isn't always the first yearly quarter
//so I accounted for that issue.

3) Group by the {@GroupDisplay} Field with Specified Sort Order. You will have to create named Specified Order Groups. Mine are:

Sun where {@GroupDisplay} = 'Sun'
Mon where {@GroupDisplay} = 'Mon'
Tue where {@GroupDisplay} = 'Tue'
Wed where {@GroupDisplay} = 'Wed'
Thu where {@GroupDisplay} = 'Thu'
Fri where {@GroupDisplay} = 'Fri'
Sat where {@GroupDisplay} = 'Sat'
Qtr1 where {@GroupDisplay} = 'Qtr1'
Qtr2 where where {@GroupDisplay} = 'Qtr2'
Qtr3 where {@GroupDisplay} = 'Qtr3'
Qtr4 where {@GroupDisplay} = 'Qtr4'
Jan where {@GroupDisplay} = 'Jan'
Feb where {@GroupDisplay} = 'Feb'
Mar where {@GroupDisplay} = 'Mar'
Apr where {@GroupDisplay} = 'Apr'
May where {@GroupDisplay} = 'May'
Jun where {@GroupDisplay} = 'Jun'
Jul where {@GroupDisplay} = 'Jul'
Aug where {@GroupDisplay} = 'Aug'
Sep where {@GroupDisplay} = 'Sep'
Oct where {@GroupDisplay} = 'Oct'
Nov where {@GroupDisplay} = 'Nov'
Dec where {@GroupDisplay} = 'Dec'

4) Create a {@QFromDate} formula:

//Defines the From Date for the Date Range
DateVar LFQFromDate;
DateVar L4QFromDate;

If Month(CurrentDate) In [1 to 3] Then LFQFromDate := Date((Year(CurrentDate)) -1,10,1) Else
If Month(CurrentDate) In [4 to 6] Then LFQFromDate := Date(Year(CurrentDate),1,1) Else
If Month(CurrentDate) In [7 to 9] Then LFQFromDate := Date(Year(CurrentDate),4,1) Else
If Month(CurrentDate) In [10 to 12] Then LFQFromDate := Date(Year(CurrentDate),7,1);

If Month(CurrentDate) In [1 to 3] Then L4QFromDate := Date((Year(CurrentDate)) -1,1,1) Else
If Month(CurrentDate) In [4 to 6] Then L4QFromDate := Date((Year(CurrentDate)) -1,4,1) Else
If Month(CurrentDate) In [7 to 9] Then L4QFromDate := Date((Year(CurrentDate)) -1,7,1) Else
If Month(CurrentDate) In [10 to 12] Then L4QFromDate := Date((Year(CurrentDate)) -1,10,1);

If {?GroupDisplay} = 'Qtr' Then LFQFromDate Else
If {?GroupDisplay} = 'Last 4 Qtrs' Then L4QFromDate;

5) Create a {@QThruDate} formula:

//Defines the Through Date for the Date Range
DateVar LFQThruDate;
DateVar L4QThruDate;

If Month(CurrentDate) In [1 to 3] Then LFQThruDate := Date((Year(CurrentDate)) -1,12,31) Else
If Month(CurrentDate) In [4 to 6] Then LFQThruDate := Date(Year(CurrentDate),3,31) Else
If Month(CurrentDate) In [7 to 9] Then LFQThruDate := Date(Year(CurrentDate),6,30) Else
If Month(CurrentDate) In [10 to 12] Then LFQThruDate := Date(Year(CurrentDate),9,30);

If Month(CurrentDate) In [1 to 3] Then L4QThruDate := Date((Year(CurrentDate)) -1,12,31) Else
If Month(CurrentDate) In [4 to 6] Then L4QThruDate := Date(Year(CurrentDate),3,31) Else
If Month(CurrentDate) In [7 to 9] Then L4QThruDate := Date(Year(CurrentDate),6,30) Else
If Month(CurrentDate) In [10 to 12] Then L4QThruDate := Date(Year(CurrentDate),9,30);

If {?GroupDisplay} = 'Qtr' Then LFQThruDate Else
If {?GroupDisplay} = 'Last 4 Qtrs' Then L4QThruDate;

6) Edit your Record Select Criteria:

If {?GroupDisplay} = 'Week' Then {tablename.cust_contact_date} In LastFullWeek Else
{tablename.cust_contact_date} In {@QFromDate} To {@QThruDate}

***Note, if you have any criteria that can be passed to the Server on the first pass then state those criteria first as the record selection criteria I've posted won't be processed until the second pass. This is the downside to not building this report against a stored procedure.***

7) Create a chart based on the Change of the {@GroupDisplay} formula and whatever summarized field you want.