Reporting solution

Hello everyone,

I'd like to ask you for your opinion on the most cost effective and least time consuming solution for business reporting.

I'm a financial controller for a smaller (90 employee) business to business sales organization operating in 2 states.  We use an industry specific Enterprise MySQL database based software.  Although the workflow as setup in the system is very much customized to our needs, the business intelligence and reporting side of the solution is significantly lacking (we'd like to create our own custom Income statement, Balance sheet, job costing, and host of other reports).  Having the developer of the software customize the reporting needs for us is very costly, time consuming, and frustrating, so we made the decision to design and deploy our own reports in house.

We don't have any developers on the staff. We have an IT with limited to no programming experience.  From our research we figured that we could either use Access 2013 to connect to our database and create the needed reports, which we'd love to make available to the management via web app reports (don't have SharePoint or just post them to our LAN and provide them with Access 2013 licenses) OR use Crystal reports to create and then deploy the reports (we'd need Crystal Server to deploy to users for self service web access).  We would love the web access for our exec team is on the go a lot.

We have some Access experience, none of which is in developing custom apps.  We have no Crystal reports experience.  However, we have been able to find a TeachUComp.com course lessons on both Crystal and Access.

What do you think would make most sense for our situation?  If we have totally missed the mark and there are other, more fitting solutions, what would those be?

Thank you so much for your comments and suggestions!
RobertCZAsked:
Who is Participating?
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

Gustav BrockCIOCommented:
This is also what Access was made for, so as you have some Access experience why not go ahead at zero cost except for the time.

You'll need the MySQL ODBC Connector and proper credentials to access the MySQL tables.
When ready, start with something very simple like a list of accounts.

Reports can be saved as PDF files which you can copy to a web server to view or download by your colleagues.

/gustav
0
Scott McDaniel (Microsoft Access MVP - EE MVE )Infotrakker SoftwareCommented:
I work with both platforms quite a bit, with more emphasis on Crystal recently.

Access is generally much easier to learn, and has a robust reporting platform. I don't know your exact requirements, but for the most part Access is more than capable of producing reports commonly used in most businesses. From the short list of report types you need, I'd think it would be more than enough to fulfill your needs.

Access can be difficult to share reports with users, of course. As gustav mentions, you can export the reports as PDF and put them on your internal company website, where users could open and view them, but that adds another level of complexity to the mix, and you'd almost certainly require some developer input on that.

Note that the new 2013 Access web apps ease some of that pain, but it also comes with other issues as well. You have to have a host to manage your web apps, for example. You could have that host in-house (i.e. you build and maintain a server for this) or you pay a company monthly to host your web app.

Crystal is more difficult to learn, but is definitely a more robust reporting engine and can do things Access cannot. It's also more costly in terms of the "server" component, I believe (at least the last time I looked into the server component), but it does provide a fairly complete environment for sharing reports and such. If you'll only be viewing reports, and not allowing all users to edit/customize them, then you really don't even need the server component - you can create a small .NET app that does nothing more than pull .rpt files from a central server location and present them in the form.
0
Ido MilletProfessor of MIS at Penn State Erie and Owner, Millet SoftwareCommented:
I believe Crystal is easier to learn and use that the Report Generator in MS Access. But since it is more powerful, there is simply more to learn if you wish to do advanced things with it.

Instead of taking a standard course, consider using individual training with a consultant who can work with you on your own database via a remote shared desktop session. For Crystal Reports, I would recommend Ken Hamady: http://kenhamady.com/protege.html

Ken can also advise you on inexpensive solutions to viewing/scheduling/web deployment of your reports. His web site provides reviews of 3rd-party Crystal Reports viewer, Schedulers, etc.
0
Big Business Goals? Which KPIs Will Help You

The most successful MSPs rely on metrics – known as key performance indicators (KPIs) – for making informed decisions that help their businesses thrive, rather than just survive. This eBook provides an overview of the most important KPIs used by top MSPs.

mlmccCommented:
I agree with most of the above.  Which is easier to learn is probably an individual opinion with some finding Access and others finding Crystal easier.

As Ido stated there are several alternatives to Crystal Server which are more cost effective.

Is your intent to have a small report development team or do you want the end users to be able to develop/modify reports?

Your answer to that will drive the cost of the solution since each report developer will need a license for the report development tool.

I have built reports in both and learned MS Access first.  I found the transition to Crystal easy since both use the same basic drag and drop onto a report template,

mlmcc
0
Jim HornMicrosoft SQL Server Developer, Architect, and AuthorCommented:
(Since the above experts nailed the Access and Crystal needs of this question, I'll come at this from a business angle)

This story is typical of many of my part-time Access clients:  Small company, a fair amount of operational and dashboard reporting needs.  They were either smart enough to frame this up for a developer to work, or tried to do it themselves but was too time-consuming and confusing.

Many that did have a developer work on it had the developer bolt on them, and are now trying to support a big pile of code the previous developer left them that they don't understand.

>Having the developer of the software customize the reporting needs for us is very costly, time consuming, and frustrating,
A typical story is a client that doesn't lay out requirements very well, and doesn't have a reference doc on how their business operates in a way that can be communicated to a developer easily.   Combine that with a developer that either isn't very good, or isn't very experienced, and the results can often be bad.

Many clients also have the mindset of 'we're small, so this shouldn't cost much', and are surprised when the price tag ends up being higher than they expected.
0
Jim HornMicrosoft SQL Server Developer, Architect, and AuthorCommented:
btw if your reporting needs are somwhat ad-hoc and controlled by one person, Excel Power Query / Power View is also in play as a solution, although this will likely require some T-SQL help.
0
RobertCZAuthor Commented:
Thank you everyone for your comments so far!

Let me add some more detail:

Gustav and mlmcc: Our ultimate desire would be creating reports that the end users could actually run themselves by picking from a list of parameters (Dates, departments, entities, ... all depending on the report).  Once we've tested and verified that the report functions correctly, users then go and run as they need.

We have successfully connected our Access 2013 to the database using an ODBC connector.  Our challenge has been being able to format our reports in Access the way we want them to appear.  We've been forced to export to Excel and finish formatting there.

Also, not all data for our reporting comes from our Enterprise database system.  We also need to utilize data that comes from ADP's time and attendance module as a CSV (Job costing data).

Jimhorn: What you described is all too familiar.  We've had costly interactions with some developers before on past projects that did not provide satisfactory solutions. And as you point out, we probably didn't do the best job on laying out our requirements and perhaps even picking the right provider.  This experience along with our current frustrations led the ownership to decide to handle the reporting needs in house.  However, we're not completely ruling out the possibility of using a developer, but right now we're trying to really understand what the right solution for our needs might be and what are our options.  Being able to present this question to such a vast number of experts seemed like a great opportunity to get insights from all points of view.

You suggested Excel power query.  Is that the same as Excel query?  I've used those to retrieve simple datasets from our database, but have not been successful in setting them up as parameter queries where I'd be asked for parameters before they run.

Ido and LSM: Thank you for your recommendations on Crystal and the link to the individual training courses.  You wrote: "If you'll only be viewing reports, and not allowing all users to edit/customize them, then you really don't even need the server component - you can create a small .NET app that does nothing more than pull .rpt files from a central server location and present them in the form. "  When you say edit, do you mean by that that the user can choose from a list of parameters and then run, or actually modify the report's appearance and logic? All we need is the choice of parameters.  I assume creating the .NET app would require a developer help.
0
Ido MilletProfessor of MIS at Penn State Erie and Owner, Millet SoftwareCommented:
There are inexpensive 3rd-party viewer applications (see list at http://kenhamady.com/bookmarks.html) that would allow your users to run the reports and provide parameter values, but not change the design of the report.  You can install these viewer applications on the local user machines or on a Citrix/Terminal Services server.

Given the description of your circumstances, I would advise against trying to create, install, and maintain a web application to do this.  But the same Ken Hamady site also provides links to web-based solutions.
0
mlmccCommented:
Most of the viewers and schedulers on the Hamady site support user entry of parameters.

Before trying to build your own application simply to view reports, consider the viewers available  The Hamady site has a good list of them

http://kenhamady.com/bookmarks.html#viewers

Most are relatively inexpensive.

The Hamady site also has a link to a review of the viewers
http://kenhamady.com/cru/comparisons/crystal-reports-viewers

mlmcc
0
Scott McDaniel (Microsoft Access MVP - EE MVE )Infotrakker SoftwareCommented:
When you say edit, do you mean by that that the user can choose from a list of parameters and then run, or actually modify the report's appearance and logic?
Generally no, your users could not modify the design of the report, but could select parameters and such to filter the data in the report.

As others have said, there are many inexpensive viewers which can be used for these purposes.
0
RobertCZAuthor Commented:
Thank you all again!

Could you please help me understand the advantages and disadvantages of both Access and Crystal, beyond what LSM pointed out?  What is it that Access can do than Crystal can't and vice versa that would affect our ability to deliver?

I'm trying to avoid going down a path (platform) that will prevent us from reaching our goal of allowing our users to run the predefined reports on their own as described above.
0
Scott McDaniel (Microsoft Access MVP - EE MVE )Infotrakker SoftwareCommented:
There is no document that compares Access reporting to Crystal.

It seems your high points are this:

-- web/remote availability
-- reports mostly financial in nature
-- no end user design capabilities needed
-- ability to work with MySQL
-- multiple offices

Would that be accurate? Can you add more to that?
0
planoczCommented:
All of the above comments are good.
so I will throw in my 2 cents.

With about 90 empolyee's I would be more incline to go with SQL2008 server and SSRS Reports.

1. The SQL Server has SQL reorting services buit it. (No Crystal R. Lic required)
2. SSRS reports is a new software compared to CR, but it is catching up fast.
3. I have personnely converted over 1200 CR reports to SSRS. So I know it can be done in SSRS.
4. It takes no longer to learn SSRS than CR reporting.
0

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
RobertCZAuthor Commented:
Thank you planocz.  Do you have a rough idea what SQL server and SSRS cost us in licensing?
0
planoczCommented:
I have not priced a software package in years. But I think it is around 5,000.00.
Some of the other EE's might know how much if they respond.
0
RobertCZAuthor Commented:
Thank you very much everyone!  Your comments helped us move through the process of investigation trying to find the best fit.  After Planocz's comment I did further digging to find out that we already run SQL server 2005 on one of our servers.  We then decided to upgrade to 2012.  We can now convert the current Access databases to SQL completely and take advantage of SSRS and web based reporting distribution.
0
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Crystal Reports

From novice to tech pro — start learning today.