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

 

[lbass]

What I said about distinctcounts of conditional formulas still holds true here. You need to use an explicit default of {@null}.

High, medium, and low of what? How do you define each of these?

What are the 8 formulas based on?

What do you mean by priority?

What problem have you run into when trying to chart?

-LB

 

[AnnetteB1305]

Hello,

Sorry High, medium and low are the priorities

The 8 formulas are if the logdate of the call is before or on that particular date and the call is not closed i.e. the relevant open support call of that priority.

Sorry did mean to say had to swap series and groups and color by series and not group

But the total calls are total distinct count and not by priority and I don't know how to include the total on the graph.

i.e. see attached.

So the total would be all of the total of the 3 columns for each day. So I need 4 columns for each day.

Sorry if not explaining this correctly.

I'm fine with the current graphs, it's adding the totals that I need help with.

Thanks and apologies for not explaining correctly.

Annette

 

[lbass]

Have you limited the report to these 8 days in your selection formula? If so, you could maybe just use:

if {opencall1.closedate}='' or
{@Closedate}>{@logdate} then
{opencall1.callref} else
tonumber({@null})

Please understand that you must use the null or your numbers will be off by one except when all records meet your conditional criteria.

-LB

 

[AnnetteB1305]

Hello,

I have filtered the report by the 8 days and I have used the @null now in my formula. I have the graph split by the 8 days which is what I require. So I need 4 bar risers per day.

I do appreciate your help.

Thanks,

Annette

 

[lbass]

Please explain whether the formula I suggested worked for the total. If not, try to explain in what way it was off.

-LB

 

[AnnetteB1305]

No it didn't sorry as it just threw all the chart off. I lost all the charts for my days and it gave an overall total.
I have found on the chart expert, data tab, that you can order the priority to a specified order and then I have specified High, Med, Low and then a new group of all 3 of these to be called Total Live Calls however this is not appearing on the chart although if I put it at the top of the order it does and is correct but none of the priorities show up on their own???

 

[lbass]

I thought you had an 'on change of' field for priority and then were adding the count formulas as summary fields. This would be an additional summary. Please explain exactly how you set up the formulas in the chart expert.

-LB

 

[AnnetteB1305]

Yes that's right but I have swapped series and groups and coloured by series

 

[lbass]

Okay, now I see. Replace your tables with a command like this:

select opencall1.priority,opencall1.closedate,opencall1.callref
from opencall1
where to_date(opencall1.closedate) > currdate-9
union all
select 'All',opencall1.closedate,opencall1.callref
from opencall1
where to_date(opencall1.closedate) > currdate-9

You need to correct this based on your database--not sure what it is, what the punctuation should be or the functions for date conversion and currentdate.

Once you have this working, priority will have an all group, and your formulas (now referencing the command fields) will work--as long as your build in the nulls. I tested this.

-LB

 

[AnnetteB1305]

Sorry maybe I'm being thick but how will this include the total calls on my graph? Also the current set up is with MYSQL.
My formulas work as they are, it's just I require total calls as well.

Sorry if I'm being ditsy or we're just not understanding each other.

Thanks,

Annette

 

[lbass]

This adds another factor to priority, so that now it will show all, high, medium, or low. This is what you need so that each of your day formulas is broken out by priority, including an "All" column.

This is the only way I know of to get the all column in the chart.

Do you see the "Add command" option above the table list in the database expert? That is where you need to enter the query. An easy way to do this is to open a new report, then go into your old one->database->show SQL query, and copy the query and paste it into the command area of your new report. Then you add the union all. Are you familiar with how to do that? Each field must have a corresponding field in the unioned query. You would just replace the priority field with 'All'.

-LB

 

[AnnetteB1305]

Oh I see, sorry thank you...I'll give it a go and let you know how I get on. Yes I know how to Add commands etc I just wasn't getting the query.
Thanks will let you know...probably tomorrow now as had enough of Crystal for one day!!!

Thanks again

Annette

 

[AnnetteB1305]

Hello,

I'm getting issues with adding 'All' to the query as it's saying unknown column in field list. This is my query can I ask for some assistance again.

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'
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'
ORDER BY `opencall1`.`priority`


Also can I add the command and join my tables to this command as I've done so much work on the report I don't really want to start this whole report again.
Thanks in advance

Annette

 

[lbass]

You should be using single quotes around 'All', not the "`" mark (I can't recall its name). You also must have the fields in the select clause on each side of the union all in the same order, as in:

SELECT `opencall1`.`priority`, `opencall1`.`callref`, `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` is null or
`opencall1`.`closedate` > dateadd('d',-8,{fn CURDATE()})
)
union all
SELECT 'All',`opencall1`.`callref`,`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` is null or
`opencall1`.`closedate` > dateadd('d',-8,{fn CURDATE()})
)
ORDER BY 1

You should NOT join the command to your existing tables, as the link will process locally and slow the report. Instead, use the add command in a subreport and add the chart there.

You also need to limit the days in the command to the last 8 days in the where clause. The above might work for that. Note that the Order by clause should show the ordinal position of the field you want to sort by, and can only be added at the end of the last union query (as you did).

-LB

 

[AnnetteB1305]

I can't use a subreport as this is already a subreport in another report.

I may join it swap all the fields and then take out my database tables after.

I also can't limit the calls to only the last 8 days as there may be calls that were raised 4 months ago but still open and therefore need to be included in my report.

 

[lbass]

Add it as a separate subreport to the other report.

As to the open calls--you are selecting open calls or those closed within the last 8 days--as shown in the where clause I added.

-LB

 

[AnnetteB1305]

Thanks, it was as easy to change everything. Also the closedate field is not a date field so I can't do this either so I have done this in the record selection but can't do it in the command as couldn't get it to work. Still waiting for it to preview to see if it's worked. It does appear slow but it was slow anyway.

 

[AnnetteB1305]

no not working I'm only getting one bar for each day which IS the total calls but not getting the each priority that I was getting before

 

[lbass]

I don't know what you did, so can't make a recommendation. I did test my solution and it worked--four bars per day.

Try the following with date conversion:

SELECT `opencall1`.`priority`, `opencall1`.`callref`, `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` is null or
cdate(`opencall1`.`closedate`) > dateadd('d',-8,{fn CURDATE()})
)
union all
SELECT 'All',`opencall1`.`callref`,`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` is null or
cdate(`opencall1`.`closedate`) > dateadd('d',-8,{fn CURDATE()})
)
ORDER BY 1

Also, just so that you can see you can do this, try it in new report.

-LB