SSRS instance name issue and how to change a SQL Server instance name

Recently we are working on installing DPM (a Microsoft product can protect server data during the server upgrading). It required a SSRS to be installed as the reporting server. However, when the SSRS instance name being entered into the installation window, an error popup said instance cannot be found. As this installation actually looking for by WMI tool, so we launch the windows management instrumentation tester (WMI Tester) by run “wbemtest.exe”. Enter the SSRS path as “\\SSRSServerName\root\microsoft\sqlserver\reportserver”, then connected without problem, then run the query: “SELECT * from __namespace”, it list the SSRS instance name, but the weird thing is our instance real name is “SSRSServer_Name”, but the name on the list is “SSRSServer_f5Name”, that’s why the installer cannot find the instance….

After a day, we suddenly realized that SQL Server instance name doesn’t allow “_”, it seems there is no any problem for regular usage even you use the underscore except in some rare case which we met luckily.

So the solution is change the instance name to exclude the underscore then.

STEP 1: running these two queries:

sp_helpserver  
select @@servername

STEP 2: get the network name

  1. Run this in Microsoft SQL Server Management Studio:
    sp_dropserver 'old_name'  
    go  
    sp_addserver 'new_name','local'  
    go
  2. Restart SQL Server service.

Then after that is done run this again, to make sure everything is changed:

sp_helpserver  
select @@servername
Advertisements

How to reference parameter label display instead of the value in SSRS

We have a SSRS report which contains a dataset which has an ID and NAME field, then a parameter was created and set its value to ID from dataset and Label to NAME of dataset. The label can be displayed in preview report and select it, but the selected parameter label display also needs to show on the report, if just use Parameters!Parametername.Value, it just shows the value or ID directly. So the correct way is as below.

Case when the parameter is Single Value :

=Parameters!<ParameterName>.Label

Case when the parameter is Multi Values :

=Parameters!<ParameterName>.Label(0)

OR

=Join(Parameters!<MultivalueParameterName>.Label,", ")

SSRS report url examples

Recently, one report project required to use url to show a SSRS report directly from a third party application, it may need to open a pdf directly, or hide the toolbar, parameter etc.

PDF version:
http://Server/ReportServer/Pages/ReportViewer.aspx?/My Reports Folder/My report&parameter1=5&parameter2=2002/01/01&rs:Command=Render&rs:Format=pdf

Report open without toolbar and parameter
http://Server/ReportServer/Pages/ReportViewer.aspx?/My Reports Folder/My report&parameter1=5&parameter2=2002/01/01&rs:Command=Render&rs:Command=Render&rc:Toolbar=False&rc:Parameters=false

SSRS permission error on the management site

There are two issues that you will find out when you finish the installation of SQL Server 2008 R2 as well as 2012:

When you try to access Reporting Services using your IE browser, it will constantly prompt you to enter username and password.
After the successful entry of login details in IE, you will get the following error message:

User ‘Domain\User’ does not have required permissions. Verify that sufficient permissions have been granted and Windows User Account Control (UAC) restrictions have been addressed.

To resolve the first issues I guess most people can find the right place, but please double check the following steps.
1. From Start > All Programs > Internet Explorer, Right Click and choose Run as administrator

2, Enter the Reporting Services URL: http://localhost/Reports/

3. Running as administrator will allow you to have access to Site Settings. Click on it:

4. Click on Security from the left side menu and choose New Role Assignment:

5. Add the username or Group that you want to grant permission to and select the appropriate role:

To resolve the second issue, please check the following setup.

1. Go to Home page and click on Folder Settings. Add the username or Group that you want to grant permission to the main Home page:

Somebody even comment as below for the second issue, but actually you definitely needn’t add the user to server local admin group and just add the user in upper setting, it will work.

We are trying to set SSRS 2008 report access as documented at the following link
[http://msdn.microsoft.com/en-us/library/aa337494.aspx] and getting the error below:

User ‘DOMAIN\user’ does not have required permissions. Verify that sufficient permissions have been granted and Windows User Account Control (UAC) restrictions have been addressed.

We are using Microsoft SQL Server 2008 (SP2) – 10.0.4000.0 (X64) Sep 16 2010 19:43:16 Copyright (c) 1988-2008 Microsoft Corporation Enterprise Edition (64-bit) on Windows NT 6.0 <X64> (Build 6002: Service Pack 2) and SSRS 2008 SP2 as well.

IE8 – tried it with Protected Mode enabled/disabled no luck.
Windows User Account Control is turned OFF on our SSRS reporting server box.
We tried pretty much all suggestions from the link below and nothing other than local admin group (which is unacceptable) can access Report Manager URL to view reports and their history as per “View Reports” description of the Browser SSRS default role.

SSRS expression cheat sheet

SSRS Expression Cheat Sheet

 Problems Expression
Return first day of current Week

(ex. Default Start Date parameter to return WTD)

Expression on the parameter default value:

=DateAdd(“d”,-DatePart(DateInterval.WeekDay,Today,0,0)+1,Today)Expression Output Example: 11/7/2010 12:00:00 AM

Return first day of current Month

(ex. Default Start Date parameter to return MTD)

Expression on the parameter default value:

 =DateAdd(“d”,-(Day(today)-1), Today)

or

=DateSerial( year(today()), month(today()), 1)

Expression Output Example:11/1/2010 12:00:00 AM

Return first day of current year

ex. Default Start Date parameter to return YTD)

Expression on the parameter default value:

=DateAdd(“d”,-DatePart(DateInterval.DayOfYear,Today,0,0)+1,Today)

Expression Output Example: 1/1/2010 12:00:00 AM

Return period over period

(ex. Default date parameters to a rolling year)

Expression on the parameter default value:

Week over Week

=DateAdd(“ww”,-1, Today)

Month over Month

=DateAdd(“m”,-1,Today)

Year over Year

=DateAdd(“yyyy”,-1, Today)

Expression Output Example:10/9/2010 12:00:00 AM

Return current month name Expression in Text Box:

=MonthName(Month(Today()))Expression Output Example:November

Uppercase fields Expression in Text Box:

=UCASE(Fields!FieldName.Value)Expression Output Example:NOVEMBER

Convert text to proper case

(ex. 1st letter in each word is uppercase)

Expression in Text Box:

=StrConv(Fields!FieldName.Value, VbStrConv.ProperCase)

Replace NULL with another value Expression in Text Box:

=iif(Fields!FieldName.Value = nothing, “No Value”,Fields! FieldName.Value)

Alternating row color (Banding effect) BackgroundColor property on Text Box:

=iif(RowNumber(Nothing) Mod 2 = 0, “Silver”, “White”)

Handling division by zero Expression in Text Box:

=iif(Fields!DenominatorField.Value = 0, 0, Fields!NumeratorField.Value/

iif(Fields!DenominatorField.Value = 0, 1, Fields! DenominatorField.Value))

security number) Expression in Text Box:

=Replace(Fields!EmailAddress.Value,”-“,””)

Common SSRS Problems and Solutions

 Problems Solutions
Parameter with dropdown box Solution: Create an additional dataset that with populate the parameter dropdown box.  Change the available values page of the parameter to get values from a query.  Select the dataset that you created to populate the dropdown box.
Cascading Parameters Solution: Cascading parameters means one parameter can populate the contents of the next parameter.  Create additional datasets to populate each of the dropdown box parameters you want.  When writing these datasets you will want to make sure that the lower level only shows values available in the higher level.  The datasets may look like this:

Select distinct EnglishCountryRegionName From DimGeography

Select distinct StateProvinceName From DimGeography

Where EnglishCountryRegionName = @Country

Select distinct City From DimGeography

Where EnglishCountryRegionName = @Country and StateProvinceName = @State

The country dataset passes the country selected into the state dataset.  Next, the State dataset passes into the city dataset the country and state.  This way when you get to the City parameter you only see cities that are in the selected country and state.

Change the available values page of the parameters to get values from a query.  Select the dataset that you created to populate the dropdown box

Create a report template Solution: Copy a Report you design to one of the following folders:

64 bit location: C:\Program Files (x86)\Microsoft Visual Studio 9.0\Common7\IDE\PrivateAssemblies\ProjectItems\ReportProject

32 bit location: C:\Program Files\Microsoft Visual Studio 9.0\Common7\IDE\PrivateAssemblies\ProjectItems\ReportProject

Deploying to SharePoint Solution: Install and configure Reporting Services add-in for SharePoint for your version of SharePoint.  Set the Deployment properties of the Report project to the following:

TargetDatasetFolder

http://yourservername/site/library/datasets

TargetDataSourceFolder

http://yourservername/site/library/datasources

TargetReportFolder

http://yourservername/site/library/reports

TargetReportPartFolder

http://yourservername/site/library/reportparts

TargetServerURL

http://yourservername/

Setting up a drillthrough report Solution: Right-click on the textbox you wish to allow the drillthrough action and select Text Box Properties.  Select the Action page and select which type of object you want to drillthrough to (Go to report, Go to bookmark, or Go to URL).
Creating a Document Map Solution: Document Maps provide an easy way to navigate through a large report with a table of contents for your report.  To add a Document Map to a report right-click on a row or column grouping (this assumes you have already added a grouping to the report) and select Group Properties.  On the Advanced page you will find the Document map property where you will select the fields to add to the table of contents.

Integreted SSRS reference with .net applications

The easiest way to integrated with .net application is: create an frame or under one content area, when the user click the report menu item and then reference to the SSRS url.

Example:
http://myreportserver/ReportServer_REPORTSRV/Pages/ReportViewer.aspx?%2fmyreportfolder%2fspecificreport&rs:Command=Render&rc:Parameters=false

Pass parameters: &SID=98
Hide parameters: &rc:Parameters=false
Hiding toolbar: &rc:Toolbar=false
Report Format (Opening Excel or Acrobat by default): &rs:Format=Excel / &rs:Format=PDF

The other way is to reference the SSRS web service directly, in this way, we can use .net code to control the report generation and be able to generate the final report format directly (PDF, excel etc.)


Dim ssrsservername As String
ssrsservername = ConfigurationManager.AppSettings("SSRS_Server")

Dim ping As Ping = New Ping()
Dim pingreply As PingReply = ping.Send(ssrsservername)
If pingreply.Status = IPStatus.Success Then
    Dim rs As New ReportExecutionService()
    rs.Credentials = System.Net.CredentialCache.DefaultCredentials

    ' Render arguments
    Dim result As Byte() = Nothing
    Dim reportPath As String = "/myreportfolder/myreport"
    'Dim format As String = Server.UrlEncode(ddlFormat.SelectedValue)
    Dim format As String = "PDF"
    Dim historyID As String = Nothing
    Dim devInfo As String = "<DeviceInfo><Toolbar>False</Toolbar></DeviceInfo>"

    ' Prepare report parameter.
    Dim parameters As ParameterValue() = New ParameterValue(1) {}
    parameters(0) = New ParameterValue()

    parameters(0) = New ParameterValue()
    parameters(0).Name = "ID"
    parameters(0).Value = infoID.Trim

    Dim credentials As DataSourceCredentials() = Nothing
    Dim showHideToggle As String = Nothing
    Dim encoding As String = Nothing
    Dim mimeType As String = Nothing
    Dim extension As String = Nothing
    Dim warnings As Warning() = Nothing
    Dim reportHistoryParameters As ParameterValue() = Nothing
    Dim streamIDs As String() = Nothing

    Dim execInfo As New ExecutionInfo()
    Dim execHeader As New ExecutionHeader()

    rs.ExecutionHeaderValue = execHeader
    execInfo = rs.LoadReport(reportPath, historyID)
    rs.SetExecutionParameters(parameters, "en-us")
    Dim SessionId As [String] = rs.ExecutionHeaderValue.ExecutionID
    Try
	result = rs.Render(format, devInfo, extension, encoding, mimeType, warnings, streamIDs)
	execInfo = rs.GetExecutionInfo()
	Debug.Print(String.Format("Execution date and time: {0}", execInfo.ExecutionDateTime))
    Catch soapError As SoapException
	Console.WriteLine(soapError.Detail.OuterXml)
    End Try

    Try
	Dim stream As FileStream = File.Create(pdfpath, result.Length)
	stream.Write(result, 0, result.Length)
	stream.Close()
    Catch IOError As Exception
	Debug.Print(IOError.Message)
    End Try

End If

Deploy reports by Reporting Service Utility

In production environment, we may not be able to deploy reports by click the “deploy” menu in the SSRS project or it may not be effective. Sometimes we may want to use batch to run the deployment, these batch will leverage the reporting service utility.

This exe file is located at SSRS server under …\Program Files\Microsoft SQL Server\100\Tools\Binn or
C:\Program Files (x86)\Microsoft SQL Server\100\Tools\Binn

You can put the following into a windows batch

RS syntax:
SET targetFolder=/MyReports
SET sourcePath=C:TempMyRDL
SET reportName=MyReport
rs.exe -i Deploy_rdl_file.rss -s http://MyServer/ReportServer
-v sourcePATH=”%sourcePath%”
-v targetFolder=”%targetFolder%”
-v reportName=”%reportName%”

RSS file (vbscript file)

Dim definition As [Byte]() = Nothing
Dim warnings As Warning() = Nothing
Public Sub Main()
Try
	Dim stream As FileStream = File.OpenRead(sourcePath + reportName + ".rdl")
	definition = New [Byte](stream.Length) {}
	stream.Read(definition, 0, CInt(stream.Length))
	warnings = rs.CreateReport(reportName, targetFolder, True, definition, Nothing)
	If Not (warnings Is Nothing) Then
		Dim warning As Warning
		For Each warning In warnings
			Console.WriteLine(warning.Message)
		Next warning
	Else
		Console.WriteLine("Report: {0} published successfully with no warnings", reportName)
	End If
Catch e As IOException
	Console.WriteLine(e.Message)
End Try
End Sub