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.[PathAS ReportPath 
      ,CAT.[DescriptionAS ReportDescription 
FROM dbo.Subscriptions AS SUB 
     INNER JOIN dbo.Users AS USR 
         ON SUB.OwnerID = USR.UserID 
     INNER JOIN dbo.[CatalogAS 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

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.

 

SSRS Conditional Changing Text Color

To make the contents of a text box render negative values in red for a field called Profit, open the Properties pane and use the following expression in the Color property:

=IIF(Fields!Profit.Value < 0, “Red”, “Black”)

To alternate the background color for every other row in a table, you can set the BackgroundColor property for each text box in the row to the same conditional expression. This technique works for detail rows only in a table with no groups.

=IIF(RowNumber(Nothing) MOD 2, “PaleGreen”, “White”)

Reff: http://msdn.microsoft.com/en-us/library/ms156400%28v=sql.100%29.aspx

Or for details look at this reff: http://blogs.msdn.com/b/davidlean/archive/2009/02/17/sql-reporting-how-to-conditional-color-1-4-the-basics-report-expressions-custom-code.aspx

By simplemsexchange Posted in SSRS

Print SSRS report directly to a printer without viewing using vb.net

1. Add a form PrintReport.vb

2. Add a report viewer in the form. configure the report viewer property (server url, path)

3.  code behind:

 

Imports System.Drawing.Printing
Imports System.Drawing.Imaging
Imports System.IO
Imports Microsoft.Reporting.WinForms

Public Class PrintReport
Dim pages As New List(Of Metafile)
Dim pageIndex As Integer = 0
Dim doc As New Printing.PrintDocument()

Private Sub PrintReport_Load(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles MyBase.Load
Dim doc As New Printing.PrintDocument()
doc = New Printing.PrintDocument()
AddHandler doc.PrintPage, AddressOf PrintPageHandler
Dim dialog As New PrintDialog()
dialog.Document = doc
Dim print As DialogResult
print = dialog.ShowDialog()

doc.PrinterSettings = dialog.PrinterSettings

Dim deviceInfo As String = _
“<DeviceInfo>” + _
“<OutputFormat>emf</OutputFormat>” + _
”  <PageWidth>8.5in</PageWidth>” + _
”  <PageHeight>11in</PageHeight>” + _
”  <MarginTop>0.25in</MarginTop>” + _
”  <MarginLeft>0.25in</MarginLeft>” + _
”  <MarginRight>0.25in</MarginRight>” + _
”  <MarginBottom>0.25in</MarginBottom>” + _
“</DeviceInfo>”

Dim warnings() As Microsoft.Reporting.WinForms.Warning ‘Warning
Dim streamids() As String
Dim mimeType, encoding, filenameExtension, path As String
mimeType = “” : encoding = “” : filenameExtension = “”

‘Report Parameter Input. Add form text box as per your requirements

Dim SALES_ORDER_NUMBER As String
Dim INVOICE_NUMBER As String
SALES_ORDER_NUMBER = CRMInvoicing.txtSALES_ORDER_NUMBER.Text
INVOICE_NUMBER = CRMInvoicing.txtINVOICE_NUMBER.Text
Dim parmSO As New ReportParameter(“SALES_ORDER_NUMBER”, SALES_ORDER_NUMBER)
Dim parmI As New ReportParameter(“INVOICE_NUMBER”, INVOICE_NUMBER)
Dim parmSO1(1) As ReportParameter
parmSO1(0) = parmSO
parmSO1(1) = parmI

Dim data() As Byte
rpt_control.ServerReport.SetParameters(parmSO1)
data = rpt_control.ServerReport.Render(“Image”, deviceInfo, mimeType, encoding, filenameExtension, streamids, warnings)
pages.Add(New Metafile(New MemoryStream(data)))

For Each pageName As String In streamids
data = rpt_control.ServerReport.RenderStream(“Image”, pageName, deviceInfo, mimeType, encoding)
pages.Add(New Metafile(New MemoryStream(data)))
Next
doc.Print()

Me.rpt_control.RefreshReport()
End Sub

Private Sub PrintPageHandler(ByVal sender As Object, ByVal e As PrintPageEventArgs)
Dim page As Metafile = pages(pageIndex)
pageIndex += 1
Dim pWidth As Integer = 827
Dim pHeight As Integer = 1100
e.Graphics.DrawImage(page, 0, 0, pWidth, pHeight)
‘e.Graphics.DrawImage(page, 0, 0, page.Width, page.Height)
e.HasMorePages = pageIndex < pages.Count
End Sub

End Class

 

 

Further Details here