Keep SSRS (SSRS2016) report manager awake

When running a report for the first time in report manager it takes a while to run, after this initial run reports run fine. 

There are a number of ways to keep reporting services awake so that when you run a report it runs straight away and is not slow to load.

I have found a number of scripts on the web but I could not get them to work, so I created my own and tweaked it and it works! :-) 

The idea is to run a report using PowerShell with a URL and then create a job which will run the job before your users start running reports.


The PowerShell script below runs a report

$IE=new-object -com internetexplorer.application
$IE.navigate2("ENTERREPORTURLLINKHERE")
$IE.visible=$true


Test the script in PowerShell on your server and then save the file.

Then create a SQL Agent job which you then schedule to run every day at say 6am.

This works on SSRS 2016 I have not tested on previous versions but it should work in principle

Extracting Users from Windows Groups on Report Server

There may be occasions when using windows active security is not ideal so you may need to create and maintain your own windows groups for security on reports. One of the downfalls is to be able to extract a list of the windows groups you have on the server and presenting it to management. It is quite frustrating to be asked about who the users are for which groups and the recipients for report subscriptions.

To get round this issue I wrote some script to extract the groups and users from a windows server, once extracted a report can be easily extracted so you can see who has access.

I have used an SSIS package to do this:

The package runs a SP on the master database on the SSRS server and then inserts the data to another server which is used for reporting. The reason for creating a package was due to not being able to insert the data to another database on a different server.

----------------------------------------------------

--SP extracts the groups and users

----------------------------------------------------

proc [dbo].[my_sp_servergroups]

as

---------------------------------------------windows groups

if object_id ('my_tbl_groups') is not null

drop table my_tbl_groups

if object_id ('my_tbl_groups1') is not null

drop table my_tbl_groups1

create table my_tbl_groups (groupname varchar(100))

create table my_tbl_groups1 (groupname varchar(100),group_name varchar(100), RowNumber int)

insert into my_tbl_groups

exec xp_cmdshell 'net localgroup'

delete from my_tbl_groups where left(isnull(rtrim(groupname),'MpTI'),4) <> '*ssr'

insert into my_tbl_groups1

select *,right(rtrim(groupname),len(rtrim(groupname))-1) as group_name,

(select count(*) from my_tbl_groups as b where b.groupname <= a.groupname)   AS RowNumber

 from my_tbl_groups a

--select * from my_tbl_groups1

-------------------------------------------------

------------------------------group members

if object_id ('my_tbl_groupsmembers') is not null

drop table my_tbl_groupsmembers

create table my_tbl_groupsmembers (members char(200))

if object_id ('my_tbl_servergroups') is not null

drop table my_tbl_servergroups

create table my_tbl_servergroups (members char(200), group_name char(200))

DECLARE @number_of_groups INT  set  @number_of_groups  = (select count( distinct groupname) from my_tbl_groups1 )

DECLARE @counter int set @counter= 1

declare @text char(200)

truncate table my_tbl_servergroups

truncate table my_tbl_groupsmembers

WHILE @counter <= @number_of_groups

BEGIN

  set @text = (select 'net localgroup '+group_name from my_tbl_groups1 where rownumber = @counter )

  insert into my_tbl_groupsmembers exec xp_cmdshell @text

  insert into my_tbl_servergroups

   select *, (select group_name from my_tbl_groups1 where rownumber = @counter) as group_name  from my_tbl_groupsmembers

    truncate table my_tbl_groupsmembers

   SET @counter = @counter + 1

END

delete my_tbl_servergroups where left(isnull(members,'Empty'),6)<> ''

----------------------------

--The script below copies the data to another server

----------------------------

if object_id ('my_tbl_servergroups') is not null

drop table dbo.my_tbl_servergroups

create table my_tbl_servergroups (members char(200), group_name char(200))

insert into my_tbl_servergroups

select *   from [SERVERNAME].[master].[dbo].my_tbl_servergroups

T SQL Concatenated Row Field

When creating new reports you sometimes need to concatenate data:

 Below is sample script which will allow you to do this using a function.



----------------------------------------------------------------------------
------ create table
----------------------------------------------------------------------------
with cte as (select 123 as customer, '2012-09-18 00:00:00.000' as orderdate
union all
select 123 as customer, '2012-09-19 00:00:00.000' as orderdate
union all
select 123 as customer, '2012-09-20 00:00:00.000' as orderdate
union all
select 124 as customer, '2012-09-21 00:00:00.000' as orderdate
union all
select 124 as customer, '2012-09-22 00:00:00.000' as orderdate
)

select * into CustomerOrder from cte




----------------------------------------------------------------------------
------ create function to concatenate dates
----------------------------------------------------------------------------
create    function [dbo].[GetCustomerOrderDates](
@customer varchar(20)

)
returns varchar (8000)
as
begin
declare @str varchar (8000)
  set @str = ''
 
if @customer is null
  return ''



select @str = @str + cast(dayz as varchar(300))+': ' FROM
(select top 1000 customer, orderdate,  convert(char(2),datepart(d,orderdate))+' '
+CONVERT(char(3),DATENAME(mm,orderdate))  as
dayz
from  CustomerOrder
where customer =@customer
order by orderdate
) as derived



if (len (@str) > 1)
  set @str = left(@str, len(@str) - 1)

return (@str)
End




----------------------------------------------------------------------------
------ run query and use function to return concatenated dates
----------------------------------------------------------------------------


select distinct customer, dbo.[GetCustomerOrderDates](123) as orderdates
from CustomerOrder

Create a data driven subscription for the last working day

The standard scheduling reporting services 2008 r2 does not include the last day of the month.

A workaround is to use sql code in a data driven subscription to create this schedule.

The code is shown below:

I have used a function to strip out the time ...


CREATE  FUNCTION wfDateWithoutTime
 (@datewithtime DATETIME)
RETURNS SMALLDATETIME
AS
BEGIN
 DECLARE @datestring AS VARCHAR(20)
 SET @datestring = CONVERT(VARCHAR(20),@datewithtime,101)
 RETURN CONVERT(SMALLDATETIME, @datestring)
END

The t sql code is then used in the sql query of the data driven subscription as follows:

with cte as
(select DATEADD(m, DATEDIFF(m, 0, GETDATE())+1, 0) -1 as tday)

select * from cte where tday = (select dbo.wfdatewithouttime(getdate()))


You then need to set the data driven subscription to run on a daily basis. The subscription will then only run on the last day of the month.

Duplex Printing

Conditional Page Breaks in SSRS 2008 r2

I have recently been looking at conditional page breaks in SSRS 2008 r2 and did not know that it is possible to do via an expression.

For example if you have say 2 tables in a report and you want to conditionally force a page break between the 2 so that they appear separately on 2 pages based on a parameter.

This is possible, to do this first select the first table and then in the properties of the table under 'General' and then 'Pagebreak' change the break location to 'end'. This will then force a page break at the end of the table one.

To make the pagebreak conditional create a parameter called pagebreak with the available values of 'yes' and 'no'. Then in the properties for the table under the break location there is a field called 'Disabled' click on the dop down and select the expression option, in the expression box type the expression as:

=iif(Parameters!pagebreak.Value="no",True,False)

When you run the report change the pagebreak parameter and you will see that this forces a page break when you select 'yes'.

SQL Server Failed jobs in SSMS

The following sql will list all the jobs that have failed in sql server:

SELECT TOP 100 PERCENT      

Z.Originating_Server,
     msdb.dbo.sysjobhistory.message as Error_Message,
     len(msdb.dbo.sysjobhistory.message) as Error_Len
,    msdb.dbo.sysjobs.name AS Job_Name
    , msdb.dbo.sysjobhistory.step_id AS Step
    , msdb.dbo.sysjobhistory.step_name AS Job_Step_Name
    , (CASE
     WHEN  msdb.dbo.sysjobhistory.run_status = 0 THEN 'Failed'
     WHEN  msdb.dbo.sysjobhistory.run_status = 1  THEN 'Succeeded'
     WHEN  msdb.dbo.sysjobhistory.run_status = 2  THEN 'Retry'
     WHEN  msdb.dbo.sysjobhistory.run_status = 3  THEN 'Cancelled'
     WHEN  msdb.dbo.sysjobhistory.run_status = 4  THEN 'In progress'
    END)  AS Status
    , cast(
                   cast(msdb.dbo.sysjobhistory.run_date as varchar) + ' ' +
                  left(right('000000' + cast(msdb.dbo.sysjobhistory.run_time as varchar), 6), 2) + ':' +
                  substring(right('000000' + cast(msdb.dbo.sysjobhistory.run_time as varchar), 6), 3, 2) + ':' +
                 right(right('000000' + cast(msdb.dbo.sysjobhistory.run_time as varchar), 6),2)
                  as smallDatetime)  AS Date
   
     , (SELECT DATENAME (dw,  (select convert (smalldatetime, convert (varchar(10), msdb.dbo.sysjobhistory.run_date))))) AS [Day]
   
    , (CASE
     WHEN len(msdb.dbo.sysjobhistory.run_duration) = 6 THEN substring(cast(run_duration as char(6)),1,2)+':'+substring(cast(run_duration as char(6)),3,2)+':'+substring(cast(run_duration as char(6)),5,2)
     WHEN len(msdb.dbo.sysjobhistory.run_duration) = 5 THEN '0' +substring(cast(run_duration as char(5)),1,1)+':'+substring(cast(run_duration as char(5)),2,2)+':'+substring(cast(run_duration as char(5)),4,2)
     WHEN len(msdb.dbo.sysjobhistory.run_duration) = 4 THEN '00'+':'+substring(cast(run_duration as char(4)),1,2)+':'+substring(cast(run_duration as char(4)),3,2)
     WHEN len(msdb.dbo.sysjobhistory.run_duration) = 3 THEN '00:0'+substring(cast(run_duration as char(3)),1,1)+':'+substring(cast(run_duration as char(3)),2,2)
     WHEN len(msdb.dbo.sysjobhistory.run_duration) = 2 THEN '00:00'+':'+substring(cast(run_duration as char(2)),1,2)
     WHEN len(msdb.dbo.sysjobhistory.run_duration) = 1 THEN '00:00:0'+substring(cast(run_duration as char(1)),1,1)
     ELSE '00:00:00'
    END)   AS [Duration]
    , msdb.dbo.syscategories.name AS Job_Category

FROM msdb.dbo.sysjobs LEFT OUTER  JOIN msdb.dbo.sysjobhistory ON msdb.dbo.sysjobs.job_id = msdb.dbo.sysjobhistory.job_id
INNER JOIN msdb.dbo.syscategories ON msdb.dbo.sysjobs.category_id = msdb.dbo.syscategories.category_id
join  msdb.dbo.sysoriginatingservers_view  Z on z.originating_server_id = msdb.dbo.sysjobs.originating_server_id
WHERE
cast(
            cast(msdb.dbo.sysjobhistory.run_date as varchar) + ' ' +
            left(right('000000' + cast(msdb.dbo.sysjobhistory.run_time as varchar), 6), 2) + ':' +
            substring(right('000000' + cast(msdb.dbo.sysjobhistory.run_time as varchar), 6), 3, 2) + ':' +
            right(right('000000' + cast(msdb.dbo.sysjobhistory.run_time as varchar), 6),2)
            as smallDatetime)  > DATEADD(dd, - 1, current_timestamp )
AND  (msdb.dbo.sysjobhistory.step_id > 0)
AND  (msdb.dbo.sysjobhistory.run_status <>1)
ORDER BY
[Date] DESC