Reporting on SharePoint Lists

Reporting on SharePoint Lists

You can do Reporting on SharePoint Lists with SQL Server Reporting Services 2005 quite easy.

  1. Configure the Datasource with integrated Security
  2. Define a query like

    <Query>
    <SoapAction>http://schemas.microsoft.com/sharepoint/soap/GetListItems</SoapAction>
    <Method Namespace="http://schemas.microsoft.com/sharepoint/soap/" Name="GetListItems">
    <Parameters>
    <Parameter Name="listName">
    <DefaultValue>SharePoint List Name</DefaultValue>
    </Parameter>
    </Parameters>
    </Method>
    <ElementPath IgnoreNamespaces="True">*</ElementPath>
    </Query>

  3. Hit the "Field Refresh" Button to get the Field from your SharePoint List
  4. Drag your Fields in the Report
  5. Use the Code to eliminate "ID;#" and "<div>","</div>" from your Fields

    Use "=Code.CuttOff(Fields!ows_LinkTitle.Value)" in your Report to display your Fields
19 Comments

I get this error

Reply

Hi,

There is an error in the query. Failed to execute web request for the specified URL.

Thank you.

 

AW: Reporting on SharePoint Lists

Reply

What shall I say? It works for me. Be sure to change the path to your webservice in the connection string. Also check if your lists display name is different from its internal name (the one in the url).

René

 

Patrick

Reply

Hi Rene it doesn’t work for me too.
I got the same error as above
Please email me if you have some solution for this to :
naijacoder@hotmail.com

 

re: Patrick

Reply

Hi Patrick,
did you doublecheck the Urls and names? The connection string has to be “http://yoursite/subweb/_vti_bin/lists.asmx”
Is there any proxy which might prevent a connection?

René

 

Not working form me ….

Reply

Hi ,

i m also getting error like “Failed to execute web request for the specified URL”

i checked my connection string in IE it is correct.

 

Lee

Reply

Works well, thanks for the tips

 

Panda

Reply

Try using the GUID of the list instead of the list name. You can get the GUID from the URL when you are editing the settings of the SharePoint list. That’s what I ended up doing to correct the errors I encountered

 

Carlos

Reply

Thank you for your great contributions. They really saved me time and allowed me to access important data we have compiled in several SharePoint list libraries. We also have quite a few SharePoint Forms libraries. Would it be too much to ask for the query to retrieve data from a form library instead? Thanks again.

 

René Hézser

Reply

Hi Carlos,
the lists webservice should also work with libraries. With form libraries, you have to watch out for the internal field names. They can be nasty.

René

 

Daniel

Reply

Is the code that is slightly cut off supposed to help remove invalid strings that might come accross?

 

René Hézser

Reply

Hi Daniel,
the invisible code is to take the second part of a lookup field (1;#one is the value of a lookup field)

René

 

Jennifer

Reply

What is the code that is cut off? I’m not familiar with this code. Thanks!

 

SharePoint Data Miner

Reply

Hi,

This approach is too slow and error prone.
Try using the SharePoint Data Miner instead.

Adrian

 

René Hézser

Reply

Great. Use my blog to promote your software…

 

You

Reply

Adrian,

Can you please stop advertising your stupid software in all the forums that talk about reporting from list? KTHXBI

 

Virtz

Reply

What is the end of below code? Thanks.
result = result.Substring((result.IndexOf(“;

 

René Hézser

Reply

Maybe “#)+2));”
I honestly don’t know. Try to see what fits best 😉

René

 

avinash

Reply

Hi Rene,

Is it possible to fetch multiple lists using these codes with any modifications ? If yes, please do the need.

Thanks,
Avinash

 

Thomas Trung Vo

Reply

Make Report by Reporting Service with SharePoint List http://sharepointtaskmaster.blogspot.com/2011/08/make-report-by-reporting-service-with.html

 

Leave a Reply