SSRS – Page 2 – Reporting Services

August 7, 2019August 7, 2019

SSRS report manager rights

SSRS Report Manager supports Windows authentication by default. There are few other authentication types supported, you can configure. See https://bit.ly/2LYNyRy. If you would like to use another type of authentication you should implement it through Reporting Services extension https://bit.ly/2yBJjmj.

When you would like to manage user access to Reporting Services it is realized by roles you set to its login. There are two areas of roles you set to users.

One is set in Report Manager site settings and its scope is System access and the second one on Report Item level (Reports, Folders, etc.)

In site settings you set roles for users that should have rights to manage Schedules for example.

Below is list of Report Item roles taken directly from MSDN. Roles are inherited to item by default from its container.

https://docs.microsoft.com/en-us/sql/reporting-services/security/role-definitions-predefined-roles?view=sql-server-2017

Content Manager Role	Item	May manage content in the Report Server. This includes folders, reports, and resources.
Publisher Role	Item	May publish reports and linked reports to the Report Server.
Browser Role	Item	May view folders, reports, and subscribe to reports.
Report Builder Role	Item	May view report definitions.
My Reports Role	Item	May publish reports and linked reports; manage folders, reports, and resources in a users My Reports folder.
System Administrator Role	System	View and modify system role assignments, system role definitions, system properties, and shared schedules, in addition to create role definitions, and manage jobs in Management Studio.
System User Role	System	View system properties, shared schedules, and allow use of Report Builder or other clients that execute report definitions.

Report items, Reports, Path go to Report properties by click on right arrow next to the report, report item or folder name.

Go to Security item from the menu.

In case of folder you can go to Security through Folder Settings from main menu.

Picture 3 – Main menu

In Security section you can see users with accessed roles. You can Edit current security settings or add new user by clicking on New Role Assignment.

When adding new user add domain\user login and check roles you would like to access to the user.

When setting System roles go to site settings on the top menu of Report Manger.

Picture 7 – Administration roles

August 7, 2019August 7, 2019

SSRS PowerShell Linked report

In this post we create Linked Report using PowerShell script.

Function bellow expect following parameters:

$proxy – Reporting Services web proxy, how to get proxy, see this post Connect to Reporting Services with PowerShell
$OriginReportPath – Report part of base report
$NewPath – path for Linked Report
$LinkedName – name for Linked Report
$LinkedDescription – Linked Report description

Function uses SSRS Web Service method CreateLinkedReport.

function CreateLinkedReport($proxy,[string] $OriginReportPath, [string]
$NewPath, [string] $LinkedName, [string] $LinkedDescription)
{   
Write-Host "Create linked report: " $NewPath"/" $LinkedName
$prop = New-Object -TypeName SSRS.ReportingService2005.Property
$prop.Name = "Description"
$prop.Value = $LinkedDescription

[SSRS.ReportingService2005.Property[]] $props = @(New-Object SSRS.ReportingService2005.Property)
$props[0] = $prop
try   { 
$Proxy.CreateLinkedReport($LinkedName,$NewPath,$OriginReportPath,$props)
         }
     catch [Exception] 
  {  Write-Host $_.Exception.Message
    }
}

Lets try.

CreateLinkedReport $proxy "/ReportPath/BaseReport" "/NewDir/LinkedReportPath" "LinkedReportName" "LinkedReportDescription"

August 7, 2019August 7, 2019

SSRS HTML

SSRS supports functionality when Report Rendering engine interprets HTML tags. It can be useful when you would like to format textbox within Tablix component. Be aware that support of HTML tags is very restricted. Here https://bit.ly/2KA357l you can find list of supported tags.

GOAL

Make format in Sales Report (you can get from one of previous post SSRS Cascading Parameters ), that members of the first group are formatted with first bold letter.

Let’s change Tablix textbox properties supporting Html tags. Click by left mouse to the Tablix Textbox of English Country Region Name, that field definition is highlighted and by right click you can see menu with Placeholder Properties item.

By clicking on the item Placeholder Properties window appears, check Html – Interpret HTML tags as styles at the bottom of the window and confirm. Now we set the Textbox to support HTML tags.

Let’s make requested format now. Go to Tablix component, English Country Region Group textbox field, open expression box and put formatting formula bellow. Use <b> tag to change letter font to bold. LEFT function ensures that bold format will be applied to first letter only. The rest of the text will be finished using MID and LEN function.

LEFT function returns defined number of letters of the string from the left. MID function extract substring from defined string starting with defined position and ending with finishing position. For finishing position, we use LEN function returning number of chars from defined string.

See final format of Sales Report,
English Country Region Name column.