Introduction
Since the emergence of the relational data model, various
means have been tried to get meaningful data, i.e. information, out of the
database. The IT professional who has braved the gamut of normalization,
foreign keys and outer joins lives in a different world to the user, albeit
highly numerate themselves, who just wants to see a grid of figures on the
screen.
Hence the popularity of the spreadsheet. Its main advantage
is that here, unlike the database, form and content are one: what you see is
how it's stored (WYSIHIS?). There's no need to normalize, join and otherwise
transform anything, because the data's right there. (The pivot table breaks
this rule, but the user who's happy creating one of those is probably running
Microsoft Access somewhere anyway)
In contrast, the database package insulates the user from
the raw data through levels of views, queries and report formatting. Those
levels are typically the domain of someone in the IT department who gets turned
on by structures, relationships and high-level programming. And yet the data
doesnt have to play so hard to get.
OfficeWriter
The idea behind OfficeWriter is to extend the functionality
of software users are already familiar with, i.e. Excel and Word, so they're
able to both acquire the data they need and arrange it into a useful form.
While OfficeWriter does come in two more specific forms, ExcelWriter and WordWriter,
this review looks at the version developed for use with SQL Server Reporting
Services (SSRS), available in both 2000 and 2005 incarnations.
In order to take advantage of the different parts of this
system, the OfficeWriter for SSRS package comes in two parts. The Designer is
what you use to create the reports, either in Excel or Word, while the Renderer
sits on the Reporting Services server.
This then is the OfficeWriter approach: from the Ms Office
front-end, reports are produced in the SSRS XML-based RDL format, then uploaded
to the SSRS ReportServer database, like any report produced using Visual Studio
(VS). This means that they can also be viewed in Report Manager along with any
other SSRS reports you have.
To manage things inside Excel or Word, you get a toolbar
with the following buttons:
- Open Report: this brings up the usual Open dialog box,
listing the RDL files in a given folder
- Select Query: this allows you to choose, for example,
between the fields in the header/footer' or the query used for the detail rows
- Insert field: this lists all the fields for the selected
query so you can place them in the grid.
- Insert Formula (only available with the Enterprise edition):
gives the options to build new formula' and manage formulas'
- Save As: saves the RDL file to a folder
- Publish: uploads the report to the ReportServer database
- View: runs the report
- Help: this provides either local help or assistance from
the SoftArtisans' website
Fields are allowed either to appear once, i.e. at the
document' level, or they're assumed to be repeated on multiple rows of the
detail section. These are easily identified by the %%= prefix used in the
cell as a data marker. Within this simple structure, the user can place and
format the fields as required in the familiar spreadsheet grid.
The facility for writing formulas brings up a window similar
to the one in VS for creating expressions. This means that not only the fields
and parameters are available for use but also ready-formed aggregates and
global constants.
Using Word
Reports can also be designed and displayed in Word. Here,
the same toolbar is used, but the user has the line-based placement of the
word-processor rather than the more formal spreadsheet grid. This allows a more
flexible approach to form design similar to the List' control in VS.
An example of where this flexibility can be put to good use
is in producing a customer-based report where different groups of repetitive
data (e.g. sales, enquiries, other responses), having different numbers and
widths of columns, need to be accommodated on the one report. In fact any
output which needs to be presented as a form, e.g. one which might need to be
signed in ink, can be written this way. Trying to do this in Excel results in
lots of extra columns, often containing blank cells; a situation which SSRS
savants are used to seeing in exports to Excel.
A feature of the latest version of OfficeWriter (3.6.1) is
that the one RDL file, can handle both Word and Excel versions of a report. You
can therefore open the file in either application and have the features
appropriate to that one available for viewing.
One outcome of all this is that you now get two further
Export options in Report Manager.
Building Queries
There are many query builder' front-ends which will protect
you from ever having to see a line of SQL let alone write one. Just pick your
fields, decide how to group and sort them and you're pretty well there. The
main preparatory work for this simple scenario though is to present a list of
useful fields with meaningful names out of the underlying data maze.
OfficeWriter uses Ms Query, but the link doesn't appear by
default requiring a tweak in the registry to enable 3 extra buttons to appear
on the toolbar: Add Query, Edit Query and Delete Query. With
the first of these you can go straight into Ms Query, choose your database
connection, pull in tables and build your SQL. Parameters are also setup here
and if you want them to be editable at run-time, you can enter a phrase in
square brackets in the criteria line, as in Ms Access.
If you don't want users to design their own queries, this
option is easily turned off from the registry, which also includes some other
basic switches for controlling the reporting environment
At this point, it's worth mentioning Microsoft's own SSRS
Report Builder (RB), which provides another way of presenting fields from the
database ready for use. Here you are asked to produce, in order, a data
source, a data source view (which includes specifying links between tables) and
finally the report model itself. This is all meant to be done behind the scenes
by the IT department, so the end user only sees the model, from which they can
select the required fields (data types are indicated by icons) and place them
in either a table, matrix or chart.
It's debatable which solution requires more of the database
expert and which more of the end user who is trying to create a report. The Ms
Query approach gives the user more flexibility, but then they need to be more
savvy about tables and joins. With the more finely honed RB report model, the
three RB controls focus the user more on the layout and interrelationships of
the data rather than the minutiae of formatting and presentation.
Installation
I have to say firstly that installation did present some
problems, but that these were almost wholly to do with the state of the already
installed SQL Server 2005 instance on my particular machine.
I already had the client tools installed, but the addition
of the server-side functionality (which I used as the testbed) didn't want to play
ball straightaway. So http://localhost/reportserver
at first showed nothing, although our networked installation of SSRS was
recognized immediately and I was also able to publish to it even before I
realized what I was doing! It's perhaps worth noting that a default full'
installation of SS2005, installs everything except the AdventureWorks database,
on which the sample OfficeWriter reports were based. The only other complaint
came from the .NET Framework, but after I re-installed this, it all worked like
clockwork.
The installation includes two products, the renderer and the
designer, and the install eventually went smoothly when running these
separately rather than launching the second from the first.
Licensing
A per CPU' fee is paid to install the software on the
server, so there are no per seat' costs. Microsoft Office is not required on
the server and, indeed, Word Viewer or Excel Viewer can be used to display
reports, so an Office license needn't be required at all unless you want to
produce new reports.
Gripes
These are minor but worth mentioning:
- The Insert formula' button on the toolbar offers a facility for
creating new expressions based on fields, parameters and constants and then
giving them a name for later referral. The menu is then configured to list such
expressions below the standards options of 'new' and 'manage', but
unfortunately the chosen name isn't used: instead the expression itself appears
which looks rather odd.
- After opening an already saved report, it would seem acceptable
to go straight ahead and view it. But if you try this you are first asked to
publish it and, because it has likely already been published, you get the
familiar question about overwriting.
Conclusion
OfficeWriter is a neat solution which for perennial
spreadsheet users, should provide an easy way in to the low-level data from
which they are normally barred. It's an advance for user democracy and also a
way for those report-writing folk in IT to free up their time for the more
stimulating and perhaps esoteric type of information delivery such as data cubes
and others more worthy of the term business intelligence'.
The Word version does actually provide something which was
one of the very few omissions I noticed when we first deployed SSRS: something
to output data, often long pieces of text, in a format that could be easily
amended in a word-processor, printed off and signed.
If I had a suggestion for how the good folk at SoftArtisans
might develop their product further, it would be to base their queries on the
Report Builder model rather than introduce Ms Query, which is a bit long in the
tooth now (even the 2003 version has the look and feel of Excel 4). Other than
that they've produced a clever and well-integrated addition to the
functionality of SSRS which will be an answer to the prayers of many numerate
folk whose only shortcoming is that they don't work in IT.
For More Information, click the links below: