Search results matching tags 'SQL Server' and 'Reporting Services'http://sqlblog.com/search/SearchResults.aspx?o=DateDescending&tag=SQL+Server,Reporting+Services&orTags=0Search results matching tags 'SQL Server' and 'Reporting Services'en-USCommunityServer 2.1 SP2 (Build: 61129.1)T-SQL Tuesday #005: Creating SSMS Custom Reportshttp://sqlblog.com/blogs/michael_coles/archive/2010/04/12/t-sql-tuesday-005-creating-ssms-custom-reports.aspxTue, 13 Apr 2010 00:14:00 GMT21093a07-8b3d-42db-8cbf-3350fcbf5496:24228Mike C<P>This is my&nbsp;contribution to the T-SQL Tuesday blog party, started by <A title="Adam Machanic" href="http://sqlblog.com/blogs/adam_machanic/archive/2010/04/05/t-sql-tuesday-005-reporting.aspx">Adam Machanic</A> and &nbsp;hosted this month by <A title="Aaron Nelson" href="http://sqlvariant.com/wordpress/index.php/2010/04/t-sql-tuesday-005-reporting/">Aaron Nelson</A>.&nbsp; Aaron announced this month's topic is "reporting" so I figured I'd throw a blog up on a reporting topic I've been interested in for a while -- namely creating custom reports in SSMS.</P>
<P>Creating SSMS custom reports isn't difficult, but&nbsp;like most technical work it's very detailed with a lot of little steps involved.&nbsp; So this post is a little longer than usual and includes a lot of screenshots.&nbsp; There's also a downloadable <A title="Sample SSMS Custom Reports" href="http://brprfa.bay.livefilestore.com/y1pu65YtRksdmF-NkqPRSNVJVjP75MjU31Yyvna97r9AoEdF9wilfgmWTFCpm7DDuuPRpS9qe9Io-h4_pms-Y2N0LV1L6j8bNeU/Custom%20Report%20Samples.zip?download">ZIP file</A> with the projects from this article included.</P>
<P><STRONG><U>SSMS Custom Reports</U></STRONG>&nbsp;</P>
<P>SQL Server 2008 and 2005 both offer custom report functionality in Management Studio.&nbsp; With a little bit of work you can create custom SSMS reports just like the standard reports that Microsoft ships with Management Studio.&nbsp; The Disk Usage Report shown below is just one of the many standard reports that comes with SSMS.</P>
<P><IMG style="WIDTH:591px;HEIGHT:496px;" title="SSMS Standard Report" alt="SSMS Standard Report" src="http://brprfa.bay.livefilestore.com/y1p96w-RdpK_UPoOGMgli4eVbzYi3Oj9d-SdGVX4zXdkABiCseQUqv8_Ye9DjYtHHdaJj39eOK0XzLEqnctlccDKSptvHhJME1c/standard-report.jpg" width=591 height=496></P>
<P><STRONG><U>Creating a Report Project</U></STRONG>&nbsp;</P>
<P>SSMS uses the SQL Server Reporting Services (SSRS) 2005 client/viewer control to render both standard and custom reports.&nbsp; This is true for both SSMS 2005 and SSMS 2008.&nbsp; So the first step to building a custom SSMS report is to fire up Visual Studio 2005 and create a new <EM>Report Server Project</EM> as shown below.&nbsp; For this example we'll create a custom report that lists missing indexes, so give the project the name <I>Missing Index</I>.</P>
<P><IMG style="WIDTH:550px;HEIGHT:305px;" title="Create New Project Dialog" alt="Create New Project Dialog" src="http://brprfa.bay.livefilestore.com/y1pkwKN2ypKEv3XJHROh7xaoTOi4R79ha01zyZnL-acBQhieH_6CAo18PEri6DjOBX5d_O2jgH6bPTvIOUWNpmr7eHf-4xxG_GI/vs2005-new-project.jpg" width=550 height=305></P>
<P>Once you create the <EM>Report Server Project</EM> right-click on <I>Reports</I> in the Solution Explorer and choose <I>Add &gt; New Item...</I></P>
<P><IMG style="WIDTH:286px;HEIGHT:258px;" title="Add Report to Project" alt="Add Report to Project" src="http://brprfa.bay.livefilestore.com/y1p8qFbsxsFq7xjtkvppNWQ-N7S5R-uoHiJif6FtkChY1pzf5qQblwl7AapydMWrSRFY1_O-XsadEk5nR6XV9DX7-2lJVp7TFWn/add-report.jpg" width=286 height=258></P>
<P>When the <I>Add New Item</I> box appears, choose the Report template and give the report a name.&nbsp;&nbsp;For this example I named the report&nbsp;<I>Missing Index.rdl</I> - the <EM>.rdl</EM> extension stands for "Report Definition Language", and is the standard extension for SSRS report definition files.</P>
<P><IMG style="WIDTH:551px;HEIGHT:244px;" title="Add Report Dialog" alt="Add Report Dialog" src="http://brprfa.bay.livefilestore.com/y1pf_VVyzWAjA0uiWHfEChyN-uwUNscnR9B5cHDipw1KDF1StNazv-T7xFtmGqtdgBVZ1kGS_daR5vqkPFk-QdqpWMVdDR5Nodv/add-report-box.jpg" width=551 height=244></P>
<P><STRONG><U>Defining the Report Dataset</U></STRONG>&nbsp;</P>
<P>Once the report is added to your project you have to add a new dataset to the report.&nbsp; The dataset defines the structure and content of the source data that will populate your report.&nbsp; Choose <I>&lt;New Dataset...&gt;</I> from the <I>Dataset:</I> dropdown.</P>
<P><IMG style="WIDTH:470px;HEIGHT:151px;" title="Add New Dataset dropdown" alt="Add New Dataset dropdown" src="http://brprfa.bay.livefilestore.com/y1pw8BZ2mr9F4SCExKCklLxVPczZu6Mq0IEo2agULy1QRoLJRF2KKDqVAv1Oft41oQu1oBaZd_iEMa91OfLT83Eeky87qAra11Z/add-new-dataset.jpg" width=470 height=151></P>
<P>Visual Studio will respond with a <EM>Data Source</EM> box.&nbsp; Just make sure the <I>Type:</I> dropdown is set to the default <STRONG>Microsoft SQL Server</STRONG> and put <STRONG>Data Source=.</STRONG> in the <I>Connection string:</I> box.&nbsp; This is all that's required since the SSMS custom report you're creating will use connections created and managed by SSMS's Object Explorer.</P>
<P><IMG style="WIDTH:569px;HEIGHT:487px;" title="Add Datasource Dialog" alt="Add Datasource Dialog" src="http://brprfa.bay.livefilestore.com/y1pZ9v1Nf3IVwj1bm5gCsMnZLstJyKbsLFI75DOGhsuGjcuHZalsEs2c_odVbbgaqiWQpwyYZkyRBc3BExbXiZweT67vn5FhBgN/add-data-source.jpg" width=569 height=487></P>
<P>After you define the data source, you can define the SQL query that will populate your report.&nbsp; Just put the query in the dataset window as shown below.</P>
<P><IMG style="WIDTH:703px;HEIGHT:358px;" title="Adding a Dataset" alt="Adding a Dataset" src="http://brprfa.bay.livefilestore.com/y1pfTvR7VFHggCKt2l8yVZ_2vlxY9-ZppnqW2MEJIDTQvzy1X7zhLg43LfaTKpRB2QI5rlzv_TcpiCKkbXPh7WQZL5d-a6PNLdP/add-new-dataset3.jpg" width=703 height=358></P>
<P>I borrowed (and slightly modified) the following query from <A title="Brent Ozar!" href="http://www.brentozar.com/">Brent Ozar</A>.&nbsp; He originally published it at <A title=SQLServerPedia! href="http://sqlserverpedia.com/wiki/Find_Missing_Indexes">SQLServerPedia</A>. &nbsp;This particular query uses SQL Server's missing index Dynamic Management Views (DMVs) to identify missing indexes.</P>
<P><STRONG>-- Begin missing index query<BR><BR>WITH cte<BR>AS<BR>(<BR>&nbsp;&nbsp;&nbsp; SELECT mid.object_id AS object_id,<BR>&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; QUOTENAME(OBJECT_SCHEMA_NAME(mid.object_id)) AS table_schema,<BR>&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; QUOTENAME(OBJECT_NAME(mid.object_id)) AS table_name,<BR>&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; QUOTENAME('IX_' + OBJECT_SCHEMA_NAME(mid.object_id) + '_' +<BR>&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; LEFT(CAST(NEWID() AS CHAR(36)), 8)) AS index_name,<BR>&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; mid.index_handle<BR>&nbsp;&nbsp;&nbsp; FROM sys.dm_db_missing_index_details mid<BR>)<BR>SELECT<BR>&nbsp;&nbsp;&nbsp;&nbsp;DENSE_RANK() OVER&nbsp;<BR>&nbsp;&nbsp;&nbsp; (<BR>&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;ORDER BY cte.table_schema, cte.table_name<BR>&nbsp;&nbsp;&nbsp; ) AS table_color,<BR>&nbsp;&nbsp;&nbsp; ROW_NUMBER() OVER&nbsp;<BR>&nbsp;&nbsp;&nbsp; (<BR>&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; PARTITION BY cte.table_schema, cte.table_name&nbsp;<BR>&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; ORDER BY (avg_total_user_cost * avg_user_impact) * (user_seeks + user_scans) DESC<BR>&nbsp;&nbsp;&nbsp; ) AS index_color,<BR>&nbsp;&nbsp;&nbsp; cte.table_schema,<BR>&nbsp;&nbsp;&nbsp; cte.table_name,<BR>&nbsp;&nbsp;&nbsp; cte.index_name AS index_name,<BR>&nbsp;&nbsp;&nbsp; (avg_total_user_cost * avg_user_impact) * (user_seeks + user_scans) AS calc_impact,<BR>&nbsp;&nbsp;&nbsp; 'CREATE NONCLUSTERED INDEX ' + cte.index_name + ' ON ' +&nbsp;<BR>&nbsp;&nbsp;&nbsp; cte.table_schema + '.' + cte.table_name +&nbsp;<BR>&nbsp;&nbsp;&nbsp; ' (' + COALESCE(mid.equality_columns, '') +&nbsp;<BR>&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; CASE WHEN mid.inequality_columns IS NULL&nbsp;<BR>&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; THEN ''<BR>&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; ELSE CASE WHEN mid.equality_columns IS NULL&nbsp;<BR>&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; THEN ''&nbsp;&nbsp;<BR>&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; ELSE ','&nbsp;<BR>&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; END + mid.inequality_columns&nbsp;<BR>&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; END +&nbsp;<BR>&nbsp;&nbsp;&nbsp; ') ' + CASE WHEN mid.included_columns IS NULL&nbsp;<BR>&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; THEN ''&nbsp;&nbsp;<BR>&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; ELSE 'INCLUDE (' + mid.included_columns + ')'&nbsp;<BR>&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; END +&nbsp;<BR>&nbsp;&nbsp;&nbsp; ';' AS create_stmt,<BR>&nbsp;&nbsp;&nbsp; mid.equality_columns,<BR>&nbsp;&nbsp;&nbsp; mid.inequality_columns,<BR>&nbsp;&nbsp;&nbsp; mid.included_columns <BR>FROM sys.dm_db_missing_index_group_stats AS migs <BR>INNER JOIN sys.dm_db_missing_index_groups AS mig&nbsp;<BR>&nbsp;&nbsp;&nbsp; ON migs.group_handle = mig.index_group_handle <BR>INNER JOIN sys.dm_db_missing_index_details AS mid&nbsp;<BR>&nbsp;&nbsp;&nbsp; ON mig.index_handle = mid.index_handle&nbsp;<BR>&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; AND mid.database_id = DB_ID() <BR>INNER JOIN cte&nbsp;<BR>&nbsp;&nbsp;&nbsp; ON cte.index_handle = mid.index_handle<BR>WHERE migs.group_handle IN <BR>(&nbsp;<BR>&nbsp;&nbsp;&nbsp; SELECT group_handle&nbsp;<BR>&nbsp;&nbsp;&nbsp; FROM sys.dm_db_missing_index_group_stats WITH (NOLOCK) <BR>)<BR>ORDER BY calc_impact DESC;<BR><BR>-- End missing index query</STRONG></P>
<P>There are a couple of caveats to keep in mind about the missing index DMVs that this query relies on:</P>
<UL>
<LI>They're not designed to be exhaustive.&nbsp; If you want to get a serious analysis of useful indexes in your databases, you definitely want to use a tool that will perform a more extensive analysis.</LI>
<LI>The DMVs only persist their information since the last time the SQL Server service was restarted.&nbsp; If you've recently restarted the service you'll get very little information back.&nbsp; These DMVs are best to use when your server has been running under normal load for a while.</LI>
<LI>The suggestions returned by the DMVs aren't always the best way to go.&nbsp; These DMVs often recommend suggested indexes with lots of overlap (lots of columns in common, often in similar order).</LI></UL>
<P>To put it another way, don't take the raw information returned by these DMVs at face value.&nbsp; The information they return is definitely useful for determining where indexes might provide benefit if your system has been running under normal load for a while, and the redundant index suggestions tend to indicate which indexes might be most useful.&nbsp; But these DMVs just amount to a starting point for analyzing your indexing needs.&nbsp; I'd recommend against creating dozens of redundant&nbsp;indexes based on the raw output of these DMVs.</P>
<P>Visual Studio will normally populate the dataset fields.&nbsp; If for some reason it doesn't, you can manually edit the fields by hitting the&nbsp;<IMG style="WIDTH:12px;HEIGHT:11px;" title="Edit Selected Dataset button" alt="Edit Selected Dataset button" src="http://brprfa.bay.livefilestore.com/y1p1RjlRPeT8nUiaHkby2NgdTPdwAZhlX8j1xno7j2uXjLVrCXDh4iCA3tat3aKVR2mak4Anfa_irdyFjJ8vd1qRUXFznVjXJLl/edit-selected-dataset.jpg" width=25 height=22>&nbsp;<I>Edit Selected Dataset</I> button to pull up the <I>Dataset</I> window.&nbsp; You can then enter the field names on the <I>Fields</I> tab if they aren't already populated.</P>
<P><IMG style="WIDTH:388px;HEIGHT:319px;" title="Edit Dataset Fields tab" alt="Edit Dataset Fields tab" src="http://brprfa.bay.livefilestore.com/y1pfvnIF4EbFtuA6q8rBYk0t072J_cDu43NCKGn3DGtJepbF5Xtr-qqAQ23gcmmMQr94DH295woheHjRbkSBuXMC-oHJsxPlDoL/dataset-fields.jpg" width=554 height=458></P>
<P><STRONG>Building the Report</STRONG>&nbsp;</P>
<P>So far most of what we've done is just setup.&nbsp; With this&nbsp;done, it's time to design and build the actual report.&nbsp; For this, click on the Visual Studio <EM>Layout</EM> tab to get to the report designer surface.&nbsp; You can drag and drop text boxes, charts, tables, images and any other controls from the Toolbox onto the designer surface.&nbsp; To keep it simple we'll just drag a text box and a table onto the designer.</P>
<P><IMG style="WIDTH:685px;HEIGHT:341px;" title="Reprot Designer Layout tab" alt="Reprot Designer Layout tab" src="http://brprfa.bay.livefilestore.com/y1pWNM-fc_9Nr_h6im9m8v9jweJclU9QXhuvkIMwSDMxkaDXMq_QqvgLUJN3wtzqqQil54T4aTHvvDOFPupnH2D_i0WQAhx9u2G/report-designer-1.jpg" width=685 height=341></P>
<P>Put the title of the report in the text box (in this case "Missing Index Report") and format it to your liking.&nbsp; The table we dragged onto the designer surface has three columns by default.&nbsp; For this example we want six columns total.&nbsp; To add more columns right-click on the top border of the table and choose <I>Insert Column to the Left</I> to add a new column.&nbsp; Repeat two more times.</P>
<P><IMG style="WIDTH:516px;HEIGHT:265px;" title="Inserting columns in SSRS table" alt="Inserting columns in SSRS table" src="http://brprfa.bay.livefilestore.com/y1pMVVRej-iyVngxatXV5eZNwxe42KRX3loOI5AMNbJYNPvaQLwNPdA2_rEfbFqaXJ1M827zxGteCq8wtIkw8mJmOigbBDgloik/insert-columns.jpg" width=831 height=459></P>
<P>In the <I>Header</I> row of the table we'll type in the headers for each column like the following:</P>
<P><IMG style="WIDTH:577px;HEIGHT:173px;" title="Editing Table Column Headers in a report" alt="Editing Table Column Headers in a report" src="http://brprfa.bay.livefilestore.com/y1pclRRkvyQ58kZth8uxowVdjARwR9LROwxTNmOxKph-la2SwSyk5UdkLqGTrgDQVNk1bi-Q4A9YX_8RUluiCHCba_EI6APPNOz/report-column-headers.jpg" width=968 height=303></P>
<P>In the <I>Detail</I> row we'll put in the formulas to populate the database fields like this:</P>
<P><IMG style="WIDTH:579px;HEIGHT:181px;" title="Editing the Report Table Details" alt="Editing the Report Table Details" src="http://brprfa.bay.livefilestore.com/y1pCcKPNTXmgXVchY8WkAtQYF-L5iPN8g65mJC8cHhd0fJ7O3tPx4iAHmhWO7XrQG8VXQ-c2p-pUE01v5noF_nqihOBBaQ9d15j/report-column-details.jpg" width=968 height=303></P>
<P>SSRS formulas begin with the equal sign (<STRONG>=</STRONG>).&nbsp; Fields from the dataset are referenced directly using the format <EM><STRONG>Fields!field_name.Value</STRONG></EM>.</P>
<P><STRONG><U>Deploying and Running the Report</U></STRONG>&nbsp;</P>
<P>At this point use Visual Studio to build the project.&nbsp; Once it builds without error, navigate to the project directory in Windows Explorer and copy the <I>Missing Index.rdl</I> file to the SSMS Custom Reports directory (on my computer this directory is located at <EM>C:\Users\Michael\Documents\SQL Server Management Studio\Custom Reports</EM>, it'll be different on yours).&nbsp; </P>
<P>You can now bring up this report in SSMS by right-clicking on a database in the Object Explorer and selecting <I>Reports &gt; Custom Reports...</I> to select the custom report.</P>
<P><IMG style="WIDTH:350px;HEIGHT:322px;" title="Running a Custom Report in SSMS" alt="Running a Custom Report in SSMS" src="http://brprfa.bay.livefilestore.com/y1pXFRn1jsLwq2U9tpYrjStVVVnrG-A7RO2mZfEAd_tZPMUVN1X3VRrc81PtQl8l0TcHgCFIMa3dPDSb1qhAxRvyxu8vL94iaPB/run-custom-report.jpg" width=567 height=551></P>
<P>Choose your custom report from the file selection box and click <I>Open</I>.</P>
<P><IMG style="WIDTH:543px;HEIGHT:340px;" title="Selecting a Custom Report in SSMS" alt="Selecting a Custom Report in SSMS" src="http://brprfa.bay.livefilestore.com/y1pwUOidWktlB26rl2DefIcRZ4YzSDVRUwqZ3IqFDNh3mAEvNg9Fs-Gy8k8m5lnVJfSPm3dEb6GNQpOXGdsK2u1ABViZ4cHivzr/choose-custom-report.jpg" width=864 height=543></P>
<P>When you run a custom report in SSMS you'll get a warning like the following:</P>
<P><IMG style="WIDTH:306px;HEIGHT:186px;" title="SSMS Custom Report Warning Box" alt="SSMS Custom Report Warning Box" src="http://brprfa.bay.livefilestore.com/y1pj7c9vCxvKz3cPrIDZuL5EIvxHJGN6FlCspgGan0cZdd08mvzz0CKuUbdxcfbKA-hPSJU4F9pP6BJb08s1vLqurCuKhYMWDHe/custom-report-warning.jpg" width=420 height=256></P>
<P>Just choose <EM>Run</EM>.&nbsp; You might also want to check the box that says "<EM>Please don't show this warning again</EM>" to keep the box from popping up every time you run a custom report.&nbsp; The simple custom report you've created looks like the one shown below.</P>
<P><IMG style="WIDTH:593px;HEIGHT:254px;" title="Simple Custom Report" alt="Simple Custom Report" src="http://brprfa.bay.livefilestore.com/y1pTSsqYVoiCmzuzPOOHutMQnibG2CwxjGijBWma-J9VL3XPF1P0CpVzbQUUbGUj9vXmYh0avAt7armpL4BnSvxub7pcAq8xT3O/simple-report.jpg" width=964 height=453></P>
<P>You can use images, color and other formatting techniques to make the report easier to read and use, and more flexible for your users.&nbsp; Consider the image below, which is a screenshot of a reformatted version of the <EM>Missing Index</EM> report.&nbsp; This one includes more information, color and collapsible sections.&nbsp; Both reports are included in the attached <A title="SSMS Custom Reports" href="http://brprfa.bay.livefilestore.com/y1pu65YtRksdmF-NkqPRSNVJVjP75MjU31Yyvna97r9AoEdF9wilfgmWTFCpm7DDuuPRpS9qe9Io-h4_pms-Y2N0LV1L6j8bNeU/Custom%20Report%20Samples.zip?download">ZIP file</A> under the Missing Index and Missing Index Color directories.</P>
<P><IMG style="WIDTH:601px;HEIGHT:488px;" title="Reformatted SSMS Custom Report" alt="Reformatted SSMS Custom Report" src="http://brprfa.bay.livefilestore.com/y1pHxjPdr2Zd4nKD0hsBC6T0BTOiIH15VmH6pfwHt3jfrod1z_bQ_dqEx6eDfZKCTt66KotLSPQXOOrX7F40Lwc3T05ZBCZGypG/final-report-color.jpg" width=991 height=810></P>
<P>You can play around with the source files included in the attached <A title="Sample SSMS Custom Reports" href="http://brprfa.bay.livefilestore.com/y1pu65YtRksdmF-NkqPRSNVJVjP75MjU31Yyvna97r9AoEdF9wilfgmWTFCpm7DDuuPRpS9qe9Io-h4_pms-Y2N0LV1L6j8bNeU/Custom%20Report%20Samples.zip?download" target=_blank>ZIP file</A>.</P>“Excel found unreadable content” when exporting a Reporting Services reporthttp://sqlblog.com/blogs/jamie_thomson/archive/2010/01/22/excel-found-unreadable-content-when-exporting-a-reporting-services-report.aspxFri, 22 Jan 2010 20:16:26 GMT21093a07-8b3d-42db-8cbf-3350fcbf5496:21358jamiet<p><em>[This is just a quickie, I’m putting it up here for anyone that received the messages above and searches for it online!]</em></p> <p>Earlier today my team experienced some issues with a Reporting Services report that when attempting to export to Excel would cause Excel to throw the error “Excel found unreadable content” (I really should have got a screenshot but I didn’t think to do that. Sorry.)</p> <p>Of course we went down the usual routes. Searching online threw up a Microsoft KB article <a href="http://support.microsoft.com/kb/929766" target="_blank">Error message when you try to open a workbook in Excel 2007: &quot;Excel found unreadable content in Book_Name&quot;</a> which describes a situation related to Analysis Services.</p> <p>Turns out it was nothing at all to do with Analysis Services. The solution was actually a lot simpler – our Reporting Services server had run out of disk space and clearing down some space on that disk immediately solved the problem.</p> <p>&#160;</p> <p>So, if you encounter the above error – check your disk usage!</p> <p>Hope that helps!</p> <p>&#160;</p> <p><a href="http://twitter.com/jamiet" target="_blank">@Jamiet</a></p>SQL Server Management Studio Standard Reportshttp://sqlblog.com/blogs/andy_leonard/archive/2008/03/11/sql-server-management-studio-standard-reports.aspxWed, 12 Mar 2008 03:01:00 GMT21093a07-8b3d-42db-8cbf-3350fcbf5496:5538andyleonard<P><A class="" href="http://blogs.msdn.com/buckwoody/" target=_blank>Buck Woody</A> has a great series going on <A class="" href="http://blogs.msdn.com/buckwoody/archive/tags/Standard+Reports/default.aspx" target=_blank>SQL Server ManagementStudio Standard Reports</A>. </P>
<P>The SQL Server team did a great job developing these insightful and intuitive reports. I often show them to customers and during presentations and the response is always positive. </P>
<P>:{&gt; Andy</P>5 things I wish every SSRS developer would dohttp://sqlblog.com/blogs/james_luetkehoelter/archive/2008/02/26/5-things-i-wish-every-ssrs-developer-would-do.aspxTue, 26 Feb 2008 10:34:00 GMT21093a07-8b3d-42db-8cbf-3350fcbf5496:5276James Luetkehoelter<P>I had a great discussion while teaching a class on Reporting Services today, discussing the basics of report design (yes, I make them consider basic principles of report design before I start talking about the technical details - I'm a stickler that way). That made me consider some of the most common design issues that I see with SSRS and the principles I'd like to see everyone stick to:</P>
<P>&nbsp;1) Design for your audience - If you develop a report that is more than one page and give it to a CEO or upper management, it will probably be ignored. If you give a one page report to a "bean-counter", they'll throw it away (and yell at you to boot). One of the hardest aspects of report design is determining the level of detail for any given audience.</P>
<P>2) Design for design - This is an odd phrase, but the whole concept of getting all of your report requirements up front, spending a fair amount of time developing the report,&nbsp;only to have the end user say "that isn't what I wanted". Often you can't get sufficient requirements from your customers without presenting them with a sample report and asking them if it accurate (data) and what they wanted (layout). That first pass through in report design should be simply to find out what the customer really wants. How often do customers ask for what they really want?</P>
<P>3) Design for reuse - No report should be a complete standalone. It may begin that way, but there should always be the opportunity to either expand on the original design or leverage it using different parameters. Rather than making a single report for a single department, investigate creating a general report that any department could use, then make it specific for that single department by means of a hidden parameter. If you combine that type of general report with the functionality of linked reports, you can have distinct, visual and securable reports all driven from the same design. Think about reusability from the very beginning - you'll be surprised how often it pays off.</P>
<P>4) Design with pagination in mind - In particular with parameterized reports, you need to be aware of how SSRS will handle pagination. Since we can set pagination rules with each individual data region, those including multiple regions (read: a list within a list within a list ad infinitum), pagination can quickly get out of control. Side by side data regions are even worse. What you thought would be a single page report can often grow into a multi-page report, page-breaking in the most awkward of places. Be sure to control your pagination (hint: ever explore the Rectangle quasi-data region?).</P>
<P>5) Design as if you aren't writing a report - SSRS gives you the ability to display marginally related data (from a database standpoint) together on the same report by means of separate data regions. Other reporting tools traditionally use a banded design: a detail row, grouping rows, report headers and footers, etc. SSRS presents us with a canvas to paint a variety of report display items from separate data sources (without subreports - no one should ever have to use a supreport again). Take advantage of that ability and fundamentally rethink what the best way to visualize data.</P>
<P>If you disagree, tell me why. If you want me to blather on more about a specific item, just let me know (those that know me well know that once you get me started, I'm off to the races - viva la propeller-heads!).</P>Installing Reporting Services 2005 on Vistahttp://sqlblog.com/blogs/andy_leonard/archive/2007/11/29/installing-reporting-services-2005-on-vista.aspxFri, 30 Nov 2007 04:45:00 GMT21093a07-8b3d-42db-8cbf-3350fcbf5496:3587andyleonard<P>I have a new Dell desktop. The first thing I did was install Vista 64-bit and bumped it up to 4GB of RAM. </P>
<P>I'm now trying to install a default instance of SQL Server 2005 Developer and can't get Reporting Services to run because IIS isn't installed. After trying the click-a-bunch-of-stuff in the "Turn Windows features on or off" dialog, I decided to read the instructions. I may have to start by reading the instructions from now on... click-a-bunch-of-stuff may no longer be my default...</P>
<P>The instructions&nbsp;are <A class="" title=Instructions href="http://support.microsoft.com/kb/934164" target=_blank>here</A> - and I am happy to Report (pun intended) that they work.</P>
<P><IMG src="http://vsteamsystemcentral.com/images/ext/VistaSSRS.png"></P>
<P>The dialog you're after looks like this:</P>
<P><IMG src="http://vsteamsystemcentral.com/images/ext/VistaSSRS1.png"></P>
<P>Be sure to install the latest service pack (SP2 at the time of this writing) when the install completes.</P>
<P>:{&gt; Andy</P>