Mahipal Padigela's Blog

Hierarchical Reporting in SQL Server Reporting Services (SSRS)

This post is to remind myself about the hierarchcial grouping Report that I did last week using SQL Server Reporting Services (SSRS). I was trying to understand the business segmentation hierarchy. Among other fields, segmentation table has 2 fields, segment and parent segment, the latter being a recursive pointer to the former (The relation between them is comparable to Employee and Manager fields in the traditional EMP table).

• Create dataset with segment and parent segment fields
• Insert a table in the “Layout” tab
• select the table and select “insert group” option
• In the grouping and sorting dialog,
• Select “Segment” field in the “Group on” expression area
• Select “Parent Segment” in the “Parent Group” area
• Uncheck “Include group footer” option

• Drag “Segment” field into the newly added Group Header
• De-select Table Header, Table Details and Table Footer sections in the Table properties, remove all un-used columns from the table
• Make sure the Segment textbox is wide enough to show full hierarchy depth

• Select Group Header box(Segment field) in the table, open properties dialog, goto “Padding” section, and add =Convert.ToString(2 + (Level()*50)) & “pt” as expression to the LEFT property. This is key step to get the indentation (adjust values accordingly) (if you’ve more fields on your report, you might need to add this expression to other fields as well)

• Optional: added =IIF(Level()=0 OR Level()=1 OR Level()=2,”Bold”,”Normal”) as expression to the “FontWeight” property to make the first two levels bold
• Report Preview

Programmatically Listing Reports/Items Dependent on a given dataSource – SSRS

How do I see a list of reports pointing to “ODS” database. This is fine if there was only one datasource for all the reports: you can simply browse to ReportServer website, goto the data source in question and select dependent items link. The following code loops through items on the ReportServer, looks for DataSources, extracts Datasource definition, retrieves ConnectString from the definition, looks for “ODS” in the Connection string and then calls the ListDependentItems function of ReportServer webservice to get a list if reports/models(It writes the list to your visual studio output window)

Note: you need to add a web reference to your ReportServer webservice

(eg: http://myreportserver/reportserver/reportservice2005.asmx?wsdl )

‘add the following imports
 Imports System
 Imports System.Collections.Generic
 Imports System.Text
 Imports System.Xml
 Imports RSUtilities.SSRSWS  ‘ (This is your ProjectName.WebServiceName)
 Imports System.IO
 Imports System.Net
 Imports System.Web.Services.Protocols ‘add a button to your form and copy the following code

Private Sub Button1_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles Button1.Click
 Dim reportName As String
 Dim fromServer As String = “myserver”
 Dim fromFolder As String = “/UAT/Reports” ‘”/” for root folder
 Dim items As CatalogItem() = Nothing
 Dim item As CatalogItem = Nothing
 Dim DependentItems As CatalogItem() = Nothing
 Dim DependentItem As CatalogItem = Nothing
 Dim definition As New DataSourceDefinition
 Dim strConn As String
 Dim fromRS As New SSRSWS.ReportingService2005

 fromRS.Url = “http://” + fromServer + “/reportserver/reportservice2005.asmx?wsdl”
 fromRS.Credentials = System.Net.CredentialCache.DefaultCredentials

 items = fromRS.ListChildren(fromFolder, True)

 For Each item In items
    If item.Type = ItemTypeEnum.DataSource Then
  definition = fromRS.GetDataSourceContents(item.Path)
  strConn = definition.ConnectString.ToLower

 If strConn.Contains(“ods”) Then
   DependentItems = fromRS.ListDependentItems(item.Path.ToString)

  If DependentItems.Length > 0 Then
    Console.WriteLine(item.Path.ToString)
   End If

  For Each DependentItem In DependentItems
   reportName = DependentItem.Path.ToString
   Console.WriteLine(“REPORT — ” + reportName)
  Next
  End If
    End If

 Next

  MsgBox(“Done”)

  fromRS = Nothing
 End Sub

Tags: Reporting services automation, reportservice2005.asmx, ssrs programming

SQL Server Reporting Services (SSRS) – Utilities

This little utility helps in the import (download)/export (publish) of Reports from/to SSRS (SQL Server Reporting Services) Report Manager. It also lets you to copy reports between Servers, for ex. when you are moving reports from UAT to Production etc. and to re-point data sources. Also useful to answer questions like; what items are dependent on this Model? which reports are pointing to my ODS database? what reports have the file-based subscriptions on my Report Server and where are they saved? etc.

It’s written in C# and consumes ReportService2005 webservice (reportservice2005.asmx). I hope to develop this into a proper tool when I get some free time.

You need .net framework 2.0 or above to be able to run this.

Sorry, download link now removed, let me know if you are interested.

Please make sure you backup your system before using it.

While I strive hard to provide accurate information, as a user, you agree that you bear sole responsibility for your own decisions to use any programs, documents, source code, tips, articles provided on this website. I do not guarantee the accuracy, timeliness and completeness of any data, Information, code on this website and shall not be liable for any costs, losses or damages caused or alleged to have been caused directly or indirectly upon reliance on any material on this website

Start/Stop windows services from SSIS

We had a requiremnet to stop Reporting Services windows service(ReportServer) at the beginning of ETL load and start it at the end. I used a couple of Script tasks (one at the beginning and one at the end of SSIS Master Package) with code something like this:

drag a script task onto control flow and add the following code to stop the service

'stop ReportServer service script task
Imports System
Imports System.Data
Imports System.Math
Imports Microsoft.SqlServer.Dts.Runtime
Imports System.ServiceProcess

Public Class ScriptMain

    Public Sub Main()
        Dim controller As New ServiceController
        controller.MachineName = "."
        controller.ServiceName = "ReportServer"

        Dim status As String = controller.Status.ToString

        If status.ToLower = "running" Then
            controller.Stop()
        End If

        Dts.TaskResult = Dts.Results.Success
    End Sub

End Class

drag a script task onto control flow and add the following code to start the service

'start ReportServer service script task
Imports System
Imports System.Data
Imports System.Math
Imports Microsoft.SqlServer.Dts.Runtime
Imports System.ServiceProcess

Public Class ScriptMain
    Public Sub Main()

        Dim controller As New ServiceController
        controller.MachineName = "."
        controller.ServiceName = "ReportServer"

        Dim status As String = controller.Status.ToString

        Do While Not status.ToLower = "running"
            Try
                controller.Start()
            Catch e As Exception
                controller.Refresh()
                status = controller.Status.ToString
            End Try
        Loop

        Dts.TaskResult = Dts.Results.Success
    End Sub

End Class

Tags: SSIS and windows services, stop reportserver