Microsoft SQL Server Reporting Services FastTrack.
Microsoft SQL =
Server
2000 and 2005 come with functionality to develop distribute and run web-ba=
sed
reports. Typically these reports can be viewed using a browser but they can
also result in XML or even PDF’s without the need for extra software=
. With
the release of SP3 for SQL Server 2000 even printing straight from the vie=
wer
is supported. The report server is actually running in a SQL-server itself,
allowing for secure distribution, scheduling and execution.
For example you could develop a report that runs once a week and gets emai=
led
to a selected group of users, or users can browse the available reports and
subscribe to specific ones. By using dynamic html as the primary output
mechanism the reports can have interactive elements. You can give users the
ability to implode and explode groups allowing summary or detail views or =
add
interactive charts and graphics.
Best of all, w=
hen you
already have SQL-server licenses, Reporting Services is a free download, a=
ll
you need is a capable server. But there’s also a drawback, you will =
need
Visual Studio to develop the reports, a server to deploy on and that has t=
o be
a real Windows server. Deploying on WMSDE is not an option so there no way
users can take an application on a laptop and run reports stand alone. You=
can
use WMSDE as a data source and during development in Visual Studio, you can
preview your work in Visual Studio without deploying to an actual SQL Serv=
er.*
![3D"Text](3D"file:///C:/AF7A90CB/ReportingServicesFasttrack_files/image001.gif")
=
So, when you a=
lready
use SQL Server as your database server it certainly makes sense to start
delivering some reports using Reporting Services. The actual execution of =
the
reports can be done by the user by going to the Reports-website that gets
created during installation or you could use a reasonably well document URL
interface from within a Win32 application such as one build using Visual
Objects. An example of such a URL is this:
Which means, s=
tart a
report called MyCoolReport.rdl, and pass it 001 as the value for the named
parameter FundCode and do not show the parameter input bar at the top. All=
you
need from Visual Objects is a web browser control on a form and call the
Navigate method with the above string. You can pass as many parameters as =
you
need so the report can be pretty dynamic while using your application inpu=
ts to
provide the parameter values. There are several other commands you can pas=
s in
on the URL including a username password when required.
The Client sys=
tems
accessing reports do need a small ActiveX control but this gets automatica=
lly
downloaded the first time a user accesses a report so there’s no nee=
d to
deploy anything special on the client. You do need to have the security se=
tup
that allows installing and running ActiveX controls from internal website =
but
that should not be a problem since it is all within the LAN or using trust=
ed
sites where the users is actually authenticated.
Getting Started with Reporting Services
First of all y=
ou need
to update you SQL-server to SP3a as a minimum, something you should already
have done anyway because of the tighter security this service pack impleme=
nts.
On the SQL server you need to install the Server components of Reporting
Services. This installs a reporting services database and website. If you
already have web sites or sharepoint running be careful to read the various
readme’s and guides available for these situations or you might end =
up
with some conflicts. But when following the instructions you can certainly=
make
them work together on one server, when starting it might be easier to run =
them
on separate servers.
Next, you need=
to
install the client components on your development machine, it’s the =
same
setup but it will install the Reporting Services examples and templates fo=
r Visual
Studio. That is assuming you do have Visual Studio running already. Don=
217;t
forget to patch the Reporting Server and the Client components to the most
recent Service Pack. This will enable the printing component and installs
Sharepoint Web Parts so you can even embed your dynamic reports in Sharepo=
int
pages. Make a copy of the image for easy restore (using Virtual Server)
Now we are rea=
dy to
develop our first report, in the true Microsoft tradition using a wizard in
Visual Studio. I will take you through these steps explaining what actually
happens behind the scenes to get a good feel for what elements make up a
reporting services solution.
Start Visual S=
tudio
and click on File, New Project.
![](3D"file:///C:/AF7A90CB/ReportingServicesFasttrack_files/image024.jpg")
Figure
1, Busin=
ess
Intelligence templates
You now have a=
new
leaf called Business Intelligence projects in which two templates are
available, a blank Report Project and a wizard. Of course we go for the wi=
zard
for our first Reporting Services project. Visual Studio creates a new proj=
ect
for you and starts the New Report Wizard. The project has Reporting Servic=
es
specific properties which we will explore later. After dismissing the
introduction page by clicking next we need to setup a data source. Having =
this
as a separate entity is convenient, this way we can specify our data
connection, and by changing it all Reports that use it will automatically =
use
these changes. There’s nothing special here clicking edit just opens=
the
regular data-connection dialog and saves a connection string in the data s=
ource
entity. The credentials button allows you to specify how to logon using th=
is
particular data source. When later executing the report you can specify wh=
ich
data source to use allowing for flexibility between different deployment
scenarios such as during development and/or testing. It makes sense to che=
ck
the share this connection checkbox so that multiple Reports can use the sa=
me
data source settings.
For this first
report, type Northwind as the name, pick your server and select the
Northwind database. On the next page we can specify the query this rep=
ort
will use as its primary source of data. Later we can add more sources for =
data
in case we have a need for that in the report (I will explain that in more
detail later). Clicking the edit-button opens a standard Microsoft Query
editor.
![](3D"file:///C:/AF7A90CB/ReportingServicesFasttrack_files/image025.jpg")
You’ve p=
robably
used this one, or something similar many times from products like excel or
visual studio. For our first report it suffices to select * from employees=
. So
you could just enter this sql-statement straight into the Query string con=
trol.
The next page of the wizard allows us to choose a report layout, we will u=
se
tabular for this report. Design the basic layout of your report by highlig=
hting
the fields on the left one by one and pressing the appropriate button to p=
lace
it into the report region you would like. Below is a picture of what would=
be
needed for a list of employees grouped by country and region.
![](3D"file:///C:/AF7A90CB/ReportingServicesFasttrack_files/image026.jpg")
The next step =
allows
us to specify even more detail for the layout based on the grouping select=
ed
above.
![](3D"file:///C:/AF7A90CB/ReportingServicesFasttrack_files/image027.jpg")
Checking Enable
drilldown will gives us an example of how the reports can be made somewhat
interactive allowing the users to expand or implode areas of the report. J=
ust
check it for now and you will see later in the preview what this does.
The next page =
of the
wizard gives a choice of styles, pick anyone you like, click next and you =
will
be able to give the report a name and look at a summary of all settings. A=
fter
clicking finish you will end up in the layout panel of the report designer=
.
Selecting the =
preview
tab from there will execute the report and display the top level groups,
clicking on the plus icons allows you to expand the data. This behavior is
because of the ‘enable drilldown’ checkbox, if we would have l=
eft
that unchecked you would see everything expanded by default and without the
icons.
![](3D"file:///C:/AF7A90CB/ReportingServicesFasttrack_files/image028.jpg")
This initial r=
eport
looks ok, but we’ve selected a few too many columns, let’s cha=
nge
the layout to using two rows per employee record. To do this select the La=
yout
tab, click on the bottom row and right click on the last ‘handleR=
17;
that appears on the left of the body section. The bottom square on the left
gives you a context menu from which you can select Insert row below.
![](3D"file:///C:/AF7A90CB/ReportingServicesFasttrack_files/image029.jpg")
Now scroll to =
the
right, select the postal code cell (not the column header!) and drag it to=
the
cell below address.
![](3D"file:///C:/AF7A90CB/ReportingServicesFasttrack_files/image030.jpg")
Drag the conte=
nts of
city below as well. We can now remove the Postal code column by right clic=
king
the word ‘Postal Code’ and choosing delete columns. There is n=
ow
room to drag the right edge of the City column to the right to widen that
column.
As you can see=
(in
the preview) the Birth Date column is a standard DateTime field, but in mo=
st
systems it is not needed to display the time of birth. This is easily modi=
fied
by applying a format to the cell. Select the cell and look for the format
property in the property window. If you’ve lost that, just hit F4 to
display it. Remember you can double click the caption of any docked window=
to
‘undock’ it and double-clicking the caption of a floating wind=
ow
puts it back to it’s previous docked position. A very handy producti=
vity
feature of Visual Studio. The formats are standard formats used all over in
excel, visual basic, access etc. ‘c’ for currency, dd/MM/yyyy =
for
Australian (or Dutch) date formatting etc.
![](3D"file:///C:/AF7A90CB/ReportingServicesFasttrack_files/image031.jpg")
As you can see=
there
are many more properties to set on fields in textboxes such as font, color=
s,
text alignment etc. You can also drag and drop other fields that were not
initially selected from the Fields window onto the report. The last thing =
to
make the body of this first report look good is adding a footer with some
calculated data. Again right click the handles on the left of the body sec=
tion
and select add table footer from the context menu. Now right click the bot=
tom
cell of the Employee ID column and select expression.
![](3D"file:///C:/AF7A90CB/ReportingServicesFasttrack_files/image032.jpg")
This opens up a
dialog to edit the formula that is executed to display the cells contents,=
with
options for all kinds of things, actually pretty much unlimited because yo=
u can
even load your own assemblies (function libraries) before running a report=
and
call the functions you have in there from these expressions.
![](3D"file:///C:/AF7A90CB/ReportingServicesFasttrack_files/image033.jpg")
Select Dataset=
s,
Northwind, Sum(Employee) and click Replace. Now, it does not make sense to=
sum
the ID’s but we could change Sum( manually into Count(. You also have
standard functions like Avg, Min and Max available. Have a look at the
Reporting Services On-line books that got installed for you to learn more =
about
expressions.
This is also a=
very
easy and convenient way to add totals etc. to numeric or currency columns.=
The
second parameter of all these functions represents the level, in our examp=
le
you could use “region” or “country” to count on th=
at
grouping level.
Again select p=
review
and be amazed by the look and feel of your report at this stage.
The next step =
in the
process is actually deploying the Report to your Report Server. In a defau=
lt
installation you will have a website on the server where you installed
reporting services (we’ll deal with SQL Express and local machine in=
a separate
article). The Reports website is normally called ‘Reports’, so=
http://<your
server>/Reports will show you the published reports. And this is=
the
same value you have to use for the TargetServerUrl property on the
Projects’ property dialog. Right click the project node in the solut=
ion
explorer and click Properties. By specifying a folder you actually create
separate folders on the website, which is a great way to organize reports =
and
set security to different groups of users on the folder level.
![](3D"file:///C:/AF7A90CB/ReportingServicesFasttrack_files/image034.jpg")
If
you now right click your Report (the RDL file) in the solution explorer you
have an option to Deploy, this will upload the report to the website and a=
llow
users to execute the report from there. Users can just click on the report=
they
want and it displays in their browser. In this view-mode they have the opt=
ion
to print, search and export to various formats such as HTML, PDF, XLS and =
XML.
If you do not like to send your users to such a website to execute reports=
, you
have other options available to you. These options will be the topic of yet
another article on VoConversion.com.au .
I
think Reporting Services as a package of tools for developing, deploying a=
nd
distributing reports is a friendly easy to learn environment. It is part of
your SQL Server license so there’s no extra cost involved, good reas=
ons
to seriously consider using it or migrating your existing reports into
Reporting Services. But best of all is the fact that when you have develop=
ed
your report in Reporting Services you have created a report definition in =
an
open xml standard. This means that whatever happens in the future you will=
have
an option to automatically migrate your existing reports to a new format or
import into a new tool. This means your investment in developing reports is
much better protected than current proprietary formats used by most Report
Writers. For me that is prob=
ably
the most compelling reason for using Reporting Services.
Ed Richard for
Software Objectives © 2007
Melbourne, Australia.