What is the best way to assign resources to projects. We are having a issue where a resources can be assigned in multiple projects simultaneously. making the executive dash board look way off.
A simple answer to the problem is to use an enterprise project planning system. Several products allow for the creation of central resource pools (including people, places, and objects). Then as a resource is allocated to a task the time on task should be visible to all users of the system. This then allows the next requester of the resource to either select a different resource or negotiate with the person using the resource first.
There is a significant caveat to this basic use of an integrated project planning system, and that is discipline is required to ensure that planners are not allowed to overload resources beyond an agreed percentage. Sometimes that overload percentage has to be zero, such as for physical resources (you cannot hold two meetings in the same conference room at the same time). However, your organization may have a policy allowing individuals a limited amount of overtime per day. That would mean you can overload the person-resource.
If you cannot overload a resource, then you will need to do resource leveling (this is not a wise thing to do automatically even though some tools allow it since it can give unpredictable results). To resource level you need to fill each next available open hours the resource has with the project need it until the demand is met. Unfortunately this frequently pushes the end date of the project out to the right an unacceptable amount. That may be the indicator that you need more temporary or permanent resources.
I hope this helps.
It is possible to assign resources to multiple projects using a desktop tool
If you are double booking some resources, it is more than likely other resources are sitting idle.
If your team is not too large a good supervisor may be able to manually assess workload and resolve idle time as well as contentions.
If you have a large pool, and if the assignments are for long term, one option is to assign resources full time to specific projects. And make the project managers responsible for keeping their resources well utilized. You need to set up project costing such a way they are motivated to offer under-utilized resources to other projects.
If you have a large pool and the assignments are for short term, as @Polymath said you need an enterprise project planning system. But these things are expensive.
A lower cost option is to use a dektop project management tool that supports resource pools. Here is an article on sharing a Resource Pool across multiple projects in Microsoft Project. The open source ProjectLibre team says they are working on it.