Report Designer allows you to develop multiple reports in a single project. A shared data source can be created to store data source information independantly of each report in the project

Use BIDS to create a Report Designer project. A BIDS solution can contain one or more projects from the types supported by BIDS. With a blank RS project, you can add reports by right-clicking the Reports node in Solution Explorer and selecting 'Add New Item' and then selecting 'Add New Report'. This starts the 'Report Project Wizard'. Reports are saved as RDL files.

Add a shared data source via the Report Data window: click 'New, Data Source. Shared Data Sources are saved as RDS files in the solutions 'Shared Data Sources' folder. RDS files are in XML format.

When defining 'Data Source Properties' for a report, the 'Use single transaction when processing the queries' option forces the queries to run in series rather than in parallel.

Add a Dataset to the report via the Report Data window. Click 'New, Dataset'. Specify 'Text' as query type to specify your own query. If you select 'Stored Procedure' as query type, a list of available SPs is displayed. If using an OLEDB or ODBC connection, you can select 'Table' as the query type.

Text for a query can be constructed dynamically using expressions. Text for the query can also be imported from a file in .sql format.

The query designer allows you to specify Parameters, Fields and Filters.

Data Regions are used to display data in a report. Data Regions can be one of: tables, matrix, list or chart. Each data region is bound to a single dataset. Groups and subtotals can be added via the context menus of the text boxes in the data region. When adding additional groups, choose whether the group will be a parent or child of the existing group.

A matrix creates a data region with a dynamic number of columns and rows, resembling a crosstab or pivot table. Groups and subtotals can be applied either to columns or rows.

Adjacent Groups allow you to apply the same groups over different data columns, e.g. display a matrix grouped by territory with data for years and categories.

In addition to data, you can add text boxes, lines and images to a report.

Expressions

Each data field on a report is a simple expression. More complex expressions can be added built-up from built-in collections: dataset fields, report items, parameters and variables. To access a Collection item use the following syntax:

 Collection!ObjectName.Property

Calculated fields can be added to the dataset by right-clicking the dataset in the Report Data pane and seleting 'Add Calculated Field'. The calculated field can be given a name in Properties dialogue box and the expression dialogue can be used to build the expression from the built-in Collections, Operators and Functions. The calculated field can then be used like any other field in the report.

For expressions that are used only once, a text box expression is most suitable.

The Report Items collection allows you to refer to the value of another expression.

Built-in fields can be accessed via the Globals collection or an "&" prefix for simple expressions

The scope of an aggregate function is inferred from the location of the expression in the report or by explicit specification of the dataset, data region or group.

Expressions can be used to sort data, although better performance is achieved by using an 'ORDER BY' clause in the query. Expressions can be used to apply conditional formatting using 'Choose', 'If' or 'Switch'.

Expressions are evaluated when viewing reports as you move from one page to the next. Variables can be scoped to a group or to the whole report. Group variables are evaluated when the group changes: report variables are calculated once at runtime. Dynamic connection strings can be created with expressions, but only for embedded connections. Parameter items can be used to replace any argument value in a connection string. Parameters can also be used to construct dynamic queries. Dynamic queries should only be added to a report once the design is finished: otherwise the field list will not update, and you cannot therefore add fields to a data region.

Reports can be configured to allow interactive sorting for text boxes: normally a column or row header in a data region. Right-click the text box and select 'Properties, Interactive Sort. The sort can be by field name or expression.

Fixed or pinned column headers are configured via the Grouping Pane: click arrow to the right of 'Column Groups', select 'Advanced Mode', click 'Static Group' in Row Groups section and set 'FixedData' to True. With Fixed Data property On, set the BackGround Colour to a non-transparent value

To configure drill-down for details rows, right-click details row and select 'Row Visibility'. Set 'Display' to 'Hide' and 'Display can be toggled...' to 'True'.

Parameters can be added to reports allowing the report to be filtered by user interaction.

To set a parameter to today, set the value as '=Today()'. Or for the last day of previous month use '=DateSerial(Year(Now()), Month(Now()), 0)'

The list of available values for a parameter can be specified from a query, or better still, by a view in the datasource.

Set the 'NoRowsMessage' text box to display a message when a data region returns no data

Cascading parameters involve setting the values list for a report parameter based upon the value selected for another parameter.

You can use the Join function to display selected parameters values in a footer:

 "Filters: + Join(Parameters!SalesTerritoryGroup.Value, ", ")

Document maps can be added via the 'Row Groups, Group Properties' dialogue. Select 'Advanced' and set Document Map to the appropriate Group

Each Text Box and Image has an 'Action' property to define a target location to display when the object is clicked. A 'Go To Report' action can be used to open another report passing values from the source to parameters in the target. A 'Go To Bookmark' can be configured to jump to a particular location in the report or a 'Go To URL' can take the user to specified internet location.

Charts

The Value Axis (y-axis) shows numerical values in the chart. The Category Axis (x-axis) shows grouping of numerical values. Series represents data in groups - each grouping is a series.

Types of charts:

Column Chart

Displays data in vertical bars. Used to compare values in multiple series

Line Chart

Used to display data over a period of time

Bar Chart

Displays data as horizontal bars. Useful when the categories are too long to read on the x-axis

Area Chart

Displays each series as a connected line and fills in the area to the axis

Stacked Area

stacks the areas to show how each contribute to the total. Should be avoided if data contains empty points

Scatter Chart

Displays numerical data as a series of points. Good for comparing thousands of data points across categories. Avoid using series or groupings.

Pie or Doughnut Chart

Shows data as proportion of total. Null, negative or zero values are ignored

Funnel or Pyramid Chart

Shows data in proportions, but ordered by size

Polar Chart

Displays each series as a set of points grouped by category in a 360 degree circle. Higher values are further from the centre. Used for grouping angle or distance values

Radar Chart

Shows data as a circular line or filled area. Used for comparing multiple categories of data

Range Chart

Used to plot Min/Max values or Gantt Charts

Stock Chart

Plots up to four values for each data point

Add a chart to a report by dragging the chart control to the report design surface. Fields are then added to the data, category and series drop zones. Numeric values are auto-aggregated with the SUM function. Non-numeric values are aggregated with COUNT.

Scale Break is used to cut out a section of the scale if the range between high and low values is very large

Add a secondary axis to display values using different measurement units, eg Sales Value and Order Quantity: use the Series Properties dialogue, Axes and Chart Area. The secondary series can be displayed as a different chart type in the same area or the series can be displayed as a separate chart using the 'Add New Chart Area' context menu from the original chart

Gauges

Use gauges to visualise KPIs. Gauges can be placed inside tables, matrices, or lists. Create a gauge using the Gauge Control from the toolbox. Gauge types can be either linear or radial.

Report Deployment

Deploying reports involves:

1. Placing report definition files on the server
2. Configuring data sources for test and production
3. Configuring defaults for report parameters
4. Configuring execution options
5. Configuring history management
6. Setting snapshot options
7. Setting security options

Reports can be deployed using BIDS, Report Manager or by using the 'RS.exe' command-line utility.

Before deployment set the following properties:

1. OverwriteDataSources - True or False
2. TargetDataSource Folder - Data Sources
3. TargetReport Folder - Reports
4. TargetServer URL - http://localhost/ReportServer

Then right-click the Report Project and select the 'Deploy' command. Alternatively, just select the reports to deploy before executing the 'Deploy' command. Deployment output displays in the output window.

Once deployment completes, the reports should be visible at http://servername/Reports/foldername.

All files are stored as binary files in the Catalog table in the RS database.

The Report Sever website ('Report Manager') allows direct upload of '.rdl' files via the 'Upload File' link. The file will then need to be associated with a data source.

To deploy '.rss' files created with VB.Net, use the 'RS.exe' utility:

 rs -i ReportProjectname.rss -s http://localhost/ReportServer 

Use Folders in Report Manager to organise content and provide security contexts. Reports can be moved or deleted by first selecting the 'Show Details' link and then selecting the reports to move or delete.

A linked report uses a base report definition but has different execution, parameter, subscription and security properties. Typically, the base report will contain all report data and the linked report will have a defined parameter to filter the report content. Create a linked report by selecting the 'Edit' link on the base report, and then select 'Create Linked Report'. Changes to the base report will reflect in the linked report.

To configure report parameters, open the report, click the 'Properties' tab and then click the Parameters link. Default values and prompt text can be configured. The option to prompt user for a value can be toggled on/off.

On a native-mode server, enable 'My Reports' to give each user a personal workspace. Users with appropriate permissions can also create their own linked reports. Enable 'My Reports' via SQL Management Server: connect to Reporting Services, right-click the server in Object Explorer and then select 'Enable a My Reports Folder for Each User'.