Re run Subscriptions

When an email server failed overnight I used this script to generate the commands to re-send reports to subscribers …

-- RerunFailedSubscriptions.sql

-- generate the commands to resend report-emails that failed for some reason

select s.LastStatus, LastRunTime, 'exec sp_start_job @job_name = ''' + cast(j.name as varchar(40)) + '''' [Command To Re-run the Job]
from msdb.dbo.sysjobs j  
join  msdb.dbo.sysjobsteps js on js.job_id = j.job_id 
join  [ReportServer$REPORTS].[dbo].[Subscriptions] s  on js.command like '%' + cast(s.subscriptionid as varchar(40)) + '%' 
where s.LastStatus like 'Failure sending mail%';

A Report that uses its location

A customer had many copies of the same report – one for each site. And the application database had multiple copies of each table – one for each site. The table names were all appended with the sites name EG: “WP London_SalesHeader”, “WP Barcelona_SalesHeader” etc.

In Report Manager there was a folder for each site, containing sub-folders for different categories of report (EG: /London/Sales, OR /London/Production).

This is a simplified account of how I created a Report that returned only information specific to its location.

In Report Builder I created a hidden parameter called @site of type Text with no “Available Values” and its “Default Values” using the global variable ReportFolder.

As the output from this built-in variable would be like “\Paris\Sales” I had to create an expression for the “Default Value” of @site searching through each site name in turn …

=IIf(Globals!ReportFolder.Contains("Barcelona"),"WP Barcelona",
IIf(Globals!ReportFolder.Contains("Paris"),"WP Paris", "WP London"))

Finally, in the report query I derived the table name using the @site parameter.

declare @cmd varchar(max) = 
'select	[SalesCode],
	[Description],
	[NetWeight],
	[SalesDate]
from	[Production].[dbo].[' + @site + '_SalesHeader]'

exec(@cmd)

(NB: As a best-practice I displayed the value of @site, along with the other parameter choices, in the report sub-title.)

Recover deleted chart axis

I deleted my Chart date/time axis for a cleaner look – but then changed my mind .

capture

Within Report Builder 3 I recovered it by

  1. making the ‘Properties’ pane visible …

capture

2. Clicking on the chart, then in the Properties pane navigating to Chart, Chart Areas …

capture

3. I clicked on the ellipses (“…”) which brought up a chart-area properties box. Again I clicked the ellipses (next to Axis, Category Axis) …

capture

4. Then changed the Visibility to ‘True’ …

capture