We help IT Professionals succeed at work.

East to use reporting systems that attach to SQL databases

INHOUSERES
INHOUSERES used Ask the Experts™
on
Hi Experts,

We have a very detailed database which imports data from clients via email and displays these records nicely to a telephone research unit (not cold calls - Market research).

The survey system is all part of the same database and until now, we have built all our reporting systems in access using excel as a helping hand.
This has been working fine till now, but we're getting more and more requests for reporting that we just can't keep up with, and with the amount of clients we have, we're struggling to keep track of all the changes and anomalies to our reporting systems.

This is creating massive problems for us.

We are VERY happy with the data side of things, but we are desperate to find something out there that can create reports on our data without much work for us (we don't want to change table structures and queries if we don't have to).

Also, a nice to have, would be for clients to access their reports themselves, or to perhaps update data in our systems.

Does anyone know of any reporting systems out there suited to a situation like this?

If it helps, our "answers" table, has a row for each answer supplied to each question
e.g:

SN - Q - Score
123 - 1 - 5
123 - 2 - 10
123 - 3 - 2
123 - 4 - 6

etc etc.

Really hope someone can help.
Comment
Watch Question

Do more with

Expert Office
EXPERT OFFICE® is a registered trademark of EXPERTS EXCHANGE®
Jeffrey CoachmanMIS Liason
Most Valuable Expert 2012
Commented:
Your question is a bit vague...

"we're struggling to keep track of all the changes and anomalies to our reporting systems."
Can you be specific, (with a specific example) perhaps there is a simpler way...

"but we are desperate to find something out there that can create reports on our data without much work for us (we don't want to change table structures and queries if we don't have to).>
If your data is structured properly (Normalized, ...etc) you should never have to change your table structure...

<Also, a nice to have, would be for clients to access their reports themselves, or to perhaps update data in our systems.>
This is a totally separate issue, and really should be dealt with in a separate, new Question...


<SN - Q - Score
123 - 1 - 5
123 - 2 - 10
123 - 3 - 2
123 - 4 - 6
>
...It is not clear from this, what you are asking for here...


In other words, instead of saying that you have all of these issue.

Just post a sample DB and clearly state what you are having issues with specifically.

;-)

JeffCoachman





Top Expert 2010
Commented:
INHOUSERES,

In my experience, Access makes for a reasonably easy and inexpensive reporting front-end to a SQL Server data store.

Perhaps what you need to do is give these folks a 'read-only' SQL Server login, install Access for them, and send them to a couple of Access training classes.

Doing it that way you will always have problems with version control/"many versions of the truth", but that will always be the case with self-service.  It's all about balancing your workload against the need for control.

Patrick
Mike McCrackenSenior Consultant
Most Valuable Expert 2011
Top Expert 2013
Commented:
There are other reporting tools available like Crystal Reports.  However you will still have the same basic issue since you have to build the reports for the clients and maintain them.

There is also a learning curve and training required which could put you further behind.

Most reporting systems are
1. Similar to Crystal Reports - you or someone builds the reports just like you do in Access
2.  Highly specialized to  specific database.  If your database is a commercial system like say QuickBooks is, then there may be reports available.  However I think yours is a custom application and will require you or someone to build reports.

mlmcc

Author

Commented:
Hi All,

Ignore the stress levels. Being very busy in this economy is only a good thing!
Also... You all probably guessed, but it should say "Easy", not "East".

We currently have processes that compile figures in Access then export them to excel for formatting.
It sounds simple but it's all fully automated and there is a lot of code involved in this.
All reports are: specify a few filters, press a button and then send the report off to the right people.

We want to be able to create this reports externally in another piece of software. I know of Crystal Reports, but I don't know what alternatives are out there and if they can easily just "clip on" to our MSSQL backend database.

It's mainly the add hoc reporting that we require.
When I do ad hoc figures I export the table I tried to describe, format the figures a little with weightings, then use pivot tables for averages.
There must be a way to do this easier, and automate, without code or slow queries.

Top Expert 2010
Commented:
Both Access and Excel can connect to a SQL Server data store to do ad hoc analysis.

If your users have Excel 2010, then they can use the free, and insanely excellent, PowerPivot add-in.  PowerPivot may not be just the greatest thing since sliced bread, but also even better than whatever was king of the hill before sliced bread came along.
Mike McCrackenSenior Consultant
Most Valuable Expert 2011
Top Expert 2013

Commented:
This question has been classified as abandoned and is closed as part of the Cleanup Program. See the recommendation for more details.