We had a similar requirement, we have global customers with sites all over the place. In SL, we used a distinct customer id for each site. And we had dumb customer id's, they were just the first few letters of the customer name followed by a number. So if we wanted to report on all of the "Acme" customers that we sold stuff too, it was a chore.
What we did, we purchased KeyChange, which is a tool that allows you to change certain key fields, customer and vendor ids are a couple that it will change.
Then we created a "template" for creating customer id's (vendor id's, employee id's etc) that were smart.
The first position was what type of entity was it (customer, vendor, overhead, employee, etc). The second position was the industry (widgets, whirlygigs, etc) and we used a single place holder, then it was the holding company or the parent company (2 character placeholder), the next was the geographic location (North America, South America, Europe, etc.), we used a single character place holder for this, after that it was more discrete geography, state or country, we used the 2 character codes used by the IRS for that, then the last three positions were a sequential number. So now we had 10 character entity id's that we could report on.
We built a cross reference of old ids to new ids, plugged it into KeyChange and away we went.
Using wildcards, we could now see what we were selling to a single global entity, or that same entity in a discrete geographic location, or just on a single site. Or we could broaden our search and look at everything we sold to those in the "widget" industry.
We did have one hiccup...after creating the template, we had users create bad customer ids, they would get the placeholders in the wrong place, or create incorrect parings (putting Texas in the Europe). So I went back in and created validation tables, that made it impossible for the users to create bad entity ids. I had to KeyChange the bad ids before I could turn on the validation though.
A bit of front end work, but now reporting is much easier.
