Duplex Printing

Conditional Page Breaks in SSRS 2008 r2

I have recently been looking at conditional page breaks in SSRS 2008 r2 and did not know that it is possible to do via an expression.

For example if you have say 2 tables in a report and you want to conditionally force a page break between the 2 so that they appear separately on 2 pages based on a parameter.

This is possible, to do this first select the first table and then in the properties of the table under 'General' and then 'Pagebreak' change the break location to 'end'. This will then force a page break at the end of the table one.

To make the pagebreak conditional create a parameter called pagebreak with the available values of 'yes' and 'no'. Then in the properties for the table under the break location there is a field called 'Disabled' click on the dop down and select the expression option, in the expression box type the expression as:

=iif(Parameters!pagebreak.Value="no",True,False)

When you run the report change the pagebreak parameter and you will see that this forces a page break when you select 'yes'.

SQL Server Failed jobs in SSMS

The following sql will list all the jobs that have failed in sql server:

SELECT TOP 100 PERCENT      

Z.Originating_Server,
     msdb.dbo.sysjobhistory.message as Error_Message,
     len(msdb.dbo.sysjobhistory.message) as Error_Len
,    msdb.dbo.sysjobs.name AS Job_Name
    , msdb.dbo.sysjobhistory.step_id AS Step
    , msdb.dbo.sysjobhistory.step_name AS Job_Step_Name
    , (CASE
     WHEN  msdb.dbo.sysjobhistory.run_status = 0 THEN 'Failed'
     WHEN  msdb.dbo.sysjobhistory.run_status = 1  THEN 'Succeeded'
     WHEN  msdb.dbo.sysjobhistory.run_status = 2  THEN 'Retry'
     WHEN  msdb.dbo.sysjobhistory.run_status = 3  THEN 'Cancelled'
     WHEN  msdb.dbo.sysjobhistory.run_status = 4  THEN 'In progress'
    END)  AS Status
    , cast(
                   cast(msdb.dbo.sysjobhistory.run_date as varchar) + ' ' +
                  left(right('000000' + cast(msdb.dbo.sysjobhistory.run_time as varchar), 6), 2) + ':' +
                  substring(right('000000' + cast(msdb.dbo.sysjobhistory.run_time as varchar), 6), 3, 2) + ':' +
                 right(right('000000' + cast(msdb.dbo.sysjobhistory.run_time as varchar), 6),2)
                  as smallDatetime)  AS Date
   
     , (SELECT DATENAME (dw,  (select convert (smalldatetime, convert (varchar(10), msdb.dbo.sysjobhistory.run_date))))) AS [Day]
   
    , (CASE
     WHEN len(msdb.dbo.sysjobhistory.run_duration) = 6 THEN substring(cast(run_duration as char(6)),1,2)+':'+substring(cast(run_duration as char(6)),3,2)+':'+substring(cast(run_duration as char(6)),5,2)
     WHEN len(msdb.dbo.sysjobhistory.run_duration) = 5 THEN '0' +substring(cast(run_duration as char(5)),1,1)+':'+substring(cast(run_duration as char(5)),2,2)+':'+substring(cast(run_duration as char(5)),4,2)
     WHEN len(msdb.dbo.sysjobhistory.run_duration) = 4 THEN '00'+':'+substring(cast(run_duration as char(4)),1,2)+':'+substring(cast(run_duration as char(4)),3,2)
     WHEN len(msdb.dbo.sysjobhistory.run_duration) = 3 THEN '00:0'+substring(cast(run_duration as char(3)),1,1)+':'+substring(cast(run_duration as char(3)),2,2)
     WHEN len(msdb.dbo.sysjobhistory.run_duration) = 2 THEN '00:00'+':'+substring(cast(run_duration as char(2)),1,2)
     WHEN len(msdb.dbo.sysjobhistory.run_duration) = 1 THEN '00:00:0'+substring(cast(run_duration as char(1)),1,1)
     ELSE '00:00:00'
    END)   AS [Duration]
    , msdb.dbo.syscategories.name AS Job_Category

FROM msdb.dbo.sysjobs LEFT OUTER  JOIN msdb.dbo.sysjobhistory ON msdb.dbo.sysjobs.job_id = msdb.dbo.sysjobhistory.job_id
INNER JOIN msdb.dbo.syscategories ON msdb.dbo.sysjobs.category_id = msdb.dbo.syscategories.category_id
join  msdb.dbo.sysoriginatingservers_view  Z on z.originating_server_id = msdb.dbo.sysjobs.originating_server_id
WHERE
cast(
            cast(msdb.dbo.sysjobhistory.run_date as varchar) + ' ' +
            left(right('000000' + cast(msdb.dbo.sysjobhistory.run_time as varchar), 6), 2) + ':' +
            substring(right('000000' + cast(msdb.dbo.sysjobhistory.run_time as varchar), 6), 3, 2) + ':' +
            right(right('000000' + cast(msdb.dbo.sysjobhistory.run_time as varchar), 6),2)
            as smallDatetime)  > DATEADD(dd, - 1, current_timestamp )
AND  (msdb.dbo.sysjobhistory.step_id > 0)
AND  (msdb.dbo.sysjobhistory.run_status <>1)
ORDER BY
[Date] DESC

CTEs

For Dates in a calendar style report you could use the following to generate the dates.

   with mycte as

   ( select cast('2012-01-01' as datetime) DateValue

        union all

        select DateValue + 1

        from    mycte  

         where   DateValue + 1 < getdate())

   -- select DateValue

   -- from    mycte

   --OPTION (MAXRECURSION 0)

select DateValue,

Year(DateValue) as Year,

DATEPART(Quarter ,DateValue) as Quarter,

DATENAME( month,DateValue) as MonthName,

DATEPART(m ,DateValue)  as MonthNo,

day (DateValue) as Day

from    mycte

OPTION (MAXRECURSION 0)

Failed Subscriptions

From time to time reports fail and you need know about them so that they can be sent to the relevant recipients without any delay. The script below has been used to create a report with data driven subscription.

SELECT
'Failed Subscriptions' as Type,
convert(char(40),c.path) as Folder_Path,
c.name as Report_Name,
case when len(convert(char(8000),parameters)) =19 then 'No Parameters' else
convert(char(700),parameters) end as parameters,
s.description as Recipients,s.lastruntime,
convert(char(500),laststatus) as Error,
'PathName' + replace(replace(c.path,'/','%2f'),' ','+') + '&ViewMode=Detail'
as [ReportURL],
'Pathname' + replace(replace(c.path,'/','%2f'),' ','+')
+ '&SelectedTabId=PropertiesTab&ViewMode=Detail&SelectedSubTabId=SubscriptionsTab'
as [SubsURL],
right(UserName,len(username)-12) as created_by,
case when s1.eventtype <>'SharedSchedule' then 'ReportSpecific' else s1.Name end as Schedule_Name
from subscriptions s
join catalog c on s.report_oid=c.itemid
join users u on s.ownerid = u.userid
LEFT OUTER join reportschedule rs on rs.subscriptionid = s.subscriptionid
LEFT OUTER join schedule s1 on s1.scheduleid = rs.scheduleid
where
(laststatus like '%fail%'
or LastStatus like 'error:%')

Action Go to URL

To open a mail message from a textbox you can use the following URL in the action box of the text.

mailto:user@gmail.com?subject=This%20is%20an%20email%20test

To open a webpage in a new page

="javascript:void(window.open('"+ "http://www.bbc.co.uk" + "','_blank'))"

In the report viewer there is no back button which will take tyou back to your original report. What you can do is create your own textbox which you use the action function to take you back to the main report. This refreshes the original report, you can use the following URL and this will take you back to the main report whithout refereshing it.

javascript:history.back(1)

T sql Scripts

Here are some scripts which I use now and then, it is quite useful to have these handy.

--- percentage Calculation returns zero
select (43/50) * 100

---percentage calculation where you cast the values
select cast(43 as float)/cast(50 as float)*100


---percentage calculation
---with 2 decimal places
select convert(decimal(10,2),cast(43 as float)/cast(50 as float)*100)

---Date Scripts

select datepart(d,getdate()) --returns day number
select datepart(dw,getdate()) --returns day of week, day 1 = Sunday and 7 = Saturday

select datename(month,GETDATE()) --retunrs Month Name
select left(datename(month,GETDATE()),3) --returns first 3 letters of Month Name

select datename(WEEKDAY,GETDATE()) --returns Day Name
select left(datename(WEEKDAY,GETDATE()),3) --returns first 3 letters of Day Name