Click here to monitor SSC
SQLServerCentral is supported by Red Gate Software Ltd.
 
Log in  ::  Register  ::  Not logged in
 
 
 

SoftArtisans OfficeWriter:Reporting Services Integration

By Jonathan Spink,

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

Total article views: 4134 | Views in the last 30 days: 3
 
Related Articles
FORUM

SSRS - Report Builder 2.0 - SQL query generation - Order of fields dragged on the report changes the generated query

Report Builder Query Generation - Order of fields dragged on the report changes the generated query ...

FORUM

Compare two tables: Which fields are different?

Show which fields are different and old/new values

FORUM

Install Reporting Services after Engine

Installing reporting services after installing sql

FORUM

Reporting Services installation problem

Reporting Services installation problem - Help me please!

FORUM

Field Explorar like Crystal Report

Field Explorar like Crystal Report

Tags
product reviews    
reviews    
 
Contribute

Join the most active online SQL Server Community

SQL knowledge, delivered daily, free:

Email address:  

You make SSC a better place

As a member of SQLServerCentral, you get free access to loads of fresh content: thousands of articles and SQL scripts, a library of free eBooks, a weekly database news roundup, a great Q & A platform… And it’s our huge, buzzing community of SQL Server Professionals that makes it such a success.

Join us!

Steve Jones
Editor, SQLServerCentral.com

Already a member? Jump in:

Email address:   Password:   Remember me: Forgotten your password?
Steve Jones