Error message

Deprecated function: The each() function is deprecated. This message will be suppressed on further calls in _menu_load_objects() (line 579 of /www/webvol38/16/9svvwb3gxs5tmhv/benzzon.se/public_html/includes/menu.inc).

SSRS, MS Reporting Services / Report Viewer

8 posts / 0 new
Last post
benzzon
benzzon's picture
SSRS, MS Reporting Services / Report Viewer
* Info:
Info ang ReportViewer:
http://www.gotreportviewer.com/
 
Verktyg för att skapa script, bra för att förenkla distribuering av rapporter..
 
Using an ADO.NET DataSet as a Reporting Services Data Source 
http://msdn2.microsoft.com/en-us/library/aa902651(SQL.80).aspx
 
Select the data source at run time:
 
(Programmatically Deploying a SQL Reporting Services 2005 Report)
 
VB.Net Sample on how to deploy RDL-files by Windows Forms application:
This sample is located in the Application Samples\RSExplorer Sample subdirectory of the Reporting Services samples directory.
For example:
"C:\Program Files\Microsoft SQL Server\90\Samples\Reporting Services\Application Samples\RSExplorer Sample"
(First download from http://www.codeplex.com/SqlServerSamples/Release/ProjectReleases.aspx?ReleaseId=4000, or install samples from the Start menu in the Microsoft SQL Server 2005 program group.)

*Problem 1:
Felmeddelande:

The report server cannot decrypt the symmetric key used to access sensitive or encrypted data in
a report server database. You must either restore a backup key or delete all encrypted content and
then restart the service. Check the documentation for more information. (rsReportServerDisabled)

*** Kan lösas med, för SQL 2000:
1: rskeymgmt -d
2: omstart av IIS
3: rsactivate -c "C:\Program\Microsoft SQL Server\MSSQL\Reporting Services\ReportServer\RSReportServer.config" (obs endast SQL 2000 !)
4: publicera om rapportdefinitioner..
5: kontrollera "automatisk inloggning", dvs sql kontouppgifter (http://localhost/reports)
   (Alternativet "Credentials stored securely in the report server")

*** Kan lösas med, för SQL 2005: (har ej testats..)
1. Shut down the Report Server Windows NT Service.
 
2. Run RSKEYMGMT using the -e, -f, and –p switches to extract the current symmetric key. If this is a new installation and no reports have been uploaded to the Report Server, you can skip this step.
(or use "rskeymgmt -d" to delete key.)
(rskeymgmt.exe resides in folder "C:\Program Files\Microsoft SQL Server\90\Tools\binn"..)

3. Open the RSReportServer.Config file located in the C:\Program Files\Microsoft SQL Server\MSSQL\Reporting Services\ReportServer directory
and locate the tag <InstallationID>. This value will be used in an upcoming step.
 
4. Verify that the user who is going to initiate the activation process for the Report Server is logged on to
the Reporting Services/IIS machine as someone who has Local Admin credentials.
 
5. Run RSKEYMGMT using the -r switch, and pass the value that you found in the RSReportServer.Config file in
step 3 of this procedure. Please note that this operation will stop and restart the Report Server Windows NT Service and IIS.
 
6. Examine the Report Service Windows NT Service log files to make sure that the service successfully activated.
You can also check this by examining the Keys table to verify that there is a row where Client = 1 and the Public
and Symmetric columns return non-null values.
 
7. Ask someone with Local Admin permissions on the IIS machine to browse to http://localhost/reportserver to force
the Web Service to activate. This will activate the Report Server Web Service.
 
8. Examine the Report Server Web Service log files to make sure that the service successfully activated.
You can also check this by examining the Keys table to verify that there is a row where Client = 0 and the Public
and Symmetric columns return non-null values.
 
9. If you used step 2 of this procedure to back up the symmetric key, reapply the symmetric key using RSKEYMGMT
and the -a, -f, and –p switches, where the value for –f is the file created in step 2 and –p is the same password used in step 2.
 
 
 
 
*Problem 2:
 
Fel som fås från Visual Studio 2003 (vid öppning av .sln) när endast Reporting Services från SQL 2005 installerats (förmodligen behöver SQL 2000 Reporting Services också installeras..)
 
The application for project 'C:\Doc-cust\ABB Fastighet\CustoR\rsabb_solution\abbrpt1.rptproj' is not installed.

Make sure the application for the project type (.rptproj) is installed.

Artikel ang setup av Reporting Services:
http://msdn2.microsoft.com/en-us/library/ms143520.aspx


* SQL 2000 / SQL 2005, saxat ifrån http://persalmi.net/
(se även: http://support.microsoft.com/kb/913048)

 

Idag var det dags att uppdatera en äldre rapportdefinition som designats i Visual Studio.Net 2003 och publiceras på SQL Server 2000 med tillägg för Reporting Services. Eftersom jag för tillfället har vissa problem med VS 2003 installationen på min dator så bestämde jag mig för att göra ett försök i Visual Studio 2005.

Tyvärr kan man inte använda VS 2005 för att designa rapporter som är fullständigt kompatibla med RDL-formatet i SQL 2000 versionen av Reporting Services, vissa begränsningar och viss handpåläggning måste till för att publicera rapporterna. Rapportdesignern i VS 2005 har utökad syntax för det nyare RDL-format som stöds av SQL Server 2005, så Report Manager applikationen i SQL2K gillar inte RDL-filerna som produceras av VS 2005. Därför fungerar inte t. ex. inte "Deploy" direkt från Visual Studio.

En del tricks finns dock att ta till för att komma runt dessa begränsningar.

  • Använd bara funktioner som fanns i Reporting Services på SQL2K, använder man nyare funktioner kommer det garanterat inte att fungera.
  • Använd inte VS2005 funktionen "Deploy" för att publicera rapporten, spara den istället som RDL-fil.
  • Öppna rapporten (RDL-filen) antingen som Xml kod i VS eller med en annan textredigerare och ändra Xml namnrymdreferensen i <Report>-taggen enligt detta:

<Report xmlns="http://schemas.microsoft.com/sqlserver/reporting/2005/01/reportdefinition"
xmlns:rd="http://schemas.microsoft.com/SQLServer/reporting/reportdesigner">

Ändra till:

<Report xmlns="http://schemas.microsoft.com/sqlserver/reporting/2003/10/reportdefinition"
xmlns:rd="http://schemas.microsoft.com/SQLServer/reporting/reportdesigner">

  • Ta bort rader som innehåller taggarna <InteractiveWidth> och <InteractiveHeight>, de är inte kompatibla med RS 2000 men genereras av VS2005. Såg ut så här i mitt fall:

...
<InteractiveWidth>8.5in</InteractiveWidth>
...
<InteractiveHeight>11in</InteractiveHeight>
...

Publicera rapporten från Report Manager applikationen genom att ladda upp RDL-filen.

Report Manager-applikationen är relativt väluppfostrad och talar om vilka taggar den inte har stöd för så om du kan ladda upp rapporten utan felmeddelanden kommer den med största sannolikhet att fungera korrekt.

Alternativ till Reporting Services, Jasper Reports: http://community.jaspersoft.com/project/jasperreports-server

 
benzzon
benzzon's picture
.

Setup med "Remote Report Server Database", bland annat:

Installing SQL Server Reporting Services: http://msdn2.microsoft.com/en-us/library/ms143736.aspx

Reporting Services Installation How-to Topics: http://msdn2.microsoft.com/en-us/library/ms143757.aspx

Setting Up Windows Service Accounts: http://msdn2.microsoft.com/en-us/library/ms143504.aspx

SQL Server 2000 Reporting Services Deployment Guide: http://www.microsoft.com/technet/prodtechnol/sql/2000/deploy/rsdepgd.mspx

 
 
*** Prestandatips:
If performance is slow on the first web service access after there have not been any
accesses for a certain time period, disable the idle timeout on the Performance tab in
the Application Pool in IIS Manager.
 
benzzon
benzzon's picture
.
*** How to determine Reporting Services Service Pack version:
 
For SQL 2000, the version information is located at the bottom
of the main page of your Report Server. (url "reportserver").

RTM: 8.00.743.00
SP1  : 8.00.878.00
SP2  : 8.00.1038.00
 
The edition information is stored in the following registry key:
HKLM\SOFTWARE\Microsoft\Microsoft  SQL Server\80\Reporting
Services\ProductCode

Standard:   {B19FEFE7-069D-4FC4-8FDF-19661EAB6CE4}
Enterprise: {33FE9EED-1976-4A51-A7AF-332D9BBB9400}
Developer: {2879CA50-1599-4F4B-B9EC-1110C1094C16}
Evaluation: {7C93251A-BFB4-4EB8-A57C-81B875BB12E4}

 
benzzon
benzzon's picture
.
SQL 2005 "Initial Service Pack 2:"
"Microsoft SQL Server Reporting Services Version 9.00.3042.00"
 
SQL 2005 Service Pack 2:
 "Microsoft SQL Server Reporting Services Version 9.00.3054.00"
 
 
benzzon
benzzon's picture
.
Q1: When trying to use "export to excel" an error is displayed as below.
"Reporting Services Error
An error occurred during rendering of the report. (rrRenderingError)
An error occurred during rendering of the report.
Object reference not set to an instance of an object."
 
A1: This could happen if report contains two tables..
If the 2:nd table has a starting "page break", and the first table is hidden.
(If the first table is visible, it should be possible to export to excel..)
 
 
Q2: When trying to access web-admin "reports" url, an error is displayed as below.
"The attempt to connect to the report server failed. 
Check your connection information and that the report server is a compatible version."
(reporting services log-fil mentions "MissingEndpointException"..)
 
A2: Could be a problem with binding for the web site in IIS..
Resolution: Select "Default Web Site" in IIS and add/specify "Site Binding" with type "http" and
specify a host name as "localhost".
Link to info: http://carlos290.vox.com/library/post/the-attempt-to-connect-to-the-report-server-failed-check-your-connection-information-and-that.html
benzzon
benzzon's picture
.
benzzon
benzzon's picture
.

Vid användning av ReportViewer-kontrollen i web-lösningar så får man se upp med "beroende" av motsvarande SQL Server version..

För Visual Studio 2010 så är "v10" default, och den funkar enbart mot SQL Server 2008..
Mer info på följande länk:

http://smehrozalam.wordpress.com/2010/08/11/reportviewer-control-look-out-for-appropriate-version-when-using-in-server-mode-remote-processing/

How to write SSRS report into server folder as Excel File
http://forums.asp.net/t/1746293.aspx?How+to+write+SSRS+report+into+server+folder+as+Excel+File
 

benzzon
benzzon's picture
SSRS - Subscriptions..

Search-words: schedule subscription

Web Service URL: http://cqvm0107/ReportServer_k8/ReportService2006.asmx

Subscriptions and Delivery: http://msdn.microsoft.com/en-us/library/ms159762.aspx
Programmatically playing with SSRS Subscriptions: http://www.codeproject.com/Articles/36009/Programmatically-Playing-With-SSRS-Subscriptions

Programmatically Create Data Driven Subscriptions in SQL Server 2005/2008
http://www.sqlservercurry.com/2009/07/programmatically-create-data-driven.html

How to create a sample application that uses the Reporting Services SOAP APIs to render a report to a selected file format on a Web page
http://support.microsoft.com/kb/875447
http://www.codeproject.com/Articles/15555/Generating-PDF-reports-programmatically-using-SQL

-- *** Show SSRS subscriptions
SELECT
c.Name AS ReportName, c.ModifiedDate, rs.ScheduleID AS JOB_NAME,
s.[Description], s.SubscriptionID, s.LastStatus, s.LastRunTime
FROM
ReportServer..[Catalog] c
 JOIN ReportServer..Subscriptions s ON c.ItemID = s.Report_OID
 JOIN ReportServer..ReportSchedule rs
 ON (c.ItemID = rs.ReportID AND rs.SubscriptionID = s.SubscriptionID)

*** Manually start subscription by SQL or by "ReportingService":

EXEC msdb..sp_start_job @job_name = 'guid-value-of-subscription-scheduleid'

Dim rs As New ReportingService()
rs.Credentials = System.Net.CredentialCache.DefaultCredentials
rs.FireEvent("TimedSubscription", "guid-value-of-subscription-scheduleid")