SSRS – How to implement hovering of data in the report (Mouse Hover Action)

Sometimes, we need to display length text in the report for supporting other attributes e.g.

Column 1 is Attribute Name
Column 2 is the attribute definition of column 1

If this text is just for supporting other attributes in report & not required in any of the exported report then we can utilize the mouse hovering feature of SSRS to show this data, in this way, we can have more space and can show more information in the report. This implementation is useful in dashboard/scorecard reports also where we need to display the summary only.  Below is the sample report where we can see text is taking more space and can be used as hovering data in the report

Steps to implement mouse hovering in the report.

      1)      Right click on the text box where we need implement mouse hover

      2)      Go to text box properties

      3)      Go to Tool tip & map the description column or we can write SSRS expression to map the column

      4)      Now run the report and see the result

 ----------------------------End of Article-----------------------

SSRS – How to give Name to sheet(s) while exporting report to excel

When we export the SSRS report to excel, SSRS export report with its default sheet name e.g Sheet1, Sheet2. Below is the example.

SSRS Report

When we export the same report into excel. It will export in Sheet1 by default.

Exported Excel Report

Steps to give Name to the Sheet(s)

      1)      Go to the property of the tablix by selecting the tablix & press F4 button

      2)      You can see the Page Name property, Give the name that you would to display for sheet(s) while exporting to excel

3)      Now Export the report. You can see the Name of the Page given by you.

This implementation is helpful when you export multiple sheet(s) report. It is helpful in identifying the correct report easily.

--------------------------End of Article-------------------

SSRS Security Model – Role Permission Matrix

Role/Permission Matrix

Following is the Role/Permission matrix for an application that uses SSRS reports for their portal. This matrix consists of 4 roles e.g. Report Admin, Schedule Admin, Report Writer & Report Reader and their associated permission(s).It can be more role(s) based on business requirement.  As SSRS works on Windows Authentication, we can create security group in active directory and assign the role(s) to these security group(s) in this way, we can manage larger of user(s) for Report easily.

Below matrix shows 4 roles e.g. Reader for only viewing the reports, Writer for read/writing/deploying the reports, Schedule Admin for Report Subscription/snapshot/history & Report Admin for system administrative work:

                                                                        

Task	Permissions	Report-Admin	Schedule Admin	Writer	Reader
Consume report	Read Content ,Read Report Definitions & Dataset	✔	✔	✖	✖
Create linked reports	Create Link Reports	✔	✖	✖	✖
Manage all subscriptions	Read, Create Delete & Update Any Subscription	✔	✔	✖	✖
Manage data sources	Create, Delete & Update Content	✔	✖	✖	✖
Manage folders	Create, Delete & Read Folder	✔	✖	✖	✖
Manage individual subscriptions	Read Properties Create Subscription Delete Subscription Read Subscription Update Subscription	✔	✖	✖	✖
Manage models	Create Model	✔	✖	✖	✖
Manage models	Read, Delete & Update Content Read, Update Data Sources Read & Update Model Item Policies	✔	✖	✖	✖
Manage report history	Read Properties Create, Delete and Execute  Report History	✔	✔	✖	✖
Manage reports	Create Report Update Parameters Read & Update  Data Sources Read & Update Report Definition Execute Read Policy & Update Policy	✔	✖	✔	✖
Manage resources	Create Delete & Update Content	✔	✖	✔	✖
Set security for individual items	Read & Update Security Policies	✔	✖	✖	✖
View data sources	Read Content & Properties	✔	✔	✔	✔
View folders	Read Execute And View contents	✔	✔	✔	✔
View models	Read Data Sources , Content &Properties	✔	✔	✔	✔
View reports	Read Content/Datasets &Properties	✔	✔	✔	✔
View resources	Read Content/Report Parts/Properties	✔	✔	✔	✔

T-SQL – How to get the count of occurrence of a keyword in a string

If there is a requirement to check how many times a particular keyword comes in a string then below T-SQL Code will be helpful in getting occurrence count.

USE [MyDB]

GO

SET ANSI_NULLS ON

GO

SET QUOTED_IDENTIFIER ON

GO

CREATE FUNCTION [dbo].[CountOccurancesOfString]

(

    @searchString nvarchar(max),

    @searchTerm nvarchar(max)

)

RETURNS INT

AS

BEGIN

    return (LEN(@searchString)-LEN(REPLACE(@searchString,@searchTerm,'')))/LEN(@searchTerm)

END

GO

--Executing the function

SELECT [dbo].[CountOccurancesOfString]('Life is beautiful,Life is not about finding yourself. Life is about creating yourself. so live life.' ,'Life')

                        -----------------End of Article-------------