SSRS

Execute SSRS subscriptions manually

USE [ReportServer];
GO 
SELECT
     S.ScheduleID AS SQLAgent_Job_Name
     ,SUB.Description AS Sub_Desc
     ,SUB.DeliveryExtension AS Sub_Del_Extension
     ,C.Name AS ReportName
     ,C.Path AS ReportPath
FROM ReportSchedule RS
     INNER JOIN Schedule S ON (RS.ScheduleID = S.ScheduleID)
     INNER JOIN Subscriptions SUB ON (RS.SubscriptionID = SUB.SubscriptionID)
     INNER JOIN [Catalog] C ON (RS.ReportID = C.ItemID AND SUB.Report_OID = C.ItemID)
WHERE
     C.Name IN('MyReportName'),


--copy SQLAgent_Job_Name from above query into below proc and execute

USE msdb
EXEC sp_start_job @job_name = '92C06E98-08F1-403A-879B-38D650A185FE' --MyReportName

Tags:

Wednesday, September 16th, 2015 SSRS No Comments

Accessing Member Properties on Parent-Child Hierarchies from SSRS Reorts

1) Edit MDX and add properties to DIMENSION PROPERTIES of the axis as below…..

SELECT	non empty {[Measures].[PnL],[Measures].[PL Limit]} ON COLUMNS , 
		NON EMPTY [Organization].[MyTree].members
   DIMENSION PROPERTIES PARENT_UNIQUE_NAME,[Organization].[MyTree].[My Level Name] ON ROWS  
   FROM [MYCUBE] 
   WHERE ([Date].[Date].&[20141210]) 

[Organization].[MyTree].[My Level Name] is a property of the MyTree at any given level.

2) SSRS query designer will automatically add a field (“My_Level_Name”) to the dataset, but it doesn’t show any data when we add the field to the Table (only in this case of Parent-Child Hierarchy, it works fine in other cases), so it is of no use to us, we might as well bin it.

3) When we need to access Member properties of the tree, we can use expression something like this….
=Fields!MyTree(“My Level Name”)

(Same as retrieving Cell Properties….=Fields!FieldName(“FORMAT_STRING”))

Tags: , , ,

Monday, December 15th, 2014 SSRS No Comments

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

Saturday, May 24th, 2008 SSRS No Comments

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: , ,

Saturday, April 12th, 2008 SSRS No Comments

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

Thursday, March 20th, 2008 SSRS No Comments

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: ,

Saturday, February 16th, 2008 SSIS, SSRS No Comments