SQL Server Reporting Services is a comprehensive, server-based reporting solution that can author, manage, and deliver both paper-oriented and interactive, Web-based reports.

Do you agree with them all? Are we missing some? Let us know what you think.

1. Do you know the 5 user experiences of Reporting Services: Vanilla, Website, Email, Windows and SharePoint?

   The 5 user experiences of Reporting services are...
   • Vanilla

   

   Figure: Example of Vanilla user experience

   • Website

   

   Figure: Example of Website user experience

   • Email

   

   Figure: Example of Email user experience

   • Windows

   

   Figure: Example of Wndows user experience

   • SharePoint

   

   Figure: Example of SharePoint user experience

2. Do you know when to use Reporting Services?

   Like any solution, Reporting Services has its pros and cons. From our experience, we have discovered these things about Reporting Services:

   Cons

   • Parameters - you are forced to use built-in controls
   • Query string - when you change the parameters and refresh a report, the values do not appear directly in the query string, making it hard to copy/paste URLs
   • Can't separate SQL into a strongly-typed dataset or middle-tier object like in ASP.NET
   • There are potential difficulties with the deployment of RS reports and the exposing of them. However, once we have the infrastructure...

   Pros

   • You can develop read only reports faster in Reporting Services than ASP.NET
   • Maintenance with RS is easier than ASP .NET, as with most cases you don't have to write any code
   • Flexibility with groupings and totals is easier. In ASP.NET you would need to iterate through the DataSet, keeping variables with the totals
   • Parameters are built-in. In ASP.NET there is code
   • Drilldown interactivity. In ASP.NET you need to code up a treeview
   • Users can have reports automatically emailed to them on a schedule
   • Users can export natively to PDF and XLS, plus a variety of other popular formats

   So in conclusion, if you will only ever need 1 report, go with ASP.NET - it is easier to get up and running. If you plan to have more than one report, use Reporting Services - it's worth the time to configure.

   For a more detailed comparison between reporting solutions, take a look at our Guidelines for Report Solutions - Web Clients .

   

   Figure: Reporting Services has built-in support for PDF/XLS export and can be embedded in your ASP.NET pages

3. Do you check that "Report Server Configuration Manager" is all green ticks?

   To ensure your Reporting Services is running in a healthy state, you need to check if you have all the green ticks in your "Report Server Configuration Manager".

   

   Bad Example - Reporting Services is not running in a healthy state

   

   Good Example - Reporting Services is configured correctly

   Note: "Report Server Configuration Manager" is only available in SQL Server 2005.

4. Do you check out the built-in samples?

   SQL Server 2008 R2 Reporting Services comes with some great samples that will help get you started. Unfortunately, they aren't installed by default.

   These samples include:

   1. Report Project based on the Adventure Works 2008 R2 database
   2. Report Builder Model based on the Adventure Works 2008 R2 database

   Upon installing the samples successfully, you should see 3 folders (shown below) in your Report Manager.

   

   Figure: A proper installation of the samples

   To install previous version of Adventure Works sample reports, see our knowledge base article: How do I install SQL Server 2005 with the Adventure Works Samples?

   AdventureWorks Reports Sample

5. Do you know your 2 migration options to show your Access reports on the web?

   The greatest advantage for Access Developers is that with Reporting Services your reports can become available on the web. If you have a heap of Access reports, what are the choices for getting them on the web?

   1. Keep the existing reports in Access and expose them on the web with SSW Access Reporter . This is the least amount of work, as SSW Access Reporter is a utility that delivers existing Access reports online with minimal re-coding. Download a free trial today and try it out for yourself.

      

      Good Example - If you want to avoid migrating then SSW Access Reporter delivers your Access reports online so you can view them anywhere

   2. Import the reports into Reporting Services. Reporting Services has built-in support for importing and converting reports from Access. We have had plenty of success with it, but you will need to re-code things like conditional formatting and any code behind.

      

      Good Example - Reporting Services has built-in support for importing your Access reports

6. Layout - Does your report print and display on the web correctly?

   A lot of the time, you will want a hard copy of your reports. Obviously reports are different sizes on screen and on paper, so you need to format your report so it exports to PDF and prints properly. Here's how.

   1. Change the report's page width to 28cm (or 11in) and top and bottom margins to 0.5cm.

      

      Good Example - For proper printing, first change the Report's Page Width to 28cm (or 11in) and top and bottom margins to 0.5cm

   2. Change the Body width to 25.4cm (or 10in)

      

      Good Example - Then change the Report's Body Width to 25.4cm (or 10in)

      

      Bad Example - PDF page with 1cm margin (wasted much space on top and bottom)

      

      Good Example - PDF page with 0.5cm margin (you have more room for content)

      You can see the 0.5cm margin looks much better than 1cm, and you have more space to organize your content, especailly for a landscape print view.
      

   3. Resize report items (tables and charts) to fit the page. The easiest way to do this is to select (Ctrl+click) all report items that should span the whole width of the page, and set their Width property to 25.4cm (or 10in).

   Tip: Export your report to PDF and do a print preview, so you don't have to print a lot of testing pages to find out the best page settings.
   Tip: Remove top and bottom paddings in header and footer text can also give you more space.

   Note: Reporting Services reports accept both inches and cm, so you can use either.

   We have a program called SSW Code Auditor to check for this rule.

7. Layout - Do you include a useful footer at the bottom of your reports?

   You should always include a useful and informative footer at the bottom of your reports. Include:

   1. Date and Time Printed and User who printed it - see warning below (e.g. Printed by SSW2000\JatinValabjee on 3/1/2006 3:16:30 PM )
   2. Execution Time (e.g. Execution time: 1 minute, 10 seconds )
   3. Page x of y (e.g. Page 3 of 10 )
   4. Link to company website + slogan  (e.g. www.ssw.com.au - Writing software people understand )

   

   Bad Example - This footer doesn't provide any useful information

   

   Good Example - Useful and informative information should be displayed in your report footer

   Use these handy report expressions to show the above information.

   Footer Item	Expression	Sample Output
   Date and Time Printed / User ID	="Printed by " + User!UserID + " on " + Globals!ExecutionTime.ToString()	Printed by SSW2000\JatinValabjee on 3/1/2006 3:16:30 PM
   Execution Time	="Execution Time: " + IIf(System.DateTime.Now.Subtract(Globals!ExecutionTime).TotalSeconds < 1, "0 seconds", ( IIf(System.DateTime.Now.Subtract(Globals!ExecutionTime).Hours > 0, System.DateTime.Now.Subtract(Globals!ExecutionTime).Hours & " hour(s), ", "") + IIf(System.DateTime.Now.Subtract(Globals!ExecutionTime).Minutes > 0, System.DateTime.Now.Subtract(Globals!ExecutionTime).Minutes & " minute(s), ", "") + IIf(System.DateTime.Now.Subtract(Globals!ExecutionTime).Seconds > 0, System.DateTime.Now.Subtract(Globals!ExecutionTime).Seconds & " second(s)", "")) )	Execution time: 1 minute, 10 seconds
   Page x of y	="Page " + Globals!PageNumber.ToString() + " of " + Globals!TotalPages.ToString()	Page 3 of 10

   

   Good Example - Footer in visual studio designer

   
   Tip: Copy and Paste this XML into the <PageFooter> for the recommended footer of all your *.rdl files.
   

   	                <PageFooter>
   
                                      Paste here
   
                             </PageFooter>
   	                

   
   

   Warning: Adding the User who printed it stops all data-driven subscriptions
   When you try to add the User your data-driven subscriptions will fail with the following error:
   
   'The '/GroupHealth' report has user profile dependencies and cannot be run unattended. (rsHasUserProfileDependencies)'.
   

   
   A quick workaround is to add a user function to fallback the error to a nice message, like: "SYSTEM",

       Public Function UserName()
       Try
       Return Report.User!UserID
       Catch
       Return "System"
       End Try
       End Function                                       
                           

   
   Use above function to replace your reference to Report.User!UserID will allow the subscription to work correctly.

8. Layout - Do you avoid using word 'Report' in your reports?

   We believe the word 'Report' is redundant in a report, so we avoid using this word in our reports.

   

   Bad Example - redundant use of the word 'report' in a report

   

   Good Example - avoidance of the word 'report' in a report

9. Layout - Do you underline items with Hyperlink Action?

   The Hyperlink Action allows users to navigate between reports smoothly, but users may ignore the navigation functionality in your reports.

   

   Bad Example - No underline for hyperlink item

   

   Bad Example - No underline for the textbox with hyperlink action

   With the underline effect on your hyperlink items, it will be easy for users to find the navigation on your reports.

   

   Good Example - Underline for hyperlink item

   

   Good Example - Underline for textbox with hyperlink action

   We have a program called SSW Code Auditor to check for this rule.

10. Layout - Do you show errors in Red ?

    Errors on reports should not occur but when they do it is best to make it clear to the reader that they have just experienced an error.
    How evident are the error messages on the 1st report below?

    

    Bad Example - Using the default NoRows property of the table control - error message is not clear enough

    

    Good Example - Use a customized textbox and icon to show the error message in red

    Reporting Services allows you to set the 'NoRows' property of a table control to warn your user when there is no data available. This is similar to handle the 'NoData event' in Access report but there is no advanced control on this message, not even a Color property - this has not been improved in RS2005, see our Better Software Suggestions page .

    

    Figure: NoRow property of Table control only allow simple string

    Here's how to add a custom "NoData" textbox with a red icon to your report:

    1. Add a rectangle to the top of your report, above any report items in the body. Set its BorderColor to Red
    2. Drop a textbox into the rectangle and give it the value No records were found matching your criteria. Please modify your parameters and try again.
    3. Add an Image control next to it (add it to your Images folder in your solution and reference it like Images/fatal_error_info.gif ). Your report will now look similar to the one below.

       

       Figure: Adding a custom error message to your report

    4. In the Hidden property of the Rectangle , add an expression to show/hide it depending on whether any rows were returned. Use the following expression, substituting the bold for your own values (e.g. checking if the sum of all orders is < 0)

       --Expression to set the visibility of the error message controls
       
       = iif( Sum(Fields!SaleTotal.Value, "MyDataSet")>0, True, False)

       

       Figure: The Hidden property of the rectangle

    5. Group all other report items into a rectangle - you want to be able to show and hide them dynamically
    6. In the Hidden property of this Rectangle , add an expression to show/hide it depending on whether any rows were returned. Switch the True and False values around, so that it shows if it does have records, and hides if it does not have records (the opposite behaviour to the error box). So, in the example above, the expression would be:

       --Expression to set the visibility of the main report items
       
       = iif( Sum(Fields!SaleTotal.Value, "MyDataSet")>0, False, True)
                               

11. Layout - Do you have consistent report name?

    A professional report should have consistent name.

    

    Bad Example - Inconsistent report name

    

    Good Example - Consistent report name

12. Layout - Do you include feedback information in the report?

    A professional report should have the feedback information, then users can give suggestions directly to the designers.

    

    Good Example - Include feedback information

13. Layout - Do you know which version of Reporting Services you are running?

    You can add the version number to the name of the reporting server by:
    

    1. Going to Site Settings
    2. Add 2005 or 2008 to the name

    

    

    Bad Example - You can't tell what version of reporting services you are running

    

    Good Example - We can clearly see that this is running Reporting Services 2005

14. Layout - Do you put content as less as possible in page header?

    Things in page header repeat on every page. To avoid duplicate and save paper when printing, we put content as less as possible in page header.

    

    Bad Example - too many things in page header

    

    Good Example - little in page header

15. Data Layout - Do you show the past 6 months of totals in a chart?

    When you are working with reports that use time-based data (sales figures, employee productivity etc.), it is handy to see how you went this month compared to the past 6 months. The best way to show this is on a bar chart.

    

    Good Example - Use of bar chart to show the past 6 months of totals at the end of your report for easy comparison

    To do this:

    1. Create a new dataset in your report
    2. Enter the following query, substituting "MyDate" for the name of the date field you are referencing, "MyTable" for the view or table you are selecting from, and "@pEndDate" for the name of the report parameter you are using for the data end date:

       SELECT DISTINCT TOP 6
         CONVERT(varchar(12), Year(MyDate), 101) + '-' + RIGHT('0' + Convert(Varchar(2), MyDate, 101), 2) AS Y
         , Sum(PaidTotal) * -1 AS Total
       FROM
         MyTable
       WHERE
         MyDate BETWEEN DateAdd(Month,-5,convert(varchar(12), Month(@pEndDate)) + '/1/' + convert(varchar(12), Year(@pEndDate))) AND 
       CASE WHEN datepart(d,@pEndDate) = 1 THEN DateAdd(d, 1, @pEndDate) ELSE @pEndDate END
       GROUP BY
         CONVERT(varchar(12), Year(MyDate), 101) + '-' + RIGHT('0' + Convert(Varchar(2), MyDate, 101), 2)
       ORDER BY
         CONVERT(varchar(12), Year(MyDate), 101) + '-' + RIGHT('0' + Convert(Varchar(2), MyDate, 101), 2)
                               

    3. Configure the new added parameter 'pEndDate'

    

    Figure: Change Data Type to DateTime and assign to the proper default values

    4. Add a chart to your report in Layout view and change its type to "Simple Column"
    5. Drag the "Total" field from the Datasets window into the Data area on the chart, and the "Y" field into the Category area. Your chart will now look similar to the one below.

       

       Figure: Build up the column chart in layout view

    6. Now you need to set the last column to be a different color so it stands out. Right-click the chart and click Properties.
    7. Click the "Data" tab, click "Edit..." next to the "Values" box, then go to the "Appearance" tab and click "Series Style..." then the "Fill" tab.
    8. In the "Color" textbox, enter this expression, then OK all dialogs to return to the report:

       =iif(Right(Fields!Y.Value, 2)=Month(Parameters!pEndDate.Value), "Blue", "Green")

16. Data Layout - Do you show data and chart in one?

    It is important to provide a chart, as well as the data which it is based on. Users of the report service will find the data easier to understand and compare. However, in order for the report to be understandable, the data and chart must be clear and uncluttered.

    

    Bad Example - there is no chart to visually represent the data

    

    Bad Example - there is a chart, however it is not clear

    

    Good Example - Data and chart are clearly integrated into one

17. Data Layout - Do you avoid using a single chart when you need it to be scaled?

    

    Bad Example - Just a chart - poor scaling for only 1 record

    

    Bad Example - Just a chart - poorly scaling when many records

    The reason for this problem is that the 'size' property of the chart control doesn't support expressions like 'Count(Rows) or queried values like 'Fields!RowCount.Value', so the chart control cannot adjust its size according to the data.
    The solution for this problem is to use an embedded chart within the table - this will create a dynamic chart list similar to the list shown below.

    

    Figure: Size property of the chart control

    

    Good Example - A table with chart

    To do this, you need to create a table in your report and add a chart into each of the rows.

    

    Figure: Embedded chart in a table will generate dynamic chart list

    Note: When rendering a report to your browser or an email, Reporting Services generates a separate image for every single image in the report, even if they are identical. When you are using graphs, images or charts in your report, this can cause a large number of images to be generated. Always include a red warning at the top of any emailed reports so that users do not try and forward or reply to them. Use a warning like this:

    Warning: Do not reply to or forward this report in an email - Outlook may slow down or even hang

18. Data Layout - Do you use expressions to show the correct scale on charts?

    In Reporting Services 2005 you can use an expression to specify the scale of your charts. If you do not specify a maximum value for your y axis, the bar charts become inaccurate, as you can see in this figure.

    

    Bad Example - With no scale value set, the charts do not display based on the correct scale

    Here's how to set the scale.

    1. In Layout view, add a new row to the bottom of the table
    2. At the bottom of the column with the chart, set the textbox value to =Max(Fields!MyTotal.Value), where "MyTotal" is the Data field you're using in the chart.

       

       Figure: Add a new row to your table and set the max value

    3. Set the textbox's Name property to MaxMyTotal (e.g. MaxCount)
    4. Set the new row's Visibility/Hidden property to true - you don't want to show it in the report
    5. Open the Chart properties and select the "Y Axis" tab
    6. Set the Maximum value to the value of the textbox, i.e. "=ReportItems!MaxMyTotal.Value"

       

       Figure: Set the maximum value to the value of the textbox

    7. If you expect to have negative values in the chart (e.g. when comparing 2 values), set the Minimum to -1 multiplied by the max value, i.e. "=-1 * ReportItems!MaxMyTotal.Value". Otherwise set it to 0 (zero).
    8. If you expect to have negative values in the chart, select the chart value in the Data tab and click "Edit..." . Go to Appearance->Series Style->Fill and enter the following expression:

       =iif(Fields!Change.Value > 0, "Green", "Red")

       Where "Change" is the name of your data field. This sets the color of the bar to green if it is positive, and red if it is negative
    9. Click OK and preview the report. The chart will now be using the maximum value across all the charts.

       

       Good Example - The scale is now correct

    This way is tedious and a "hack". We think that the scale should be automatically set with an option to customize it via an expression. See our suggestion about this on Microsoft SQL Reporting Services Suggestions .

19. Data Layout - Do you show change in your reports?

    It is important to show previous and current data, as well as the changes between the two.

    

    Bad example - does not show the change between the previous and current data

    

    Good example - shows the change between the previous and current data

    There will be cases in which the Change column has no meaning then you'd better to make this column invisible in your reports. In one of our reports we use an expression on Hidden property of this column to determine whether to show it based on the value of a parameter.

    =iif(Parameters!ComparedExtractionID.Label = "N/A",true,false)

    Expression for Hidden property

20. Data Layout - Do you avoid showing change as a percentage?

    When comparing two sets of data in a report (for example, sales this month compared to last), showing the change as a percentage is a bad idea. For example, if you made 1 sale last month and 2 this month, you have had a 100% increase. If for another product you made 1000 sales last month and 2000 this month, that is also a 100% increase, but you've sold 1000 of that product compared to 1 of the other product.

    For this reason, show the difference as an actual value, so you can compare all values easily. See the figures below for examples. To see how to create the negative/positive valued chart, see Do you use expressions to show the correct scale on charts?

    Bad - Notice how the "change" column in the report doesn't accurately reflect the difference in downloads - 1 download last month and 2 downloads this month will yield a 100% increase - which looks impressive as a percentage but really isn't.

    

    Bad Example - The percentage change column in this Reporting Services product downloads report is misleading

    Good - This works better just showing the difference between the two values over the 2 months.

    

    Good Example - The column works better as just a difference between the current and previous download totals

21. Data Layout - Do you use alternating row colors?

    For readability, always use alternating row colors. Use White and Gainsboro (a light shade of grey). Select the row, and enter this expression in the BackgroundColor property:

    = iif(RowNumber(Nothing) Mod 2, "White", "Gainsboro")

    

    Good Example - Alternating row colors greatly improve the readability of reports, and is very easy to do in Reporting Services

22. Data Layout - Do you have nodes count like Outlook?

    A report with drill through like this should have the number in nodes like Outlook.

    

    Bad Example - This does not have the nodes count

    

    Good Example - This does have the nodes count

    Here's how to add nodes count to the textbox with "collapsed(+)" in your report:

    In the Expression property of the Textbox , add an expression to show nodes count. Use the following expression:

    --Change the SQL ( or add a new DataSet )
    SELECT a.TerritoryID, ( CONVERT ( varchar,a.TerritoryDescription ) + ' (' + CONVERT ( varchar, count ( c.TerritoryID ) ) + ')' ) AS Number , ... FROM Territories a INNER JOIN EmployeeTerritories b ON a.TerritoryID=b.TerritoryID, ... GROUP BY a.TerritoryID, a.TerritoryDescription,...
    
    --Expression to show nodes count
    
    = Fields!Number.Value

    Bad Example - Get the Outlook Node Count look by changing the SQL.

    
    

    --Expression to show nodes count
    
    = Fields!Name.Value + "(" + CStr ( CountRows( ) ) + ")"

    Good Example - Use the CountRows() function to get the Outlook Node Count look.

    Note: The CountRows function is one of the several native functions provided by Reporting Services and returns the count of rows within a specified scope. If no scope is specified, it defaults to the innermost scope, which in our case resolves to the static group that defines the values in the data cells.

23. Data Layout - Do you avoid displaying decimal places?

    Moved to Power BI – Do you avoid using too many decimal places in reports?

    Having decimal places is generally not required when the numbers are there to show a general indication. Only include decimal places on reports for accountants that will be used for reconcilations.

    

    Bad Example - This does display decimal place

    

    Good Example - This avoid displaying decimal place

    Here's how to remove decimal place in your report:

    In the Expression property of the Textbox , add an expression to format currency values. Use the following expression:

    --Expression to remove decimal place
    
    c0 = FormatCurrency( Sum(Fields!TotalDue.Value), 0)

    Note: The FormatCurrency function is one of several native functions provided by Reporting Services and returns an expression formatted as a currency value using a currency symbol according to the language setting on the textbox.

    What is some managers don't want decimals and accountants do?

    For some users (usually accountants) the number is critical to accurate reporting and reconciliation. In such cases, add a parameter to let the users choose.

    This workaround should not be necessary. See the suggestion to Reporting Services ?Give users a runtime option to increase or decrease decimals places , so we don't need this workaround.

    

    Good Example - Add a parameter to set the decimal places format

24. Data Layout - Do you have consistent height of table row across all your reports?

    Same height of table row across all reports gives users consistent and professional impression. We use the default height of table row (0.63492cm) as a standard to make sure all tables in our reports have consistent row height.

    This rule also applies to height of Textbox in all reports.

    

    Bad Example - Bad Height

    

    Good Example - Good Height

    

    Bad Example - Bad Height In Design View

    

    Good Example - Good Height In Design View

    We have a program called SSW Code Auditor to check for this rule.

25. Data Layout - Do you display zero number as blank in your reports?

    It looks better to display zero numbers as blank than to leave lots of '0' in a report. It's easy to do with a simple format code '#,#'.

    

    Bad Example - Bad Zero Number Format

    

    Good Example - Good Zero Number Format

    

    Good Example - Format Code

26. Data Layout - Do you know the best way to show your data?

    

    Bad Example - Plain table

    

    Good Example - Plain table with separate chart

    

    Good Example - Table with bar chart embedded

27. Data Layout - Do you show time format clearly?

    It will give a professional look for your report to show time in a clear and consistant format.

    

    Bad Example - bad time format

    

    Good Example - clear time format

    A related rule about time format is Do you keep Time formats consistent across your application?

28. Data Layout - Do you show all of the report parameters in body?

    It is much better to display all of the report parameters in report body, because it will be clear for users to know what they search not only in IE but also in exported file (PDF, Excel).

    

    Bad Example - not displaying all report parameters

    

    Good Example - displaying all report parameters

29. Data Layout - Do you know how to use logical page breaks?

    Sometime you want your report to break at somewhere to separate different part of content into individual pages. A logical page break is what you need.

    Logical page breaks are defined in the report definition by using the PageBreakAtStart and PageBreakAtEnd properties in various report elements, including group, rectangle, list, table, matrix, and chart.

    Here is an example of how we add logical page breaks in a report to make each subreport start showing at right beginning in a new page.

    

    Figure: Insert a logical page break before a rectangle containing a subreport

    

    Figure: The subreport started in a new page

30. Data Layout - Do you know how to prevent charts growing with rows?

    By default charts in table grow with rows. This makes height of charts inconsistent and the report ugly.

    

    Bad Example - inconsistent chart height

    Putting a rectangle in table cell and placing your chart in the rectangle fixes this problem.

    

    Good Example - consistent chart height

31. Data Layout - Do you take advantage of vertical text (when there are lots of thin columns)?

    When a report has many columns and each column contains small amount of data, it is a good idea to use vertical text for the column headers.
    By changing the WriteMode of a text box from
    

        lr-tb (left to right, top to bottom)
        

    to
    

        tb-lr (top to bottom, left to right) 
    

    your text will become vertical, and you save lots of space.

    

    Bad Example - Not using vertical text for headings, when you have lots of thin columns

    

    Bad Example - Not using ticks and crosses

    

    Good Example - The report width has been reduced by clever use of the vertical text in the column headers

    Note: Microsoft have not given us the option of having the vertical text bottom to top. It would be easier to read. This suggestion has been added to Suggestions for Microsoft RS

32. Data Layout - Do you use gray color for past data?

    It's common that gray color means old, so we use gray for past data in reports.

    

    Bad Example - old data not in gray

    

    Good Example - old data in gray

33. Data Layout - Do you use 3D cylinder in column chart?

    3D cylinder chart will make your report cool.

    

    Bad Example - normal 2D column

    

    Good Example - cool 3D cylinder

34. Data Layout - Is title centered in chart?

    Title should be centered in chart.

    

    Bad Example - title not centered

    

    Good Example - title centered

35. Data Logic - Do you use de-normalized database fields for calculated values?

    Most reports contain some sort of calculation - order totals, freight costs and so on. You have 3 options on how to display this in your report:

    1. Use an expression in the report (bad). Avoid doing this because your logic is scattered throughout the report, and also because this logic cannot be shared around reports or with your other web and windows applications.
    2. Call an assembly with the calculated logic (better). This is better then using a calculation expression because the logic can be shared over multiple reports, and it is easy to find as all the logic is inside the one .NET project. It is not the best solution because there is an extra level of complexity as you have to build, compile and reference the assembly containing the logic.
    3. Use a denormalised database field (best). This is the best way because not only is the calculated value accessible directly from the report's data set, but the value is already pre-calculated which can provide a performance improvement (compared to calculating the value each time the report runs).

    

    Bad Example - Figure: Avoid using expressions for calculated values

    

    Bad Example - Figure: Avoid using external assemblies for calculated values - it adds an unnecessary level of complexity

    

    Good Example - Figure: Use a denormalised database field for calculated values

36. Data Logic - Do you use SQL Ranking functions to rank your data

    SQL Ranking functions are introduced since SQL 2005. With these handy functions, you can easily rank your data.

                        SQL Snippet
                        SELECT Rank() Over(Order A.BillableTimeTotal Desc) As Rank
                    

    

    Figure: Rank by SQL Ranking functions

37. Parameters - Do you avoid showing empty reports by at least setting Default parameters?

    When a user opens a report that has no default parameters, there is always a split-second of confusion as to why the page is blank. Default parameters allow the user to see what they expect... a report, and they also show users the expected format for parameters and make it easier to run validation tests to see if all the reports on your server are working correctly.

    When you create reports, always set default parameters.

    

    Bad Example - There are no Default Values set for the Parameter

    

    Good Example - Default Parameters can be specified manually (non-queried) or from a query

38. Parameters - Do you avoid unnecessary words in the parameter?

    Words such as: "Select", "Enter" and "Type" used in the parameter names are always a bad practice and should avoided

    

    Bad Example - Parameters with unnecessary words

    

    Good Example - Parameters without unnecessary words

39. Parameters - Do you avoid showing empty reports by the most intelligent default?(espacially for dates)

    When user opens a report, they expect to see something. It is the developer's job to get the default values for parameters right. And of course you don't get it right by hard coding defaults.
    
    

    

    Bad Example - making a user select the parameters before seeing the data

    
    The following report shows nothing, because the parameters are using meaningless default values (in this case, old dates for the year 2006)

    

    Bad Example - Empty report caused by incorrect parameter default values (probably hard coded for when the developer wrote the report in 2006)

    
    

    

    Good Example - This report shows initial data as the developer configure useful parameters (in this case default values for the entire month of October Note: in US date format)

    In SQL Reporting Services, parameters can be:

    • hard coded
    • an expression, or
    • from a query

    Hard coded values should never be used. Expressions may be good for some instances, but because it's not linked with your data, it may not be good enough.

    --Expression to get the 1st day of the previous month (aka Start Date)
    DateSerial(iif( Month(DateTime.Now)=1, Year(DateTime.Now)-1, Year(DateTime.Now)), iif( Month(DateTime.Now)=1, 12, Month(DateTime.Now) - 1), 1)
    
    --Expression to get the 1st day of the current month (aka End Date)
    DateSerial(Year(DateTime.Now), Month(DateTime.Now),1)
    
    --Expression to get the 1st day of the next month
    DateSerial(iif( Month(DateTime.Now)=12, Year(DateTime.Now)+1, Year(DateTime.Now)), iif( Month(DateTime.Now)=12, 1, Month(DateTime.Now) + 1), 1)

    Bad Example - Expressions to set the date range to the current month

    

    Bad Example - Using an Expression to set the default values.(This will not be good enough if there is no data in the current month)

    
    

    The Solution:

    In order to give report parameters correct default values, you should always use query to generate these values from database. This will ensure your default values come from your data, so they won't fail to give some records.
    

    --Query to generate valid date from existing data
    SELECT
    CONVERT(
    DATETIME, 
    '1, ' + 
    DATENAME(month, DATEADD(month, 1, MAX(OrderDate)))+ 
    DATENAME(year, DATEADD(month, 1, MAX(OrderDate)))
    ) AS EndOfMonth,
    CONVERT(
    DATETIME, 
    '1,'+ 
    DATENAME(month, MAX(OrderDate))+ 
    DATENAME(year, MAX(OrderDate)) 
    ) AS StartOfMonth
    FROM Orders

    Good Example - Using a query to retrieve the last month of available data

    

    Good Example - Using a query to set default values for report parameters

    The dataset 'ValidDates' looks similar to this:

    

    Figure: Create a dataset to query the data and provide useful default parameter values for your report

40. Parameters/Internationalization - Do you use the DateTime data type for date parameters?

    Use the DateTime data type for date parameters instead of using strings. There are 3 reasons to do this:
    1) Stop the bug "Cannot read the next data row for the data set"
    Although a hardcoded string will work, it will not work for all users regional date/time settings.
    E.g. a string data type parameter with a value of "26/01/2006" is correct for "dd/mm/yyyy", but it is wrong for "mm/dd/yyyy"
    2) When SQL Reporting Services is using the DateTime data type parameter, it will get the datetime value on the users setting (aka the Culture DateTime format).
    3) Your users also get the advantage of a date/time picker control, which automatically works out the correct regional date format. This solves the US/Australian date problem. (i.e. DD and MM are reversed).
    

    

    Bad Example - Using the String data type for date parameter

    

    Good Example - Use DateTime data type for the date parameter - you will not get internationalization bugs and it gives users a calendar control

    We have a program called SSW Code Auditor to check for this rule.

41. Do you have consistent parameter names?

    All display names referring to the same parameter should be consistent in everywhere of your reports. In addition, the parameter name and value should be in the same line if possible.

    

    Bad Example - Inconsistent parameter names

    

    Good Example - consistent parameter names

    
    

    Note: If your data is not live, but based on ETL/SSIS
    Then each time log each import to a table Eg. Once a week. Then on the report parameters show this - so users know how old the data is.

42. Performance - Do you cache popular reports for better performance?

    When you have a large number of users all trying to access the same reports, the performance of your report server can suffer dramatically. Caching reports for a certain period of time can drastically reduce the load placed on the report server, leaving it ready to handle other requests.

    When a report has caching enabled it only needs to be processed once within the expiry period. This means that when the first of your eager users hits that report, the report server will keep a copy until it expires, and will serve up this copy to any users that request the report within that period. This leaves the report server ready to process other reports quicker as it is not busy processing the popular reports over and over again.

    For more information on setting up report caching please see the following KB article: http://www.ssw.com.au/ssw/KB/KB.aspx?KBID=Q1668240

    

    Figure: Enable caching for frequently used reports to improve performance

43. Performance - Do you schedule snapshots of slow reports for quicker access?

    A report should never take more than 30 seconds to run. Slow reports frustrate users, and also take valuable server performance away from the report server.

    When dealing with slow reports, it is a good idea to setup scheduled snapshots. As an example,  you would schedule a long running report to create a snapshot at night when the server is idle and not under a lot of stress. We recommend creating new snapshots every night so that the information displayed in the reports is never more than 24 hours old.

    As an example:

    Do create a snapshot for a sales summary
    Do not create a snapshot for an invoice report

    For information on setting up scheduled snapshots see the following KB article: http://www.ssw.com.au/ssw/KB/KB.aspx?KBID=Q1119699

    

    Figure: Create a scheduled snapshot of slow reports to improve performance

44. Internationalization - Do you keep use regional friendly formatting?

    You know how we write one million like $1,000,000.00, well when I was in Brazil I discovered they did it wrong and display it like $1.000.000,00

    I tried to correct many but they didn't listen :-)

    So, be culturally sensitive and use regional friendly formatting.

    

    Bad Example - Bad Number Format

    

    Good Example - Good Number Format

    

    Bad Example - Bad Currency Format

    

    Good Example - Good Currency Format

    

    Bad Example - Bad Percentage Format

    

    Good Example - Good Percentage Format

45. Internationalization - Do you make sure your language follows the user's regional settings?

    Developers too often change the 'Language' settings on reports in order to make it look ok for how they want to see it. Without realizing that they are now not supporting multiple cultures. To do this, you need to set the 'Language' to "=User!Language" . Then the report will recognize user client's culture settings, e.g. IE's languages settings.

    Now you can specify this on either the culture sensitive controls or the whole report. Generally, is better specify this property on the whole report.

    

    Bad Example - Here the 'Language' setting is set to a specific culture

    

    Good Example - Here the 'Language' setting is set to '=User!Language' to detect user's culture automatically

    

    Figure: Good Example - Now the data respects user's Language preference of IE in this case English (Australia)

    

    Figure: Good Example - Likewise the data also respects user's Language preference of IE in this case Chinese (China)

    Warning: Adding the 'User' who printed the report, stops all data-driven subscriptions
    When you try to add the 'User' your data-driven subscriptions fail with the following error:
    'The '/GroupHealth' report has user profile dependencies and cannot be run unattended. (rsHasUserProfileDependencies)'.
    The reason is the report doesn't know which language to choose
    the workaround is to add a user function to fallback the error to a default language, like: "en-AU"

    Public Function Language()
        Try
            Return Report.User!Language
        Catch
            Return "en-AU"
        End Try
    End Function                        
                     

    Good Example - Use above function to replace your reference to Report.User!Language it allow the subscription to work correctly.

46. Internationalization - Are you aware of your date format of parameter bar?

    The format in parameter bar is always decided by the Language settings of client browser regardless of the Language property of your report.

    

    Figure: Date in English(AU) format in parameter bar

    

    Figure: Date in French(Canada) format in parameter bar

47. Internationalization - Do you make sure your language rule has an exception for Currency Fields?

    Although we can make the report support multiple cultures (as per Do you make sure your language follows the users regional settings? ), we suggest you don't do this for currency fields. Instead:

    1. Have the Language set specifically to the culture you want.

       e.g. If you do a report for Australian Dollars, then it should be "English(Australia)"; if for Chinese Yuan, it should be "Chinese(People's Republic of China)". Because the format of currency should not change as per user's culture setting as $100 AUD <> 100 CNY !

    2. Have the currency column header set include the currency.

       Because $100 USD <> $100 AUD !

    

    Bad Example - using default language for currency field

    

    Good Example - This currency field stores Australian Dollars and will always display it that way

    

    Figure: AUD currency

    

    Good Example - This currency field stores Chinese Yuan and will always display it that way

    

    Figure: Chinese Yuan currency

    If you don't want to get currency fields hard coded in reports, you can use an expression to read settings from your database.

    

    Good Example - using specified language as per value of column CurrencyType in table SystemValue

48. Admin - Do you validate all your reports?

    Reporting Services does not have a build button, and thus, there is no way to verify that every single report is error free.
    Every Reporting Services installation should include this validator to check that all the reports are good.

    

    Figure: SSW SQL Reporting Services Auditor

    SSW SQL Reporting Services Auditor is Web-Application that iterates through all the reports on a report server and shows whether they have rendered correctly or if any errors occurred.

    The SSW SQL Reporting Services Auditor web application returns an XML dataset that can be consumed natively by SQL 2005 Reporting Services. The XML dataset can also be consumed by SQL 2000 Reporting Services; however, this requires you to write a custom Data Processing Extension as SQL 2000 Reporting Services does not natively support XML data sources.
    For more information read Microsoft's guide on Using an External Dataset with SQL 2000 Reporting Services

    

    Figure: SSW SQL Reporting Services Auditor in Action!

    Download SSW SQL Reporting Services Auditor (Requires SQL Server 2005 Reporting Services).

49. Admin - Do you create a separate virtual directory for Admin access?

    When going public with Reporting Services, you should have 2 ports:

    1. A public access port. This allows your public users to access their reports normally on a port which has been configured for anonymous users.
    2. An admin access port on your web site. This allows authenticated internal users to administer the report server via the Report Manager.

    To set this up you need to perform the following:

    In Windows Explorer:

    1. Create a Windows User account for the anonymous reporting services site to run as. e.g. IUSR_ReportViewer
    2. Open up the ReportingServices directory ( C:\Program Files\Microsoft SQL Server\MSSQL.3\Reporting Services)
    3. Duplicate the ReportServer and the ReportManager folders then rename postfix them with _External e.g. ReportServer_External and ReportManager_External
    4. Set the file access security on the new folders so that "Everyone" has full permissions.
    5. Edit the ReportServer_External/rsreportserver.config file. Update the URL node <UrlRoot>http://xxxx:81/ReportServer</URlRoot>
    6. the ReportManager_External/RSWebApplication.config file. Update the URL node <ReportServerUrl>http://xxxx:81/ReportServer</ReportServerUrl>

    
    In IIS Manager:

    1. Create another website on another port (i.e. port 81)
    2. Create Virtual Direcoties for ReportServer and Reports then point them to the new folders we just made. Make sure they are setup as applications.
    3. Change the Authentication of these 2 virtual directorys to use the user we have already created "USR_ReportViewer". Ensure that all other Authenticated access is unchecked.
    4. In the Reports Virtual Direcotry, make sure that it is running the same version of ASP.NET. Set the Applicaiton to execute Scripts and Executables. Add Home.aspx into the Default Documents.
    5. In the ReportServer Virtual Directory, make sure that it is running the same version of ASP.NET. Remove all the Application Mappings in the Application Confguration. Then add a wildcard mapping to the Executable C:\WINDOWS\Microsoft.NET\Framework\v2.0.50727\aspnet_isapi.dll

    In SQL Management Studio:

    1. Add the user for these folders to have access in SQL

    In Reporting Services:

    1. Go to http://localhost/reports
    2. Click Properties -> New Role Assignment
    3. Enter in IUSR_ReportViewer and click Browser then click OK

    Note #1: The default website will be used for internal Admin (secure) use, and a website on a different port (in this example we use port 81) will be used for external anonymous access.

    Note #2: Do these steps again every time you install a Reporting Services service pack

    Once complete, you should now have authenticated access available on the standard port (80) and public access available on the new port (81).

    We think that we should have the ability to choose how IIS authenticates clients - read our Reporting Services suggestion .

    

    Figure: Create a separate virtual directory for admin access

    The process is a little simpler in SQL 2000:

    In Windows Explorer:

    1. Open up the ReportingServices directory (typically c:\Program Files\Microsoft SQL Server\MSSQL\Reporting Services\)
    2. Make a copy of the ReportManager folder and call it ReportManagerPublicAccess
    3. Duplicate the file access security settings on ReportManager in ReportManagerPublicAccess
    4. Edit the RSWebApplication.config in the ReportManagerPublicAccessfolder to point to http://server:81/ReportServer

    
    In IIS Manager:

    1. Configure the default website's ReportsServer virtual directory to give access to IUSR_ServerName (for public access)
    2. Export the Report and ReportServer virtual directory to an XML file
    3. Create another website on another port (i.e. port 81)
    4. Add the Report and ReportServer virtual directories using the XML files created in step 2
    5. Set the Reports virtual directory to point to the ReportsManagerSecure directory instead of just ReportsManager
    6. Set the directory security on the ReportServer on port 81 to use windows integrated security

50. Admin - Do you take advantage of 'Integrated Security' to do Payroll reports?

    Payroll report should only show the records of the current user, Reporting Services support "Integrated Security" which you can use to identify the user who is running the report and only return relevant result for the current user.

    

    Bad Example - Everyone can see others' rate changing history (maybe useful for administrative, but not for your employees)

    

    Good Example - The current employee can only see his own record

    To generate such a report, you need to use the filter on the data table:

    

    Figure: Specify the filters on your data table and select Globals->User!UserID

    Note: 'Edit Expression' dialog is only available on RS 2005, but the UserID global variable is available on RS 2000.

51. Admin - Do you remove @ExecutionTime in subject of subscription email?

    In subscription settings, @ExecutionTime should be removed from subject, because it ruins conversation threading in Outlook - You cannot sort them by subject.

    

    Bad Example - Keep @ExecutionTime in subject

    So we always make subject of subscription exactly same as report name.

    

    Good Example - Subject same as report name

52. Do you know to embed an RS report in ASP.NET page the right way (using Report Viewer instead of IFrame)?

    This rule is obsolete and has been replaced with http://rules.ssw.com.au/SoftwareDevelopment/RulesToBetterMVC/Pages/Do-you-know-the-best-way-to-do-printable-reports.aspx using MVC 5.

    Visual Studio provides a Report Viewer control for WebForms, so use it instead of the old IFrame method. The report viewer control is super easy to use - just drag the control into your page designer and select the properties you like.

    The bad old way was to use an IFrame and point it to the report's URL (including parameters). This is bad because you might encounter a typing error. If you want to disable vertical scrollbar, you need to adjust the height of IFrame manually. Furthermore, you can't configure the report's authentication separately.

    <IFRAME width="100%" height="700" TITLE="Report" src="http://reports.internal.ssw.com.au/ReportServer?
        %2fTimeProOnlineReports%2fClientRegisteredProductsByDate
        &rs:Command=Render&ClientContactID=<%=mintClientContactID%>&ClientExInfo=<%=clientInfo%>&rc:Parameters=false">
    </IFRAME>
                            

    Bad Example - Embed report using IFrame

    <rsweb:ReportViewer ID="ReportViewer1" runat="server" SizeToReportContent="True" ProcessingMode="Remote" Width="100%" AsyncRendering="false">
        <ServerReport ReportServerUrl="http://reports.internal.ssw.com.au/reportserver"
        ReportPath="/TimeProOnlineReports/ClientRegisteredProductsByDate" />
    </rsweb:ReportViewer>
                            

    Good Example - Embed report using Report Viewer

    

    Bad example - IFrame with vertical scrollbar

    

    Good example - Report Viewer without vertical scrollbar

53. Do you know how to get email list of report subscription?

    You can get email list in ExtensionSettings, which is an XML column in Subscriptions table in database of reporting services. Subscriptions table has a FK with Catalog table, which contains report name and report path information. Then we can XQuery the ExtensionSettings to get TO and CC fields according to report name and report path.

    See the following example. You need to fill report name and report path parameters(@mReportName and @mReportPath). Then this example will return the email dataset of the report's subscriptions.

    

    Figure: Report parameters

    

    Figure: Transfer parameters to dataset

    DECLARE xmlCursor CURSOR FOR 
    SELECT       ExtensionSettings
    FROM         SubScriptions, [Catalog]
    WHERE        SubScriptions.Report_OID = [Catalog].ItemID AND 
                 [Catalog].Name = @mReportName AND 
                 [Catalog].Path = @mReportPath 
    DECLARE @settingsXML AS XML 
    DECLARE @toEmail AS XML DECLARE @ccEmail AS XML
    DECLARE @comment AS XML 
    CREATE TABLE #subscrpt(toEmail XML, ccEmail XML, Comment XML) 
    OPEN xmlCursor 
    /* Perform the first fetch.*/ 
    FETCH NEXT FROM xmlCursor INTO @settingsXML 
    /* Check @@FETCH_STATUS to see if there are any more rows to fetch.*/ 
    WHILE @@FETCH_STATUS = 0 BEGIN
        SELECT @toEmail = @settingsXML .query('data(/ParameterValues/ParameterValue [Name = "TO"]/Value)')
        SELECT @ccEmail = @settingsXML .query('data(/ParameterValues/ParameterValue [Name = "CC"]/Value)')
        SELECT @comment = @settingsXML .query('data(/ParameterValues/ParameterValue [Name = "Comment"]/Value)')
        INSERT INTO #subscrpt VALUES (@toEmail,@ccEmail,@comment) 
        /* This is executed as long as the previous fetch succeeds.*/ 
        FETCH NEXT FROM xmlCursor INTO @settingsXML 
    END
    SELECT toEmail, ccEmail, Comment FROM #subscrpt 
    DROP TABLE #subscrpt 
    CLOSE xmlCursor 
    DEALLOCATE xmlCursor
                        

    Good Example - Get email list

54. Do you add report owner in your report?

    Report owner is the person who looks after this report. It's a good way to put the owner on the report in order to get any response or suggestion on time.

    There're four things you have to take care of.

    1. Please make sure you use a group (or a team) as the report owner rather than individual names. We do this to reduce maintenance work - whenever a person comes or leaves we do not need to modify the report.
    2. Make the report owner a hyperlink which links to the definition of the group (or the team).

       

       Good Example - Show report owner in the report

    3. When you cannot refer to a group, refer to the person
    4. Use the same idea for web pages, however in that case use the term "Page Owner"

    In CRM, the report can read from the CRM database since the report owner is stored by CRM against the report:

    

    CRM - you already have a report owner so display this in your report

55. Do you use single line box instead of double line box?

    Double line box makes reports look messy and inelegant, so it is better to use single line box.

    

    Bad Example - Double line box makes the report above look messy

    

    Good Example - Single line box makes the report above look clean and elegant

56. Do you change the name of site settings?

    The default site settings name of SSRS is "SQL Server Reporting Services" regardless of version 2005 or 2008. So we'd better add the version to the site settings name then users can find the version of SSRS here.

    

    Bad Example - Site settings without SSRS version

    

    Good Example - Site settings with version of SSRS 2005

    

    Good Example - Site settings with version of SSRS 2008

57. Do you use the correct authentication for your report?

    Using anonymous authentication is not recommended because of security reasons.

    • Anonymous accounts (the IUSER_* and IWAM_* accounts) are managed by windows security system. We do not want to use these accounts because we have to manually configure our report server security settings.
    • We do not want everyone on the Internet update or overwrite stuffs on the report server.

    Besides, anonymous authentication is no longer supported in RS 2008.

    The best way to expose your report is to use ReportViewer and setup the credentials on it using ReportViewerCredentials.

    Dim userName As String = ConfigurationManager.AppSettings("ReportViewer_UserName")
    Dim password As String = ConfigurationManager.AppSettings("ReportViewer_Password")
    Dim domain As String = ConfigurationManager.AppSettings("ReportViewer_UserDomain")
    reportViewer.ServerReport.ReportServerCredentials = New ReportViewerCredential(userName, password, domain)
    
    Dim paramList As List(Of ReportParameter) = New List(Of ReportParameter)
    paramList.Add(New ReportParameter("ClientContactID", mintClientContactID, False))
    reportViewer.ServerReport.SetParameters(paramList)

58. Do you have URL Access link for your report?

    Reporting Service makes buliding, generating, managing and publishing report very easy, however sometime you want to refer to your report by URL from somewhere else. You will find that you can only link to the report default status, but not when you have changed the parameters. It should be as easy as using QueryString in ASP.NET application, but Microsoft didn't do that. See our Better Software Suggestions for Reporting Service.

    A workaround for this is to use URL Access to bulid up a link by yourself then put it at the bottom of the report, so any time you want to refer to your current report status, you can just copy this link and put into your emails.

    At SSW, we have "URL Access" at the bottom of reports for easy reference.

    

    Figure: URL Access link for quick reference at the bottom of report

    http://localhost/ReportServer/Pages/ReportViewer.aspx?%2fSSWTimePRO_CompanyPerformance%2f01+-+BillableTimeSummary&rs:Command=Render

    Bad Example - Only refer to the default report URL, parameters on the report will be lost

    http://localhost/ReportServer/Pages/ReportViewer.aspx?/SSWTimePRO_CompanyPerformance/01%20-%20BillableTimeSummary&rs:Command=Render&rs:ParameterLanguage=en-AU&pStartDate=1/06/2009&pEndDate=7/06/2009&pEmpID=ALZ

    Good Example - Refer to the report with all parameters ready

    Please note "rs:ParameterLanguage=en-AU" in the query string, this is very important espeically when you are passing culture sensitive data (DateTime) over URL. The server may not know your intended culture so you have to specify this together with the actual data in the URL.

    Refer to the following MSDN references for more details:
    • URL Access for Reporting Services : http://msdn.microsoft.com/en-us/library/ms153586.aspx

59. Do you have a clear labelling for including / excluding GST?

    In reporting, total sales / revenue amount can be categorised as two types, which are include GST or exclude GST. It is very important to have clear labelling to avoid the user guessing on whether it includes GST or exclude GST.

    

    Figure: Bad Example - Unclear labelling

    

    Figure: Good Example - Clear labelling

60. Do you have a summary and a detailed version of your report?

    In the vast majority of cases where the readership of the report is wide, some viewers like or need great detail while others just need a quick summary.

    

    Bad Example – Only one version of the report with too many columns for most readers to digest

    

    Good Example – Only the most important columns included for the summary version and the detailed version can be left as in the "Bad Example" above.

61. Analyzing with Excel - Do you use a live data feed?

    

    Figure: Bad example - static data that will need to be re-exported

    

    Figure: Good example - as the data is always live

    This is a great feature as it take advanatages of Excel 2010 PowerPivot together with Reporting Services 2008 R2 to allow end users to subscribe to live data from a report.
    See what the experts said below:

    "And that, by the way, makes it very useful, as it means that PowerPivot models can get data from Essbase and SAP BW (aka Netweaver BI) (via SSRS), which standard Analysis Services cannot do. But it also means that developers can write LINQ queries against reports and that whatever OData clients sprout up can get at that data as well. In general, it means that reports in SQL R2 support a RESTful interface."
    - Andrew Brust

    "Using atomsvc feeds and loving it :)
    We've used this feature in our last 4-5 BI projects and the clients are all applauding this feature. It is a very nice way of surfacing information to users that want to use the information as a starting point for doing more analysis. It does indeed take a lot of the burden off the IT dept that previously had to build custom reports for everything."
    - Trond Brande

62. Do you follow the naming converstion standards in Reporting Service?

    We use SQL Server naming standards in Reporting Service management.

    • Don't use plurals in folder names
    • Add zs prefix to system folders

63. Do you use SharePoint Integration Reporting Mode over Normal(Native) Reporting Mode?

    With the use of SharePoint 2010 Integration mode of Reporting, users can have following advantages:

    • The users Can easily deploy data sources, reports to sharePoint document libraries instead of Report Manager.
    • The users can be much more self-sufficient with SharePoint.
    • Very easy one step configuration of the add-in
    • 37 languages supported including bi-directional languages.
    • Accessing Reporting in local mode when Access Services is enabled.
    • Improved Report Preview experience with Report Builder 3.0 and edit sessions and deploy their reports to SharePoint document libraries, leveraging SharePoint for security.
    • The users can take advantage of the new version of Report Builder that came with SQL Server 2008 R2 and deploy their reports to SharePoint document libraries, leveraging SharePoint for security.

    

    Bad Example – SQL Report Manager (which requires Visual Studio and TFS if you want source control).

    

    Good Example – SharePoint Integration (you get nice source control via SharePoint and you can use the nice Report Builder 3).

64. Do you know how to display reports in Firefox, Chrome and Safari (SQL Reporting Services 2008R2/2012)?

    SQL Reporting Services works great with Internet Explorer but other browsers sometimes don’t work correctly, Here’s the solution.

    

    Figure: Bad Example – SQL RS does not work in Chrome by default

    This issue is caused by Reporting Services emitting Quirks Mode HTML, to fix this make the following changes to the ReportingServices.js file the full path to this file is:

    C:\Program Files\Microsoft SQL Server\MSRS10_50.MSSQLSERVER\Reporting Services\ReportManager\js\ReportingServices.js

    Add the following java script:

    function pageLoad() {    
    var element = document.getElementById("ctl31_ctl10");
    if (element) 
    {
           element.style.overflow = "visible"; 
    } }

    Once this change is made reports will be visible.

    

    Figure: Good Example – SQL RS fixed to correctly display in Chrome

    More information at: stackoverflow.com/questions/5968082/ssrs-2008-r2-reports-are-blank-in-safari-and-chrome

    Read how to display reports properly for Reporting Services 2005/2008 .

65. Do you know how to display reports properly in Firefox / Chrome (Reporting Services 2005/2008)?

    Sometimes users try to view SQL Server Reports in Firefox or Chrome Browser, it does not display at all or displays in a half screen.

    They found it strange when the same report works absolutely fine in Internet Explorer.

    

    Bad Example – SQL Report viewed in Firefox / Chrome (which does not display report properly or display only half the screen.)

    The Solution:

    Add the code below to "%ProgramFiles%\Microsoft SQL Server\MSSQL.3\ReportingServices\ReportManager\Styles\ReportingServevices.css"

                    .DocMapAndReportFrame
                    {
                        min-height: 860px;
                        min-width: 2000px;
                    }
                    

    

    Good Example – SQL Report viewed in Firefox / Chrome (which displays properly in Firefox and Chrome ).

    Read how to display reports properly for Reporting Services 2008R2/2012 .

66. Do you have a report which refresh your data source?

    If you have a SQL database data source with data coming from an external source (i.e. MYOB), then you should create a report which allows user to manually refresh data. Your report should have:

    • A checkbox/radio button which allows user to trigger the refresh.
    • A table display the history of previous refresh including start time, duration and status...

    

    Good Example: A report with a radio button allows you to refresh data and a table showing the history.