A reader, who has requested to remain anonymous, asks:
We would like to collect statistical data and then consolidate and report these statistics. The data will be inputted online at various remote sites. Q1. Can InfoPath be used to collect the data? Q2. Can the data be stored in an Oracle database? Q3. Do users (from the remote sites) require InfoPath loaded on their computers to access the reports?
Note: Parts of the original comment have been altered to protect the reader’s privacy.
Hi,
Thanks for your questions.
Can InfoPath be used to collect the data?
Yes, one of the design goals of InfoPath was to collect data. Having said that, InfoPath is not strong as a reporting tool.
If you want to collect data online, you’ll have to use InfoPath along with the SharePoint plus InfoPath Forms Services.
Can the data be stored in an Oracle database?
Yes. While InfoPath does not offer a native data connection for Oracle databases, you can (and will have to) build custom web services to be able to retrieve data from and store data in Oracle databases.
Do users (from the remote sites) require InfoPath loaded on their computers to access the reports?
If you store the data in an Oracle database, I’d recommend not storing the InfoPath forms as XML, but have the web service which the forms are submitted to and which pre-processes the data of each InfoPath form, read the data from the form and split/store it in separate fields/columns in tables in the database.
This way you can use any reporting tool that’s compatible with relational data stored in Oracle for your reporting purposes. The reporting would then be independent of InfoPath.
Users would not need InfoPath installed on their PC for reporting nor for filling out the forms, because you’d be making use of an independent reporting tool and of InfoPath web-based forms. However, you would need at least 1 InfoPath client installation to be able to design and publish the InfoPath form template that all forms would be based on.
Conclusion
If your organization has already invested in SharePoint and InfoPath Forms Services, you may want to consider InfoPath as a possible solution for your scenario.
On the other hand, since you’d have to write a custom web service anyway to be able to store the data in Oracle, you could also choose to go with an ASP.NET forms solution instead of an InfoPath solution. The latter would also make more sense if your organization does not already use SharePoint.
Hope this answers your questions.
Useful posts for further reading
- InfoPath Q&A: How can I use InfoPath forms without having InfoPath installed on users’ PCs?
- InfoPath Q&A: Where is InfoPath form data stored?
2 Responses to “InfoPath Q&A: Can InfoPath be used with Oracle databases for data gathering and reporting?”
Hi there,
Thank you so much. Your response was extremely helpful. Thank you also for the recommendation regarding ASP.NET. My organization does not use SharePoint, so ASP.NET might be a very favorable option. When you say “you’d have to write a custom web service,” is this a very involved and costly process? How many hours of develpment work should we anticipate for this project?
Thanks again for your kind help. I was very re-assured when I received your response, and I appreciated you taking the time to make it simple to understand for a non-technical person like myslef.
Best regards
Note: Parts of this comment have been deleted to protect the commenter’s privacy.
By Anonymous on Apr 7, 2009
Hi,
Writing a .NET web service to interact with InfoPath and Oracle should not take long if you already have the InfoPath form template and Oracle database in place.
Depending on the complexity of the form, an experienced .NET developer who’s familiar with InfoPath and Oracle should easily be able to build a custom web service within 2 days. A less experienced developer could easily take 1-2 weeks, depending on their level of experience and knowledge.
If you go the ASP.NET route, please remember that it takes more time to build an ASP.NET form than it would to build an InfoPath form. But again, this depends on how complex the form is.
SharePoint, InfoPath Forms Services, and InfoPath are meant to save organizations time when trying to build collaborative solutions, because much of the plumbing that you would have to write yourself if you used ASP.NET has already been taken care of for you. This includes time to test and resolve defects.
I guess you’ll have to look whether you have people with the right skills in house and then weigh the cost/benefit of a custom ASP.NET solution against that of an InfoPath/SharePoint solution.
The decision for going with one or the other is not always clearcut…
By S.Y.M. Wong-A-Ton on Apr 7, 2009