How to get the disk usage report working

If you've downloaded the SSMS reports and want to get the Disk Usage report working. Follow these steps.

  1. Remove all comments from the data sources, it seems that RS ignores CRs when the SQL is specified as an expression. This means that anything that appears after the comment is treated as part of the comment when -- is used. The other option is to enclose the comment in /* */ this gives the comment a defined end and so the SQL can continue afterwards.
  2. Add an ; after any declare statment and a non declare statement. declare statements can occure after each other on the same line, however other statements require a command terminator or a new line. i.e. "declare @fred int declare @jones int" is fine but "declare @simon int select 1 from mytable" isn't but "declare @simon int; select 1 from mytable" is.

I'm sure this all stems from the use of XML to store the RDL definitions. Whitespace is rolled up/ignored unless CDATA is used to wrap the text and even then CRLFs are still converted to LF (if memory serves me I discuss this in depth in SQL Server XML Distilled)

-
Published 04 September 2006 15:08 by simonsabin

Comments

25 October 2006 16:05 by Chris Conner

# re: How to get the disk usage report working

Simon, this is great - thanks for the tip!

Just out of curiousity, do you know how to create template reports that you can choose from the Add new report dialog? In 2000 RS you could simply drop an RDL file into ..\80\Tools\Report Designer\ProjectItems\ReportProject and voila! it would show up in the add new report dialog so you wouldn't have to create reports from scratch all the time.