Chart Help Need to Add Totals 1

[AnnetteB1305]

Hello,

I have created a chart in a subreport of a report which is showing calls remaining at the end of the day and split by priority. However I also want to add the total calls on this graph but struggling.

The chart is set up to On change of priority show values of Distinct Counts of 8 formulas dependant on the day these are as follows:-

@count1: If {@logdate}<=dateadd("d",-8,CurrentDate)and (isnull({opencall1.closedate}) or {@Closedate}>dateadd("d",-8,CurrentDate))
then {opencall1.callref}

@count2: If {@logdate}<=(DateAdd("d",-7,CurrentDate))and ({opencall1.closedate}='' or
{@Closedate}>(DateAdd("d",-7,CurrentDate)))then {opencall1.callref}

and so on until it's current date minus 1 day.

The @Closedate formula is just as follows:
If {opencall1.closedate}<>'' then Date({opencall1.closedate}) as the closedate is not a date field.

How can I include the total calls for each day i.e. High, Med and Low for all 8 days.

I have also made a crosstab for this report but I had to do a manual crosstab to incorporate all the info.

Thanks in advance.

Annette

 

[AnnetteB1305]

It's saying error in syntax near cdate(`opencall1`.`closedate`) > dateadd('d',-8,{fn CURDATE()}) so I tried changing the part of the query to date(`opencall1`.`closedate`) > dateadd('d',-8,currentdate) but this isn't working either....sorry to be a pain but pulling my hair out with this!

 

[lbass]

What kind of database are you working with?

You can help by checking in the field explorer->SQL expression and seeing what function works there to convert a string date to an actual date.

-LB

 

[AnnetteB1305]

Hi,

It's MYSQL and not sure where you mean re sql expressions...are we talking in crystal. I don't have a sql expressions in field explorer?

 

[lbass]

Open a new report to see if SQL expressions are listed in the field explorer. If you already have a command in a report, SQL expressions are removed as an option in the field explorer.

You can test ways to convert strings to date in the command also--it's just easier to isolate what works in the SQL expression editor.

Sorry--I forgot you mentioned MySQL earlier.

-LB

 

[AnnetteB1305]

Sorry yes see what you mean now. However I think either someone is trying to get at me or something is bizarre the attached are the only date functions I have, and I have tried to concatenate day, month and year and tried a load of the functions in the sql expression in a new report but the field is just blank constantly so nothing is working.

http://www.mediafire.com/?u2pdv37nzas82o5

 

[lbass]

That is the same set of functions as the list I get with Access
where cdate() successfully converted a string to a date.

How does your string date display? yyyy-dd-MM? Or?

-LB

 

[AnnetteB1305]

dd-mm-yyyy and time.

I've tried cdate in the sql expression along with isdate, is_date, todate, to_date, date, texttodate, str_to_date and about 100 others, nothing is working. Bring back sql!

 

[lbass]

You can try the convert function to convert it to a date.

Or go ahead and try linking the command to the other tables on the ref field I think. This will probably cause row inflation and also slow your report, but you could then just limit records in the record selection formula.

-LB

 

[AnnetteB1305]

Can't get convert to work, am trying a few things in the command but joining the tables to the command has slowed it to a virtual stop! I do appreciate your help. I shall keep trying!

 

[AnnetteB1305]

Still not getting anywhere with this, if anyone else can offer anything I would truly appreciate it

 

[lbass]

I think you should post in the MySQL forum and find out how to convert the string to a date. I really can't help you with that. Once you have that, my suggestion should work, assuming dateadd() also works with MySQL.

-LB

 

[AnnetteB1305]

Hi,

Yes just getting sorted with the str_to_date selection and yes tried dateadd() that does work, sorry was in desperation mode....hopefully getting there and don't want to tempt fate but may get this finalised today. I do appreciate your help.

Thanks again

Annette

 

[AnnetteB1305]

Don't worry it's me, I've got it working.

Thank you so much for you help....you are a star!!!

 

[lbass]

Can you post the command that did work for you? Thanks.

-LB

 

[AnnetteB1305]

SELECT `opencall1`.`priority`,`opencall1`.`callref`, `opencall1`.`priority`, `opencall1`.`logdate`, `opencall1`.`closedate`, `mdis_status1`.`status_description`, `opencall1`.`cust_id`, `opencall1`.`suppgroup`, `updatedb1`.`updatetxt`
FROM (`swdata`.`opencall` `opencall1` INNER JOIN `swdata`.`updatedb` `updatedb1` ON `opencall1`.`callref`=`updatedb1`.`callref`) INNER JOIN `swdata`.`mdis_status` `mdis_status1` ON `opencall1`.`status`=`mdis_status1`.`status_code`
WHERE `opencall1`.`priority`<>'NL-MDIS SALES OFF SEND'
and (`opencall1`.`closedate`='' or
(concat(substring(`opencall1`.`closedate`,7,4),'-',substring(`opencall1`.`closedate`,4,2),'-',substring(`opencall1`.`closedate`,1,2),' ',
substring(`opencall1`.`closedate`,12,2),':',substring(`opencall1`.`closedate`,15,2),':',substring(`opencall1`.`closedate`,18,2))
>date_format(date_add(curdate(),interval -8 day),'%Y-%m-%d %H:%i:%S')
) =1)
union all
SELECT 'All',`opencall1`.`callref`, `opencall1`.`priority`, `opencall1`.`logdate`, `opencall1`.`closedate`, `mdis_status1`.`status_description`, `opencall1`.`cust_id`, `opencall1`.`suppgroup`, `updatedb1`.`updatetxt`
FROM (`swdata`.`opencall` `opencall1` INNER JOIN `swdata`.`updatedb` `updatedb1` ON `opencall1`.`callref`=`updatedb1`.`callref`) INNER JOIN `swdata`.`mdis_status` `mdis_status1` ON `opencall1`.`status`=`mdis_status1`.`status_code`
WHERE `opencall1`.`priority`<>'NL-MDIS SALES OFF SEND'
and (`opencall1`.`closedate`='' or
(concat(substring(`opencall1`.`closedate`,7,4),'-',substring(`opencall1`.`closedate`,4,2),'-',substring(`opencall1`.`closedate`,1,2),' ',
substring(`opencall1`.`closedate`,12,2),':',substring(`opencall1`.`closedate`,15,2),':',substring(`opencall1`.`closedate`,18,2))
>date_format(date_add(curdate(),interval -8 day),'%Y-%m-%d %H:%i:%S')
) =1)
ORDER BY 1

 

[lbass]

Thank you.

-LB