Review: OfficeWriter v8

Share this...

Twitter

Linkedin

Facebook

Pinterest

Reddit

As a BI consultant specializing in SSRS, I have had lots of frustrations and hard times because of Excel. Every now and then, I have some or other business user coming up to me and asking for some feature which is there in Excel but not in SSRS. If you have been following my blog, you would already know that I am more of a work-around man, trying to find some alternative for features which are not supported out of the box. But when it comes to Excel related features, most of my attempts end in disappointment. So naturally, my ears perked up when I was asked to review a plugin which claimed to build SSRS reports using excel and word. So I downloaded OfficeWriter v8 and spent close to a week playing around with it. Even though I encountered some minor quirks (v8.0 doesn’t run on the 64 bit version of Office 2010 yet – luckily I had a home pc with a 32 bit version of Office; got some minor issues when editing and deploying an existing SSRS report with shared data sources – got around it by setting the data sources once again from the report manager), overall I have been very pleased and of course, excited at the different prospects that this plugin opens up. You can quickly have a look at one of the reports created with OfficeWriter by clicking on the image below. Let me note down quickly what I thought of it:- Advantages
1) XLSX support – Right now, xlsx support is available only for SQL 2012, but with OfficeWriter, you can take advantage of all the features including overcoming the 65,326 row limit (listed here) in your current version of SSRS.
2) Full excel features support including charts and pivot tables – Since excel is fully supported, you can make use of all the charts, macros and other features which are there in Excel but not in SSRS.
3) Ability to have 2 views for a report –  If we are editing a report with OfficeWriter from an existing report made through BIDS, then we will have 2 views for it – one which is displayed in report manager and other would be when exported to Excel. It can be helpful in scenarios where you view a fixed report and want to drill down and play around with the data further, in which case you can export to excel and then use a pivot table.
4) Charts are not rendered as images when exported to excel. The export to Excel feature in SSRS will return images rendered as a static image. However, with OfficeWriter, you get the chart exactly as you designed in Excel with all the the interactivity and even tooltips.
5) Can enable subscriptions, security – The tight integration with SSRS ensures that we can use subscriptions and other security features available in SSRS for the OfficeWriter reports.
6) Workbook Protection – You can password protect your workbook or lock your cells for edit, which is an often requested feature by SSRS power users for subscription. Limitations
1) Can’t build MDX queries in OfficeWriter, will have to make in BIDS and then use them.
2) Will not work on the 64 bit version of Office, as of now.
3) This is not a limitation actually and is by design, but I couldn’t help wishing if only the charts and data designed through OfficeWriter in excel would display properly in SSRS also. Even though OfficeWriter has more features including integration with Word and SharePoint, I have reviewed only the integration with SSRS for Excel part. For folks who are further interested in this, I will show you how to create a sample report using OfficeWriter and post some useful links below it. Creating / Editing  a sample report For the demo purpose, I am using OfficeWriter v8.0, the 32 bit version of Excel 2010 and SQL Server 2008 R2. Once the installation of OfficeWriter is done, you can find a new toolbar like shown below in the Add-Ins tab of Excel. Now, there are two ways in which you can proceed from here –
I) Create a new report in Excel with OfficeWriter
II) Edit an existing report created with BIDS (Business Intelligence Development Studio) and deployed to the server The steps to create a new report is shown below:- 1) Click on Add Query in the OfficeWriter toolbar and enter a name for your query. 2) Add a new Database connection if there is none existing, enter the server information and add the required tables for the SQL query. Once that is done, select the required columns. In this example, I have connected to the AdventureWorks database and used the English Product Name in DimProduct table & Order quantity measure in the FactInternetSales.   3) Even though you can group using the OfficeWriter features, I edited the SQL query directly to group by the English Product Name, as shown below and click on OK. Close the query editor after that.   4) Click on Insert Field and select the English Product Name in cell A1 and Order Quantity in B1. 5) Click on View. This will prompt you to enter the name of your report and save it in your local drive. Click on Save and a new dialog box opens which will prompt you to enter the name of your report server. Click on OK to publish your report to the report server. 6) Once that is done, the report preview would be shown in Excel. You can click on Close Report View to go back to the designer. 7) You can also preview the report from the report manager, which will display a message that the report was made in OfficeWriter and would display correctly only when exported to Excel with OfficeWriter. Editing an existing report is also simple but you would not be able to edit the queries made through BIDS. Follow the steps below to edit an existing report. 1) Click on Open Report and then click on Retrieve as shown in the image below. 2) Enter the report server url and click on Refresh. The list of existing reports would be displayed below and you can select from them. 3) Insert the fields after selecting the datasets (notice that Add Query and Edit Query is disabled). You can also include charts as shown below. 4) Click on View to save and publish your reports. You can then preview the report in excel.
Useful Links
1) OfficeWriter Home Page
2) OfficeWriter tutorials and Documentation
3) OfficeWriter FAQ
4) OfficeWriter System Requirements
5) OfficeWriter Download Evaluation version P.S.
This is a paid review. However, I have tried to maintain a totally neutral review, and if there is any point that you disagree with me, I would be pleased to hear about in the comments section.