Gavin McKay

Hi folks,

I just *no* I'm missing something obvious here but anyway...

I want to be able to create a report based on the Infopath 2007 web-based forms in a doc library on our WSS 3.0/Forms Services/SQL 2005 server. Our system allows users to submit Infopath forms to our WSS site. I have tried the following:

1. Accessing the content db via SQL 2005 - data is stored in binary format and unusable. Seems that the data is not stored in a SQL 2005 xml data type, which makes sense I guess seeing as all WSS 'documents' are stored as binary data

2. Access the web services (Lists.asmx, GetList() from memory) - data returned, but stored in metainfo tag and unusable as a data source

3. Export to Excel - works, but not really any good to provide in a server environment (i.e. report contained within WSS)

4. Installed Reporting Services and Sharepoint Reporting Services add-in - doesn't seem to be any way to get data using Reporting Services

I just *have* to be missing something!!! The end result is that I want to be able to hook up a reporting application (Crystal Reports XI or Reporting Services) to the Infopath 2007 xml within a document library. I don't mind if the data is in Xml format or 'sql' dataset format. Any ideas anyone

Thanks in advance!

Gavin.



Re: SharePoint - InfoPath Forms Services How do I create reports on Infopath document libraries?

Jian Zhang

I am currently doing the same thing and believe me or not... I have the same though as you do to have SSRS or something like crystal report to extract the data from the form lib and put it on somewhere in the SPS portal. The notes you have to be sure is that

1. do you really want a report but not a flashy web part which you can let user do something else, like hook up with the user list in the portal site and do some filtering, and other user interaction

2. if you are to make a report instead of a handcrafted webpart, are you aware of the fact that the underlying database table's column name might be different across different servers even if the form library or list is created in the same way. Don't believe me try ... You will find fiddling with WSS db is very intriguing (and by that I mean very dark road!)

If you really agree with me on above 2 points, ok, the simpliest way is to use the report webpart provided to backward support the WSS 2.0 service which you can find in sql server's reporting serivce folder: C:\ Program Files\Microsoft SQL Server\90\Tools\Reporting Services\SharePoint\RSWebParts.cab by default. By using this webpart , you can create data model and therefore create you own report using Businee Intelligence studio coming with SQL 2K5 to put your report onto your SQL Report server and hook up the report with your report web part so you will be able to show your report in sharepoint...(please read http://msdn2.microsoft.com/en-us/library/ms159772.aspx for more details on how to integrate this webpart in your site) In this case, showing them in sharepoint is easy, but how do you figure out the creepy data in sharepoint database is a huge problem. Unless you can make an object datasource which extracts data using sharepoint's object model or web service, I don't see a clear way of implementing this against sharepoint lists.

Or if you are brave enough , you can make a handcrafted webpart which will look like a report but you have totally control of the sharepoint data using web service and object model. But it will be hard to make your webpart as good looking as a report generated by SSRS...

So the best bet will be searching google for making a object data source to bind to your report in BI project and show it in the report web part I mentioned above, otherwise, it will be very painful Smile

Because all I need is a pivot table like web part building with datagrid, I didn't use reporting service, but I do appreciate if you can find something about that. Let me know how you get on






Re: SharePoint - InfoPath Forms Services How do I create reports on Infopath document libraries?

Gavin McKay

Hi there, thanks for your reply!

Yes we do need to create reports - they are presented to management based on the form data and workflow status, so they need to be pretty Wink

I'm not sure if this answers my question, by the sounds of it you are saying I need to hand-craft something either by calling the in-built web services or (as you correctly point out) struggle with the direct data stored in the database I just find it really surprising that forms services does so much, but I can't seem to report on the data!!!

My ideal scenario is:

- use SQL 2K5 SP2 and the Sharepoint add-in component to integrate reporting services inside Sharepoint (installed it - looks great dl from http://www.microsoft.com/downloads/details.aspx displaylang=en&FamilyID=1e53f882-0c16-4847-b331-132274ae8c84)

- some sort of groovy data adaptor that will allow me to create an MS Reporting Services report that uses a Form Library in WSS/Sharepoint as the data source

Heck, if something isn't available I might just have to write one!!!

Gavin.





Re: SharePoint - InfoPath Forms Services How do I create reports on Infopath document libraries?

Jian Zhang

see the Microsoft SQL Server 2005 Reporting Services Add-in you downloaded, is that not the same as the report viewer &report explorer as the one I mentioned in my first reply Does that allow you to use object data source somehow for retrieving sharepoint list does that solve your problem

If you want no user interaction within the report like search, filter, or some controls to trigger postback to get your other splist information, go for the report solution and let me know. I am eager to know the solution as well since I only did it using webpart. and it's not looking nice -_-!






Re: SharePoint - InfoPath Forms Services How do I create reports on Infopath document libraries?

Gavin McKay

Not sure if it's the same or not - I know that this one can only be installed if you have already installed Sql Server 2005 Service Pack 2, so could be the same one. Still doesn't have any data sources that seem to be able to help out unfortunately, it just allows you to integrate your reporting services reports inside Sharepoint.

However I just came across this post "Reporting on InfoPath Documents":

http://groups.google.com/group/microsoft.public.infopath/browse_thread/thread/625bc78d95c92060/670d83fbb5903cd8 lnk=st&q=reporting+services+infopath+forms+server&rnum=6&hl=en#670d83fbb5903cd8

and perhaps the best way is to follow the suggestion about hooking into events and saving the infopath xml data into a separate table/database that uses the Xml column, and then you can use XQuery to run your reports. That would be fairly simple to implement I think

Still seems clunky though - would be much better if there was a data provider or web services specifically for Forms Server that would let you do it!

Gavin.





Re: SharePoint - InfoPath Forms Services How do I create reports on Infopath document libraries?

Jian Zhang

well, before we find out that data adapter you can use sharepoint list data source, I think we can only write sharepoint list data into SQL and use XQuery. That's not the only way I think, will keep you informed once I get something new. Thanks for the link .




Re: SharePoint - InfoPath Forms Services How do I create reports on Infopath document libraries?

David Dean

Here are a couple of other alternatives to consider:

  • Depending on your form design, you can fairly easily report on elements that are promoted as columns to the SharePoint list using either Access 2003/2007 or by calling the SharePoint web services.
  • Another approach would be to write a web service method that uses a CAML query to find the appropriate list items, reads the XML from each list item using the SPFile class, and combines them into a single XML document.