Line chart for number of calls per 30mins interval
Printed From: Crystal Reports Book
Category: Crystal Reports 9 through 2020
Forum Name: Technical Questions
Forum Discription: Formulas, charting data, Crystal syntax, etc.
URL: http://www.crystalreportsbook.com/forum/forum_posts.asp?TID=7274
Printed Date: 28 Apr 2024 at 1:54pm
Topic: Line chart for number of calls per 30mins interval
Posted By: guan
Subject: Line chart for number of calls per 30mins interval
Date Posted: 06 Aug 2009 at 2:51am
|
hi guys, i just started learning crystal report and im very very new to it would u guys help me out with the current problem that i have i really do not how or where to start. i have connected the crystal report to ms access it just has 1 table qlog(no., call_in_time, call_in_date, name, telephoneNumber) so basically my problem is, i wanna generate a line chart showing the number of calls in 30mins interval in one business day. call_in_time is the time for when they called in call_in_date is the date for when they called in. thanks in advance! |
Replies:
Posted By: DBlank
Date Posted: 06 Aug 2009 at 6:28am
|
**** Can you please delete out the extra posts you made that are a repeat of this one. What are the field data types for CAll_in_Time and CAll_In_Date?
|
Posted By: guan
Date Posted: 06 Aug 2009 at 6:36am
|
hi sorry about that, i didnt know how it happen to post 4 times. i have set both of them as Text datatype previously it was date/time, but i had some problems with it because from crystal report it was showing both date AND time for both data field i hope u understand wat i meant thanks |
Posted By: DBlank
Date Posted: 06 Aug 2009 at 6:47am
|
Are you flexible on the 1/2 hour interval to an hour interval?
Crystal has a built in function to group these at the hour level.
Go back to leaving the field as a DATETIME and do not split it into 2 text fields.
It is really straight forward although I do not know what you are graphing...a count of row or a sum of a field in rows or something else. I will assume it is a count of the rows in this sample:
Add a Chart (usually to your Report HEader).
The Chart Expert will open.
Click on the TYPE tab and select LINE chart.
Pick the one you want to use (either Line Chart or Line CHart with markers ...the two on the far left).
Click on DATA tab.
On Change of a add your DATETIME field.
now click on the DATETIEM field that is in the On Change of window. This willa ctivate the "Order" button.
Click on the Oder Button.
Change the This section will be printed: section to "for each hour" and click on OK.
Add your unique record field (usually a primary key) to the "Show value(s)" window.
Click on it to activate it and then click on the "Set Summary Operation" button.
Change the calcualte this summary to "DistinctCOunt".
Close it and chaeck out your graph
|
Posted By: guan
Date Posted: 06 Aug 2009 at 6:53am
|
ah yeah i understand thanks! how ever wat if i already have input 230++ data and all of them are split into two data field which is the call_in_time and call_in_date ? means i need to re edit all the data? |
Posted By: DBlank
Date Posted: 06 Aug 2009 at 7:07am
|
Try converting it via a crystal formula as:
Cdatetime({table.call_in_date} + " " + {table.call_in_time})
place this on your details and validate it is giving you the correct DATETIME per row.
If it is working use it to group on in the chart (per hour as noted above).
NOTE this all assumes you want it to trend it out per day per hour.
If you want it trending per hour only regardless of the day (meaning clump all of your 9-10 calls for every day all togther) then use another formula
as:
timevalue({table.call_in_time}) and use that to do the group on (per hour).
You can do both in the same report (not the same chart) if you want.
|
Posted By: guan
Date Posted: 06 Aug 2009 at 7:21am
|
alright i will give it a try thanks a lot for your help! |
Posted By: guan
Date Posted: 06 Aug 2009 at 9:43am
|
i have pasted that formula below but it keeps sayin "A string is required here" and it highlights this part of the formula {Qlog1.Call_In_Time}) |
Posted By: DBlank
Date Posted: 06 Aug 2009 at 9:47am
|
if you place the {Qlog1.Call_In_Time} field on your report and hover over it with the cursor what field type is appearing in the parenthesis at the end of the pop up information? |
Posted By: guan
Date Posted: 06 Aug 2009 at 4:54pm
|
hey im very sorry for the wierd timing of replies, i really appreciate u helping me out here. btw im currently from singapore, and my last msg that i posted here was already midnite and i had to go to bed because i needed to wake up 5am =(. well TGIF today! anyways.. i manage to solve the string problem, and i manage to follow all your steps however when it is about to generate the chart, it says "bad date-time format string". i will try to troubleshoot it hahaha |
Posted By: guan
Date Posted: 06 Aug 2009 at 5:44pm
|
i manage to solve the problem by using this formula datetime(date({Qlog1.Call Date}),time({Qlog1.Call In Time})) however, the chart is now showing everyday and everyhour umm.. forexample 13/07/09 -- 8am, 14/07/09 -- 9am, 15/07/09 -- 10am and so on haha i think its the formula? |
Posted By: guan
Date Posted: 07 Aug 2009 at 5:53pm
| hi can anyone else help? thanks |
Posted By: guan
Date Posted: 09 Aug 2009 at 11:47am
| up |
Posted By: DBlank
Date Posted: 09 Aug 2009 at 1:26pm
|
WHat field did you use for the SUmmary and what is the SUmmary set to? |
Posted By: guan
Date Posted: 10 Aug 2009 at 7:34am
| the field i set to is "no." and its set to distinct count. |
Posted By: DBlank
Date Posted: 10 Aug 2009 at 7:46am
|
This is hard to diagnose without seeing the data and your set up so I need to ask a few more questions... 1. Are you filtering the report data at all (or do you need to and are not)?
2. Do you want more than one day of data to appear? If so do you want a different chart poer day?
3. Or do you want to collapse all of the days into one and just show the hours in your chart regardless of that date?
4. Can you post sample row level data so i know what I am dealing with at a data level?
|
Posted By: guan
Date Posted: 10 Aug 2009 at 9:31am
|
do you want me to upload the files here? basicaly i wanna show for exmple on 1st of june, the number of calls from 8am to 6pm in 1 hr interval. you told me that 30mins interval will be tricky and i should use the build in function which is the 1hr interval. it would be good if i could show 1 chart per day with the 1hr intervals from 8am to 6pm heres the sample row of data No. Call Date Call In Time Caller Name Employer ID 1 14/7/2009 9:00AM Rebecca 443432 |
Posted By: guan
Date Posted: 10 Aug 2009 at 9:37am
|
hey DBlank, i will see your reply tmr morning its already 1230am here, i need to wake up for work tmr thanks again and sorry for the trouble |
Posted By: DBlank
Date Posted: 10 Aug 2009 at 2:52pm
|
OK here is what I would do.
Create date parameters to filter the overall report dates (if needed based on size of data).
1. Create a formula field for the for the date as "DAte Group" :
date({Qlog1.Call Date})
2. Group on this formula and change to "For each Day".
3. create a time formula as "Time GRoup":
time({Qlog1.Call In Time})
4. create a Group header1B.
5. Go into your existing Chart and change the "On Change of" field to the @TimeGroup formula field.
6. Drag the chart to the GH1B
Check it out and see is working as you want per day (one chart per day group header).
Youc an also exclude data using your select expert with:
time({Qlog1.Call In Time}) in TIME(08,00,00) to TIME(18,00,00)
If you really want this to be every 1/2 hour you can write a formula to convert your time field to be every half hour and group on that instead but it will be a long statement...
if time({Qlog1.Call In Time}) in TIme(08,00,00) to Time(08,30,00) then time(TIme(08,00,00) else
if time({Qlog1.Call In Time}) in TIme(08,30,01) to Time(09,00,00) then time(TIme(08,30,00) else
if time({Qlog1.Call In Time}) in Time(09,00,01) to Time(09,30,00) then time(TIme(08,00,00) else ...
If I come up with a better formula for the 1/2 hour I willpost that for youlater but that is easy to swap out if the rest of it works
|
Posted By: guan
Date Posted: 10 Aug 2009 at 5:43pm
|
DBlank it works!, u r a real genius! thank you so much for your time and patience in helping an idiot like me. thanks!! |
Posted By: DBlank
Date Posted: 11 Aug 2009 at 6:40am
This is a no self degradation web site 
But I am glad you got it working.
If you really want / need this to be every half hour you will need to alter your times fall on each 1/2 hour then alter your group to "for each second".
i think this formula should work for that a way to do that but create it and drop it on your cnavas to make sure it converts all your times to the correct 1/2 hour increment
TIME(HOUR(TIME({Qlog1.Call In Time})),if(minute(TIME({Qlog1.Call In Time})))>=30 then 30 else 00,00)
|
Posted By: guan
Date Posted: 11 Aug 2009 at 4:57pm
|
hi thanks again sorry do i put this formula under group expert? TIME(HOUR(TIME({Qlog1.Call In Time})),if(minute(TIME({Qlog1.Call In Time})))>=30 then 30 else 00,00)
|
Posted By: DBlank
Date Posted: 11 Aug 2009 at 5:38pm
|
Update your "TimeGroup" formula with it.
In stead of just comverting the Qlog1.Call In Time field from text to a time field it will also change it to the half hour time...for example
9:17 am will become 9:00:00 am
10:34 am becomes 10:30:00 am
10:29 am becomes 10:00:00 am
etc.
Now all your data falls into 1/2 hour fields to the second.
By doing that you can change your chart from grouping every hour to grouping for ecery second. Since this field converts to only displaying every 1/2 on the second you can get counts per 1/2 hour.
Make sense?
|
Posted By: guan
Date Posted: 11 Aug 2009 at 7:17pm
|
woohoo! it worked! ahah thanks again! crystal report is getting more fun. im going to try to learn ms report from ms sql 2008 too |
Posted By: guan
Date Posted: 12 Aug 2009 at 1:51am
|
today i tried doing monthly instead of daily it worked however there was some slight error it manage to generate a report chart for july however for august it had some trouble. for august i had entered around 1 week worth of data. there was some grouping issues, the dates for august became i.e. 8/4/09, 8/5/09, 8/6/09 hence it generated 3 line chart for 3 months. i checked at group expert, DAte Group, then i clicked browse data. all the dates for august was 8/4/09, 8/5/09, 8/6/09 instead of 4/8/09, 5/8/09, 6/8/09. july however was alright. any ideas? i have already checked the format settings and everything was okay. dammmittttt |
Posted By: DBlank
Date Posted: 12 Aug 2009 at 7:54am
|
Your date({Qlog1.Call Date}) formula is inverting Month and Day fields.
Change it to this mess...(copy and paste it)
date(
tonumber(right({Qlog1.Call Date} mailto:%7b@DATE%7d,4 - ,4 )) , tonumber(mid({Qlog1.Call Date} mailto:%7b@DATE%7d,instr%28%7b@DATE%7d,/%29+1 - ,instr({Qlog1.Call Date},"/")+1 , (if mid({Qlog1.Call Date} mailto:%7b@DATE%7d,instr%28%7b@DATE%7d,/%29+3,1%29=/ - ,instr({Qlog1.Call Date},"/")+3,1)="/ " then 2 else 1) )) , tonumber(left({Qlog1.Call Date} mailto:%7b@DATE%7d,instr%28%7b@DATE%7d,/%29-1 - ,instr({Qlog1.Call Date},"/")-1 )) ) |
Posted By: guan
Date Posted: 12 Aug 2009 at 4:50pm
|
once again, u r a life saver thank you so much =) now i wat i need to do is study all the formulas that u have taught me, i wanna understand how it works alright i will start now thanks again! |