Maintaining a Frequently Changing Record Select Expert

[random621]

I am using Crystal XI against MySQL database.

I have multiple reports that show daily call statistics from our recorded calls database.

There is a field in the database ('Agentid') that shows all extensions involved in a phone call. The data from this field can look like the following:
120 -> Extension 120 was the only part of the call
120:125 -> 120 transferred call to 125
120:124:130 -> 120 transfer to 124 transfer to 130

I run different phone reports on daily/monthly basis based on Division of the company. However there is no division field or even specific extension extension field in the recorded call database.

To get a report for a particular division I end up running a select expert that says:

({Agentid} like "*130" or {Agentid} like "*131" or {Agentid} like "*132")

My problem is I always forget to go back and change the select expert when the members of the division change.

I've been playing around with setting up a simple access table with three fields "Name", "Extension", "Division" so that in the phone report I could just state Division = "XYZ" and that would be that. But I can't link Extension ("121") to Agentid ("121" or "134:121").

Any suggestions are appreciated.

 

[CR85user]

Why not set up a number of parameters and use those in your selection criteria?

Setting these up with multiple values you could then leave your selection criteria alone and edit only the parameter values.

These could then be whacked in the repository so that multiple reports could use the same pre-defined parameters to state divisions.

It would still require manually updating which you won't get away from until your db holds that info.

Point the selection criteria at the newly defined parameter:

({Agentid} in {?Division})

Then set up each division with it's relevant numbers - This way you can simply select a division (or multiple divisions if preffered) at runtime.

'J

 

[synapsevampire]

If you always want different divisions, then you might use a paramter to prompt for the division.

I'd see to it that your dba is relocated to a division outside the company, that's just a silly, yet too common, poor relational design.

Now some solutions might include a large UNION ALL as in:

select '50' divid from table where agentid like '*130*'
UNION ALL
select '50' divid from table where agentid like '*131*'
UNION ALL
select '50' divid from table where agentid like '*132*'
...etc...

Or you might create a table which uses something like the transform function in Oracle to seperate the values within that string. I doubt that MySQL has that functionality as this point, still seems very primitive in many ways.

Anyway, I would address it by fixing the bad design permanently, and make sure that this sort of architecture isn't permitted.

-k