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."
