Thursday, September 27, 2012

SSRS Regex Expression for SharePoint Lookup Column

Scenario
  • You have a multi-select (multivalue) lookup field in your SQL Server Reporting Services 2008 R2 report
Issue
  • The field also displays the Field IDs and you want to format it correctly
    • Ex: Value1;#5;#Value5;#6;#Value6
Resolution
  • Use the following expression
    • =System.Text.RegularExpressions.Regex.Replace(Fields!MyLookupColumn.Value, ";#\d+;#", ", ")

Tuesday, September 25, 2012

SharePoint Integrated SSRS: BIDS Deployment: Multiple Login Prompts

Scenario
  • You are using SQL Server Business Intelligence Development Studio to deploy a report project to a  SQL Server Reporting Server in SharePoint Integrated Mode
  • You are attempting to use the SharePoint Server address with "/reportserver/" or the Report Library address. 
    • Note: BIDS asks for this format which may not work: "http://<servername>/reportserver"
Issue
  • You repeatedly receive "Reporting Services Login" authentication prompts when attempting to deploy and eventually fail to deploy
Resolution
  • Use "http://servername/" or "/_layouts/ReportServer" in the TargetServerURL path in the project properties
    • Ex: "http://intranet.company.com/" or "http://intranet.company.com/_layouts/ReportServer"
    • If you use the server name, make sure to include the trailing forward slash
  • Use the Report Library/Folder location in the Target*****Folder settings
    • Note: You can use folder paths if your library contains folders. 
      • Ex: ".../Reports/Folder1/Folder2"
  • Here's an example of the settings (Right Click the project and select properties)