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!
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!
SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
(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.
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.
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.
ASKER
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.
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.
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.
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.
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
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
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.
ASKER
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.
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.
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?
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?
ASKER CERTIFIED SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
ASKER
Thank you planocz. Do you have a rough idea what SQL server and SSRS cost us in licensing?
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.
Some of the other EE's might know how much if they respond.
ASKER
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.
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