Speed up Reports for MS SQL Server 2014 Reporting Services SSRS and Access Log Information

How to Modify a Reporting Services Configuration File (RSreportserver.config)
https://technet.microsoft.com/en-us/library/bb630448(v=sql.120).aspx

There are a few configuration values can be changed to improve long running reports.

SQLServer 2014: \Program Files\Microsoft SQL Server\MSRS12.MSSQLSERVER\Reporting Services\ReportServer\rsreportserver.config

Updated to:

https://www.mssqltips.com/sqlservertip/2197/how-to-speed-up-ms-sql-server-reporting-services-ssrs-on-first-run/
https://msdn.microsoft.com/en-us/library/ms157273(v=sql.120).aspx

Check Trace log file :
The trace log files are ReportServerService_.log and are located in the following folder:
C:\Program Files\Microsoft SQL Server\MSRS12.MSSQLSERVER\Reporting Services\LogFiles

Read log information from ExecutionLog3 view

–T-SQL
Use ReportServer
select * from ExecutionLog3
Order by TimeStart DESC

https://msdn.microsoft.com/en-us/library/ms159110(v=sql.120).aspx

–Some sample values
https://blogs.msdn.microsoft.com/mariae/2009/09/24/troubleshooting-timeout-errors-in-reporting-services/

—Configure Available Memory for Report Server Applications
https://technet.microsoft.com/en-us/library/ms159206(v=sql.120).aspx

Modify SessionTimeout for SSRS with rs.exe utility

https://blogs.msdn.microsoft.com/jgalla/2006/10/11/session-timeout-during-execution/


SSRS Format Negative Number

=Format(Fields!colFieldName.Value, “###,###.;(###,###.)”))

Result examples:
(7,146,711)
(429,999)
763,607
1,500
300


Get all days in a month

  
  
 
 DECLARE @month TINYINT
SET @month = 10;

DECLARE @year INT
SET @year = 2016;

--get the beginning of the passing month and year
declare @fromDate datetime = Cast( Cast(@year as char(4)) +Right('0'+Cast(@month as varchar(2)),2)+'01' as datetime)

---get beginning of next month 
DECLARE @toDate DATETIME =Dateadd(month,1,@fromDate)  

;WITH mycte
     AS (SELECT @fromDate AS dt
         UNION ALL
         SELECT dt + 1
         FROM   mycte
         WHERE  dt + 1 < @toDate)

  SELECT replace(convert(CHAR(11),Dt, 106), ' ', '-') as SelDate FROM mycte

 

Dynamic Pivot with CASE

  
  
 
 --Create table questions (Question  varchar(30), Answer    varchar(30),             Legend  varchar(30))

--Insert into questions values ('Q1','Y','2015'),('Q1','N','2016'),('Q2','#','2015')

-- --===== Create number table on-the-fly
--;WITH Num1 (n) AS (
--SELECT 1 as n
--UNION ALL SELECT n+1 as n
--FROM Num1 Where n <101), 
--Nums (n) AS (SELECT ROW_NUMBER() OVER(ORDER BY n) FROM Num1
--WHere n<= @Param ) ---Control the total columns list
 
 Declare @tablename sysname='questions'
 Declare @sqlCase as NVarchar(4000) =null
Declare @sql as NVarchar(4000)=null

 
declare @ColumnHeaders VARCHAR(MAX), @ColumnHeaders1 VARCHAR(MAX);
set @ColumnHeaders = STUFF( (SELECT DISTINCT ',' + 'Max(CASE WHEN Legend=' + quotename(Legend,'''') + ' THEN Answer else null end ) as ' + quotename(Legend,'[')  + char(10)+char(13)
                             FROM questions
                             FOR XML PATH(''), TYPE).value('.', 'varchar(max)'), 1, 1, '');
  --print @ColumnHeaders

Set @sql  =' Select Question,'+   @ColumnHeaders + ' from Questions Group by Question ';
    
print @sql
EXEC(@SQL)
 


--Another sample:
USE [AdventureWorks2008R2]
GO

DECLARE @Sql NVARCHAR(4000) =null 
  
declare @ColumnHeaders NVARCHAR(4000) ;
set @ColumnHeaders = STUFF( (SELECT DISTINCT ',' + 'AVG(CASE WHEN DaysToManufacture =' + quotename(DaysToManufacture,'''') + ' THEN  StandardCost  else null end ) as ' + quotename(DaysToManufacture,'[')  + char(10)+char(13)
                             FROM  Production.Product
                             FOR XML PATH(''), TYPE).value('.', 'varchar(max)'), 1, 1, '');
  --print @ColumnHeaders
 
Set @sql  =N' Select  ''AverageCost'' AS Cost_Sorted_By_Production_Days, '+   @ColumnHeaders + ' from  Production.Product ';
     
--print @sql
EXEC(@SQL)



--another example:
Create table Capacity(Name char(1),Value int)
Insert into Capacity values('A',6),('B',6),('C',5)
Create table Completed (Name char(1), Date date, Value int)
Insert into Completed values('A','18 jan,2017', 5)
,('A','19 jan,2017', 3)
,('B','17 jan,2017', 4)
,('B','17 jan,2017', 4)
,('B','20 jan,2017', 2)

declare @startdate date='17 Jan 2017' 
declare @enddate date= '20 Jan 2017'


DECLARE @Sql NVARCHAR(4000) =null
   
declare @ColumnHeaders NVARCHAR(4000) ;
;with dates as (
Select dateadd(day,n ,@startdate ) dt 
from(values(0),(1),(2),(3),(4),(5),(6),(7)) D(n)
Where dateadd(day,n ,@startdate )<=@enddate)


--load date list to a temp table
Select dt 
into tempdates
from dates

Select @ColumnHeaders = STUFF( (SELECT DISTINCT ',' + 'max(CASE WHEN dt =' + quotename(dt,'''') + ' THEN  value1  else null end ) as ' 
+ quotename(Cast(datepart(day,dt) as varchar(2))+'Capacity','[')  + char(10)+char(13)
+
',' + 'isnull(max(CASE WHEN dt =' + quotename(dt,'''') + ' THEN  value2  else null end ),0) as ' 
+ quotename(Cast(datepart(day,dt) as varchar(2))+'Complete','[') + char(10)+char(13)
FROM  tempdates
FOR XML PATH(''), TYPE).value('.', 'varchar(max)'), 1, 1, '');

 --print @ColumnHeaders


;with  names as (
Select distinct Name from Capacity
)
,combs as (
Select Name, dt from names,tempdates)

,finaldataset as (
Select c0.name,c0.dt,c1.value as value1, c2.value as value2 from combs  c0 join Capacity c1 on c0.name=c1.name
left join  Completed c2 on c0.name=c2.name and c0.dt=c2.Date
)


Select * into finaldataset2
from finaldataset

Select @sql  =N' Select  name, '+   @ColumnHeaders + ' from  finaldataset2 Group by name';
      
--print @sql
EXEC(@SQL)

--cleanup
Drop table finaldataset2,tempdates

drop table Completed,Capacity



---Another sample
create table test (ID int,  yearCol int, code1 int, code2 int)
Insert into test values (1   ,2014,  11 ,  14),(1   ,2014,  11,   15),(1   ,2014  ,12 ,  16)
,(1   ,2015,  11 ,  14),(1   ,2015,  12,   15),(1,2015,13,16)
 

Declare @sqlCase as NVarchar(4000) =null
Declare @sql as NVarchar(4000)=null
 
  
declare @ColumnHeaders VARCHAR(MAX), @ColumnHeaders1 VARCHAR(MAX);
set @ColumnHeaders = STUFF( (SELECT DISTINCT ',' + 'Max(CASE WHEN rn2=' + Cast(rn2 as varchar(2)) + ' THEN code2 else null end ) as ' + 'code1_'+Cast(rn2 as varchar(2))  
+ char(10)+char(13)  +','+ 'Max(CASE WHEN rn2=' + Cast(rn2 as varchar(2)) + ' THEN code1 else null end ) as ' +  'code2_'+Cast(rn2 as varchar(2)) 
+ char(10)+char(13)
FROM (
Select id,yearcol,code1,code2, row_number() Over(Partition by ID,yearCol,rn  Order by Code2) rn2
From (
Select ID ,yearCol,code1,code2
,row_number() Over(Partition by ID,yearCol,code1,Code2  Order by Code2) rn   
from test) t
WHERE rn=1
) t1
 FOR XML PATH(''), TYPE).value('.', 'varchar(max)'), 1, 1, '');
---print @ColumnHeaders
 
Set @sql  =' ;with mycte as (
Select ID ,yearCol,code1,code2
,row_number() Over(Partition by ID,yearCol,code1,Code2  Order by Code2) rn   from test) 
,mycte1 as (
Select id,yearcol,code1,code2,
 row_number() Over(Partition by ID,yearCol,rn  Order by Code2) rn2
From mycte
WHERE rn=1
)
Select id,yearcol,'+   @ColumnHeaders + ' from mycte1
Group by  id,yearcol ';
     
print @sql
 
EXECUTE sp_executesql @sql 



 drop table test


--another with two set columns
CREATE TABLE Table1( ID INT ,[year] INT);
INSERT INTO Table1( ID, [year]) VALUES  (1,2015) ,(2,2016) ;
CREATE TABLE Table2( ID INT ,TXCode INT , Spcode INT   );
INSERT INTO Table2( ID, TXCode  ,Spcode) 
VALUES  (1, 11, 12) ,(1, 23,12)  ,(2, 88,22) ,(2,92,111);
 

--dynamic with case
DECLARE @Sql NVARCHAR(4000) =null
    
declare @ColumnHeaders NVARCHAR(4000) ;
set @ColumnHeaders = STUFF( (SELECT DISTINCT ',' + 'Max(CASE WHEN rn =' + quotename(rn,'''') + ' THEN  TXCode  else null end ) as ' + quotename('TXCode'+Cast(rn as varchar(10)),'[')  + char(10)+char(13)
                           +',' + 'Max(CASE WHEN rn =' + quotename(rn,'''') + ' THEN  Spcode  else null end ) as ' + quotename('Spcode'+Cast(rn as varchar(10)),'[')  + char(10)+char(13)
						     FROM  ( select t2.ID,t1.[year],t2.TXCode,t2.Spcode 
, Row_number() Over(Partition by t2.ID Order by t2.TXCode  ) rn 
from Table1 t1 join Table2 t2 on t1.ID=t2.ID) t
                             FOR XML PATH(''), TYPE).value('.', 'varchar(max)'), 1, 1, '');
  --print @ColumnHeaders
   
Set @sql  =N' ;with mycte as (
 select t2.ID,t1.[year],t2.TXCode,t2.Spcode 
, Row_number() Over(Partition by t2.ID Order by t2.TXCode  ) rn 
from Table1 t1 join Table2 t2 on t1.ID=t2.ID) 
Select   ID, [year], '+   @ColumnHeaders + ' from mycte Group by  ID, [year] ';
       
--print @sql
EXEC(@SQL)
  

drop table Table1,Table2

 
  
 CREATE TABLE test ( BENR VARCHAR(255) , ORGEAN VARCHAR(255) )
 INSERT INTO test values ('*0088041', '5060057720790'),('*0088041', '5060057721933'),('*0088041','8718104010208');
--static with case
;with mycte as (
select * , Row_number() Over(Partition by BENR Order by ORGEAN ) rn from test)

Select BENR, Max(Case when rn=1 then ORGEAN end) as  ORGEAN1
,Max(Case when rn=2 then ORGEAN end) as ORGEAN2,
Max(Case when rn=3 then ORGEAN end) ORGEAN3 from mycte
Group by BENR



--dynamic with case
DECLARE @Sql NVARCHAR(4000) =null
   
declare @ColumnHeaders NVARCHAR(4000) ;
set @ColumnHeaders = STUFF( (SELECT DISTINCT ',' + 'Max(CASE WHEN rn =' + quotename(rn,'''') + ' THEN  ORGEAN  else null end ) as ' + quotename(ORGEAN+Cast(rn as varchar(10)),'[')  + char(10)+char(13)
                             FROM  ( select * , Row_number() Over(Partition by BENR Order by ORGEAN ) rn from test) t
                             FOR XML PATH(''), TYPE).value('.', 'varchar(max)'), 1, 1, '');
  --print @ColumnHeaders
  
Set @sql  =N' ;with mycte as (
select * , Row_number() Over(Partition by BENR Order by ORGEAN ) rn from test) 
Select  BENR, '+   @ColumnHeaders + ' from mycte Group by BENR ';
      
--print @sql
EXEC(@SQL)
 



drop table test
 


 

https://social.msdn.microsoft.com/Forums/en-US/afafd877-7498-431d-892e-e22b825710e9/sql-pivot-help?forum=transactsql

https://social.msdn.microsoft.com/Forums/en-US/c1831f46-c5be-4dbd-98ab-23515179a274/transform-records?forum=transactsql

https://social.msdn.microsoft.com/Forums/sqlserver/en-US/9ef8cbe9-c64e-4d94-815b-97ed25b4de2b/pivot-with-characters?forum=transactsql

https://social.msdn.microsoft.com/Forums/en-US/93af8ff1-ad3b-488f-9134-1b987664257b/adding-extra-column?forum=transactsql


Dynamic Case List for Pivot

  
 

 https://social.msdn.microsoft.com/Forums/en-US/245f9c25-0975-4faa-bea4-bd9871a3c17b/build-dynamic-output-query-from-sql-based-on-parm-passed-it?forum=transactsql

 ---All tables have the same structure

CREATE TABLE [dbo].[F315-Line12](
	[Stamp_time] [datetime] NOT NULL,
	[Bead1Tst] [decimal](18, 0) NULL,
	[Bead2Tst] [decimal](18, 0) NULL,
	[Bead3Tst] [decimal](18, 0) NULL,
	[Speed] [int] NULL,
	[Volts] [int] NULL)

 

INSERT INTO  [F315-Line12]  VALUES ('2016-12-12 09:25:00.000',5 ,6,8,70,20)

Declare @tablename nvarchar(256)
DECLARE @Param INT =10
---output file called 315xx

--If @Param  = 5  
--Begin
--Set @tablename ='F315-Line5'
--End
If @Param  = 10  
Begin
Set @tablename ='F315-Line10'
End

--If @Param  = 11  
--Begin
--Set @tablename ='F315-Line11'
--End
If @Param  = 12  
Begin
Set @tablename ='F315-Line12'
End



Declare @sqlCase as NVarchar(4000) =null
Declare @sql as NVarchar(4000)=null
 
  
 --===== Create number table on-the-fly
;WITH Num1 (n) AS (
SELECT 1 as n
UNION ALL SELECT n+1 as n
FROM Num1 Where n <101), 
Nums (n) AS (SELECT ROW_NUMBER() OVER(ORDER BY n) FROM Num1
WHere n<= @Param ) ---Control the total columns list

Select @sqlCase =  COALESCE(@sqlCase + ', ', '') 
+ ' CASE WHEN @Param=' + Cast(  n   as varchar(10))+ ' THEN '   
+  QUOTENAME(COLUMN_NAME)+' ELSE 0 END as ' + QUOTENAME(COLUMN_NAME  + Cast(n as varchar(10))  ) +char(10)+char(13)
FROM   [INFORMATION_SCHEMA].[COLUMNS]
cross apply (Select n From Nums) d (n)
WHERE TABLE_NAME= @tablename and COLUMN_NAME Like 'Bead%Tst'
order by n, COLUMN_NAME
print @sqlCase


Set @sql='Select  [Stamp_time], ' + @sqlCase + ' from  ' + QUOTENAME(@tablename)
 print @sql
 EXEC sp_executesql @sql, N'@Param int ' ,@Param=@Param ;
 
 

 

A fatal alert was generated and sent to the remote endpoint. This may result in termination of the connection. The TLS protocol defined fatal error code is 10. The Windows SChannel error state is 1203.

Support from HP site:

http://h20564.www2.hpe.com/hpsc/doc/public/display?docId=mmr_kc-0123982

In Group Policy Editor (run: gpedit.msc),
Go to Computer Configuration > Administrative Templates > System > Distributed COM > Application Compatibility and enable “Allow local activation security check exemptions”.