Quick query to list which Reporting Services report the SQL Agent jobs relate to.

SELECT
  jb.[name] as [job_name]
  , cat.[name] as [report_name]
  , cat.[path] as [report_location]
  , sub.[description] as [sub_desc]
  , sub.[LastRunTime], sub.[LastStatus]
FROM (
    select 
      jb.[name]
      , replace(right(stp.[command]
        , charindex('=ataDtnevE@', reverse(stp.[command]))-1)
          , '''', '') as [SubscriptionID]
    from [msdb].[dbo].[sysjobs] jb
      inner join [msdb].[dbo].[sysjobsteps] stp
      on jb.job_id = stp.[job_id]
    where jb.[category_id] = 100
  ) jb
  JOIN [ReportServer].[dbo].[ReportSchedule] sch 
  ON jb.[SubscriptionID] = sch.[SubscriptionID]
    JOIN [ReportServer].[dbo].[Subscriptions] sub
    ON sch.[SubscriptionID] = sub.[SubscriptionID]
      JOIN [ReportServer].[dbo].[Catalog] cat
      ON sub.[report_oid] = cat.[itemid]

Listing Report Server Subscription Details

Ever get annoyed by the list of SQL Agent Reporting Services jobs with their non-sensical names. You don't know which ones are active subscriptions and which ones are only once off? Users ring you to say they're not receiving the report they subscribed to, and there is no quick way to see how the subscription is setup.

In Report Manager you can only view the subscriptions for the selected report. To see the schedule details to need to drill down another couple of screens.

Using Management Studio you are still stuck with looking at each individual report and then each subscription. At least when you bring up the properties for the subscription it's all within one dialog.

The following query will list the basic details about all the subscriptions in the ReportServer database. In the next iteration I hope to include more details such as file format, parameters selected, etc...

SELECT
  cat.path AS [ReportServerPath]
  , own.username AS SubscriptionCreatedBy
  , jb.date_created AS [SubscriptionCreatedOn]
  , mod.username AS [SubscriptionModifiedBy]
  , sub.ModifiedDate AS [SubscriptionModifiedOn]
  , sub.Description AS [SubscriptionDescription]
  , sub.EventType AS [SubscriptionType]
  , CASE
      WHEN PATINDEX('%Recurrence%', sub.MatchData) > 0THEN 'Recurring' 
      ELSE 'Once Off'
    END AS [ScheduleType]
  , sch.LastRunTime AS [SubscriptionLastRun]
FROM msdb.dbo.sysjobs jb
  INNER JOIN [ReportServer].[dbo].[Schedule] sch
  ON jb.name = CAST(sch.scheduleid AS VARCHAR(40))
    INNER JOIN [ReportServer].[dbo].[Subscriptions] sub
    ON sch.eventdata = sub.subscriptionid
      INNER JOIN [ReportServer].[dbo].[Catalog] cat
      ON sub.Report_OID = cat.ItemID
      INNER JOIN [ReportServer].[dbo].[Users] own
      ON sub.OwnerID = own.UserID
      INNER JOIN [ReportServer].[dbo].[Users] mod
      ON sub.ModifiedByID = mod.UserID 

Programmatically printing reports ... SP2 = Ahhh!

As I mentioned in a previous post Phills Philosophies: Programmatically printing reports ... SP2 = Ouch! printing reports directly to a printer had some undesirable effects when using SP2.

Well thanks to some assistance from Daniel Reib on the MSDN forums we discovered that not only did I need to set the printing points as per a comment by Keith Walton, I also needed to explicitly set the PageHeight and PageWidth.

I was able to retireve these easily enough via the web service and then based on the page sizing set the Landscape property in the DefaultPageSetting for the document.

My next trick is to intergrate what is now two command line apps (one for printing and one for report mailing) so we have a single application that provides all means of distributing the rendered reports.

Programmatically printing reports ... SP2 = Ouch!

I've been doing some more hack development work with SQL 2000 Reporting Services. I really like the programmability available via the web service.

Courtesy of some code provided on Reporting Service stalwart Bryan Keller's blog, I've been playing around with sending reports rendered via the web service direct to the printer. Now for some reason the reporting server I was testing against was only the RTM version and everything was working terrific. That was 3 days ago, my how things change in three days.

Come today we wanted to run out our 2500+ reports, which was going to save our Backoffice staff hours of manually printing individual printouts, we find that the production reporting server has SP2 installed and for some reason this causes the footer of the report to be dropped and the right-hand margin to be cut-off.

I have a feeling it might be something to do with rendering the reports with Letter sizing rather than A4 sizing. I'll post later when I discover the culprit. Meanwhile we're running our production report printing off an RTM Report Server, oh joy!

 

RE: Reporting Service article

Roman Rehak let's us know about his latest article.

My latest article called Programming Report Rendering in Reporting Services is in the latest issue of SQL Server Magazine. Since it's in the current issue, you don't have to be a subscriber to read it. It should be pretty useful for you if you need to call the SOAP API to generate reports from custom applications.

[Via SQLJunkies Blogs]

Running Reporting Services Script from Visual Studio

Been playing around with Reporting services scripting a bit lately.

The one gaping flaw with the Report Project in Visual Studio is that it doesn't support multiple levels of folders. So if you have Report Manager organised with reports in different folders depending on the Department/Run Time/Category/etc... you'll to choose from some not so nice options,
1) For each folder in Report Manager that holds reports, create a corresponding Report Project in Visual Studio.
2) Create one Report Project in Visual Studio and after deployment use Report Manager to move the reports to the appropriate folder.

For some time I was thinking that the second option was pretty cool. I had a nice simple Report Project. All my datasources and report resources were located in one spot. However, now that I'm deploying and re-deploying reports on a day-to-day basis I've discovered that even this is a real drag.

So then I started looking into deploying the reports via script, it kinda works at the moment, but still needs some bugs ironed out. (I'll post it when it's ready). Then came the hassle of jumping from Visual Studio to the command prompt and back again. Trying to remember the correct syntax for the parameters, etc... It was quickly after this that I discovered the 'External Tools...' option in Visual Studio. Turned out to be a very, very simple to setup and is a joy to behold ;-)

Choose 'External Tools...' from the 'Tools' menu in Visual Studio then follow through the steps,
1. Click Add to create a new entry
2. Give it a name you'll recognise
3. Browse to the location of the RS.EXE application
4. Use the arguments to add the ItemPath for the -i parameter and include the URL for your Report Server for the -s parameter
5. Set the initial directory to the project directory, or whichever directory you choose.
6. Personally I keep the check box option off, but try the different options and see which works best for you.

Once everything has been setup click 'Apply' and you'll have a new option on the 'Tools' menu.
I have the RSS file included in my Reports solution under Miscellaneous files. When I want to deploy the reports I open the RSS file and choose the run the script via the newly menu option.