Charting based on date range's selection in Crystal Report Options

[Former Member]

Post Author: albertleng

CA Forum: Charts and Graphs

Dear,

My report starts with a chart in page 1 and then page 2 will start with data as below. The chart in page 1 is based on the data below.

AlarmTime Details Zone -

---

-

---

-

---

(dd/mm/yyyy hh:mm:ss) XXXX XXX

The report captures data from a SQL table. The user can filter the report shown based on selections: 1) Start Date 2) Range of Date, ie Daily, Weekly, Monthly, Yearly

Descriptions: 1) Start Date is the selection of starting date of AlarmTime. 2) a) Daily - show the records on "Start Date" only. b) Weekly - show the records from "Start Date" to "Start Date + 6 days" c) Monthly - show the records from "Start Date" to "Start Date + 30 days"

Other than that, a chart showing the number of records on the first page is required based on the selection of 1) and 2). The table of data will start from page 2.

If user selects 2)a) Daily, the chart will show the hourly number of records. That means the y-axis is number of records and x- axis will be 1st hour, 2nd hour, 3rd hour, ... , 24th hour.

If user selects 2)b) Weekly, the chart will show daily number of records. Y- axis is number of records and x-axis will be day1, day2, ... day7

If user selects 2)c) Monthly, the chart will show weekly number of records, Y- axis is number of records and x-axis will be week1, week2, week3 and week4.

That means the chart is created on run-time based on user's selections.

I can't figure out how to create the chart. My problem is how to get the number of records for 1st hour (Start Date 00:00:00 to Start Date 00:59:59), 2nd hour (Start Date 01:00:00 to Start Date 01:59:59) and other hours and then, show on the y-axis of the chart. I checked through "COUNT" function but it doesnt work that way. I also thought of using SQL query, ie . Select COUNT(*) from ALARM where AlarmTime between ...". However, it doesnt make sense because the StartDate is determined on run-time by user. Apart from that, i also can't find out the number of records in day 1, day 2... day 7 if user selects "WEEKLY". Anybody has any idea in this?

My other question is whether the chart can be drawn dynamically in crystal report in "Daily" form, "Weekly" form or "Monthly" in run- time.

Thanks.

Albert

[Former Member]

Post Author: qcheng

CA Forum: Charts and Graphs

Insert five groups grouped by AlarmTime, in the group options select 'for each hour', 'for each day", 'for each week', 'for each month' and 'for each year' respectively. Create a chart for each group. Use the parameter 'Range of Date' to suppress the groups and charts you don't want to display.