Command Issue

[AnnetteB1305]

Hello,

I have had a request to add a further bar to a graph which is ran as a sub report on a main report. This sub report is ran on a command as the graph is rather complicated. The graph shows a count of calls by priority and then a count of on hold calls and it is now required to show open thirdparty calls as a bar on the graph.
I have tested in my mysql query analyser the query to add to the command and all works ok and there are entries that fit the criteria, however when I add the mysql statement to my command in crystal it does not show any bar and states zero in the group details where I show a count of the relevant group in a manual cross tab.

The command that I am using is as follows (this is including the new addition to the command):-


SELECT `opencall1`.`priority`,`opencall1`.`callref`, `opencall1`.`priority`, `opencall1`.`logdate`, `opencall1`.`closedate`, `mdis_status1`.`status_description`, `opencall1`.`cust_id`, `opencall1`.`suppgroup`, `updatedb1`.`updatetxt`,`company1`.`custom_f`,`company1`.`companyname`
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` INNER JOIN `swdata`.`company` `company1` ON `opencall1`.`companyname`=`company1`.`companyname`
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`,`company1`.`custom_f`,`company1`.`companyname`
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` INNER JOIN `swdata`.`company` `company1` ON `opencall1`.`companyname`=`company1`.`companyname`
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 'Hold',`opencall1`.`callref`, `opencall1`.`priority`, `opencall1`.`logdate`, `opencall1`.`closedate`, `mdis_status1`.`status_description`, `opencall1`.`cust_id`, `opencall1`.`suppgroup`, `updatedb1`.`updatetxt`,`company1`.`custom_f`,`company1`.`companyname`
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` INNER JOIN `swdata`.`company` `company1` ON `opencall1`.`companyname`=`company1`.`companyname`
WHERE `opencall1`.`priority`<>'NL-MDIS SALES OFF SEND' and `updatedb1`.`updatetxt` like "%placed on hold%"
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)
and
(concat(substring(`updatedb1`.`updatetime`,7,4),'-',substring(`updatedb1`.`updatetime`,4,2),'-',substring(`updatedb1`.`updatetime`,1,2),' ',
substring(`updatedb1`.`updatetime`,12,2),':',substring(`updatedb1`.`updatetime`,15,2),':',substring(`updatedb1`.`updatetime`,18,2))
>date_format(date_add(curdate(),interval -8 day),'%Y-%m-%d %H:%i:%S')
=1)
union all
Select 'THIRDPARTYOPEN',`opencall1`.`callref`, `opencall1`.`priority`, `opencall1`.`logdate`, `opencall1`.`closedate`,
`mdis_status1`.`status_description`, `opencall1`.`cust_id`, `opencall1`.`suppgroup`, `updatedb1`.`updatetxt`,`company1`.`custom_f`,
`company1`.`companyname`
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` INNER JOIN `swdata`.`company` `company1` ON
`opencall1`.`companyname`=`company1`.`companyname`
WHERE `opencall1`.`priority`<>'NL-MDIS SALES OFF SEND' and (`opencall1`.`suppgroup` = "_THIRDPARTY" or `updatedb1`.`groupid` = "_THIRDPARTY"
or left(`updatedb1`.`updatetxt`,50) like "Call assigned to third party contract %" ) 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)
and
(concat(substring(`updatedb1`.`updatetime`,7,4),'-',substring(`updatedb1`.`updatetime`,4,2),'-',substring(`updatedb1`.`updatetime`,1,2),' ',
substring(`updatedb1`.`updatetime`,12,2),':',substring(`updatedb1`.`updatetime`,15,2),':',substring(`updatedb1`.`updatetime`,18,2))
>date_format(date_add(curdate(),interval -8 day),'%Y-%m-%d %H:%i:%S')
=1)
ORDER BY 1

Can anyone tell me where I am going wrong?

The data is mysql and I am using Crystal version 11

Thanks,

Annette

 

[AnnetteB1305]

It's ok I realised someone had set the date incorrectly on the report so ignore this post my report is fine

Thanks,

Annette