I have recently installed Reporting Services Integrated mode with minor issues and at the end I was able to execute reports as well as add the content types.
I am now working on doing the same exact thing except I do not see Report Server Content types. I am able to upload a report and execute it but cannot add the Content Types. When I login into Central Administrator I see the content type in there. Is there something I am missing to add it to "My Site"
We have reports in SharePoint integrated mode which are really slow when compared to native mode. I have been asked to research and give info on what exactly causes the delays.
Any articles which give me information as to what happens when a report is run from SharePoint server and where does it log.
We have a Report Server integrated with SharePoint. A month ago we deployed a report (rdl file) in SharePoint, but the person who deployed it was no longer connected with us. So we decided to deactivate it in our Active Directory. Yesterday, our users returned issues regarding they are unable to access their report. I believe the issue was "object reference not set to an instance of an object".
So I tried to download the rdl file and redeployed it but i am getting error upon deploying to SharePoint. My workaround is to delete the rdl file in SharePoint, redeploy the shared datasource and shared datasets and the rdl file. It went and was now able to run the report in SharePoint.
Is there something relation/happened when deactivating the account in AD that users unable to access the report in SharePoint?
i have created an report which retrieve data from table which contains a datetime field. The data of that particular field is stored in UTC time. when i navigating the report from sharepoint site i wish the datetime field will be converted into local time based on the selected timezone of the user.
issue where SSRS reports being executed from a report library in SharePoint intermittently are reloaded after hitting the apply button to run. Â We are using SSRS 2010 and SharePoint 2013 in integrated mode. So a user clicks on the report, fills in the parameters and hits apply. Maybe 70% of the time the report runs fine. Â The other 30% the report just reloads and wipes out any parameter selections. We do have a load balancer in front of the web servers.
I've ran fiddler to capture some of the error logging.
Things we have tried. Multiple versions of IE 8,9,10,11, Firefox, Chrome. Â All browsers seem to exhibit the behavior.
We ran so more traffic monitoring and it doesn't appear on these refreshes that report actually connects to the data context so it seems to be failing prior to that. (Possibly at the SharePoint server. Logging from the server apparently didn't show much in terms of when this occurred) It doesn't seem to matter if the report has 1 or 10 parameters.Â
We did notice that when we took the load balancer out of the equation and just hit the web server directly, the refreshes "appeared" to decrease in regularity but still occur.Also, the reports do not exhibit the refresh issue when running locally within Visual Studio 2013.
I am receiving the following error when I run the report builder query. I am able to successfully select the dimensions and measures that I am intrested in but when I run the report I get the error message.
The 'PerspectiveID' custom property for the 'query' perspective is either not set or is not set to the string data type. ---------------------------- Semantic query execution failed. ---------------------------- Query execution failed for data set 'dataSet'. ---------------------------- An error has occurred during report processing.
I am testing this against both Adventureworks standard and enterprise cubes. Additionality I saw a post in this forum http://forums.microsoft.com/MSDN/ShowPost.aspx?PostID=368962&SiteID=1 that seemed related but I am using reporting services in sharepoint integrated mode so I am having a hard time trying to troubleshoot this.
We have SSRS reports (pointing to SQL 2012) containing cascading parameters that we have deployed on SharePoint 2013 and once a user makes a selection to the parent parameter, child parameters are getting refreshed based on the selection in the parent parameter and then we see this:
Once we hit Apply, the Loading image comes up and approximately10 seconds later, The whole webpage gets refreshed and the parameter selections get reset to default parameters.Â
This is getting frustrating because there are about 10 parameters in the report and once a user makes selections and hit apply if the page gets reset, the user has to make all the selections again.
We are working with Microsoft support on this who suggested us to increase the timeout setting of DistributedCache service on all our SharePoint servers which did seem to work initially but I do see this issue happening occasionally.
I wonder where in Reporting Services Configuration i can change the mode to Share Point Integrated? Do i need to create a new database, or can i change the mode for the one i use?
Up until an hour ago, I was using the Sql Server Reporting Services Report Viewer webpart in Sharepoint with forms authentication, and was not having an issue. The only thing that I did (or remember doing) before things went sour was upload a new report to the document list and since then whenever I try to access a report, manage the data source on a report, change the report URL in the webpart, I get the following message:
An unexpected error occurred while connecting to the report server. Verify that the report server is available and configured for SharePoint integrated mode. --> The request failed with the error message: -- <html><head><title>Object moved</title></head><body> <h2>Object moved to <a href="/_layouts/login.aspx?ReturnUrl=%2f_vti_bin%2fReportServer%2fReportService2006.asmx">here</a>.</h2> </body></html>
--.
I found some related posts and have tried the following, but none have helped:
Removed all reports and reuploaded only the one that was definitely working. I changed the application pool for the report server to be the same app pool as Sharepoint (runs under domain admin account). I changed the application pool for the report server back to it's original (which the Reporting Services Configuration prefers - was running under Network Service acct before the problem started, now running under domain admin acct) I can browse to http://server/reportserver. I'm running under forms authentication, which had been working. I can access the three configs for Reporting Services under Application Management in Central Admin - I've tried changing the settings. I can access http://server/_vti_bin/reportserver/reportservice2006.asmx as well as http://server/_vti_bin/reportserver/reportserviceauthentication.asmx (that was suggested in one post) I've restarted IIS numerous times, and also finally rebooted the server. I've created a new document list and set it for report content, just like the other list.
I also just noticed that even though I've set up my admin account as a site collection administrator, I now no longer see my report options under the New button in the document library (both the old and new). I don't know if this is related.
I'm sure that this is a case of something minor that I did that I forgot about. Any suggestions or other troubleshooting that I can do?
I am trying to run a Reporting services report and pass a parameter using url access and SP Integrated mode.
I have managed to come up with the following: http://DashBrdRptSite/reportserver?http://npvmdashbrd1:2764/Reports/dashbrdrptstore/real%20time%20running/test.rdl&rs:Command=Render&Year=2006&Year=2007&Month=1
This works perfect as long as I run it from the actual server that has Sharepoint and reporting services installed.
If I try to run it from my desktop, I get prompted for a user name and password. And now matter how many times I enter my user/pass... it does not work.
If I just run the sharepoint link that appears on the report when I run it from within sharepoint... such as
That runs no problem no matter where I run it from (my desktop or the Sharepoint Server), but it looks like I cannot pass it parameters. I guess becuase it is useing RSViewerPage.aspx.
I'm running MOSS 2007 in Integrated Mode, and I have a number of reports that are deployed to the Reports Library, and are working just fine when you go there and run them interactively.
Now however, I need to address the report via a URL, and pass it a parameter. Just to add a bit more pain, the report is in mdx, and expects an mdx parameter.
Here is the parameter "prm_cost_centre":
[DIM LEARNER AIM].[COST CENTRE DESC].&[Business]
Here is the URL of the report:
http://vmmoss:88/ReportsLibrary/Report1.rdl
(If you hit that URL as is, it runs fine, using the default parameter)
Now, I know that we have to escape the ampersand, so I'm thinking that this URL should do it.
http://vmmoss:88/ReportsLibrary/Report1.rdl&rs:Command=Render&prm_cost_centre=[DIM LEARNER AIM].[COST CENTRE DESC].%26[Business]
But it doesn't - It throws a 400 Bad Request error.
I know I must be very close, but I just can't work out what URL I need to pass in order to get the damn thing to run.
I have a report in which the jump to URL always works in Preview mode in Visual Studio. However, when I publish it to Sharepoint the link does not seem to want to render.
Most of the time if the link is short like http://server.domain.com it works fine.
When I try ="http://servername/projectcontrols/reports/Pages/PhaseExpenditureBreakdown.aspx?Phase="&Fields!PhaseName.Value as the expression it never works in Sharepoint, but does work in Preview Mode.
If I try ="http://"&Fields!PhaseName.Value then it renders the URL in Sharepoint.
We have been successful in creating a custom Excel Renderer that works very well on a server configured for SSRS Native mode. However we have another server that is configured to work in SSRS integrated mode that we can not get this custom renderer to work properly. The steps we follow are:
After the report results are displayed we select Actions -> Export -> Excel Custom . So I can see the name of our custom rendering type. After a very short amount of time (no more than a second or 2) I get a frame that says: an unexpected error has occurred. Here is the URL of the web site:
Is there anything that has to be done differently to get a custom renderer to work in SharePoint integrated mode?
Note: here is the article we followed to initially create the renderer that will work in SSRS native mode: http://msdn.microsoft.com/msdnmag/issues/05/02/CustomRenderers/
I'm deploying a performance point site which includes several parameterized SSRS reports. Reporting services is configured to run in Sharepoint integrated mode.
I'd like to pass the parms to certain reports via URL posts when a user clicks on certain fields in a scorecard.
I can setup the basic URL to open a report, as follows:
When this URL is executed, the sharepoint site redirects with the following URL, which opens the report and prompts for a text date paramater (DatePrompt) in the format dd-mmm-yy (i.e. 01-Sep-07)
I'm working with the Dec CTP of SQL 2005 SP2 and the associated Sharepoint addin.
I've been able to get a report created in VS2005 and manually uploaded to the Document Library holding reports, and the report works when I open it (using an internal datasource, not shared).
My first problem is I can't get VS2005 to deploy datasources or reports to my doc library. I have configured TargetDataSourceFolder http://server/site/doclib TargetReportFolder http://server/site/doclib TargetServerURL http://server/
When I try to deploy my Shared Data Source, it asks me to login, and will not accept my credentials (I am farm administrator, and site collection administrator, and local administrator, and a site owner)
Secondly, for the report I manually uploaded, I can't edit it in Report Builder (from Sharepoint) because I get stuck on entering something that works into the Select Site or Server dialog box. I've tried http://server/ and http://server/site/ and http://server/site/doclib
I am trying to follow the instructions on: http://technet.microsoft.com/en-us/library/bb677365.aspx they contain many "if"s and links to other sites and I got totally confused and stuck. The idea is - I need to have one machine with SQL for reporting services and SharePoint databases and the reporting services and the other machine for a complete SharePoint installation (the main server of the farm)
Now I have done the following: two servers (lets call them SQL and MOSS)
The domain contains the following users: sqlservices: for running all the SQL services and reporting services moss: for administering MOSS databases moss-services: for running all the MOSS services Machine SQL - installed Windows Server 2003 Standard Edition SP1, joined to the domain as SQL The following is done from a local Administrator account: IIS 6 with ASP.NET installed SQL Server 2005 with SP2, Reporting services installed but not configured all the services are running as domainsqlservices .NET 3.0 installed http://support.microsoft.com/kb/918642 fix installed done WINDOWSMicrosoft.NETFrameworkv2.0.50727aspnet_regiis €“i MOSS as Web Front installed, not configured
Machine MOSS - installed Windows Server 2003 Standard Edition SP1, joined to the domain as MOSS The following is done from a local Administrator account: IIS 6 with ASP.NET installed .NET 3.0 installed http://support.microsoft.com/kb/918642 fix installed done WINDOWSMicrosoft.NETFrameworkv2.0.50727aspnet_regiis €“i Computers->My computer->DCOM Config->IIS WAMREG admin Service->PropertiesSecurity->Customize->Edit, added domainmoss-services and domainMOSS users with rights Local launch, Local activate MOSS Complete installed, configured for: New server farm Database server: SQL Username: domainMOSS Port: Default Auth: NTLM SharePointRS.msi installed to enable RS in SharePoint Admin page Then temporarily gave to the user domainMOSS sysadmin rights in the SQL server on the SQL machine to avoid errors with creating databases while configuring SharePoint from the Admin Portal. From the Admin Portal configured Office SharePoint Server Search with domainmoss-services account Shared Services Administration €“ New SSP created with 2 separate web apps for SSP and MySites, both app pools run as domainmoss-services and SSP itself runs as domainmoss-services Windows SharePoint Services Search configured, Service Account - domainmoss-services, Content Access Account - domainmoss-services
Everything went OK.
Now came back to the machine SQL: run Reporting Services Configuration and: Report Server Virtual Directory €“ created new, called it ReportServer. When hit Apply, the applicatin suddenly quit, but I checked €“ the Virtual Directory was registered on the IIS and working. Checked if Windows service identity is domainsqlservices, OK. Web service identity: Application Pool: New Name: ReportServer Windows Account: domainsqlservices Apply, everything went fine.
Database setup: choose server SQL, Connect Database name: New name: ReportServer, checked Create the report server database in SharePoint Integrated Mode OK Credeantials Type - Service credentials (I guess it means previously set domainsqlservices will be used) Apply,OK, everything went fine.
Launch SharePoint Products and Technologies Configuration Wizard, Connect to an existing farm, Database server: SQL, Retrieve datbase names: selected SharePoint_Config, Username: domainMOSS, Next, everything fine here.
Now as far as I understand I need to give reporting services an access to SharePoint databases? Went to the machine MOSS, launched SharePoint 3.0 Central Administration:
Application Management-> Reporting Services-> Manage integration settings, URL: http://SQL/reportserver, Windows Authentification, OK then back, Grant database access-> Server Name: SQL, OK, now it is asking for credentials. And now the problem: no matter what I enter (domainsqlservices, domainMOSS) I get a message: €žA connection to the computer cannot be established€?. Why, why, why? I can ping the server, SharePoint services are running fine and they are using the SQL server on the SQL machine and there are no errors in the event viewer (they should be if there were any problems to connect the SQL databases). If I enter wrong credentials, I receive "Unable to log on with the given username and password.", so I guess there IS a connection to the computer (or how else could SharePoint determne that login is wrong?).
Also, if I open http://SQL/reportserver, I get €žThe report server has encountered a configuration error€? and the log in C:Program FilesMicrosoft SQL ServerMSSQL.2Reporting ServicesLogFiles says €žSharePoint content service is null. Report Server may not have joined the SharePoint farm, or Report Server service account may not have been granted access to farm.€? I guess it is OK while I have not granted access to the SharePoint databases? Please, show me, what did I do so wrong that €žA connection to the computer cannot be established€?? How can I grant the access and finalize configuring Reporting Services? Thanks.
Having finally managed to get over the hump of getting Sharepoint integrated mode working in a farm install, I am having a problem deploying reports from Visual Studio to Sharepoint.
Visual Studio is updated to Service Pack 2, as is of course the report server.
The reports APPEAR to deploy - they actually appear in the document library after deployment! But the result is:
------ Build started: Project: Harvest Reports, Configuration: Debug ------ Build complete -- 0 errors, 0 warnings ------ Deploy started: Project: Harvest Reports, Configuration: Debug ------ Deploying to http://ppdwn-bl10:8888/ Deploying data source 'http://ppdwn-bl10:8888/ReportTest/Data Sources/ReportWarehouse.rsds'. Report Server has encountered a SharePoint error. Deploying report 'http://ppdwn-bl10:8888/ReportTest/Harvest Reports/ColourBreakdown.rdl'. Report Server has encountered a SharePoint error. Deploying report 'http://ppdwn-bl10:8888/ReportTest/Harvest Reports/ColourBreakdownSaleable.rdl'. Report Server has encountered a SharePoint error. Deploying report 'http://ppdwn-bl10:8888/ReportTest/Harvest Reports/FaceBreakdown.rdl'. Report Server has encountered a SharePoint error. Deploying report 'http://ppdwn-bl10:8888/ReportTest/Harvest Reports/LustreBreakdown.rdl'. Report Server has encountered a SharePoint error. Deploying resource 'http://ppdwn-bl10:8888/ReportTest/Harvest Reports/paspaley.gif'. Report Server has encountered a SharePoint error. Deploying report 'http://ppdwn-bl10:8888/ReportTest/Harvest Reports/ShapeBreakdownByPercentage.rdl'. Report Server has encountered a SharePoint error. Deploying report 'http://ppdwn-bl10:8888/ReportTest/Harvest Reports/ShapeBreakdownByPM.rdl'. Report Server has encountered a SharePoint error. Deploying report 'http://ppdwn-bl10:8888/ReportTest/Harvest Reports/Summary.rdl'. Report Server has encountered a SharePoint error. Deploy complete -- 9 errors, 0 warnings ========== Build: 1 succeeded or up-to-date, 0 failed, 0 skipped ========== ========== Deploy: 0 succeeded, 1 failed, 0 skipped ==========
Unfortunately I cannot view the reports in Sharepoint - I can see them in the document library but when I click them I get the same error, except in the web browser. "Report Server has encountered a SharePoint error".
The Report Server log shows the following:
w3wp!library!1!03/06/2007-09:38:24:: Call to GetPermissionsAction(http://ppdwn-bl10:8888/ReportTest/Harvest Reports/ColourBreakdown.rdl). w3wp!library!1!03/06/2007-09:38:24:: Call to ListParentsAction(http://ppdwn-bl10:8888/ReportTest/Harvest Reports/ColourBreakdown.rdl). w3wp!security!a!03/06/2007-09:38:24:: i INFO: Exception while running with elevated privileges w3wp!security!a!03/06/2007-09:38:24:: i INFO: Access is denied. (Exception from HRESULT: 0x80070005 (E_ACCESSDENIED)) w3wp!library!a!03/06/2007-09:38:24:: e ERROR: Throwing Microsoft.ReportingServices.Diagnostics.Utilities.SharePointException: Report Server has encountered a SharePoint error., ; Info: Microsoft.ReportingServices.Diagnostics.Utilities.SharePointException: Report Server has encountered a SharePoint error. w3wp!security!a!03/06/2007-09:38:25:: i INFO: Exception while running with elevated privileges w3wp!security!a!03/06/2007-09:38:25:: i INFO: Access is denied. (Exception from HRESULT: 0x80070005 (E_ACCESSDENIED)) w3wp!library!a!03/06/2007-09:38:25:: e ERROR: Throwing Microsoft.ReportingServices.Diagnostics.Utilities.SharePointException: Report Server has encountered a SharePoint error., ; Info: Microsoft.ReportingServices.Diagnostics.Utilities.SharePointException: Report Server has encountered a SharePoint error.
I encounter this error when user try to subscribe using email delivery to one of the report. I have configured the Reporting Services to use Sharepoint integrated mode. This user is a regular user that has read access to the report item.
Below is the message detail:
A subscription delivery error has occurred. (rsDeliveryError) A subscription delivery error has occurred. (rsDeliveryError) One of the extension parameters is not valid for the following reason: The account you are using does not have administrator privileges. A subscription cannot be created for [emailaddress]. (rsInvalidExtensionParameter)
Is it true that user needs to have administrator privilege in order to subscribe for report? I don't think so, right?
I got the following exception from the code listed below:
System.Web.Services.Protocols.SoapException occurred Message="The operation is not supported on a report server that is configured to run in SharePoint integrated mode. ---> The operation is not supported on a report server that is configured to run in SharePoint integrated mode." Source="System.Web.Services" Actor="http://haileizws32b:8080/ReportServer/ReportService2005.asmx" Lang="" Node="http://haileizws32b:8080/ReportServer/ReportService2005.asmx" Role="" StackTrace: at System.Web.Services.Protocols.SoapHttpClientProtocol.ReadResponse(SoapClientMessage message, WebResponse response, Stream responseStream, Boolean asyncCall) at System.Web.Services.Protocols.SoapHttpClientProtocol.Invoke(String methodName, Object[] parameters) at Microsoft.PerformancePoint.Planning.Client.Common.ReportServer.ReportingServices2005.ListChildren(String Item, Boolean Recursive) in c:Officedev14ppsplanningclientCommonReportingServices2005.cs:line 1001 at Microsoft.PerformancePoint.Planning.Client.Common.DataAgent.RetrieveDirectoryStructureFromReportingServices(Object caller, Object parameters) in c:Officedev14ppsplanningclientCommonAgentsDataAgent.cs:line 2027 InnerException:
The code is like:
ReportingServices2005 reportingServices = new ReportingServices2005();
Well, I have SQL Server 2005 SP2 Reporting Services installed and configured in sharepoint integrated mode, so I am deploying reports in sharepoint libraries. Also I configured sharepoint to use forms authentication with builtin AspNetSqlMembership provider, but after this action I couldnot open reports through sharepoint library, instead of this, error was displayed mentioned below. I searched and found hotfix Cumulative Update 3 build 3186 for this issue, request it from microsoft support and applied it, but nothing has changed!
Error I have been experiencing is following:
An unexpected error occurred while connecting to the report server. Verify that the report server is available and configured for SharePoint integrated mode. --> The request failed with the error message: -- <html><head><title>Object moved</title></head><body> <h2>Object moved to <a href="/_layouts/login.aspx?ReturnUrl= %2f_vti_bin %2fReportServer%2fReportService2006.asmx">here</a>.</h2> </body></html>
I continued to dig and find out that this hotfix should update Microsoft.ReportingServices.SharePoint.UI.WebParts.dll from version 9.0.3042.0 to new version 9.0.3180.0, but when I checked file version after applying hotfix, it remained the same as was before i.e. 9.0.3042.0. Apparently this cumulative update does not fix this issue. anyone has any idea how to solve it? Thanks in advance.
We are researching a SSRS and SharePoint integrated mode install and I have a question about report development. When SSRS is installed as integrated mode, there is no report sever to publish to and the reports are published to document libraries. What do you do when developing reports in Visual Studio? How do I publish the new reports?
I saw a white paper what seemed to suggest that you have to do report development in a native reporting services environment and re-publish to SharePoint. Is that true?
does anyone know, when will the functionality of linked reports, my reports, data-driven subscriptions in MOSS integrated mode be enabled? Now it is according to Microsoft not supported. Will this be supported in the future ?Thanks.
I have configured Reporting services to use MOSS integrated mode. I have two servers, one for MOSS and the other for SQL database and reporting server.
Since I am using NTLM for IIS authentication, I can only use "Trusted Account" for Reporting Service Integration.
Then I created a report in SQL Server BI development studio and deployed to a MOSS document library. The deployment was successful. However, when I tried to open the report, I got the following error message:
An error has occurred during report processing. This data source is configured to use Windows integrated security. Windows integrated security is either disabled for this report server or your report server is using Trusted Account mode.
I have tried to switch to "Windows Authentication" for Reporting Service Integration, but this will give error for "Set Server Defaults" screen. Also the deployment from BI studio to MOSS will fail with authentication error. So I think I have to stay with "Trusted Account" option there.
What should I do to make the report accessible while using "Trusted Account"? Is there any configuration which has to be done for this purpose? Any input is appreciated.
I am setting up sharepoint and sql server integration environment. I am considering the following topology: PowerPivot for SharePoint 2013 and Reporting Services in SharePoint mode Two Server Deployment
[URL]
I am looking to follow the topology example by the letter, which involves installing PowerPivot for SharePoint (aka SSAS in SharePoint mode) in the same server as my SQL, and installing SSRS in SharePoint (SP) integrated mode in the same server as SharePoint.
I understand, however, that if I wanted to install SSRS in SP mode in the same server as SQL, I could but only if the server contains the SP Object Model.
My first question is, what would involve having the SP Object Model in the SQL Server?
Would only installing SP binaries be enough; or Do I need to do a minimal install of SP in the SQL server enough for it to joing the SP farm? And most importantly, what would be the licensing implications for SP in case I want proceed down this route and have SSRS in SharePoint mode installed in the same server as the SQL?
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.
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)
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
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
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.
Is it possible to connect to the Report Viewer web part in SharePoint? This is the SharePoint integrated guy. I am displaying a report. I'd like to have links in the report that cause a sub-report or even a custom web page to open in another web part on the same page.
I am trying to deploy reports through visual studio. In the Target server URL, specified the url of the report server as http://<localhost>:<portnumber>/reportserver/Shared Documents/. But When i deploy it asks for username and password. when i provide them, again login screen appears. Finally deployment fails when i press cancel.
I have an installation with Reporting Services in integrated mode with WSS 3.0, with Reporting Services Add-in for SharePoint.
I have a document library in witch I added a data source to an SSAS database and a Report model on top of this. When I click Add a Report with Report Builder, the report builder opens, but it fails to connect to the model.
There is a dialog window "Select Site or Server "asking for a URL. I put in there http://localhost, but is not working. I am not able to connect to the report model.
I will appreciate any help on what could be the reason or how can I investigate this.
Thanks,
Dan
P.S.
I did the same scenario on the development environment it works from start, without any problems. But now on production €¦.
hello good afternoon, I am having this issue, I need to move reporting services from one server to another, but I need that credentials, roles and reports stay the same. But as the reporting services is integrated with sharepoint, dont know if I can move both at the same or it has to be done one at a time. this is just data migration, but still I will like to Know how it can be done having the same structure with the roles and credentials. or it is prefered to do a ghost?? but the issue will be that we have the reporting services in one place and the data in another... don´t know how this will affect. thanks in advanced for the help.
1. Filter Web Part that uses a BDC-driven list of clients
2. Part showing RDL's from a doclib
3. Report viewer Web Part
I can use the Filter part to supply a parameter value to 3. when I select an RDL manually, I can set the Viewer connections to get the Report Definition from 2. But I can't get both options to work at the same time. The reports in 2. all have the same Parameter so there shouldn't really be a problem.
Looking forward to any suggestions or a way to provide feedback on the CTP (RS SharePoint Addin).
We have started to use reporting services in integrated mode with windows sharepoint. Since this installation the drillthrough reports have stopped working. We get the following error when a hyperlink is cllicked:-
The item 'http://vtrends/reports/IslandRegionCountryState' cannot be found. (rsItemNotFound)
In the textbox properties we have set the 'Jump to Report' hyperlink option to use a condition. Based on the link clicked, one of two reports will be displayed. If i remove this condition and manually select the report to run then there are no problems. This however is not the functionality that is needed. Any help will be appreciated.
