-
Do you know the 4 user experiences of Reporting Services: Vanilla, Website, Email, Windows?
The 4 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 Windows user experience
-
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 set up.
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
-
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".
- Figure: Reporting Services is not running in a healthy state
- Figure: Reporting Services is configured correctly
Note: "Report Server Configuration Manager" is only available in SQL Server 2005.
-
Do you check out the built-in samples?
SQL Server 2005 Reporting Services comes with some great samples that will help get you started. Unfortunately, they aren't installed by default.
These samples include:
- Database called Adventure Works
- Report Project based on the Adventure Works database
- Report Builder Model based on the Adventure Works database
Upon installing the samples successfully, you should see 3 folders (shown below) in your Report Manager.
- Figure: A proper installation of the samples
For more information, see our knowledge base article: How do I install SQL Server 2005 with the Adventure Works Samples?
AdventureWorks Reports Sample
-
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?
- 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.
- Figure: If you want to avoid migrating then SSW Access Reporter delivers your Access reports online so you can view them anywhere
- 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.
- Figure: Reporting Services has built-in support for importing your Access reports
-
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.
-
Change the report's page width to 28cm (or 11in) and left and right margins to 1cm
- Figure: For proper printing, first change the Report's Page Width to 28cm (or 11in) and left and right margins to 1cm
-
Change the Body width to 25.4cm (or 10in)
- Figure: Then change the Report's Body Width to 25.4cm (or 10in)
- Figure: PDF page with 1cm margin (you have more room for content)
You can see the 1cm margin looks much better than 2.5cm, and you have more space to organize your content.
- 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.
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. -
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:
- 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)
- Execution Time (e.g. Execution time: 1 minute, 10 seconds)
- Page x of y (e.g. Page 3 of 10)
- Link to company website + slogan (e.g. www.ssw.com.au - Writing software people understand)
- Figure: This footer doesn't provide any useful information
- Figure: 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 " + DateTime.Now.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
- Figure: 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 subscriptios will fail with the following error:
'The '/GroupHealth' report has user profile dependencies and cannot be run unattended. (rsHasUserProfileDependencies)'.
-
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.
- Figure: Bad Example - redundant use of the word 'report' in a report
- Figure: Good Example - avoidance of the word 'report' in a report
-
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.
- Figure: No underline for hyperlink item
- Figure: 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.
- Figure: Underline for hyperlink item
- Figure: Underline for textbox with hyperlink action
We have a program called SSW Code Auditor to check for this rule. -
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?
- Figure: Using the default NoRows property of the table control - error message is not clear enough
- Figure: 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:
- Add a rectangle to the top of your report, above any report items in the body. Set its BorderColor to Red
- 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.
- Add an Image control next to it. Use this error icon as the Image (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
- 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
- Group all other report items into a rectangle - you want to be able to show and hide them dynamically
- 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)
-
Layout - Do you have consistent report name?
A professional report should have consistent name.
- Figure: Inconsistent report name
- Figure: Consistent report name
-
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.
- Figure: Include feedback information
-
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.
- Figure: 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:
- Create a new dataset in your report
- 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 TotalFROM MyTableWHERE 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 ENDGROUP 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) - Configure the new added parameter 'pEndDate'
- Figure: Change Data Type to DateTime and assign to the proper default values
- Add a chart to your report in Layout view and change its type to "Simple Column"
- 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
- Now you need to set the last column to be a different color so it stands out. Right-click the chart and click Properties.
- 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.
- 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")
-
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.
- Figure: Bad Example - there is no chart to visually represent the data
- Figure: Bad Example - there is a chart, however it is not clear
- Figure: Good Example - Data and chart are clearly integrated into one
-
Data Layout - Do you avoid using a single chart when you need it to be scaled? Data Layout - Do you use expressions to show the correct scale on charts?
- Figure: Bad Example - Just a chart - poor scaling for only 1 record
- Figure: 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
- Figure: 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
-
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.
- Figure: With no scale value set, the charts do not display based on the correct scale
Here's how to set the scale.
- In Layout view, add a new row to the bottom of the table
- 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
- Set the textbox's Name property to MaxMyTotal (e.g. MaxCount)
- Set the new row's Visibility/Hidden property to true - you don't want to show it in the report
- Open the Chart properties and select the "Y Axis" tab
- 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
- 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).
- 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 - Click OK and preview the report. The chart will now be using the maximum value across all the charts.
- Figure: 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 customise it via an expression. See our suggestion about this on Microsoft SQL Reporting Services Suggestions.
-
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.
- Figure: Bad example - does not show the change between the previous and current data
- Figure: 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
-
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 Sample report 
- 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.
- Figure: The percentage change column in this Reporting Services product downloads report is misleading
Good Sample report - This works better just showing the difference between the two values over the 2 months.
- Figure: The column works better as just a difference between the current and previous download totals
-
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")
- Figure: Alternating row colors greatly improve the readability of reports, and is very easy to do in Reporting Services
-
Data Layout - Do you have nodes count like Outlook?
A report with drill through like this should have the number in nodes like Outlook.
- Figure: This does not have the nodes count
- Figure: 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 - Figure: Get the Outlook Node Count look by changing the SQL.
- --Expression to show nodes count
= Fields!Name.Value + "(" + CStr(CountRows()) + ")" - Figure: 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.
-
Data Layout - Do you avoid displaying decimal places?
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.
- Figure: This does display decimal place
- Figure: 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
= 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.
-
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.
- Figure: Bad Height
- Figure: Good Height
- Figure: Bad Height In Design View
- Figure: Good Height In Design View
We have a program called SSW Code Auditor to check for this rule. -
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 '#,#'.
- Figure: Bad Zero Number Format
- Figure: Good Zero Number Format
- Figure: Format Code
-
Data Layout - Do you know the best way to show your data?
- Figure: Plain table
-
- Figure: Plain table with separate chart
- Figure: Table with bar chart embedded
-
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.
- Figure: bad time format
-
- Figure: clear time format
A related rule about time format is Do you keep Time formats consistent across your application?
-
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).
- Figure: not displaying all report parameters
-
- Figure: displaying all report parameters
-
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
-
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.
- Figure: inconsistent chart height
Putting a rectangle in table cell and placing your chart in the rectangle fixes this problem. 
- Figure: consistent chart height
-
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:
- 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.
- 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.
- 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).
- Figure: Avoid using expressions for calculated values
- Figure: Avoid using external assemblies for calculated values - it adds an unnecessary level of complexity
- Figure: Use a denormalised database field for calculated values
-
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.
- Figure: There are no Default Values set for the Parameter
- Figure: Default Parameters can be specified manually (non-queried) or from a query
-
Parameters - Do you avoid showing empty reports by the most intelligent default?
When user opens a report, they expect to see something. It is the developers job to get the default values for parameters right. And of course you don't get it right by hard coding defaults.
The following report shows nothing because the parameters are using incorrect default values:
- Figure: Empty report caused by incorrect parameter default values
- Figure: Report shows initial data by giving correct parameter default values for the entire month of October (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)
- Figure: Expressions to set the date range to the current month
- Figure: Use Expression to set default values. (this may not be good enough if there is no data in the current month)
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.
- --Query to generate valid date from existing data
SELECT
CONVERT(
DATETIME,
'1, '+
DATENAME(month, DATEADD(month, 1, MAX(OrderDate)))+
DATENAME(year, DATEADD(day, 1, MAX(OrderDate)))
)
AS EndOfMonth,
CONVERT(
DATETIME,
'1,'+
DATENAME(month, MAX(OrderDate))+
DATENAME(year, MAX(OrderDate))
)
AS StartOfMonth
FROM
Orders
- Figure: Query to retrieve the last month of available data from database
- Figure: Use query to set default values for report parameters
The dataset 'ValidDates' looks similar to this:
- Figure: Create a dataset to query default parameter values for your report
-
Parameters - Do you use the DateTime data type for date parameters?
Use DateTime data type for date parameter. E.g. a string data type parameter with value "26/01/2006" is correct for "dd/mm/yyyy", but it is wrong for "mm/dd/yyyy". It is no trouble for DateTime data type parameter to get the correct datetime value for any Culture DateTime format. You 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).
- Figure: Using String data type for date parameter
- Figure: Use DateTime data type for date parameter
We have a program called SSW Code Auditor to check for this rule. -
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.
- Figure: Inconsistent parameter names
- Figure: 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.
-
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
-
Performance - Do you schedule snapshots of slow reports for quicker access?http://www.ssw.com.au/ssw/KB/KB.aspx?KBID=Q1119699
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:
- Figure: Create a scheduled snapshot of slow reports to improve performance
-
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.
- Figure: Bad Number Format
- Figure: Good Number Format
- Figure: Bad Currency Format
- Figure: Good Currency Format
- Figure: Bad Percentage Format
- Figure: Good Percentage Format
-
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 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. In our experience, you'd better specify this property on the whole Report. Because, this can make Report more steady and friendly (all the culture sensitive controls on the Report will get managed). Then, you will have no worry about culture issue.
- Figure: Here the 'Language' setting is set to a specific culture
- Figure: Here the 'Language' setting is set to '=User!Language' to detect user culture automatically
- Figure: Now the data respects the English (Australia) Language Preference of IE
- Figure: Likewise the data also respects the IE Chinese (China) Language Preference
-
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
-
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:
- 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 shouldn't change as per user's culture setting as $100 AUD <> 100 CNY !
- Have the currency column header set include the currency.
Because $100 USD <> $100 AUD !
- Figure: using default language for currency field
- Figure: This currency field stores Australian Dollars and will always display it that way
- Figure: AUD currency
- Figure: 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.
- Figure: using specified language as per value of column CurrencyType in table SystemValue
-
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).
-
Admin - Do you create a separate virtual directory for Admin access?
When going public with Reporting Services, you should have 2 ports:
- A public access port. This allows your public users to access their reports normally on a port which has been configured for anonymous users.
- 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:
- Create a Windows User account for the anonymous reporting services site to run as. e.g. IUSR_ReportViewer
- Open up the ReportingServices directory (C:/Program Files/Microsoft SQL Server/MSSQL.3/Reporting Services)
- Duplicate the ReportServer and the ReportManager folders then rename prefix them with _External e.g. ReportServer_External and ReportManager_External
- Set the file access security on the new folders so that "Everyone" has full permissions.
- Edit the ReportServer_External/rsreportserver.config file. Update the URL node <UrlRoot>http://xxxx:81/ReportServer</URlRoot>
- the ReportManager_External/RSWebApplication.config file. Update the URL node <ReportServerUrl>http://xxxx:81/ReportServer</ReportServerUrl>
In IIS Manager:
- Create another website on another port (i.e. port 81)
- Create Virtual Direcoties for ReportServer and Reports then point them to the new folders we just made. Make sure they are setup as applications.
- 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.
- 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.
- 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:
- Add the user for these folders to have access in SQL
In Reporting Services:
- Go to http://localhost/reports
- Click Properties -> New Role Assignment
- 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:
- Open up the ReportingServices directory (typically c:/Program Files/Microsoft SQL Server/MSSQL/Reporting Services/)
- Make a copy of the ReportManager folder and call it ReportManagerPublicAccess
- Duplicate the file access security settings on ReportManager in ReportManagerPublicAccess
- Edit the RSWebApplication.config in the ReportManagerPublicAccessfolder to point to http://server:81/ReportServer
In IIS Manager:
- Configure the default website's ReportsServer virtual directory to give access to IUSR_ServerName (for public access)
- Export the Report and ReportServer virtual directory to an XML file
- Create another website on another port (i.e. port 81)
- Add the Report and ReportServer virtual directories using the XML files created in step 2
- Set the Reports virtual directory to point to the ReportsManagerSecure directory instead of just ReportsManager
- Set the directory security on the ReportServer on port 81 to use windows integrated security
-
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.
- Figure: Everyone can see others' rate changing history (maybe useful for administrative, but not for your employees)
- Figure: 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.
-
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.
- Figure: Keep @ExecutionTime in subject
So we always make subject of subscription exactly same as report name.
- Figure: Subject same as report name
-
Do you know to embed an RS report in ASP.NET page the right way (using Report Viewer instead of IFrame)?
The report viewer control was introduced in Visual Studio 2005, 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>
Figure: 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>
Figure: Good example - Embed report using Report Viewer
- Figure: Bad example - IFrame with vertical scrollbar
- Figure: Good example - Report Viewer without vertical scrollbar
-
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 ExtensionSettingsFROM 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 XMLDECLARE @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 ENDSELECT toEmail, ccEmail, Comment FROM #subscrpt DROP TABLE #subscrpt CLOSE xmlCursor DEALLOCATE xmlCursor - Figure: Get email list