Using SoftArtisans OfficeWriter with SQL Server Reporting Services 2005
page 8 of 10
by Mike Campbell
Average Rating: This article has not yet been rated.
Views (Total / Last 10 Days): 38749/ 96

Redesigning Reports with the OfficeWriter Designer

With OfficeWriter's specialized designer, you can easily redesign existing Reporting Services Templates to enable rich functionality in SSRS reports.  To do this, you need to add additional instructions to the Report's definition which lets it know how you would like it rendered when exported through the custom OfficeWriter rendering extensions.  Happily, providing these instructions is terribly simple with the OfficeWriter Designer.

To modify your existing report:

1.      Open up Microsoft Excel on your development machine that has Office and the OfficeWriter Designer installed.

2.      In Excel, click on the Open Report button in the OfficeWriter Designer Toolbar as shown in Figure 6.

Figure 6

3.      Click on the Retrieve button to fetch your existing report definition from your Reporting Server.  You will need to specify the location of your Reporting Server (just enter the server URL without a path, i.e. http://myserver/).  Then navigate through the list of folders and locate the report you created earlier.  Once you have selected your sample report, click OK to pull it down.

Once the report is opened, you will notice nothing but a big empty screen.  The OfficeWriter designer does NOT render your existing report for you in Excel.  While this may initially seem like a bad idea, it actually makes perfect sense.  Think about it: you do not want a copy of static data being rendered in Excel since you already have that functionality.  What you want is the ability to completely change the layout of your report so that you can infuse it with formulas, conditional formatting, macros, data-bound charts, and a host of other goodies.  Best of all, when you are done formatting your new report, you can just save it back up to your server and all of the formatting specifications you have made will be completely ignored unless specifically requested by a client who wants the report output in "native" Excel using the OfficeWriter rendering extensions.

To redesign your sample report, just build some formatting loosely mirroring what you created in the Visual Studio designer (as depicted in Figure 7) and make sure to add a new column, Visual Deficit, as you will need this for Excel formula functionality.

Figure 7

Once you have laid things out as you like (you can meddle with it as long or little as you would like - I chose some simple formatting and a border with divider lines), it is time to specify where data will go when the report is rendered.  To specify where data should be placed:

4.      Click on the Select Query drop down in the OfficeWriter Designer toolbar and select the AdventureWorks dataset.  This is the default dataset created in your report and is being interpreted directly out of the RDL for this report.  Now, highlight the first row underneath your header (Cell 4A in Figure 7) and then click the Insert Field drop down.  From the list of fields, click on the Part_Number field.  When you do this, a new data marker is inserted which will be replaced with Reporting Services data when the report is rendered.

5.      Fill in the rest of the fields by inserting them as needed.  Once you have specified the contents for each column, you are ready for some fun.  Select the Reorder Point, Current Count and Deficit columns then right click and specify the Format Cells menu option.  Specify that the cells should be formatted as Numbers and specify any number of decimal places that you like.

6.      Now add some conditional formatting to the Deficit column.  Select the cell where your Deficit column data-marker is located (Cell 4E in Figure 7) and then select the Format | Conditional Formatting menu option.  From the Conditional Formatting dialog, set up a rule for values greater than 200- for example, turn the text red.

7.      Finally, add a bit of visualization in the form of a formula that will create an inline bar graph using the REPT() function to add a new | (bar) character for every 10 deficit products.  Just add the formula shown in Figure 8.  (Note: Once you "mouse out" of cell F4, the formula will throw an error, just ignore it.)

Figure 8

With your report redesigned and with some native formatting and functionality in place, it is time to publish your report back to the server.  You can save it with a completely different name if you would like, but there is no negative in just saving it over the previous version. (It might make sense to just append _OW to the end of it in real world scenarios so consumers can easily see which reports will render with full Excel functionality.)  To save your changes, just click the Publish button in the OfficeWriter Designer Toolbar.  Then navigate the ensuing dialog and save the report as desired.

Once the report is saved, you can view it by clicking the View button from the OfficeWriter Designer Toolbar.  You will be prompted to enter a location which is still done with a dropdown and once the report is rendered, you will see that it managed to populate your template with Reporting Services data perfectly and formatted everything as you specified.  Had you placed any charts, formulas, macros, or other goodies anywhere, they too would have remained in place just like your conditional formatting and added formula for an inline "chart."

View Entire Article

User Comments

Title: RA   
Name: SA
Date: 2008-04-24 12:05:05 PM
Can Office writer help in renaming the sheet names once the report is exported from Report server
Title: Dynamic Rendering   
Name: ST
Date: 2007-10-01 6:14:24 AM

I have tested the code given on the above. During the render, I get this exception "Client found response content type of '', but expected 'text/xml'.
The request failed with an empty response." on the function Render(...). What am I missing on here?

Title: RE: Dynamic Rendering (@giovanni)   
Name: Michael K. Campbell
Date: 2006-08-31 8:37:50 PM
Giovanni, glad you liked the article.

Make sure you've got the SoftArtisans SAXW6NET.dll copied in to your /bin/ directory - that will likely be your biggest issue.

I also generated a VB.NET conversion of the default.aspx.cs page, and posted it up in my uploads section: (but you'll need to do wire-ups for code-behind/partial classing etc...)

Title: Render Dynamic   
Name: GIovanni Rivas
Date: 2006-08-31 6:06:35 PM
Hi, thx for the article, but i tryed to copy the code in cs that you give here and doesn't work, i have to so all the references to the Interop libary for excel and the methodthat you are calling there with the arguments doesn't exist. am i missing something, do you havea code in that could work like this. thx
if you can reply at

Community Advice: ASP | SQL | XML | Regular Expressions | Windows

©Copyright 1998-2024  |  Page Processed at 2024-06-25 12:17:21 PM  AspAlliance Recent Articles RSS Feed
About ASPAlliance | Newsgroups | Advertise | Authors | Email Lists | Feedback | Link To Us | Privacy | Search