I would like to create a report designer that has the same functionality and ease of use as the Visual Studio IDE. The ability to drag controls onto a form, select various subsets of those controls, align left, evenly space, etc.
The only tools I have ever encountered are really bad. Crystal Reports, the MS Access report crapola, ComponentOne, etc. Is there a third party tool that does this right, or do I have to roll my own?
Um, sounds like you want to do ad-hoc reporting. I have never seen one of these done well outside of a COTS product like BusinessObjects Web Intelligence, which I am using right now on one of my projects.
http://www.businessobjects.com/product/catalog/web_intelligence/
I think you'll have to roll your own here.
The problem here is one of balance. Third party reporting tools have to be flexible, open and highly customizable to the whims of every potential user. As a result, these tools end being not usable to a lay person.
That said creating a visual report designer is not a trivial task. So my suggestion would be to distill the user's voice to find the minimal set of functionality (report elements, formatting, etc.) done first in a usable manner... may not be pretty the first time. And then iterate.
I'm working on a similar need and currently evaluating if MS Word with some custom add-ins would get the job done.
Some years ago I used Active Reports from a company called Data Dynamics. It was nice in that it allowed my users to create their own reports - and they loved not having to contact me to create some ad-hoc report. I am sure the product has evolved since 2001, might want to give it a look.
There is an SQL MS Reports Solution. It's called SSRS and you may have heard of it, but what you might not know it there is an OK end user targeted reporting tool. It works especially well if you are using datasets. The reports can be saved as files and run and/or built inside a VS project, within SQL SSRS, or using the free standing tool (i'd start here). They can also be run from a web control. They can export to excel and even PDF I think.
See SQL2008 Tool here - http://www.microsoft.com/downloads/details.aspx?FamilyID=9f783224-9871-4eea-b1d5-f3140a253db6&displaylang=en
More info here - http://en.wikipedia.org/wiki/SQL_Server_Reporting_Services
This is also available in SQL2005 also I believe.