SoftArtisans OfficeWriter:Reporting Services Integration

, 2006-11-01

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:

OfficeWriter

SoftArtisans

Rate

Share

Share

Rate

Related content

Total SQL Analyzer Review

Often times, a DBA is asked by clients to document their entire server. This usually includes jobs, database objects and DTS packages. What an utterly boring task it is for a DBA to document the properties of each column, table, and database. FMS's new Total SQL Analyzer to the rescue, freeing up time for people who are tired of creating mountains of documentation.

2001-12-12

3,498 reads