I am attempting to solve/plan a strategy for a business using SharePoint 2010. They have data from multiple external databases. I would like to display this data in a report based on the current logged in user. These users are clients that would like to view current reports in a friendly UI. My initial thought is to use and ECT and create an association between external data sources. Then use filters to filter the unwanted data. Is this the right approach to this problem? Is this possible using SPD? What are the best webparts(CQWP, DVWP, ect...) for this?Any suggestion will be appreciated.
When using BCS, the best and the most secure way to filter data based on user is to use UserContext filter in BCS model.
(quote from MSDN)
To implement this, you have to export BCS model from SharePoint Designer, edit it by hand to include the UserContext filter, and then import back to SharePoint through BCS Service Application on Central Administration site.
Detailed article explaining this approach step-by-step: