August 2019 – Page 2 – Reporting Services

August 7, 2019August 16, 2019

SSRS report Chart

Here I come with post where I create graph that displays results from my testing lab I described in previous post. Reporting Services offers lots of possibilities of data visualisations. Graphs are basic components for scenarios you need to visualise your data.

File -> New -> Project

Continue with Business Intelligence -> Reporting Services -> Report Server Project. At the bottom part of New Project window set Project Name and its location.

New empty Report Server project was created.

Add New Report, right click Reports in Solution Explorer.

Go to Toolbox pane and click on Chart component.

Select Bar Chart type from chart palette.

New Chart component is added into the Visual Studio design area.

Right click on Chart to get Chart Properties dialog box. Map Chart to its dataset. Select GetData dataset from Dataset name select box.

Our goal is to get something similar on picture bellow. We want to get results from our tests. It will be grouped by categories represented by tested counters. Set each scenario to chart series.

When you click on chart component you get Chart Data dialog box on the right side.

In Values section select CounterValue, results from our tests.
Set CounterName to Category Groups to get testing perspective of our data, counters.
TaskOwnerName will be set as Series Groups, scenarios we tested

Finally, we got simple chart as on picture bellow.

Reporting Services Chart is very complex component. I didn’t show all its possibilities it has. In this post I created the simplest one Chart to represented lab data.

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, 2019November 10, 2019

Get data to lab report

In this post we continue with lab series. I fill prepare structures with tested data. To collect testing results I will use Extended Events, Extended Events utility from this post.

In post we will demonstrate very simple demonstration of clustered index impact on query performance. One table will be created with clustered index and one table stay as heap. After filling tables with data, we start Extended Event Session and run two queries against created tables. When queries finish, we collect the data to lab structure we created in previous post and stop and clean sessions.

Let’s prepare lab structures first.

CREATE TABLE dbo.TestTableData
(id INT )
 
CREATE TABLE dbo.TestTableDatClustered
(id INT PRIMARY KEY CLUSTERED
)
 
INSERT INTO dbo.TestTableData
SELECT TOP 1000000 ROW_NUMBER() OVER (ORDER BY (SELECT 1)) rn 
FROM sys.objects a
JOIN sys.objects b ON 1=1
JOIN sys.objects c ON 1=1
 
INSERT INTO dbo.TestTableDatClustered
SELECT TOP 1000000 ROW_NUMBER() OVER (ORDER BY (SELECT 1)) rn 
FROM sys.objects a
JOIN sys.objects b ON 1=1
JOIN sys.objects c ON 1=1

Clean cache and get SPID.

SELECT @@SPID
DBCC FREEPROCCACHE
CHECKPOINT
DBCC DROPCLEANBUFFERS

Open new query window and start session with the utility.Extended events monitoring utility

Create and start session.

/*Lets start event session*/ EXEC mnt.MNT_SessionHandler 'CreateSession;StartSession','Test','QueryPlan;IOStat','<Predicate><DatabaseID>5</DatabaseID></Predicate>' /*Lets start events*/

Run test queries.

SELECT * FROM dbo.TestTableData WHERE Id = 1000
SELECT * FROM dbo.TestTableDatClustered WHERE Id = 1000

Get data from Session to temporary table.

EXEC mnt.MNT_SessionHandler 'GetDataFromSession' ,'Test','IOStat;QueryPlan' /*Run query you would like to analyze*/

Let’s clean up the extended event session.

/*Stop session*/ EXEC mnt.MNT_SessionHandler 'EndSession', 'Test' /*clean session*/ EXEC mnt.MNT_SessionHandler 'CleanSession', 'Test' /*Or all in one raw*/ EXEC mnt.MNT_SessionHandler 'CleanSession;EndSession', 'Test' /*Get data to analyze,lets make your own views, predicates... */ SELECT * FROM ##Test

In the script bellow the data from extended events are stored to Lab structures.

 
INSERT INTO dbo.LabTable
( [CounterValue],[CounterName],[TaskOwnerName], [WorkoutName] )
SELECT CPUTime/ 1000,'CPUTime (sec)', SqlText,'ClusteredIndexTest'
FROM dbo.##Test WHERE SqlText = ' SELECT * FROM dbo.TestTableData WHERE Id = 1000'
AND [Type] = 'QueryPlan'
INSERT INTO dbo.LabTable
( [CounterValue],[CounterName],[TaskOwnerName], [WorkoutName] )
SELECT Duration/ 1000,'Duration (sec)', SqlText,'ClusteredIndexTest'
FROM dbo.##Test WHERE SqlText = ' SELECT * FROM dbo.TestTableData WHERE Id = 1000'
AND [Type] = 'QueryPlan'
INSERT INTO dbo.LabTable
( [CounterValue],[CounterName],[TaskOwnerName], [WorkoutName] )
SELECT PhysicalReads,'PhysicalReads (pages)', SqlText,'ClusteredIndexTest'
FROM dbo.##Test WHERE SqlText = ' SELECT * FROM dbo.TestTableData WHERE Id = 1000' AND [Type] = 'IOSql'
INSERT INTO dbo.LabTable
( [CounterValue],[CounterName],[TaskOwnerName], [WorkoutName] )
SELECT LogicalReads,'LogicalReads (pages)', SqlText,'ClusteredIndexTest'
FROM dbo.##Test WHERE SqlText = ' SELECT * FROM dbo.TestTableData WHERE Id = 1000' AND [Type] = 'IOSql'
 
INSERT INTO dbo.LabTable( [CounterValue],[CounterName],[TaskOwnerName], [WorkoutName] )
SELECT CPUTime/ 1000,'CPUTime (sec)', SqlText,'ClusteredIndexTest'
FROM dbo.##Test WHERE SqlText LIKE '%dbo.TestTableDatClustered%' AND [Type] = 'QueryPlan'
INSERT INTO dbo.LabTable ( [CounterValue],[CounterName],[TaskOwnerName], [WorkoutName] )
SELECT Duration/ 1000,'Duration (sec)', SqlText,'ClusteredIndexTest'
FROM dbo.##Test WHERE SqlText LIKE '%dbo.TestTableDatClustered%' AND [Type] = 'QueryPlan'

INSERT INTO dbo.LabTable ( [CounterValue],[CounterName],[TaskOwnerName], [WorkoutName] )
SELECT PhysicalReads,'PhysicalReads (pages)', SqlText,'ClusteredIndexTest'
FROM dbo.##Test WHERE SqlText LIKE '%dbo.TestTableDatClustered%' AND [Type] = 'IOSql'

INSERT INTO dbo.LabTable ( [CounterValue],[CounterName],[TaskOwnerName], [WorkoutName] )
SELECT LogicalReads,'LogicalReads (pages)', SqlText,'ClusteredIndexTest'
FROM dbo.##Test WHERE SqlText LIKE '%dbo.TestTableDatClustered%' AND [Type] = 'IOSql'

In next post we visualise the data in SSRS chart, so stay tuned.