Category SSRS

Oct 31 2019

Check SQL Lock 

SELECT s.session_id    AS 'SessionId',
        s.login_name    AS 'Login',
        COALESCE(s.host_name, c.client_net_address) AS 'Host',
        s.program_name  AS 'Application',
        t.task_state    AS 'TaskState',
        r.start_time    AS 'TaskStartTime',
        r.[status] AS 'TaskStatus',
        r.wait_type     AS 'TaskWaitType',
        TSQL.[text] AS 'TSQL',
        (
            tsu.user_objects_alloc_page_count - tsu.user_objects_dealloc_page_count
        ) +(
            tsu.internal_objects_alloc_page_count - tsu.internal_objects_dealloc_page_count
        )               AS 'TotalPagesAllocated'
FROM   sys.dm_exec_sessions s
        LEFT  JOIN sys.dm_exec_connections c
             ON  s.session_id = c.session_id
        LEFT JOIN sys.dm_db_task_space_usage tsu
             ON  tsu.session_id = s.session_id
        LEFT JOIN sys.dm_os_tasks t
             ON  t.session_id = tsu.session_id
             AND t.request_id = tsu.request_id
        LEFT JOIN sys.dm_exec_requests r
             ON  r.session_id = tsu.session_id
             AND r.request_id = tsu.request_id
        OUTER APPLY sys.dm_exec_sql_text(r.sql_handle) TSQL
WHERE  (
            tsu.user_objects_alloc_page_count - tsu.user_objects_dealloc_page_count
        ) +(
            tsu.internal_objects_alloc_page_count - tsu.internal_objects_dealloc_page_count
        ) > 0;



--kill 80
Jul 27 2017

SSRS: Repeat rows of a table, if they break across a page

how to repeat rows of a table, if they break across a page.  In the grouping section/pane, turn on advanced, so you can see the static lines:

1

When you select one of those, you will see the border around at least 1 cell of the row that you are selecting, in the table you are on:

2

On the properties pane you will see RepeatOnNewPage. Set that to True:

3

I verified that this does work, as long as the table is built properly.

By simplemsexchange • Posted in SSRS
0
Jun 14 2017

List all SSRS subscriptions 

USE [ReportServer];  -- You may change the database name. 
GO 
 
SELECT USR.UserName AS SubscriptionOwner 
      ,SUB.ModifiedDate 
      ,SUB.[Description] 
      ,SUB.EventType 
      ,SUB.DeliveryExtension 
      ,SUB.LastStatus 
      ,SUB.LastRunTime 
      ,SCH.NextRunTime 
      ,SCH.Name AS ScheduleName       
      ,CAT.[Path] AS ReportPath 
      ,CAT.[Description] AS ReportDescription 
FROM dbo.Subscriptions AS SUB 
     INNER JOIN dbo.Users AS USR 
         ON SUB.OwnerID = USR.UserID 
     INNER JOIN dbo.[Catalog] AS CAT 
         ON SUB.Report_OID = CAT.ItemID 
     INNER JOIN dbo.ReportSchedule AS RS 
         ON SUB.Report_OID = RS.ReportID 
            AND SUB.SubscriptionID = RS.SubscriptionID 
     INNER JOIN dbo.Schedule AS SCH 
         ON RS.ScheduleID = SCH.ScheduleID 
ORDER BY USR.UserName 
        ,CAT.[Path];


Refference: https://gallery.technet.microsoft.com/scriptcenter/List-all-SSRS-subscriptions-968ae4d5
Oct 6 2014

DataSet panel in SSRS designer (BIDS or SQL Server Data Tools for SQL 2012) is gone

Dataset not showing in the left side ?

If you are using BIDS with SQL 2008 R2 you can only get the “Report Data” menu by clicking inside the actual report layout itself.

  1. Click inside the actual report layout.
  2. Now select “View” from the main menu bar.
  3. Now select “Report Data” which is the last item.