Crosstab Formulas

[pork1977]

Hi all,

I have a problem with a crosstab report, the below show's an example of how it should look.

Dec 06 Jan 07
ADASTRA 7 4
DEVELOPMENT 1 4
HELPDESK 22 34
IT SUPPORT 3 1
LOCAL IT 1 0

The numbers relate to the number of calls that are assigned to each of the groups in a given month. So, for example there were 22 calls assigned to the Helpdesk in Dec 06 and 34 assigned in Jan 07.

Now, it's not actually showing what I've mentioned above but instead showing the number of calls that are currently OPEN. This is because of the 'select EXPERT' part on the main report screen has been set to display only OPEN calls.

When a call gets assigned to a GROUP, there becomes an event on that call that will read "Call forwarded to Group Helpdesk" for example and is recorded in the {Incident_Details.Description} table.

My question is how to write a simple formula and put into the 'summarized fields' section of the crosstab that displays the assigned to calls and not a count of calls that are open still. At the moment, what I have in there is a count of {Incident.Incident #} where 'Incident #' is the reference number so it just counts what is open.

I hope the above makes sense, it's a bit tricky to explain.

Paul

 

[lbass]

Is the description field populated by a selection from a dropdown or is it a comment field, where the entry could vary? If the entry is standard, then you can write a formula like:

if {Incident_Details.Description} = "Call forwarded to Group Helpdesk" then 1

Then use this formula as a summary field (to be summed, not counted) in the crosstab.

If the entries can vary, you need to determine what word or words will always be present in the field and then use a formula like:

if instr({Incident_Details.Description}, "Group Helpdesk") > 0 then 1

Or possibly:

If {Incident_Details.Description} startswith "Call forwarded" then 1

-LB

 

[pork1977]

Thanks for your comment lbass, the description field is chosen from a list so you can't put what you want as this is the groups you can assign calls to so they always stay the same.

I'll try what you say tomorrow when back in the office and see if it works but previously I had tried the following...

If {Incident_Details.Description} = "Forwarded to Group Helpdesk" then {Incident.Incident #}

and...

{Incident_Details.Description} = "Forwarded to Group Helpdesk"

but to no avail. I hadn't tried putting the 1 on the end but will try and let you know, thanks for your help.

Regards,
Paul

 

[lbass]

If you used your first formula and then tried a count, it would only count the number of times the formula executed, not the number of times the field met your criterion. That's why you need to sum the formula I gave you, not use a count on it. If you need a distinct count of incident #'s for some reason, there is a slightly different approach.

-LB

 

[pork1977]

OK, so I tried the first formula you gave and it seems to come back with thousands of records, infact if I let it go Crystal normally crashes out because so many!

I have put a sum on it but not sure why it's happening.
The description field isn't a field that can be browsed through Crystal, will that make a difference? I have used similiar formulas on it before but not in a crosstab before.

This is the problem I keep running into where it just comes back with so many records all the time, the links between tables are set correctly and I have three added in.

 

[pork1977]

OK, click on the link below to see a screenshot of what I done so far.

http://pork.andym.co.uk/

This is showing any call that has got the "Forwarded to Group HARMONI ITS" in it. It show's way more in the Harmoni ITS group obviously because that's where you'd expect to see them but it also shows other Groups where calls had been assigned to incorrectly.

My point is, that formula is only picking up on 1 type, how is it possible to get the results to show against their correct Groups? Do I have to have a formula in the ROW also?

 

[lbass]

Oh, now I see. What you need to do is create a formula:

mid({table.field},instr({table.field},"Forwarded to Group")+2)

Use this formula as your row field. Then use count (or distinctcount) of {Incident.Incident #} as your summary field.

-LB

 

[pork1977]

OK I will do that, would you mind explaining the formula and how it works?

 

[lbass]

This just picks out the group name from your field. The mid function is used to pick out a value that starts somewhere after the first character in a string. The second argument identifies the position at the beginning of the string to be extracted. The instr() function returns a number that identifies the starting position of the second argument ("Forwarded to Group") and I just realized that the number added to this position should be 20, NOT 2, so change the formula to:

mid({table.field},instr({table.field},"Forwarded to Group")+20)

This sort of thing is described well in the CR help section--a good resource.

-LB

 

[pork1977]

+20 gives me nothing! but +1 gives me the correct groups. Numbers don't tally up with what I expected though. I'll have a look through the CR help section to see if I can get to the bottom of it, thank you for your help.

Paul

 

[lbass]

Please show samples from your field and also post the exact formula you are using.

-LB

 

[pork1977]

OK, it does work correctly! I was just mis counting some numbers somewhere. Thanks alot for the help, the code will be useful for similiar reports.

Regards,
Paul