Reports based on several SharePoint lists

Tech-Archive recommends: Repair Windows Errors & Optimize Windows Performance

Hello,

I am not sure whic discussion is more suitable. I have already posted same
question to SharePoint Server Discussions in Development and Programming but
then I have found this one. I am sorry for second post.

I was assigned to a new project where I have to create few reports based on
content stored in several SharePoint lists. I wonder how to do this in the
most efficient way?

First I can't use Reporting Services and Access (project limitations). Only
MOSS 2007 Enterprise edition and SQL Server are availible.

Regarding many articles from Interned I decided direct querying of content
database is not the right way to go. So my assumtion is to create one SPList
for each report, create data connection for each of these lists and store
them in Data connection library. Then use these connections by Excel
workbooks to create reports. Finaly upload these Excel files to document
library and use them on dashboards.

First I really don't know how to prepare those lists (or views) because it
requires to do join operations between lists (data are normalized). Is there
any way how to do this operation? I have checked SPSiteDateQuery, SPQuery (as
well as Query CAML schema), view definitions and CrossListQueryInfo and I
haven't found any way how to perform this essential operation.

I tryed to do reports on test lists (manually filled) but it does not work.
I added document library with my reports to trusted location in Excel
services settings and allowed external data and I also added data connection
library to trusted connections. When I open my Excel report by Excel services
it does not refresh data. When I tryed to refresh data it always fails with
some stupid localized information.

I guess the best way to do reports from SharePoint lists is to create
package in Integration Services which will call Lists.asmx web service for
each needed list and marge (=join) these lists toghether. Then use this
package as data source for Reporting Services. The problem is I am not
allowed to use SSIS neither SSRS.

Can anybody help me little bit with this task?

Thanks in advance.
Ladislav


.

Relevant Pages

  • Re: why>?
    ... times as many people as now skilled at producing reports. ... Access MDB can do it in about 1/10th of the time that Excel can. ... Data validation lists or from the Forms toolbar. ... Excel has poor text processing tools, but that's to be expected since ...
    (microsoft.public.excel)
  • Re: New User Help with lots of information
    ... from the excel sheets with some work. ... Post back when you get the structure set up and ask for help in setting up the ... Each tab I have a listing of different reports. ... lists all the deposits for every company that uses a specific product. ...
    (microsoft.public.access.gettingstarted)
  • Reporting
    ... What would be the best way to create formatted reports ... from Sharepoint lists, preferably in Excel or Word? ... output Outlook form data to Excel and format the ...
    (microsoft.public.sharepoint.portalserver.development)
  • Re: Cant publish Excel list to Sharepoint
    ... If your list is called Reports, it will be set up as a separate list with a ... Microsoft MVP - Excel ... > able to pull a new list into Sharepoint. ... > As to the 2nd comment, I think I want a List of Lists ...
    (microsoft.public.excel.misc)
  • Is there a way to create a Printer-friendly View of Sharepoint Li.
    ... I would like to be able to generate reports for users without export the data ... to Excel. ... view for Lists and Document Libraries. ...
    (microsoft.public.sharepoint.portalserver.development)