Re: Creating a managment reporting tool using InfoPath and Sharepoint or Access.

Patrick Halstead — Wed, 18 Feb 2009 07:23:34 GMT

Hi David and welcome to the forum,

This is a common problem of SharePoint - you can't promote repeating sections. That's why we created our SQL database accelerator back in 2004. Because people want reporting on their XML data and they can't use SharePoint to report on data in repeating sections or tables. They need to create relational tables in SQL and map to them. That's what our Web service lets you do. In fact, in less than 30 minutes you can have this set up, and I'm happy to have someone show you that working. We don't use Access because it's hard to use Access across the Internet. That's another reason why SQL and Web Services is the best practice. Of course, you can just download the free MSDE or SQL Express version of SQL, so no cost is required there. A free trial of our Web service is available here: http://www.qdabra.com/en/products/DBXL.aspx . Good luck and let me know if you want a free hour of help on this.

Best,

Creating a managment reporting tool using InfoPath and Sharepoint or Access.

David J. Kiss — Tue, 17 Feb 2009 14:28:31 GMT

Let me start by defining the general problem I am aiming to solve:

PartA: Create a weekly reporting form(s) for 4 sections: Service, Sales, Credit, Costs (this one is monthly or "4 weekly").

PartB: From the forms that are submitted, an excel or access program should create visual reports (charts, pivot tables) using the information in the forms that were submited up till that point in time.

This is the first time I am using InfoPath, and the journey has been one of discovery. I create a new form that consists of 4 optional parts, each opion being one of the above mentioned sections and 1 must be filled out before submiting. When the user fills out a section it is saved as "[Week#].[Department]" for example if the Sales section is filled out for week 12 it is saved as 12.Sales.xsn

Using sharepoint these are uploaded to the form library. So far so good. I figured I could make an excel to summarize all the forms (a dashboard of sorts) that can make use of the figures if the "export to excel" command is used (from SharePoint) and that file is saved with a predetermined name at a predetermined path.

However, in the Credit and Cost section I used repeating tables. And on the Sharepoint form overview only the first rows are shown of the repeating table. How can I access the "top 10" from the table?

After battling with that for a while, I thought the better approach is to have the submit button submit to a local Access database, and skip sharepoint all together. But this is were I hit a wall. Reading forums and articles I haven't found any detailed enough posts about how to do this. So what is the best way to create a "dashboard" or overview document and use InfoPath forms to collect information for each department via network or sharepoint or email. The point is to have it as automated as possible.

Thanks a million for all your help!

David

SQL Database Integration

Re: Creating a managment reporting tool using InfoPath and Sharepoint or Access.

Creating a managment reporting tool using InfoPath and Sharepoint or Access.