mikevanzap – Page 9 – Reporting Services

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.

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.