Hi there, I have a stupid question
When I want to configure reporting service in a computer it needs Windows SharePoint Service. But When I installed and configured it in my own PC it asked for database server location instead. Anybody have any idea about this?
Hi all,
I have some problem about reporting service add-in.
After I install reporting service add-in for SharePoint, reporting service menu does not appear in Application Management Tab in SharePoint Central Administration.
I try to uninstall and re-install again, it remain not work.
How can I solve this problem?
Thank you very much.
Hi all, I tried to configure reporting services configuration by the following
step 1. Click Start, point to Programs, point to Microsoft SQL Server 2005, point to Configuration Tools, and click Reporting Services Configuration.
when i did the above step, i received the following error..
No report servers were found on the specified machine.
Details: Invalid namespace
But i installed SQL server 2005 version.
Pls help me to solve this..
Thanks in advance..
Regards Nataraj.C
I replied to an thread in the pre-2010 forum before I realized the forum I was in...
I'm using Visual Studio 2008 to Design my SSRS reports. The report viewer is using SharePoint 2010 SP1 integrated mode.
I am using a conditional statement to determine which report to drill to based on the SSRS textbox's column.
=iif((Fields!Type.Value =
"Total")OR
(Fields!Type.Value ="Variance - (F)/U"),"DivisionSpendingReport","DivisionSpendingReportDetail")
Works fine in Visual Studio. Once deployed to SharePoint though, the drill through returns an error that says report can't be found. So I added the .rdl to the conditional statement in the report Design.
=iif((Fields!Type.Value =
"Total")OR
(Fields!Type.Value ="Variance - (F)/U"),"DivisionSpendingReport.rdl","DivisionSpendingReportDetail.rdl")
It now works in SharePoint but not in Visual Studio...
Do I need to keep adding and removing the ".rdl" in order to make any changes to this report???!!!
Hi there,
I have a problem for integrating sharepoint to reporting service. In my project web access page, when I tried to open a .rdl report, the following error occured:
An unexpected error occurred while connecting to the report server. Verify that the report server is available and configured for SharePoint integrated mode--> Server was unable to process request
Then when I go to the reporting service configuration manager. I saw that in the Server Status tab, the "Initialize" field is "No". And for the Sharepoint Integration tab, there was a blue "!" mark on it.
I read from the web (http://www.helloitsliam.com/archive/2007/10/23/moss2007-€“-reporting-services-add-in-and-configuration.aspx) that if the database setup is ok, then the sharepoint integration will also be ok and the report manager virtual directory will turn gray. However, there is an error when setting up the database. In the task status, "Verifying Database Edition", "Verifying Database Version", "Creating a Grant Rights script for xxxxxxxxxx" and "Assigning Reporting Services Rights to User" were all marked as a green tick. However, there is a yellow "!" for "Setting Connection Info for Reporting Service" and the detailed error is as follows:
ReportServicesConfigUI.WMIProvider.WMIProviderException: The encrypted value for the "LogonCred" configuration setting cannot be decrypted. (rsFailedToDecryptConfigInformation)
at ReportServicesConfigUI.WMIProvider.RSReportServerAdmin.ThrowOnError(ManagementBaseObject mo)
at ReportServicesConfigUI.WMIProvider.RSReportServerAdmin.ListReportServersInDatabase(RSReportServerInfo[]& serverInfos)
Does anyone know how to solve it? Thanks in advance!!
Regards,
Ryan
I have an installation of Reporting Services with Sharepoint integration. Most of my reports are Analysis Services reports with MDX queries. These reports are typically with 9-10 parameters.
When I try to pull these query these reports in the sharepoint site, it takes typically 3-5 minutes before the report is rendered. If these reports are queried from BIDS itself, it takes only a couple of seconds. To make things worse, every change of parameters causes the report to re-render and the entire load time has to report itself again, which is unacceptable by any standard.
I suspect this has something to do with the report viewer render time, does anyone have similar experience or is able to advise on how this can be fixed?
Thanks.
Vista + SQL Server 2005
Reporting Service Configuration Tool: No Red Cross.
http://localhost/reportserver get this error:
HTTP Error 403.1 - Forbidden
You have attempted to run a CGI, ISAPI, or other executable program from a directory that does not allow executables to run.
http://localhost/reports get this error:
w3wp!ui!4!20/02/2008-17:17:04:: e ERROR: Unable to connect to the remote server
w3wp!ui!4!20/02/2008-17:17:04:: e ERROR: HTTP status code --> 500
-------Details--------
System.Net.WebException: Unable to connect to the remote server ---> System.Net.Sockets.SocketException: No connection could be made because the target machine actively refused it 127.0.0.1:443
at System.Net.Sockets.Socket.DoConnect(EndPoint endPointSnapshot, SocketAddress socketAddress)
at System.Net.Sockets.Socket.InternalConnect(EndPoint remoteEP)
at System.Net.ServicePoint.ConnectSocketInternal(Boolean connectFailure, Socket s4, Socket s6, Socket& socket, IPAddress& address, ConnectSocketState state, IAsyncResult asyncResult, Int32 timeout, Exception& exception)
--- End of inner exception stack trace ---
at System.Net.HttpWebRequest.GetRequestStream()
at System.Web.Services.Protocols.SoapHttpClientProtocol.Invoke(String methodName, Object[] parameters)
at Microsoft.SqlServer.ReportingServices2005.ReportingService2005.ListSecureMethods()
at Microsoft.SqlServer.ReportingServices2005.RSConnection.GetSecureMethods()
at Microsoft.ReportingServices.UI.Global.RSWebServiceWrapper.GetSecureMethods()
at Microsoft.SqlServer.ReportingServices2005.RSConnection.IsSecureMethod(String methodname)
at Microsoft.SqlServer.ReportingServices2005.RSConnection.ValidateConnection()
at Microsoft.ReportingServices.UI.ReportingPage.EnsureHttpsLevel(HttpsLevel level)
at Microsoft.ReportingServices.UI.ReportingPage.ReportingPage_Init(Object sender, EventArgs args)
at System.EventHandler.Invoke(Object sender, EventArgs e)
at System.Web.UI.Control.OnInit(EventArgs e)
at System.Web.UI.Page.OnInit(EventArgs e)
at System.Web.UI.Control.InitRecursive(Control namingContainer)
at System.Web.UI.Page.ProcessRequestMain(Boolean includeStagesBeforeAsyncPoint, Boolean includeStagesAfterAsyncPoint)
w3wp!ui!4!20/02/2008-17:17:06:: e ERROR: Exception in ShowErrorPage: System.Threading.ThreadAbortException: Thread was being aborted.
at System.Threading.Thread.AbortInternal()
at System.Threading.Thread.Abort(Object stateInfo)
at System.Web.HttpResponse.End()
at System.Web.HttpServerUtility.Transfer(String path, Boolean preserveForm)
at Microsoft.ReportingServices.UI.ReportingPage.ShowErrorPage(String errMsg) at at System.Threading.Thread.AbortInternal()
at System.Threading.Thread.Abort(Object stateInfo)
at System.Web.HttpResponse.End()
at System.Web.HttpServerUtility.Transfer(String path, Boolean preserveForm)
at Microsoft.ReportingServices.UI.ReportingPage.ShowErrorPage(String errMsg)
I googled but not found anything useful.
Thanks in advance!!!
When I opened "Reporting Service Configuration Manger" from SQL server configuration tool list, The "Report Server Installation Instance window came out. It showed my local machine name on the field of Machine Name. When I click "Find" button, a message box came out. It says: "No report server were found on the specified machine. Details: Invalid namespace
What is the problem and how to solve it.
Hi,
I have installed SQL Reporting Service on one server. Using the config tool, I am trying to setup the Reporting Database on a seperate SQL instance. This SQL instance is running on a Clustered SQL 2005 server.
The creation of the Database is sucessful. However when I give the Windows credentials to connect to this database on the same page, I always fail at the following task : Setting Connection Info for Reporting Server. The error message is :
"System.Runtime.InteropServices.COMException (0x800706B3)
at System.Runtime.InteropServices.Marshal.ThrowExceptionForHRInternal(Int32 errorCode, IntPtr errorInfo)
at System.Management.ManagementObject.Get()
at ReportServicesConfigUI.WMIProvider.RSReportServerAdmin.SetDatabaseConnection(String server, String database, ConfigurationCredentialsType credsType, String userName, String password)"
The Windows Account I am using has Full SQL Rights to the Clustered SQL Server. I have seen other posts on the forums about the 0x800706B3 error, but most of them are related to a problem with RPC.
The Reporting Services account is runing under 'Network Services'. I am not sure if this is causing the problem?
Thoughts anybody?
Thanks,
Mahmood
Hello Everybody,
I am presently working on a project which handles much larger amount of data. The application demands extensive reporting from the SharePoint data. I'd like to know how I can generate reports from the SharePoint lists using Reporting Services.
Planning to install in SQL Server Integrated mode
Thank you,
Arun
Maybe someone can help me. I am running the reporting service add in for Sharepoint and we have only Basic Authentication running on our site (it is on SSL). When i try and open a report in sharepoint it errors out on me. When i go back into IIS and set Windows and Basic authentication it works, but it requires me to enter our domain and username. We need to only use Basic so it will not need the domain. Anyone have any suggestions on what i can do to make this work?
View 5 Replies View RelatedI am running SQL 2005 reporting services on a windows 2003 machine with SharePoint over SSL
My reporting services web directories are virtual directories under the SharePoint website.
I am having a problem with loading https://sharepoint.mysite.com/Reports/Pages/Folder.aspx
When you first open IE and bring up the page it takes about 30 seconds to load. Once you have connected via any machine, all other machines can connect at normal speed 1-2 seconds. All testing is being done on our LAN so there is no bandwidth issues when opening the pages.
My guess is that something is going into a sleep mode, but I am not sure where that would be set.
Thanks for any suggestions
Robert
Hi,
I am working on sharepoint 2007 integration with reporting service 2005. There are few facts which i am trying to understand and need some clarifications. Please provide your comments
1) While configuring "Database setup" in Repoting Service Configuring Manager" i found two modes 1) Native and 2) SharePoint Integration. When creating a report server database if we check "Create the report server database in Sharepoint Integration Mode" then it set the mode to "SharePoint Integration Mode". There is no option of switching the same database to native mode. So i have to create another database in "Native Mode". (IS THIS CORRECT. DO WE NEED TO CREATE TWO REPORT SERVER DATABASE SEPERATE FOR SHAREPOINT INTEGRATION AND NATIVE)
2) I am using the new report viewer web part in sharepoint 2007. When i am in sharepoint integration mode i can create datasource, report model and with report builder i can create rdl file. On selecing any RDL file in doument library and it gets displayed in Report Viewer Web Part. When i switch to Native mode i want to configure the web part to use the report created with SQL Server Bussiness Intelligence Dev Studio and deployed on http://localhost:808/ReportServer. (IS THERE ANY WAY SO THAT I CAN EXPORT AND IMPORT ALL .RDL FILE FROM http://localhost:808/ReportServer to http://localhost/ReportServer)
I AM NOT ABLE TO CONFIGURE WEB PART WHEN I AM IN NATIVE MODE i.e giving path of .RDL in report textbox throw a error saying "Item not found" i tried all combination. THIS can be related to datasource not reading in .RDL file in native mode while confuring report viewer web part.
http://localhost:808/ReportServer (Report Server)
http://localhost/ReportServer (SharePoint Mode) (Default Top Level Site)
3) LAST : I want to create a .RDL file report from the data in sharepoint, say lists or news etc. So when creating a RDL file how can i connect to sharepoint database. I read some where that in previous release they used to DATA EXTENSIONS and get the data from list in sharepoint with something like http://<servername>/<sitename>/List=Announcement when creating RDL file. Is the alternative in new release to create the report based on sharpoint list data.
Thanks and i would really appreciate your help
I have the following setup
Machine 1
WSS 3.0
Installed Reporting Service Add-in
Machine 2
Reporting Service
Sharepoint Object Model
SQL Server
Sharepoint and Report Server DB
I have congigured Reporting Service for Sharepoint Integrated Mode adn Created new Reprot Server DB
Sahre Point Integration is Green and verified link
In Sharepoint Central Administration
Manage Reporting Service Integratio i have selected the URL and "Trusted Account" as my web applications use "Windows Integration - NTLM".
All services, DB access everything running under one domain user account.
I am logged into sharepoint with same account. When clieck on set server defaults i get following error
An unexpected error occurred while connecting to the report server. Verify that the report server is available and configured for SharePoint integrated mode. --> Server was unable to process request. ---> The request failed with HTTP status 401: Unauthorized.
Same error when i try to add a report to a sharepoint webapplication
The domain account is part of "Farm Administration", "site colletion administrato" and local administrator also on both servers.
Can someone let me know how to fix this.
I get the following error when I try to navigate to a report/model/data source, stored in a Sharepoint Document Library using a Sharepoint URL based on an Extranet or Intranet zone - but it works OK using the url for the Default zone:
System.Web.Services.Protocols.SoapException: The specified path refers to a SharePoint zone that is not supported. The default zone path must be used. ---> Microsoft.ReportingServices.Diagnostics.Utilities.SecurityZoneNotSupportedException: The specified path refers to a SharePoint zone that is not supported. The default zone path must be used.
Is this a limitation of the SQLRS-WSS3 integration or is there a workaround?
Many thanks to anyone who can help!
Hello,
I'm trying to set up SharePoint Integration for our report server. The setup we have is: 1. SQL server hosting the SharePoint databases and the Reporting Server, 2. SharePoint web front end hosting all SharePoint services. I've installed the reporting services SP2 on the SQL server and the reporting services add-in on the SharePoint server.
I configured the report server following the directions here: http://download.microsoft.com/download/f/d/c/fdcb3a53-0cb1-4e67-a1b6-45b89b3c59cf/readme_rsaddin.htm and here: http://www.sharepointblogs.com/helloitsliam/archive/2007/02/21/19801.aspx
Everything seems to be set up correctly, but when I open the Reporting Services Configuration Manager, the SharePoint Integration option shows the error, "The report server cannot access setting in the SharePoint configuration database. Most likely, the Windows SharePoint Services object model...."
I do have WSS 3.0 installed on the report server.
When I look in the logs on the report server it says the login failed for the account I have set as the Windows Service Identity however, when I look at the logs on the SharePoint server it shows that it is granting the account read and write access to each of the SPWebApplications I have set up.
Any suggestions?
Thanks,
Cynthia
Since I wasn't getting any replies with answers or suggestions to my initial posting, I figured I would continue to list the problems and solutions I've encountered while trying to setup and utilize SharePoint integration mode, and read data from SharePoint with SSRS reports (developed in VS2005). Hopefully this is helpful to anyone that is also trying to learn all this stuff and figure it out.
(Single server - W2K3R2_SP2 as: DC / SQL2005 Standard / MOSS2007 Enterprise - see the bottom of this posting for the steps I took to set that up)
Note: After I get this to work, I intend to post my experiences for setting up SharePoint Integration with SQL and SP on separate boxes.
1. In the new reporting section under SharePoint application management in the Central Admin Console, the Grant database AND Set Server Defaults work when using the domain admin account, but not when using the SQLSPS service account I used to setup both SQL and SharePoint. Why can't I use the service account? Is the domain admin account now going to be used as a service account for accessing the sharepoint database?
This question remains unanswered as of yet.
2. Reporting Services configuration tool shows Sharepoint integration as red x when I create a new database in integration mode. (the service account SQLSPS and the Server object are members of the group WSS_WPG). If I switch back to native mode (the original reportserver database), the red x goes to a blue exclamation instead. Using Service Credentials or Windows credentials (as either the service account SQLSPS or as domain admin) doesn't change anything.
RESOLVED by using a domain user account for the ReportServer WebApp. Even though everything is installed on the same machine, it seems using the default of NETWORK SERVICE wasn't acceptable. Possibly because SharePoint is configured to allow additional machines in the farm?
3. http://servername:8080/reportserver will display Report Server information as it did before the integration on port 80, but http://servername/reportserver doesn't display anything through SharePoint.
Resolved. I needed to define a repository before I could view it (go figure). When I created a sharepoint site based on a report center template, created a data connection library and report library, and then referenced that by http://servername/sites/myReportCenterSite/myReportLibrary, I was able to see the equivalent of http://servername/reports (which is now disabled due to integration mode).
4. Unable to deploy to new report center sharepoint site I mention above using Visual Studio. I kept getting "A connection could not be made to the report server http://servername/sites/ReportCenter. Server was unable to process request. The request failed with HTTP status 503: Service unavailable (System.Web.Services). In Visual Studio, the project properties were: TargetDataSourceFolder = http://servername/sites/myReportCenterSite/myDataConnectionLibrary, TargetReportFolder = http://servername/sites/myReportCenterSite/myReportLibrary, TargetServerURL = http://servername/sites/myReportCenterSite/
Found these errors in the event logs on the server.
Event Type: Warning
Event Source: W3SVC
Event ID: 1021
Description:
The identity of application pool, 'ReportServer' is invalid. If it remains invalid when the first request for the application pool is processed, the application pool will be disabled. The data field contains the error number.
Event Type: Warning
Event Source: W3SVC
Event ID: 1057
Description:
The identity of application pool 'ReportServer' is invalid, so the World Wide Web Publishing Service can not create a worker process to serve the application pool. Therefore, the application pool has been disabled.
Event Type: Error
Event Source: W3SVC
Event ID: 1059
Description:
A failure was encountered while launching the process serving application pool 'ReportServer'. The application pool has been disabled.
RESOLVED the issue by changing the Application Pool used by reporting services from ReportServer to DefaultAppPool and changing the account DefaultAppPool used from NETWORK SERVICE to my service account SQLSPS. I have no idea why ReportServer is invalid, it was set to use the service account SQLSPS the same as DefaultAppPool is. Perhaps the reporting services add-on makes a change to invalidate this?
5. Attempting to read a SharePoint list using a SSRS report. This should be fun.
First, created a new custom list in the ReportCenter SharePoint sites I've been using and called it myCustomList. I added one item with a title of myCustomListItem1. I don't know if I need to do more than that for a simple test of reporting or not yet.
Second, configured new shared data source. Name = myCustomList1DataSource, type=XML, Connection String = http://servername/_vti_bin/lists.asmx (this URL is viewable in IE), Using Windows authenticaion.
Third, in VS2005 on a workstation in the domain, create new report item (report1.rdl). New dataset for report; Name=Report1DataSet, Data Source=myCustomList1DataSource (Shared), Command type = text, Query String = [See Queries and associated errors below]:
Initial query obtained from: Connecting SQL Reporting Services to a SharePoint List (David Wise)
http://www.sharepointblogs.com/dwise/archive/2007/11/28/connecting-sql-reporting-services-to-a-sharepoint-list-redux.aspx
Note: Queries when executing the dataset (which errors out) and when executing a query in CAML Viewer for the same information (which does work just fine) both initially return 401.1 and 401.2 errors (as viewed in Fiddler). Since the CAML query works, I have not tracked this down yet.
A. Original format from posting
<Query>
<Method Namespace=http://schemas.microsoft.com/sharepoint/soap/ Name="GetListItems"/>
<SoapAction>
http://schemas.microsoft.com/sharepoint/soap/GetListItems
</SoapAction>
</Query>
Parameter; Name => listName, Value => =myCustomList
Was NOT prompted to define the query parameters
- Gets this error: "An error occurred while setting the command text property of the data extension command. The XmlDP query is invalid. (Microsoft.ReportingServices.DataExtensions)."
- Advanced information from the error => 'http' is an unexpected token. The expected token is '"' or '''. Line 2, position 19. (System.Xml)
B. Changed: Placed the namespace URL in quotes (also tried with and without / in namespace after /soap)
<Query>
<Method Namespace="http://schemas.microsoft.com/sharepoint/soap/" Name="GetListItems"/>
<SoapAction>http://schemas.microsoft.com/sharepoint/soap/GetListItems
</SoapAction>
</Query>
Parameter; Name => listName, Value => =myCustomList (also tried Value => myCustomList) (no equal sign)
Clicked "!" to execute, and got prompted to define the query parameters
Entered Name => listName, Value => myCustomList
- Gets this error: "failed to execute web request for the specified URL (Microsoft.ReportingServices.DataExtensions)."
- Advanced information: <faultstring>Server did not recognize the value of HTTP Header SOAPAction: http://schemas.microsoft.com/sharepoint/soap/GetListItems/.</faultstring>
C. Try the query string that seems to work for Stramit CAML Viewer to read myCustomList
Stramit CAML query:
<Query xmlns="http://schemas.microsoft.com/sharepoint/soap/">
<OrderBy>
<FieldRef Name="ID" />
</OrderBy>
</Query>
Also tried
<Query xmlns="http://schemas.microsoft.com/sharepoint/soap/">
</Query>
And tried entering no query...
Parameter; Name => listName, Value => =myCustomList (also tried Value => myCustomList) (no equal sign)
Clicked "!" to execute, and got prompted to define the query parameters
Entered Name => listName, Value => myCustomList
- Gets this error: "failed to execute web request for the specified URL (Microsoft.ReportingServices.DataExtensions)."
- Advanced information: <soap:Fault><soap:Code><soap:Value>soap:Receiver</soap:Value></soap:Code><soap:Reason><soap:Text xml:lang="en">Server was unable to process request. ---> Data at the root level is invalid. Line 1, position 1.</soap:Text></soap:Reason><soap: Detail /></soap:Fault>
RESOLVED. Went back to the query used in B above, and mysteriously it works now. This could be the "funny things happen" symptom I've been reading about. We'll see if it continues to function.
Here's what I've learned so far on Issue #5:
1. XML is confusing. XmlDP is even more confusing...whatever that is. Apparently it's new to/with Reporting Services in SharePoint integration mode?
2. There is far too little documentation regarding XmlDP and querying SharePoint lists from SQL Reporting Services.
3. According to http://msdn2.microsoft.com/en-us/library/ms159741.aspx: You must use the generic query designer to create the query. The query is not analyzed to identify parameters; therefore you must create parameters through the Parameter tab on the Dataset dialog box.
Tried this query:
<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>myCustomReport2</DefaultValue>
</Parameter>
<OrderBy>
<FieldRef name="ows_Date" />
</OrderBy>
</Parameters>
</Query>
I was still prompted for the listName and the list returned with ows_Date still out of order. So maybe that proves this statement is true?
<translation: why make it convenient when it's already [somewhat] functional?>
4. When building the dataset, anything I place in the "Query string" box has to be enclosed in <query></query> tags or it errors out. I am trying to understand how to translate the CAML viewer query (which does work but isn't wrapped in query tags) into something that fits between query tags, and it hasn't gone well so far.
<translation: To be at one with your reports Grasshopper, you must understand xml>
5. According to http://srv1/sites/ReportCenter/_vti_bin/lists.asmx, "GetListItems" operation is supported
<translation: you're getting warmer...>
6. According to the soap output of my site http://srv1/sites/ReportCenter/_vti_bin/lists.asmx?op=GetListItems, I am only allowed to specify the following parameters: listName, viewName, query, viewFields, rowLimit, queryOptions and webID.
<translation: that and $3.00 will get you a cup of coffee>
7. According to http://www.sharepointblogs.com/dwise/archive/2007/11/28/connecting-sql-reporting-services-to-a-sharepoint-list-redux.aspx, only listName, viewName and rowLimit parameters are usable(?) (query and queryOptions may not be working properly with SSRS).
<translation: that kind of sucks>
8. When this started mysteriously working again, I tested using the "/" at the end of the namespace (wouldn't work without) and the "=" sign in front of the parameter value as defined on the parameter tab of the dataset (turns out to be optional).
As long as I can query SharePoint and get my list information back, I'm going to consider this issue closed and move on to actually using this information in a report. I'm sure that won't work right either, but why stop now?!
6. Ok, now that I can read SharePoint list data with SSRS, I need to try and actually do something with it.
A. Begin by setting up new data to report on
1. Create a new custom list (myCustomList2) and add a test record. This list will simulate the data we intend to submit to SharePoint using InfoPath, so it has a number of custom/created columns in it
2. Modify the working information from #5 above; add a new shared data source (myCustomList2DataSource), tell the dataset to use the new shared data source, change the information set in the parameter tab (Name => listName, Value => =myCustomList2) and test the dataset. Lucky me, it actually returned the record I entered on the first try.
B. Begin to build the report with the dataset returned
1. So, as I'm feeling good that it's actually reading the list in Step A without me threatening to destroy the machine, I look to the left in the Dataset results navigation pane in VS2005 and realize there are NO fields for me to choose to report on.
2. At this point, I have no clue what to do about getting this XML data into a report. Not that I have a large clue about SSRS otherwise, but I have at least been able to build SSRS reports when using a SQL database as the datasource. Fyi, http://msdn2.microsoft.com/en-us/library/ms159741.aspx states: "Reports can use data from XML documents and Web services, or embed XML in the query. There is no built-in support for retrieving XML documents from a SQL Server database.", in case anyone is trying to do that....
RESOLVED. Since the fields were not appearing automatically, I had started to add new fields to the dataset manually (if it doesn't work, beat it with a hammer, right?). I entered half a dozen or so fields, each one with the exact name as displayed in the query results, and then decided it would be fun to hit the refresh fields icon. As soon as I did that, all the fields shown in the query immediately appeared in the dataset and I was able to add them to a report. I don't know if I hit the refresh fields when working in issue #5, so I can't say if they are there now because of that, or because of adding some fields manually first, a combination of the two, or if it's just one of those mystery items I'm reading about with sharepoint reporting.
7. Rows are being returned even though they have NULL values (so far anyway), and most data is returning nicely for the bulk of the columns I created in the list I'm reporting on. Here are the current issues:
A. I have one column in my SharePoint list that is multi-lined text. If the text type is either rich text or enhanced rich text, the value returned in the query is formatted like this: "<div class=ExternalClassyadayada<div>My notes display here". If I set the column to be plain text, this doesn't happen. I will need to figure out how to filter that for my report.
RESOLVED. David Wise had some code listed at http://www.sharepointblogs.com/dwise/archive/2007/11/28/connecting-sql-reporting-services-to-a-sharepoint-list-redux.aspx that addressed this issue somewhat. I modified it to match my circumstances. Disclaimer: I'm not a programmer, so use this at your own risk.
function ExtractMultiLineText(myField as string) as string
dim strBegin as string
dim strEnd as string
dim myBegin as integer
dim myEnd as integer
dim myTextLength as integer
strBegin = "<div>"
strEnd = "</div>"
if myField = nothing then return ""
if myField = "" then return ""
myBegin = Instr(myField, strBegin) + Len(strBegin)
myEnd = Instr(myField, strEnd)
myTextLength = myEnd - myBegin
if myBegin < 1 then return myField
return mid(myField, myBegin, myTextLength)
end function
B. I have two columns in the SharePoint list that are set as number values with 0 decimal places. When I query those however in SSRS, they return with the number plus like 12 zeroes behind the decimal. Not a major issue, but I'll put it on the list anyway.
RESOLVED. Used the Int() function; =Int(Fields!ows_myProblemNumber)
C. If I go to "/_vti_bin/lists.asmx?op=GetListItems", it appears from the sample soap output that I am allowed to ask GetListItems for is: listName, viewName, query, viewFields, rowLimit, queryOptions and webID. As I mentioned in issue #5, according to http://www.sharepointblogs.com/dwise/archive/2007/11/28/connecting-sql-reporting-services-to-a-sharepoint-list-redux.aspx, only listName, viewName and rowLimit parameters are usable(?) and query and queryOptions may not be working properly with SSRS.
So, if I can't put anything other than this in my query string;
<Query>
<Method Namespace="http://schemas.microsoft.com/sharepoint/soap/" Name="GetListItems"/>
<SoapAction>http://schemas.microsoft.com/sharepoint/soap/GetListItems</SoapAction>
</Query>
and I only have a couple parameters to pass on top of that, then it appears to me that my options for filtering the amount of data returned from the SharePoint list is to figure out a way to do it with expressions in the parameters or in the report itself. Am I missing something? Is this really all I get to work with out-of-the-box?
More to come...
==================================
Installation steps taken to build the server VM:
==================================
(Using VirtualPC 2007)
1. Build OS (2003R2SP2) and promote to DC
1a. Create domain user service account SQLSPS
2. Prep for SQL
2a. Install ASP.NET, IIS (www) and .NET 2.0
3. Install SQL/SSRS 2005 Std
3a. Verify SSRS works
3b. RS config tool shows SP integration as blue exclamation
3c. Use service account SQLSPS for anything requiring Windows credentials
4. Install SQL2005 SP2
4a. Verify SSRS works
4b. RS config tool shows SP integration as blue exclamation
5. Prep for SharePoint
5a. Install .NET 3.0
6. Install MOSS2007 Enterprise and configure
6a. Webapps, SSP and anything else use svc acct SQLSPS
6b. Move Default website to port 8080 and re-activate (SharePoint stopped the site)
6c. Verify SSRS works on 8080
7. Install Reporting Services Add-in
8. Create new RS database in integration mode (with service credentials)
8a. RS config tool shows SP integration as red x
9. SP central admin /application management / manage integration settings
9a. reports server = http://srv1:8080/reportserver (since default port has been moved to 8080)
10. SP central admin /application management/grant database access
10a. using the FQDN, confirm servername and default instance - click OK
10b. Prompted for an account to use to retrieve information; svc acct SQLSPS did not work, domain admin did though
11. SP central admin / application management/set server defaults
11a. Accepted all defaults
12. Verify reportserver answers at http://srv1:8080/reportserver (via reportserver AppPool and DefaultAppPool)
13. Changed the application pool identity for the ReportServer AppPool from Network Service to the domain user service account I used for all SQL and SharePoint configurations (SQLSPS) and rebooted. The red x for sharepoint integration in the RS configuration tool is now a green checkmark.
Hello everybody,
I'm developing a report using the following structure :
declare @sql as nvarchar(4000)
declare @where as nvarchar(2000)
set @sql = 'select ....'
If <conditional1>
begin
set @where = 'some where'
end
If <conditional2>
begin
set @where = 'some where'
end
set @sql = @sql + @where
exec(@sql)
I run it in query analyser and works fine, but when I try to run in Reporting Services, Visual studio stops responding and the cpu reaches 100 %.
I realize that when I cut off the if clauses, then it works at Reporting services.
Does anybody know what is happening?
Why the query works in query analyser and doesn't work in Reporting Service ?
Thanks,
MaurĂcio
Hi
How to work with SQL mail and How to Work with SQL mail
can some body help me
Dear All:
My reporting manager and reporting server were working fine in IIS. But when I booted the system one day, one error message came "Unexpected error" for the IIS. So I removed the IIS and reinstalled he same.
But after that, I tried to give virtual directories to reporting manager and reporting server sites. But it ain't working. Could you please help me?
My IIS 5.1
Reporting service 2005 Express with SQL Server Express
ASP.NET 2.0 Express.
Please help.
Thanks in Advance
Emmanuel Mathew
i have a list report
some values of this are blank or null
i want to avoid that spacing if it is null
like
name:jjj
lastname kkk
firstname
address uuuu
i want it should be
name jjj
lastname kkk
address uuuu
I recently renamed my server from tmpSrv to tfsSrv (names have been changed to protect the innocent). When I go to the Reporting Services Configuration Manager I have all greens except for the Web Service Identity field.
Trying to set that isn't working...the ASP.Net service account is NT AuthorityNetwork Service, and the application pools defined are using that Network Service account as well. When I hit the "Apply" button I get a message saying:
Setting Web Service Identity
There was an error setting the identity for the web service. The previously set identity will still be used.
I'm trying to install Team Foundation Server on the box, and when I run the tfrsconfig /verify tool I see that the Web Service Identity is not set at all.
I've been battling this for a couple of days now with no effect. I even broke down and gave "Everyone" administrative access on the server in an attempt to fix it.
Thanks for any help
Ok, I think this may have a simple answer. Basically I have no problems in setting up QueryString/Control/etc parameters when I use SELECT in the Configure Data Source Wizard as it prompts me for the necessary parameters. But when I try to use the Configure Data Source Wizard with an UPDATE, INSERT or DELETE it does NOT prompt me for the required parameters.Is this a bug or am I just missing something? Do I have to put them in manually or something?Thanks!
View 5 Replies View RelatedBasically, I am asking if it is possible to use SSMS (Query Analyzer window) with Informix. I am thinking linked server. If I have the ODBC driver installed on my workstation (win 7 pro VM workstation -- SSMS on same VM machine) is this doable? Do I just do the standard linked server routine or is there a special way (if it is even doable).
View 4 Replies View RelatedVery Lost and very Frustrated
Hello all,
I am going to try to keep it short.
Been banging my head with this SQL 2005 Reporting System. My system configuration is as follow:
Windows 2000 server €“ (all services packs install)
Microsoft SQL Server Management Studio 9.00.1399.00
Microsoft Analysis Services Client Tools 2005.090.1399.00
Microsoft Data Access Components (MDAC) 2000.085.1117.00 built by: (_sqlbld)
Microsoft MSXML 3.0 5.0 6.0
Microsoft Internet Explorer 6.0.2800.1106
Microsoft .NET Framework 2.0.50727.42
Operating System 5.0.2195
Not sure you guys need all that information, but anyway, the problem is that when I run the Reporting Service Configuration I am unable to setup the €śWeb Service Identity€?
I am also receiving the following error:
ReportServicesConfigUI.WMIProvider.WMIProviderException: The account name is not valid. Specify an account in the form domainalias.
at ReportServicesConfigUI.WMIProvider.RSReportServerAdmin.SetWindowsServiceIdentity(String accountName, String password, Boolean useBuiltinAccount)
I am not sure.. but I think is something to do with IIS 5.
Help
Thanks all in advance.
1-We have sql server 2008 with 2 nodes with cluster(Active/Passive).
2-Due to performance problem we are going to separate the reports from database.
3-I want to create reporting server,as i am new for this pls let me know the step by step process to create reporting server.
4-Is it possible to create new instance for reporting in passive node and shear the cluster resources??
We are using the whole BI-package from Microsoft - from SQL, DTS-package,Raporting Service and Analyze Service.
It should be very helpful to be able to create a metadata databases where you could find all releations between different objects (tables,views,reports,cubes,DTS-package,Databases.
Just to get answer for: 'where is view xxx used', 'what are Report xxx depending upon'.
While everything exists in different SQL databases it should possible to do.
Has anyone any comments or the same needs ?
I am trying to configure a reporting server on my laptop at work. This laptop is running Vista and IIS 7. When I install reporting services, the option for "Install the default configuration" is disabled (greyed out) during setup. The install is successful, but then when I try to connect in the Reporting Services Configuration Manager, I get an unhandled exception ("A WMI error has occurred an no additional error information is available."). If I continue, this error continues to crop up as I attempt to configure the Report Server. When I attempt to configure the virtual directory, the configuration manager crashes. Then, when I navigate to the reportserver page, I get a page that states:
Server Application Unavailable
The web application you are attempting to access on this web server is currently unavailable. Please hit the "Refresh" button in your web browser to retry your request.
Administrator Note: An error message detailing the cause of this specific request failure can be found in the application event log of the web server. Please review this log entry to discover what caused this error to occur.
Any ideas for a solution or workaround would be greatly appreciated.
Thanks,
Dave
I've created a SSIS package with a DataReaderDestination and a SSRS report that points to it.
I referenced these links during development and I have everything working as expected locally. **I changed the config files as stated on both my local machine and our server. My config matches the example exactly.
http://msdn.microsoft.com/en-us/library/ms345250.aspx
http://msdn.microsoft.com/en-us/library/ms159215.aspx
When I attempt to deploy my report project to the server I get this error message:
"An attempt has been made to use a data extension 'SSIS' that is not registered for this report server."
In the report manager data source properties page it reads:
"The data processing extension used for this report is not available. It has either been uninstalled, or it is not configured correctly."
In SQL Server Management Studio data source properties page SSIS is not a data source type option.
I've tried restarting both the Reporting Services and Integration Services on the server to no avail.
Any suggests? My problem is I can't get the SSIS to be recognized source on the server.
We have a sharepoint <\cescesgypdav> that is a WebDAV connection, not an actual fileshare. A SSRS Subscription fails on file write ... "Failure writing file <name here > : The network path was not found" ... to this path.
Can SQL Reporting write to a WebDAV sharepoint?
Hi my current setup is:
Server 1 - Office sharepoint server 2007
Server 2 - SQL Server 2005 with Service Pack 2 and reporting services
When I try to install the Reporting Services Add-in for Sharepoint on Server 1, I get an error - "Setup was interrupted"
Would you know how to fix it?
Thank you
Hi everybody,
I have installed Reporting in SharePoint Integrated mode on a MOSS 2007 farm (with SP1 (MOSS) and SP2 (SQL Server) installed). When I want to go on the MOSS Administration => Application Management I don't see the RS administration links ...
First, i believed this is due to a problem during the RS add in for Sharepoint install, but it wasn't.
If you try to tape the links of the aspx pages of RS administration in MOSS :
http://<server_name>:<admin_port>/_layouts/ReportServer/ReportServerSiteSettings.aspx
http://<server_name>:<admin_port>/_layouts/ReportServer/ManageTrustedAccounts.aspx
You can access to the admin tools ...
I don't know why the MOSS administration page were not modify during the add in installation but the RS pages works ... : http://<server_name>:<admin_port>/_admin/applications.aspx/_admin/applications.aspx
If someone know what is the origin of this problem ...
PS : To resolve this problem we add two links in the Home pages of the central administration ...
Thanks.
Xavier.
Hello,
In our project, we would like to use the same data source for our analysis service database cubes and for our reporting service reports.
I created the analysis service project first, deployed successfully. When trying to setting up the data source in the report model project, I selected the "create a data source based on another object", and then selected the "create a data source based on an analysis service project". However, there is no analysis service project to select, and no browse button to see where the reporting service is looking for analysis service project either.
I have tried creating a new analysis service project with data source views, cubes, dimensions and all the stuff, but still cannot see the analysis service project in the drop down box to be selected for my reporting model project data source.
As I am fairly new to the reporting service, I'm sure I'm missing something, but couldn't find much information in the help or on the web. Any suggestion would be much appreciated.
Thank you very much,
Annie