December 29, 2008 at 12:34 pm
I am utilizing the web service for SSRS 2005 and am wondering how to extract the report's schedule name. Want to receive blank if it is an unscheduled report.
Any tips are appreciated!
December 29, 2008 at 2:01 pm
December 29, 2008 at 2:10 pm
Currently I'm parsing the Schedules and then figuring out if my one report is in the schedule...Here's what I have so far.
Private Sub getReportHistory(ByVal ReportName As String)
'Generates Full Report Path
Dim fullReportPath As String = ConfigurationManager.AppSettings("reportFolder").ToString + ReportName
Dim rs As New ssrs.ReportingService2005
Dim isPeriodEndReport As Boolean = False
rs.Credentials = System.Net.CredentialCache.DefaultCredentials
Try
'Checking for the Period End Shared Schedule to see if this report is
'in that schedule or not.
Dim sch() As ssrs.Schedule
Dim ss As ssrs.Schedule
sch = rs.ListSchedules()
For Each ss In sch
If ss.Name = ConfigurationManager.AppSettings("reportSchedule").ToString Then
'This is the report schedule that we're concerned with!
Dim ctl() As ssrs.CatalogItem
ctl = rs.ListScheduledReports(ss.ScheduleID)
Dim myctl As ssrs.CatalogItem
'Finding all reports that are in this schedule.
For Each myctl In ctl
If myctl.Name = ReportName Then
'IF we find our report that means it is one we're looking for and we can exit.
isPeriodEndReport = True
Exit For
End If
Next myctl
Exit For
End If
Next ss
Dim rptLimit As Int32
Dim rptCount As Int32 = 0
If isPeriodEndReport = True Then
'PERIOD END reports will show the Month/Year and Denote Year End reports when it's a MAY End Report
rptLimit = CInt(ConfigurationManager.AppSettings("PeriodEndLimit").ToString)
Dim props() As ssrs.Property
props = rs.GetProperties(fullReportPath, Nothing)
Dim pp As ssrs.Property
For Each pp In props
Response.Write((pp.Name + ": " + pp.Value + "
"))
Next pp
Dim snap As ssrs.ReportHistorySnapshot
For Each snap In rs.ListReportHistory(fullReportPath)
If rptCount = rptLimit Then
Exit For
End If
Dim url As String = ConfigurationManager.AppSettings("reportURL").ToString + fullReportPath + "&HistoryID="
url = url + snap.HistoryID
Dim l As New HyperLink
l.Text = MonthName(Month(DateAdd(DateInterval.Month, -1, snap.CreationDate)), True) & " " & Year(DateAdd(DateInterval.Month, -1, snap.CreationDate))
l.NavigateUrl = url
Me.Page.Controls.Add(l)
Response.Write("
")
rptCount += 1
Next snap
Else
'NON PERIOD END reports will show the actual date of execution and only expose the past 5
'REPORT PROPERTIES
rptLimit = CInt(ConfigurationManager.AppSettings("OtherLimit").ToString)
Dim props() As ssrs.Property
props = rs.GetProperties(fullReportPath, Nothing)
Dim pp As ssrs.Property
For Each pp In props
Response.Write((pp.Name + ": " + pp.Value + "
"))
Next pp
Dim snap As ssrs.ReportHistorySnapshot
For Each snap In rs.ListReportHistory(fullReportPath)
Dim url As String = ConfigurationManager.AppSettings("reportURL").ToString + fullReportPath + "&HistoryID="
Response.Write("
")
Response.Write(snap.HistoryID & " - " & snap.CreationDate)
url = url + snap.HistoryID
Dim l As New HyperLink
l.Text = MonthName(Month(DateAdd(DateInterval.Month, -1, snap.CreationDate)), True) & " " & Year(DateAdd(DateInterval.Month, -1, snap.CreationDate))
l.NavigateUrl = url
Me.Page.Controls.Add(l)
Response.Write(vbCrLf)
Next snap
End If
Catch a As Exception
Response.Write(a.Message)
End Try
End Sub
December 30, 2008 at 7:02 am
So I'm sure there's a way to do this with the Web Service, although I think it looks like what you're doing should work, but if it was me, I'd probably just query the ReprotServer Database directly. Something like the following should return what you are looking for. You just need to format the NULLS however you want to...
SELECT cat.[Name] AS ReportName,
[Schedule].[Name] AS ScheduleName
FROM [Catalog] cat
LEFT JOIN [ReportSchedule] rs
ON cat.[ItemID] = rs.[ReportID]
left JOIN [Schedule]
ON rs.[ScheduleID] = [Schedule].[ScheduleID]
WHERE cat.TYPE=2 --Is a report
I suppose you could even pass in the name of the report in your where statement if you're looking for a specific report AND cat.Name = 'MyReport'
Hope this helps.
-Luke.
December 30, 2008 at 7:05 am
Thanks. After playing with the web service, I have decided that SQL will be much easier to grab the information that i need.
December 30, 2008 at 7:08 am
Viewing 6 posts - 1 through 5 (of 5 total)
You must be logged in to reply to this topic. Login to reply