Tag Archives: SSRS Custom Assembly

Prevent A Same SSRS Report From Running Concurrently

Some report could have quite long run more then some minutes. Usually due to heavy query or big content.
This blog will show how to prevent a specific SSRS report to have two or more report instances being run in same time.
So it applies only to each report you want to validate.

Creating Class Library

First, you need to make a class library either using VB.Net or C# and add a Web Reference to SQL Server Reporting Service API.
This class library is intended to list all active SSRS report jobs.

  1. Create a VB.Net Class Library Project and save it as SSRSLib.vb
  2. Add Reporting Service Web Reference.
    To do that, click Add Service Reference, Advanced, Add Web Reference.
    Inside Url text box type http://yourServIpAddr/ReportServer/ReportService2010.asmx for SSRS 2008 R2 version, click ‘go’ arrow icon.
    Edit the Web Reference Name to your prefer i.e ReportService2010 and then click Add Reference button.
  3. Write a class to retrieve list of SSRS Active report jobs
    Public Class SSRSInstance
    Public Sub New()
    
    End Sub
    Public Function getJobs() As DataTable
    	Dim dt As New DataTable()
    	dt.Columns.Add("JobID")
    	dt.Columns.Add("Name")
    	dt.Columns.Add("ComputerName")
    	dt.Columns.Add("UserName")
    	dt.Columns.Add("Action")
    	dt.Columns.Add("StartTime")
    	dt.Columns.Add("Status")
    
    	Dim rs As New ReportingService2010()
    	rs.Credentials = New System.Net.NetworkCredential("yourUserId", "yourPwd")
    	Dim jobs As Job() = Nothing
    	jobs = rs.ListJobs()
    
    	Dim job As Job
    	Dim dr As DataRow
    	For Each job In jobs
    		dr = dt.NewRow()
    		dr("JobID") = job.JobID
    		dr("Name") = job.Name
    		dr("ComputerName") = job.Machine
    		dr("UserName") = job.User
    		dr("Action") = job.JobActionName
    		dr("StartTime") = job.StartDateTime
    		dr("Status") = job.JobStatusName
    		dt.Rows.Add(dr)
    	Next job
    
    	Return dt
    End Function
    End Class
    
  4. Build your project. Since my assembly named SSRSLib so that build project will produce two dll files which are SSRSLib.dll & SSRSLib.XmlSerializers.dll
  5. It is better to test your class Lib with Console Application before proceeding any further to know whether it contains error or success

Okay first step has done. The SSRS list jobs class library is created. Usually the class library created for SSRS called custom assembly.
Continue reading

Advertisements

Accessing Database Record in Sql Server Reporting Service (SSRS) Custom Code

SSRS can use Custom Code to have more flexible way to achieve complex tasks like calculation, conditional result, and also accessing records on database.

Custom Code use System.Data Class to work with database which means read a records. This class have to be registered in SSRS config file and also have to be referenced in Report file.

In this blog post I will show you on how to read sql server record using SSRS Custom Code. I use SQL Server 2005 Express Edition and the database is AdventureWorks sample database.
I will list all Product Category with count number of product in specific category. I use Custom Code to calculate how many of product in category.

Here’s how to make Database enabled SSRS Custom Code:
Continue reading