Tag Archives: SSRS

SQL 2014 and SSRS Integrated “Hello States.rdl”

Recently I create a new lab virtual machine with SharePoint 2013 and SQL 2014 to test SSRS Integrated mode.  

 

I found the screenshot based instructions at http://rajeshagadi.blogspot.com/2013/07/installing-reporting-services-in.html even more helpful than TechNet official documentation http://msdn.microsoft.com/en-us/library/jj219068.aspx.     Both are good and make configuration easier.   Also I needed to follow the C2WTS configuration steps http://msdn.microsoft.com/en-us/library/hh231678.aspx and Kerberos steps http://technet.microsoft.com/en-us/library/gg502594.aspx to enable single sign on for Claims authentication and SSRS Integrated mode.   To enable SMTP and IMAP email routing I followed https://www.spjeff.com/2011/02/05/how-to-free-internal-sharepoint-email-imappopsmtp-inbound-and-outbound/ to configure both IIS 6.0 inbound to SharePoint and HMailServer SMTP/IMAP for end user mailboxes.

 

With SSRS online the next task was to build a sample report.  Below are the steps I followed.  

In the end we have a nice graphical USA map with data points coming from SPList.  Enjoy the screenshots!    shades_smile

 

  1. Open SP2013 Team Site
  2. Create new list named “States” with columns “Title” and “Number”
  3. QuickEdit to add a few sample rows
  4. Activate “PowerView” site collection feature
  5. Add SSRS Content Types to a library
  6. Launch Report Builder 3.0
  7. Create a map with wizard
  8. View in IE to test
  9. Schedule daily job to run report and email PDF
  10. View PDF

 

image

image

image

image

image

image

image

image

image

image

image

image

image

image

image

image

image

image

image

image

image

image

image

image

image

image

image

image

VIDEO – Tour of SSRS, PowerPivot, and PowerView

I wanted to record an introduction to the new reporting and Business Intelligence capability within SharePoint 2013.   After loading SP2013, there are a few steps to activate the backend Service Applications for BI.   The SQL 2012 install media is needed for this to install SSRS Integrated Mode and PowerPivot.

However, once those are online you can load great samples like “PowerView – Hello World Picnic” to browse real data and confirm it all works correctly.   Very cool stuff, enjoy!   Smile

References

 

SSRS-PowerPivot-PowerView from Jeff Jones on Vimeo.

VIDEO – How to test SSRS 2012 (map of US states)

Check out the below video to learn how to test SQL Server Reporting Services.  After installing to Central Admin with the steps from http://msdn.microsoft.com/en-us/library/jj219068.aspx  I wanted to see a real report to confirm successful install.    A simple SharePoint list with US state postal abbreviations connects to RDL with Map Wizard and is a great way to show the full pipeline works correctly:

  • SSRS Service Instance (on server)
  • SSRS Service Application (on farm)
  • Authentication (C2WTS or Kerberos)
  • Site Feature (PowerView Integration)
  • Site Content Types  (Report Builder Model – SMDL, Report Builder Report – RDL, Report Data Source – RSDS)
  • Report Builder 3.0  (web deploy click once)
  • RDL web rendering engine
  • Export to PDF  web actions menu

If you find this helpful, please leave a comment and I’ll try to do more videos.  Smile

 

How to test SSRS 2012 on SP 2013   (length 7:12) from Jeff Jones on Vimeo.

New Version of SSRS add-in released for SharePoint 2010

Today a new download showed up on my RSS feeds and I wanted to take a minute to compare this new MSI file against the trusty old MSI I’ve used for so long now.   As you know, the SharePoint 2010 Prerequisites installer requires “rsSharePoint.msi” to be loaded before you can install SharePoint 2010.   Below is the OLD link for SSRS which most people use today for cool stuff like a PowerShell script to download all Prerequisites for offline install. as well as the NEW link.   The release of Service Pack 2 for SQL 2008 R2 seems to be triggering lots of new downloads so it might be worthwhile to make sure you’re SharePoint front end binaries match the backend SQL server version.   If patching SQL to SP2, then it’s probably a good idea to update the web front end components and look for the “4000” version number.

 

NEW –
SP2
http://www.microsoft.com/en-us/download/details.aspx?id=62210.50.4000.07/27/201236.9 MB
OLD –
SP1
http://www.microsoft.com/en-us/download/details.aspx?id=2813210.50.2500.0011/3/201136.9 MB

 

It might be a good idea to download the most recent add-in for any new server installs.  There appear to be updates to most all files, but “sqlserverspatial.dll” grew more than most possibly hinting at better geographic data support.

 

image

 

Old Package – File Version Detail

image

 

New Package – File Version Detail

image

 

\Program Files\Common File\Mmicrosoft Shared Web\Server Extensions\14\Template\Admin\ReportServer\reportserverproperties.aspx
\Program Files\Common File\Mmicrosoft Shared Web\Server Extensions\14\Template\Admin\ReportServer\reportserversitesettings.aspx
\Program Files\Common File\Mmicrosoft Shared Web\Server Extensions\14\Template\Layouts\reportserverrsaction.aspx
\Program Files\Common File\Mmicrosoft Shared Web\Server Extensions\14\Template\Layouts\reportserverrsintegrationresults.aspx
\Program Files\Common File\Mmicrosoft Shared Web\Server Extensions\14\Template\Layouts\reportserverrsviewerpage.aspx
\Program Files\Common File\Mmicrosoft Shared Web\Server Extensions\14\Template\Layouts\reportserversitelevelsettings.aspx
\Program Files\Common File\Mmicrosoft Shared Web\Server Extensions\14\Template\Layouts\reportserverstyles1025sqlrvdefault.css
\Program Files\Common File\Mmicrosoft Shared Web\Server Extensions\14\Template\Layouts\reportserverstyles1026sqlrvdefault.css
\Program Files\Common File\Mmicrosoft Shared Web\Server Extensions\14\Template\Layouts\reportserverstyles1027sqlrvdefault.css
\Program Files\Common File\Mmicrosoft Shared Web\Server Extensions\14\Template\Layouts\reportserverstyles1028sqlrvdefault.css
\Program Files\Common File\Mmicrosoft Shared Web\Server Extensions\14\Template\Layouts\reportserverstyles1029sqlrvdefault.css
\Program Files\Common File\Mmicrosoft Shared Web\Server Extensions\14\Template\Layouts\reportserverstyles1030sqlrvdefault.css
\Program Files\Common File\Mmicrosoft Shared Web\Server Extensions\14\Template\Layouts\reportserverstyles1031sqlrvdefault.css
\Program Files\Common File\Mmicrosoft Shared Web\Server Extensions\14\Template\Layouts\reportserverstyles1032sqlrvdefault.css
\Program Files\Common File\Mmicrosoft Shared Web\Server Extensions\14\Template\Layouts\reportserverstyles1033sqlrvdefault.css
\Program Files\Common File\Mmicrosoft Shared Web\Server Extensions\14\Template\Layouts\reportserverstyles1035sqlrvdefault.css
\Program Files\Common File\Mmicrosoft Shared Web\Server Extensions\14\Template\Layouts\reportserverstyles1036sqlrvdefault.css
\Program Files\Common File\Mmicrosoft Shared Web\Server Extensions\14\Template\Layouts\reportserverstyles1037sqlrvdefault.css
\Program Files\Common File\Mmicrosoft Shared Web\Server Extensions\14\Template\Layouts\reportserverstyles1038sqlrvdefault.css
\Program Files\Common File\Mmicrosoft Shared Web\Server Extensions\14\Template\Layouts\reportserverstyles1040sqlrvdefault.css
\Program Files\Common File\Mmicrosoft Shared Web\Server Extensions\14\Template\Layouts\reportserverstyles1041sqlrvdefault.css
\Program Files\Common File\Mmicrosoft Shared Web\Server Extensions\14\Template\Layouts\reportserverstyles1042sqlrvdefault.css
\Program Files\Common File\Mmicrosoft Shared Web\Server Extensions\14\Template\Layouts\reportserverstyles1043sqlrvdefault.css
\Program Files\Common File\Mmicrosoft Shared Web\Server Extensions\14\Template\Layouts\reportserverstyles1044sqlrvdefault.css
\Program Files\Common File\Mmicrosoft Shared Web\Server Extensions\14\Template\Layouts\reportserverstyles1045sqlrvdefault.css
\Program Files\Common File\Mmicrosoft Shared Web\Server Extensions\14\Template\Layouts\reportserverstyles1046sqlrvdefault.css
\Program Files\Common File\Mmicrosoft Shared Web\Server Extensions\14\Template\Layouts\reportserverstyles1048sqlrvdefault.css
\Program Files\Common File\Mmicrosoft Shared Web\Server Extensions\14\Template\Layouts\reportserverstyles1049sqlrvdefault.css
\Program Files\Common File\Mmicrosoft Shared Web\Server Extensions\14\Template\Layouts\reportserverstyles1050sqlrvdefault.css
\Program Files\Common File\Mmicrosoft Shared Web\Server Extensions\14\Template\Layouts\reportserverstyles1051sqlrvdefault.css
\Program Files\Common File\Mmicrosoft Shared Web\Server Extensions\14\Template\Layouts\reportserverstyles1053sqlrvdefault.css
\Program Files\Common File\Mmicrosoft Shared Web\Server Extensions\14\Template\Layouts\reportserverstyles1054sqlrvdefault.css
\Program Files\Common File\Mmicrosoft Shared Web\Server Extensions\14\Template\Layouts\reportserverstyles1055sqlrvdefault.css
\Program Files\Common File\Mmicrosoft Shared Web\Server Extensions\14\Template\Layouts\reportserverstyles1058sqlrvdefault.css
\Program Files\Common File\Mmicrosoft Shared Web\Server Extensions\14\Template\Layouts\reportserverstyles1060sqlrvdefault.css
\Program Files\Common File\Mmicrosoft Shared Web\Server Extensions\14\Template\Layouts\reportserverstyles1061sqlrvdefault.css
\Program Files\Common File\Mmicrosoft Shared Web\Server Extensions\14\Template\Layouts\reportserverstyles1062sqlrvdefault.css
\Program Files\Common File\Mmicrosoft Shared Web\Server Extensions\14\Template\Layouts\reportserverstyles1063sqlrvdefault.css
\Program Files\Common File\Mmicrosoft Shared Web\Server Extensions\14\Template\Layouts\reportserverstyles1087sqlrvdefault.css
\Program Files\Common File\Mmicrosoft Shared Web\Server Extensions\14\Template\Layouts\reportserverstyles2052sqlrvdefault.css
\Program Files\Common File\Mmicrosoft Shared Web\Server Extensions\14\Template\Layouts\reportserverstyles2070sqlrvdefault.css
\Program Files\Common File\Mmicrosoft Shared Web\Server Extensions\14\Template\Layouts\reportserverstyles2074sqlrvdefault.css
\Program Files\Common File\Mmicrosoft Shared Web\Server Extensions\14\Template\Layouts\reportserverstyles3082sqlrvdefault.css
\Program files\Microsoft SQL Server\100\keyfile1033rssharepoint_keyfile.dll
\Windows\GAC\microsoft.reportingservices.sharepoint.ui.serverpages.dll
\Windows\GAC\microsoft.reportingservices.sharepoint.ui.webparts.dll
\Windows\GAC\microsoft.sqlserver.types.dll
\Windows\GAC\rssharepointsoapproxy.dll
\Windows\GAC\_32microsoft.sqlserver.types.dll
\Windows\System32\sqlserverspatial.dll
\Windows\SysWOW64\sqlserverspatial.dll

FIXED – An attempt has been made to use a data extension ‘ADS’ … with Access Services 2010

The below error might appear if you are using Access Services 2010 for the first time.  The local Access 2010 publishing wizard will create RDL and RSDS files for reports you have.   However, the SSRS server side configuration may require a minor update to enable ADS  (Access Data Server).  See http://technet.microsoft.com/en-us/library/ee662542.aspx for full details.

The instructions are pretty clear except for where to find the “rssrvpolicy.config” file.  Mine was under C:Program FilesMicrosoft SQL ServerMSRS10_50.MSSQLSERVERReporting ServicesReportServer.   Hope that helps.  If you’d like to learn Access Service 2010 on your system be sure to check out the Northwind 2010 Web Database at http://office.microsoft.com/en-us/templates/results.aspx?qu=northwind&ex=1     Smile

 

Successful Report

image

image

 

 

Error Detail

image

image

  • An error has occurred during report processing. (rsProcessingAborted)
    An attempt has been made to use a data extension ‘ADS’ that is either not registered for this report server or is not supported in this edition of Reporting Services. (rsDataExtensionNotFound)
  • An error has occurred during report processing. (rsProcessingAborted)
    The Group expression for the grouping ‘Group1’ contains an error: Request for the permission of type ‘Microsoft.Office.Access.Server.Security.AccessServicesPermission, Microsoft.Office.Access.Server.Security, Version=14.0.0.0, Culture=neutral, PublicKeyToken=71e9bce111e9429c’ failed. (rsRuntimeErrorInExpression)

Server Farm Product and Patch Status

Today I attempted to join a SQL Reporting Servers machine to an existing farm and saw the below error.  While self explanatory, I wanted to document it for others that haven’t seen this.  WSS 3.0 and MOSS 2007 were more lenient allowing a mix between the two product SKUs.  The below screen is new to 2010 and acts as a bouncer to enforce server farm consistency.  It appears to check three categories:

  • Product SKU  (Server / Foundation)
  • Patch (version number)
  • Language pack(s)

So what I’ll do next is install the full SharePoint Server product (with Dec 2010 CU slipstream on the Updates subfolder) and then try to join Config DB once again.  Smile

image

Error: Some farm products and patches were not detected on this or other servers.  If products or patches are missing locally, you must quit this program and install the required products and patches on this server before restarting this wizard. If products or patches are missing on other servers, you must install the required products and patches on the specific servers, and you may then click the Refresh button to perform the status check again.

NOTE –  You may need to run the command “Get-SPProduct -local” on each machine after applying the EXE/MSP updates for it to detect correctly in the SP Config Wizard.  

Enabling SSRS 2008 R2 on SharePoint 2010

Today I felt the need to step outside of SharePoint a little and wander over to the SQL consoles and learn something new.  Bottom line:  works the same, just more reliable.   SSRS 2008 R2 seems to me to be roughly equal on config options to what we had for SQL 2005.   Similar wizard to get it created, similar Central Admin link to give the URL, even the menu names match.

However, the whole thing “feels” more reliable.    Once complete I can clearly see the SSRS features on the Site Settings menu.   Next, I need to learn BIDS (Business Intelligence Development Studio) to create a report (RDL) and try uploading it for rendering.

 

Steps Taken

  1. Open “Reporting Services Configuration Manager” and follow wizard steps to create SSRS on SQL backend.
  2. When asked use “SharePoint integrated” mode.
  3. It will make a SQL database and IIS website.  I used my main SP web application (http://sp2010) so that final URL is http://sp2010/ReportServer/
  4. Open “SharePoint Central Administration
  5. Navigate to “General Application Settings > Reporting Services Integration
  6. Type that same URL, choose “Windows Authentication”, and provide a user account.
  7. I’m using DEMOadmin for everything which is not a best practice, but works in DEV systems to avoid any “access denied” messages.
  8. Create desktop shortcut to “C:Program FilesMicrosoft SQL ServerMSRS10_50.MSSQLSERVERReporting ServicesReportServerReportBuilderReportBuilder_3_0_0_0.application
  9. Click the desktop icon for first install
  10. Enjoy the Report Builder 3.0 application to create .RDL and upload to SharePoint

 

image

image

 

image

image

 

image

image

image

image

Return to Top ▲Return to Top ▲