Thursday, May 29, 2008

Deploy Reporting Services (SSRS) Solution

Background

I needed to deploy some SSRS reports for an application we are writing and I wanted to make sure the entire application is deployable through build scripts. When working with SSRS reports I know many get accustomed to deploying using Visual Studio and that is just not an acceptable method for a production deployment. I did some research and with the help of my colleague Ray Barley I was able to the following.

The solution I have is based off the RS Utility for Reporting Services. Go here to get some good background
on it. As well, all of the code is based off of these sample scripts.

My scenario is simple I need to create a folder, then create a shared data connection and then deploy five reports. The challenge I ran into was how to deploy the .rds data connection file that in Visual Studio because I had become accustomed to that being deployed for me. We could not find anything at the moment that would deploy the file however the RS Utility allows for one to be created on the fly.

The way this works is some VB.net code is written that will call the Reporting Services web service to complete the operations that I need. The VB.net code is placed into a .rss file which can be called via command line. What we did is create three .rss files for each of the major operations we need to complete and then created a little master .cmd file that drives the whole deployment. This should be pretty re-usable for future projects.

Deploy Folder

The following code will create a folder in Reporting Services and is in a file named deploy_folder.rss.

'
' Deploy SSRS Folder
'
' Sample Command Line:
'
' rs -i deploy_report.rss
' -s http://<server>/reportserver
' -v FOLDER="Test Deploy"
'
' FOLDER name of the folder
'

Dim parentFolder As String = "AdventureWorks Sample Reports"

Public Sub Main()

rs.Credentials = System.Net.CredentialCache.DefaultCredentials

'Create the parent folder
Try
rs.CreateFolder(FOLDER, "/", Nothing)
Console.WriteLine("Parent folder {0} created successfully", FOLDER)
Catch e As Exception
Console.WriteLine(e.Message)
End Try

End Sub

Deploy Data Source Integrated
Here is the code to deploy a data source that is using Windows Integrated Security and is in a file called deploy_datasource_integrated.rss.

'
' Deploy SSRS DataSource Integrated
'
' Sample Command Line:
'
' rs -i deploy_datasource.rss
' -s http://<server>/reportserver
' -v EXTENSION="SQL"
' -v CONN="Data Source=localhost;Initial Catalog=operationsdatamart;Connect Timeout=60"
' -v NAME="OperationsDataMart"
' -v FOLDER="/dev/Data Sources"
'
' CONN is the connection string
'
' FOLDER is the folder name to deploy to on the report server; it must already exist
'

Dim definition As [Byte]() = Nothing
Dim warnings As Warning() = Nothing

Public Sub Main()

Try
Dim definition As New DataSourceDefinition()
definition.CredentialRetrieval = CredentialRetrievalEnum.Integrated
definition.ConnectString = CONN
definition.Enabled = True
definition.EnabledSpecified = True
definition.Extension = EXTENSION
definition.ImpersonateUser = False
definition.ImpersonateUserSpecified = True
definition.Prompt = Nothing
definition.WindowsCredentials = False

rs.CreateDataSource(NAME, FOLDER, True, definition, Nothing)
Console.WriteLine("Data source {0} created successfully", NAME)

Catch e As Exception
Console.WriteLine(e.Message)
End Try
End Sub

Deploy Data Source Stored

The following code is for doing a deployment of a datasource where the credentials are stored. This will support using a Windows or SQL Server account. The code is put in a file called deploy_datasource_stored.rss.

'
' Deploy SSRS DataSource Stored
'
' Sample Command Line:
'
' rs -i deploy_datasource.rss
' -s http://<server>/reportserver
' -v EXTENSION="SQL"
' -v CONN="Data Source=localhost;Initial Catalog=operationsdatamart;Connect Timeout=60"
' -v NAME="OperationsDataMart"
' -v FOLDER="/dev/Data Sources"
' -v ISWINDOWS=True/False
' -v USER="CORPORATE\rbarley"
' -v PASSWORD="**********"
'
' CONN is the connection string
'
' FOLDER is the folder name to deploy to on the report server; it must already exist
'

Dim definition As [Byte]() = Nothing
Dim warnings As Warning() = Nothing

Public Sub Main()

Try
Dim definition As New DataSourceDefinition()
definition.CredentialRetrieval = CredentialRetrievalEnum.Store
definition.ConnectString = CONN
definition.Enabled = True
definition.EnabledSpecified = True
definition.Extension = EXTENSION
definition.ImpersonateUser = False
definition.ImpersonateUserSpecified = True
definition.Prompt = Nothing
definition.WindowsCredentials = ISWINDOWS
definition.UserName = USER
definition.Password = PASSWORD

rs.CreateDataSource(NAME, FOLDER, True, definition, Nothing)
Console.WriteLine("Data source {0} created successfully", NAME)

Catch e As Exception
Console.WriteLine(e.Message)
End Try
End Sub

Deploying a Report

Finally this is the code that will deploy a report. The code is in a file named deploy_folder.rss.

'
' Deploy SSRS Report
'
' Sample Command Line:
'
' rs -i deploy_report.rss
' -s http://<server>/reportserver
' -v PATH="C:\\Projects\\OperationsDataMart\\SSRS\\"
' -v REPORT="WeeklyOperations"
' -v FOLDER="/dev/WeeklyOperationsReports"
'
' PATH is the folder on the local hard drive where the report .rdl file exists. You must use \\
' for every backslash in the path; you must have a trailing backslash (to make this script
' simpler)
'
' REPORT is the file name of the report; do not include the .RDL extension
'
' FOLDER is the folder name to deploy to on the report server; it must already exist
'

Dim definition As [Byte]() = Nothing
Dim warnings As Warning() = Nothing

Public Sub Main()

Try
Dim stream As FileStream = File.OpenRead(PATH + REPORT + ".rdl")
definition = New [Byte](stream.Length) {}
stream.Read(definition, 0, CInt(stream.Length))
stream.Close()

warnings = rs.CreateReport(REPORT, FOLDER, True, definition, Nothing)

If Not (warnings Is Nothing) Then
Dim warning As Warning
For Each warning In warnings
Console.WriteLine(warning.Message)
Next warning

Else
Console.WriteLine("Report: {0} published successfully with no warnings", REPORT)
End If
Catch e As IOException
Console.WriteLine(e.Message)
End Try
End Sub

Running the Code

The following is a simple cmd file will create a folder, create a stored data connection using a windows account and upload five reports. Pretty simple.

set FOLDER=DemoReports

set SSRS_URL=http://localhost/reportserver

set DATA_SRC_NAME=DemoReportsConnection

set DB_CONNECTION=Data Source=localhost;Initial Catalog=Operations;Connect Timeout=60

set DB_EXTENSION=SQL

set DB_IS_WINDOWS_CONNECTION=True

set DB_USERNAME=CORPORATE\jason

set DB_PASSWORD=Pass@word1

set RDL_PATH=C:\\Demo\\Reports\\


rs -i deploy_folder.rss -s %SSRS_URL% -v FOLDER="%FOLDER%"

rs -i deploy_datasource_stored.rss -s %SSRS_URL% -v EXTENSION="%DB_EXTENSION%" -v CONN="%DB_CONNECTION%" -v NAME="%DATA_SRC_NAME%" -v FOLDER="/%FOLDER%" -v ISWINDOWS=%DB_IS_WINDOWS_CONNECTION% -v USER="%DB_USERNAME%" -v PASSWORD="%DB_PASSWORD%"

rs -i deploy_report.rss -s %SSRS_URL% -v PATH="%RDL_PATH%" -v FOLDER="/%FOLDER%" -v REPORT="DepartmentReport"

rs -i deploy_report.rss -s %SSRS_URL% -v PATH="%RDL_PATH%" -v FOLDER="/%FOLDER%" -v REPORT="ExecutiveReport"

rs -i deploy_report.rss -s %SSRS_URL% -v PATH="%RDL_PATH%" -v FOLDER="/%FOLDER%" -v REPORT="AccountingReport"

rs -i deploy_report.rss -s %SSRS_URL% -v PATH="%RDL_PATH%" -v FOLDER="/%FOLDER%" -v REPORT="FinanceReport"

rs -i deploy_report.rss -s %SSRS_URL% -v PATH="%RDL_PATH%" -v FOLDER="/%FOLDER%" -v REPORT="RollUpReport"

Tuesday, May 13, 2008

Recursive SQL Query

Some time ago I was done a ton do SQL 2005 development and I tons of requirements to do recursive queries. Well I had it come up again where I wanted to return back a tree of data. Not to say I could to the recursion on in some .Net code however it is a little cleaner in my situation to return the data from a stored procedure ready to go. There are tons of various solutions out there so use it if you like… I had found this solution on some blog two years ago, I lost the link and I luckily found the code sitting around deep in the bowels of one my code directories...

Here is the table definition:

CREATE TABLE [dbo].[Reference](
[ID] [int] IDENTITY(1,1) NOT NULL,
[Name] [nvarchar](100) NOT NULL,
[Phone] [nvarchar](50) NULL,
[Fax] [nvarchar](50) NULL,
[ParentID] [int] NULL,
[SortOrder] [int] NULL,
[Location] [nvarchar](50) NULL)

The table schema is really simple. ParentID is the ID to the record that is the parent. The SortOrder is the order specific to the level. This will make sense shortly but it is the sort for each level; an example would be:


ID    ParentID    SortOrder
1 NULL 0
2 1 0
3 1 1
4 2 0
5 2 1
6 2 2
7 3 0
8 3 1

So the SortOrder is relative to the grouping the ID is associated to. To select the data so that you get back all the data in the tree and so the data is nicely ordered just like above use this query.

    WITH Tree (id, [name], phone, fax, parentID, sortOrder, location, GroupLevel, SortKey) AS
(
-- Anchor query.
SELECT DISTINCT R.id, R.[name], R.phone, R.fax, R.parentID, R.sortOrder, R.location,
0, CAST(R.sortOrder AS VARBINARY(900))
FROM Referenceas R
WHERE R.parentID IS NULL
UNION ALL

-- Recursive query.
SELECT R.id, R.[name], R.phone, R.fax, R.parentID, R.sortOrder, R.location,
T.GroupLevel+1, CAST(T.SortKey + CAST (R.sortOrder AS BINARY(4)) AS VARBINARY(900))
FROM Referenceas R, Tree as T
WHERE R.parentID = T.id
)
SELECT
T.id,
T.[name],
T.phone,
T.fax,
T.parentID,
T.sortOrder,
T.location,
T.GroupLevel,
T.SortKey
FROM Tree as T
ORDER BY T.SortKey

This is how it works:

  • There is an inner query referred to as the anchor; this will get the root node. One limitation of this query is that there must be a root node. In this case, it is there the parentID IS NULL. However if you do not what to return the root node, you can add WHERE T.parentID IS NOT NULL to the outer query.
  • Then there is the recursive query which used the anchor query.
  • Both the anchor and recursion are based on the sortOrder.
  • The outer query selects data from the two inner queries and orders them based on the SortKey. The SortKey was generated by the two inner queries and will ensure that both the hierarchy and the SortOrder within the results are respected.

Monday, May 12, 2008

New SharePoint Enterprise Search Presentations

Microsoft has just published about 17 hours of new presentations on Enterprise Search. Check them out.

If you want to check out some free virtual labs, these have been up for a long time but check them out too. There are 8 labs that focus on Enterprise Search.

Thursday, May 8, 2008

Deploying SSRS with SharePoint 2007 Integration

After going through a deployment of integrated SSRS with MOSS I figured it would be good to post up a bunch of references that may help.

As well, I had a situation for some reason 32 bit SQL was running on a 64 bit box with MOSS on it (it is a dev box so no real harm). This article shows how to run SSRS using SQL 32 bit on a 64 bit IIS however that will break MOSS so we had to do a SQL upgrade to 64 bit.

I have not run into this yet but I will be setting this up in a distributed environment. I do know that once I do that, Kerberos authentication will be required for a double hop if reporting services is on a different machine than MOSS (similar issue with K2). Some colleagues sent me the following links to help me through this when I get there (SharePoint and Kerberos, forcing NTLM 1, forcing NTLM 2).

Tuesday, May 6, 2008

The Hidden Costs of Inefficiency

Sergio Del Piccolo wrote the following article called The Hidden Costs of Inefficiency. This is absolutely fantastic. Please give this a read as this is a great way to start talking about K2 BlackPearl.

When reading this you may think to yourself that this is nothing new, we are just talking about automation of offline business processes. However when talking K2 there are paper processes that K2 can automate but guess what there are tons of offline processes that need to be implemented to support many of the ERP solutions that are purchased. It is very common to hear there is a business process before we can enter data into our ERP and it is managed through email! There are tons of implicit business processes that can still be automated.