Hi friends,
I'm really happy to say that SQL Server 2008 was released on Wednesday! This is by far our highest quality and most feature rich release, and I'm looking very forward to hearing any and all feedback about the product. Here is some more information:
Now that SQL Server 2008 is out, you may be wondering where to find Report Builder 2.0 (known in some of the early CTPs as "Report Builder Preview"). The main Reporting Services team blog has information about the plan for Report Builder 2.0 here:
I recently wrote a whitepaper highlighting a great tool by one of our partners: SoftArtisans OfficeWriter.
OfficeWriter is a design tool and custom rendering extension that lets you use Excel and Word documents as report layout templates. This differs from conventional out-of-the-box renderers that translate RDL-defined layout into HTML/PDF/CSV/etc.
It's a different way of thinking about report design, definition, and rendering. It has certain limitations, but what you gain is an Office-based design experience and high-fidelity Excel and Word output from Reporting Services.
If you have a specific need for rich Excel or Word export, I encourage you to read this article and contact SoftArtisans to get set up with an eval:
http://msdn.microsoft.com/library/default.asp?url=/library/en-us/dnsql90/html/SQLSRSandOW.asp
Many Reporting Services users ask this question:
"I'm rendering to a physical page format (PDF, Image, Print) and for some reason a blank page gets inserted between all of the other pages in the report. Thank you, Reporting Services, for the free pages. It's a charming feature, but paper ain't free and I'm getting paper cuts pulling out every other blank page after it's printed. How do I make it stop?!"
To which I reply:
This is almost always caused by the Body of your report being too wide for your page. Make sure that the Width of the Body is less than the physical page size, less the margins. For those of you who are into the math thing, make sure that:
Body Width <= Page Width - (Left Margin + Right Margin)
For physical page renderers, it's important to keep in mind the concept of Usable Area. The Usable Area is the amount of space left on a page for your report to be drawn after reserving space for margins, headers and footers, and column spacing (for multi-column reports).
Horizontal usable area:
X = Page.Width - (Left Margin + Right Margin + Column Spacing)
Vertical usable area:
Y = Page.Height - (Top Margin + Bottom Margin + Header Height + Footer Height)
Any time objects on your report (including the Body itself) extend past the usable area either because they are defined like that or because they grow at runtime, the physical page renderers have no choice but to break onto a new page.
Let me know if there are more questions about this.
One issue that continually vexes users of the SQL Server Reporting Services Excel rendering extension is that of merged cells in the Excel output. Merged cells can be a problem because Excel's sort functionality is very particular about the way cells have to be merged in order to work properly. Excel requires that ranges of merged cells be identically sized in order to be sorted.
So, why does the renderer merge cells? It's important to keep in mind that the Excel renderer is primarily a layout renderer. Its goal in life is to replicate your defined report layout as closely as possible as an Excel workbook. At that task, it does a really good job. A consequence of this design goal is that in striving to preserve the layout, cells need to be merged on the worksheet surface.
Consider this example. This is a very common case that I see a lot of users running into. Say you have a report with a single Table and a Textbox above it acting as a header that is laid out like this:
When rendering to Excel, in order to preserve the layout defined above (in particular, the dimensions of each Textbox relative to one another), the worksheet cell grid is configured like this:
If you want to sort the Table data in Excel you cannnot because the first Table column consists of merged columns A and B and the second is column C. Excel's sort function cannot handle this case.
If exporting to Excel with the ability to sort is important to you, here are some tips to help you reduce the amount of merged cells in your workbooks:
- Make sure the left and right edges of all report items line up with one another. This is the #1 cause of merged cells. Referring to the example above, if the header Textbox's left and right edges lined up precisely with the width of the first table column, the merge would not be necessary. This technique should solve this problem for the majority of cases.
- Even if you do line up everyting precisely, you may find in some rare cases that there are still some columns merged. This could be due to internal rounding and unit conversion issues when we lay out the Excel worksheet at render time. Report Definition Language allows you to specify position and size values in a number of different measurement units such as inches, pixels, centimeters, and points. But, internally the Excel format wants everything to be in points. To minimize the amount of conversion that we have to do at render-time to change your inches and centimeters to points, consider specifying all of your measurments in points for the most direct results. One inch is 72 points. This is a much rarer case, so consider following this step if the technique described in #1 still does not solve your problem. This will be substantially improved in the next version of Reporting Services.
- Use the third-party report design and rendering tool SoftArtisans OfficeWriter. Using OfficeWriter, reports are designed using Excel as the authoring tool and the Excel document itself becomes the report layout definition. Because you are both designing and delivering in Excel, you can achieve precise Excel layout.
I hope this helps clear up some of the questions surrounding this issue. Let me know if you have any feedback.
Hello, I'm Chris Baldwin. I'm a Program Manager on the SQL Server Reporting Services team. I primarily work on the report rendering area of the product where we're all very hard at work making sure that your reports look the way you want them to and behave the way you expect when exported to the various formats that we support. I'll use this newly created blog (my first ever!) to discuss some of the finer points of report rendering and hopefully offer some insight and tips that you will find useful in your own reporting scenarios. I came to Microsoft from a company that specialized in Office-based reporting so I'll also talk quite a bit about the future of Office integration with Reporting Services.
I invite you to leave comments and contact me through this blog if you have any feedback or questions on any Reporting Services topics in this area.
