I need to select particular records from a diary table

[AnnetteB1305]

Hello,

My report is a services report which reports on various information in the chosen month and compared to the previous month.

My table is a helpdesk call diary table. It creates an entry in this table every time the call is updated/escalated etc. In this table is a groupid field which holds information as to which group of people handled that update. My problem is I want to select calls that have only ever been updated by 2 groups uniquely and not any other groups. However calls may have been updated by these groups and then escalated to other groups. I don't want to show these calls. So basically I want calls that have only ever been handled by 1st and 2nd line and not escalated to any other department. If I select where groupid= then this would include calls that have also been escalated. Any ideas on how I could do this...do I need a subreport?

 

[lbass]

Create formulas like this:

//{@AorB}:
if {table.groupid} in ["A","B"] then 1

//{@NotAorB}:
if not({table.groupid} in ["A","B"]) then 1

Then go to report->selection formula->GROUP and enter:

sum({@AorB},{table.callID}) > 0 and
distinctcount({@AorB},{table.callID}) = 2 and
sum({@NotAorB},{table.callID}) = 0

This would return calls where BOTH A and B were involved, but no other groups. If you mean A OR B, but no others, you can just remove the distinctcount line.

-LB

 

[AnnetteB1305]

I'll give that a try....I do need A or B or A and B so I take it then the formula would need to be :-

sum({@AorB},{table.callID}) > 0 or
distinctcount({@AorB},{table.callID}) = 2 and
sum({@NotAorB},{table.callID}) = 0

One thing though I need to select this in for a formula to count rather than a group as I have to report on all other calls too so would this part be better off in a sub report do you think?

Thanks,

Annette

 

[lbass]

No, for A OR B, use:

sum({@AorB},{table.callID}) > 0 [red]and[/red]
sum({@NotAorB},{table.callID}) = 0

If you just need these criteria for a count, then remove the formula from the group select area and instead, use it in a running total that does a distinctcount of call ID, evaluates using a formula (the one above), reset (if you want results at a group level) or Never (if at the report level.

-LB

 

[AnnetteB1305]

Sorry think I'm getting confused again so I need calls that have only ever been in group A or Group A and B or Group B but that have never been in any other groups so would this be:-

sum({@AorB},{table.callID}) > 0 and
sum({@NotAorB},{table.callID}) = 0

 

[lbass]

Yes, as a formula in the evaluation section of the running total.

-LB

 

[AnnetteB1305]

Hi...me again

Am trying to sort this, have got the above formulas in my report and have now created a formula for my count but I can't summarise on the formula

I have created the @AorB and @NotAorB and then created @HDUnique which is the:-
sum({@AorB},{opencall1.callref}) > 0 and
sum({@NotAorB},{opencall1.callref}) = 0

and this gives me true or false

and then as I have to do this by current and previous month have created the following formula to count on

@HDUniqueCurr
If month({@Logdate})=month({?Month}) and {@HDunique} =TRUE then {opencall1.callref} else tonumber({@null})

@HDUniquePrev
If month({@Logdate})=month(dateadd("m",-1,{?Month})) and {@HDunique} =TRUE then {opencall1.callref} else tonumber({@null})

I also tried using then 1 to sum this however again won't let me summarise, I am guessing there are too many formulae involved but I can't see how else I can do this with everything else that is in my report.

Thanks for any help

Annette

 

[lbass]

Instead of using conditional formulas, use one running total for each month, and evaluate using a formula like this:

month({@Logdate})= month({?MonthDate}) and
sum({@AorB},{table.callID}) > 0 and
sum({@NotAorB},{table.callID}) = 0

For the other one, use:

month({@Logdate})= month(dateadd("m",-1,{?MonthDate})) and
sum({@AorB},{table.callID}) > 0 and
sum({@NotAorB},{table.callID}) = 0

If Logdate can cover multiple years, you need to build the year into the formulas, too.

-LB

 

[AnnetteB1305]

Sorry I get what you are saying now....I don't know why I always forget about running totals and it has now clicked what you were saying....

thanks again...I'll give this a whirl

Annette

 

[AnnetteB1305]

I'm just getting 0 for the selected month and 1 for the previous month and this is not correct. I am using this in the report header so haven't set a reset. I'll keep looking at it and let you know

Thanks,

Annette

 

[lbass]

Running totals cannot be placed in the report header--they haven't accumulated yet. Place them in the report footer.

-LB

 

[AnnetteB1305]

Oh course well there's a problem as I can't put it in the report header as need this at the top of the report that I have and have a number of other information in details etc that I need to put underneath this, so I take it I would have to put it in a sub report?

 

[lbass]

Yes.

-LB