The following table is just one consultant's opinion, but I am asked frequently about the many pieces of the MS BI tool stack used for reporting purposes. In response, I made up this slide in Power Point, that I'll offer up here for your amazement or amusement. It is not "gospel truth", just my opinion and I am happy to hear yours.
You have to start with something when making a recommendation. What is "best" for you is totally dependent upon what you are trying to accomplish, and the resources you have to get there.
For report automation, I think of things in this fashion:
Self-Service BI capabilities are one of the fastest evolving tools in the MS BI tool stack. Without diving into what I call "Power to the [n]th Degree", this is how I categorize self-service BI offerings.
|Microsoft Business Intelligence||
SQL Server reporting services