Need Help To Add Further formulae on an already crowded chart!

[AnnetteB1305]

Hello,
I have already posted re this chart but this is a new issue. I have a report which now has 5 sub reports the sub reports are graphs and crosstabs based on various data. It is the final section which is giving me issues. This final section of the report is to show the number of calls that were still open by day in the last 8 days. These are then split into priority and also calls that are placed on hold. So the graph currently shows a count of the calls for High,Med and Low priority calls and then a bar which shows the total of these three priorities per day. Showing 4 risers per day.
The cross tab which is on a separate sub report is actually a manual cross tab as there are so many formulae, it was the only way I could get it to work. On the cross tab there is as the graph but there is also a row which shows the calls that were on hold in that day and I need this on the graph also.

The sub report that the graph is formed from is on a command which is as follows:-

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


The graph is then made up as follows:-

Advanced Bar chart which I have had to swap series and groups and colour by series
On change of command.all and then I have show values as follows
Distinct Count of @count1
Distinct Count of @count2
Distinct Count of @count3
Distinct Count of @count4
Distinct Count of @count5
Distinct Count of @count6
Distinct Count of @count7
Distinct Count of @count8

The count formula are as follows:-

If {@logdate}<=dateadd("d",-8,CurrentDate)and (isnull({Command.closedate}) or {@Closedate}>dateadd("d",-8,CurrentDate))
then {Command.callref} else tonumber ({@@null})

and so on up to Day before current date

The crosstab subreport sql query is as follows:-
SELECT `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'
ORDER BY `opencall1`.`priority`


The onhold formulae on my cross tab are as follows:-
//@OnHold1
If date({Command.logdate})<currentdate and ({@Closedate}>dateadd("d",-8,currentdate) or {Command.closedate}='')
and{Command.updatetxt} like "*placed on hold*"
then {Command.callref} else tonumber ({@@null})

and so on again till @onhold8 which is the date before the current date.

Obviously I have just tried to add them to the report but it does not make any sense.

So is this possible to add to my chart or am I trying to blow crystal up or maybe there is a simpler formula and then it would make it possible. I have been known to try and make things more complicated than they are or need to be!!

Any help would be greatly appreciated but if you think this is impossible, please let me know

Thanks in advance

Annette

Final section of report attached

 

[lbass]

If this is intended as a fifth bar per day, then I think you would have to add another unioned query (like the one you did for 'All'), but with 'Hold' instead, and using the criteria that makes it a "hold" in the where clause--to distinguish it clearly from the other data.

-LB

 

[AnnetteB1305]

So would that be 4 queries or just 3

 

[AnnetteB1305]

Also do I need to put the date criteria in the where clause too, do you think, or just the criteria:-

where {Command.updatetxt} like "*placed on hold*

 

[AnnetteB1305]

and how do I deal with the order by....does this new union query need to be in the middle of the 2 I currently have? I do what the on hold to appear before the All column if possible....

Thanks again...you are truly my hero!!!

 

[lbass]

This would be 3 queries, the order by must be at the end, and the the order of the queries is irrelevant to the chart order. I can't really answer your question about the criteria for certail, as I don't follow how hold records differ from the others, but I would think you would still want to limit to the same date criteria and yes, you would add the hold criteria. This assumes that these records are not present in the data under the high, medium, and low priorities of your earlier queries.

-LB

 

[AnnetteB1305]

I have used the following as my command however the 'Hold' is not showing as an available field. Have I done something wrong?

Thanks again



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)
union all
Select 'Hold',`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 `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)

ORDER BY 1

 

[AnnetteB1305]

Actually I have just slightly changed it as noticed the formula: `updatedb1`.`updatetxt` like "*placed on hold*" should be `updatedb1`.`updatetxt` like "%placed on hold%" and I now have 5 bars per day but the numbers aren't right?

As I need the on hold data to split per day do I need to put the on hold formula in each of my @count1 formula too?

How I determine a call has been on hold is from the updatetxt field

 

[lbass]

What numbers are not right? Just the hold numbers? In what way are they incorrect? How is a "hold" call associated with a particular date? Are you specifying the date it is placed on hold? Your date criteria need to relate to the date of the hold status assignment somehow.

-LB

 

[AnnetteB1305]

Well the date criteria for the initial on hold formula and the date criteria for the count formula that is on my graph are the same the only difference is the placed on hold in the updatetxt field which is in my command now.

It is only the onhold figures that differ from my manual crosstab and the graph. Not all of them differ but for example

I have 26 on my crosstab and there is 40 on the graph. I am just going to check the numbers in query analyser, maybe my formula on my crosstab is incorrect!

 

[AnnetteB1305]

It's ok I've sorted it my formula was incorrect on my report and I had to add extra criteria to my command.

Thanks again for your help. I salute you!

Annette

Final Command is as follows:-


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)
union all
Select 'Hold',`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 `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)
ORDER BY 1