Tuesday, 26 October 2010

Exporting to Excel and renaming sheets in 2008 R2

In Reporting Services 2008 R2 Microsoft has added functionality to rename multiple sheets, so if your report is more than one page and you want to rename the worksheets this will work automatically out of the box.

You can either rename the sheets for a group, if each group has a page break between them or you can rename sheets if there is page break between objects.

To rename sheets in a table with groups all you have to do is select the group as shown below and then in the properties window (press F4 if the properties window is not shown) change the setting for pagename under the Group section to the value you want. This could be text or a field from your dataset.

The export to excel will rename the sheets with the group name.

Exporting to Excel and renaming sheets in 2005

In Reporting Services 2005 there was no functionality to rename sheets when your report was more than one page. You had to either use custom code to rename the sheets or use a macro.

We created a field in one of the cells in the report and then changed the font colour to white, then we created a macro to copy the value in the cell and rename the sheets. The macro is shown below:

Sub RenameSheets()
' Renames all the sheets based on the contents of the cell A1
' If the cell A1 is empty the sheet will be renamed and there is a limit of 31 characters if this is exceeded then only the first 31 are used
' If the following characters are used then an error will be returned:
' []?*/\:

Dim i As Integer

For i = 1 To Sheets.Count

If (Len(Worksheets(i).Range("A2").Value) < 32) Then
If (Len(Worksheets(i).Range("A2").Value) > 0) Then
Sheets(i).Name = Worksheets(i).Range("A2").Value
End If
Sheets(i).Name = Left(Worksheets(i).Range("A2").Value, 31)
End If
End Sub

Monday, 25 October 2010

New Line in a Text Box

In a text box field you can use the following code to force reporting services to start a next line.

'This the first line'+ VBCRLF +
'This is the second line'+ VBCRLF +
'This is the third'

Subscriptions SQL query

Due to the fact the reporting services allows you to query the tables you can easily write sql which will allow you to return data from the reportserver database.

The script below specifically looks at the subscriptions table which can be created into a report with filters. It is quite handy when you have hundreds of subscriptions to easily find the one you are looking for.

Also as some developers will chose to create specific report subscriptions rather than use shared schedules it can be difficult to work out when they will be sent.

The way that the fields for the report specific times are stored is bascially
1,2,4,8,16 etc for example for the day of the week field
1 is Sunday, 2 is Monday, 4 is Tuesday, 8 is Wednesday etc

All the fields are coded like this so it may be best to look at all the distinct values and then work out all the coding in your case statements.

The script will work for both reporting services 2005 and 2008 r2

note remember to change the from table it is looking a reportserver_native!


case when parameters like '' then 'No Parameters' else
parameters end as Para,substring(path,2,50) as path_link,
recipients+' - '+cc+' - '+bcc as AllRecipients,
* from (

month as month2,

when recurrencetype = 1 then 'One off'
when recurrencetype = 2 then 'Hour'
when recurrencetype = 4 then 'daily'
when recurrencetype = 5 then 'monthly'
when recurrencetype = 6 then 'month Week'

end as Typ,

case when daysofmonth = 1 then '1'
when daysofmonth = 2 then '2'
when daysofmonth = 4 then '3'
when daysofmonth = 8 then '4'
when daysofmonth = 16 then '5'
when daysofmonth = 32 then '6'
when daysofmonth = 64 then '7'
when daysofmonth = 128 then '8'
when daysofmonth = 256 then '9'
when daysofmonth = 512 then '10'
when daysofmonth = 1024 then '11'
when daysofmonth = 2048 then '12'
when daysofmonth = 4096 then '13'
when daysofmonth = 8192 then '14'
when daysofmonth = 16384 then '15'
when daysofmonth = 32768 then '16'
when daysofmonth = 65536 then '17'
when daysofmonth = 131072 then '18'
when daysofmonth = 262144 then '19'
when daysofmonth = 524288 then '20'
when daysofmonth = 1048576 then '21'
when daysofmonth = 2097152 then '22'
when daysofmonth = 4194304 then '23'
when daysofmonth = 8388608 then '24'
when daysofmonth = 16777216 then '25'
when daysofmonth = 33554432 then '26'
when daysofmonth = 67108864 then '27'
when daysofmonth = 134217728 then '28'
when daysofmonth = 268435456 then '29'
when daysofmonth = 536870912 then '30'
when daysofmonth = 1073741824 then '31'
when daysofmonth = 8193 then '1st and 14th day'
end as daysofmonth,

when Month = 4095 then 'All Months'
when MONTH = 585 then 'Jan,April,July,October'
when Month = 1 then 'Jan'
when Month = 2 then 'Feb'
when Month = 4 then 'Mar'
when Month = 8 then 'Apr'
when Month = 16 then 'May'
when Month = 32 then 'Jun'
when Month = 64 then 'Jul'
when Month = 128 then 'Aug'
when Month = 256 then 'Sep'
when Month = 512 then 'Oct'
when Month = 1024 then 'Nov'
when Month = 2048 then 'Dec' end as Month,
isnull(MinutesInterval,0) AS MinutesInterval,
case when isnull(MinutesInterval,0) <60 then 0 else minutesinterval/60 end AS Hours, case when daysofweek = 1 then 'Sun' when daysofweek = 2 then 'Mon' when daysofweek = 4 then 'Tue' when daysofweek = 8 then 'Wed' when daysofweek = 16 then 'Thu' when daysofweek = 32 then 'Fri' when daysofweek = 64 then 'Sat' when daysofweek = 62 then 'Mon-Fri' when daysofweek = 10 then 'Mon AND Wed' when daysofweek = 127 then 'All Days' end as Daysofweek, subs.subscriptionid, c.path,c.Name as Report_Name, c.description as Report_Description, --right(,len(u1.username)-len(left(u1.username,12))) , right(u1.username,len(u1.username)-len(left(u1.username,12))) as Createdby, subs.Description,subs.laststatus,subs.lastruntime, right(u.username,len(u.username)-len(left(u.username,12))) as Modifiedby, subs.modifieddate, parameters, replace(replace(CAST(CAST(extensionsettings as XML).query('/ParameterValues/ParameterValue/Value[../Name = ''TO'']') as varchar(1000)), '', ''), '', '') + '; ' as Recipients, replace(replace(CAST(CAST(extensionsettings as XML).query('/ParameterValues/ParameterValue/Value[../Name = ''CC'']') as varchar(1000)), '', ''), '', '') + '; ' as CC, replace(replace(CAST(CAST(extensionsettings as XML).query('/ParameterValues/ParameterValue/Value[../Name = ''BCC'']') as varchar(1000)), '', ''), '', '') + '; ' as BCC, case when s1.eventtype <>'SharedSchedule' then 'ReportSpecific' else s1.Name end as Schedule_Name,
s1.startdate,s1.nextruntime,s1.eventtype,subs.description as [Subscription Description]
ReportServer_native.dbo.Subscriptions subs with(nolock)
join catalog c on c.itemid = subs.report_oid
join users u on u.userid = subs.modifiedbyid
join users u1 on u1.userid = subs.ownerid
join reportschedule rs on rs.subscriptionid = subs.subscriptionid
join schedule s1 on s1.scheduleid = rs.scheduleid
) as D

Thursday, 21 October 2010

Sending reports using SQL

Sometimes there may be a requirement to send a report using sql code or you may be fed up with having to change the time in report manager when you are develping a report.

1)The first step would be to create a shared schedule which is in the past
and give it a name eg My_schedule
see the screenshot for details.

In the reportserver database you will need to create a stored procedure which will allow you to do this but first you will need to create a Synonym to the schedule table. My understanding is that a synonym is normally used to save you time writing an object name if its on a different server, eg if you want to refer to a table called customers which was on a different server you would type
you can create a synoynm which will allow you to refer to this object without having to type the full name
2)create synonym dbo.mycutomers for [servername].[databasename].dbo.customers

So first create synonym for the schedule table on the reportserver database

create synonym dbo.runreportschedule for [servername].[databasename].dbo.schedule

Then create a store procedure which will fire the report schedule
CREATE PROC [dbo].Runreportschedule(@EventName AS NVARCHAR(1000))
dbo.dbo.runreportschedule AS s
NAME = @EventName
EXEC msdb..sp_start_job @job_name =@myScheduleID

You will need to give the system prcoedure sp_start_job permissions so that it can fire the report.

Everything is now set, All you need to do is create a subscription on report manager and then use the shared schedule you have created.

Then in query analyser execute the stored procedure

[dbo].Runreportschedule 'Myschedule'

This will then send the report immediately

T SQL Functions

T SQL Function which returns the date for the previous month, the date is required in 2 digits

select dbo.wflastmonthdate(27)
2010-09-27 00:00:00

create FUNCTION [dbo].[wflastmonthdate] (@day int)
returns smalldatetime

cast (
convert(char(4),case when datepart(mm,getdate())=12 then datepart(yy,getdate())-1 else datepart(yy,getdate()) end)
CASE WHEN len (case when datepart(mm,getdate())=12 then 1 else datepart(mm,getdate())-1 end) = 1 THEN
'0'+convert(char(1),case when datepart(mm,getdate())=12 then 1 else datepart(mm,getdate())-1 end) ELSE
convert(char(2),case when datepart(mm,getdate())=12 then 1 else datepart(mm,getdate())-1 end)
'-'+convert(char(2),@day)+ ' 00:00:00.000'
as smalldatetime)