Export (0) Print
Expand All

Reporting concepts for new users (Azure SQL Reporting)

Updated: May 9, 2014

ImportantImportant
SQL Reporting will discontinue service on October 31, 2014. See this FAQ for details. For reporting on Microsoft Azure, visit Virtual Machines on WindowsAzure.com.

If you are new to SQL Reporting on Azure, review the following highly summarized description of reporting and report features.

What is a Report?

SQL Reporting reports are based on SQL Server Reporting Services (SSRS) technology. Reports are Report Definition Language (.rdl) files in XML format. Each report definition contains elements and attributes that represent data sources, datasets that include a query command, report parameters that correspond to query variables, and data regions that define the layout of the report. Think of a dataset as representing a query result set. Think of a data region as a programmable control that displays data from a dataset. Primary data regions include a customizable grid layout called a tablix (can be a table, matrix, or free-form layout), and various types of charts, gauges, and maps. There is a 1:N correspondence between dataset and data region: one dataset can be displayed on multiple data regions, for example, the same data displayed as a table and as a chart.

What is a Reporting Service?

To share reports with others, reports are published to a reporting service on Azure that also provides Web service access. When a report is viewed, the report definition is processed and rendered. The processing engine runs the dataset queries against the data sources, evaluates expressions, combines data with the layout elements, and then passes the combined information to a rendering extension. The rendering extension renders the report.

How Are Reports Viewed?

Reports are viewed in an application in a ReportViewer control or in a browser rendered in HTML. The report toolbar provides a way to export reports in a different rendering format. The current release of SQL Reporting supports the following rendering extensions: XML, CSV, TIFF, PDF, MHTML, EXCEL and, WORD. Each rendering extension determines how much information fits on a page. For more information about these rendering extensions, see Exporting Reports (http://go.microsoft.com/fwlink/?LinkId=250935).

Back to top

About Published Reports

When a report is published to a reporting service, some elements in the report definition are identified, stored separately, and can be managed independently from the report. The following list describes these report server items:

  • Data sources—Published data sources can be changed to point to a different database.

  • Shared Datasets—Published shared datasets are a way to provide one query that can be used by multiple reports.

  • Report parts—Published report parts are a way to create effective data visualizations that can be shared by multiple reports.

  • Parameters—Reports can have multiple sets of parameters that apply to the same report definition. These are known as linked reports.

  • Images—Reports can include embedded images up to 1 MB in size.

In Windows Azure SQL Reporting, some report server items can be managed directly and some cannot. This does not mean you cannot include these items in reports deployed to SQL Reporting; it just means that you cannot manage them in the portal. The following table lists report server items and their management and use.

 

Report Server Item Manage From Portal Supported in SQL Reporting

Data sources

Yes

Yes

Shared datasets

No

Yes

Report parts

No

Yes

Parameters

No

Yes

Images

No

Yes*

* Only embedded images and images stored in databases are supported in Windows Azure SQL Reporting. You cannot use externally stored images. In Windows Azure SQL Reporting, the maximum size of externally stored images is 1 MB.

noteNote
Report parts can be added to a report only in the tool Report Builder 3.0. If you created a report in Report Builder 3.0, you can either add it to a report server project in SQL Server Data Tools and deploy it to a SQL Reporting instance from there, or you can upload the report directly to the SQL Reporting server. For more information, see Deploy a Report Project (Azure SQL Reporting).

Back to top

About Report Data

From a data perspective, a report definition includes the following elements:

  • Data source—Includes the connection string and credentials needed to connect to a Microsoft Azure SQL Database. A Microsoft Azure SQL Database must be specified in the connection string. Credentials are specified separately from the connection string. Only Microsoft Azure SQL Database login credentials are supported.

  • Dataset, which includes a query command—Includes a pointer to the data source and a query that is a SELECT statement or a call to a stored procedure or a function. Think of a report dataset as representing the first result set returned by the query when the report is processed. Multiple result sets from a single query are not supported. There is a Lookup function that can provide limited functionality for looking up values in related datasets, but there is no ability to join data from multiple datasets. Joining data from multiple objects in a database must occur in the query. SQL Server 2008 R2 Reporting Services provides a query designer specifically for queries that use the Azure SQL Database connection type.

  • Parameters—Includes a report parameter that corresponds to each query variable or input parameter to a stored procedure or function, identified when the query is processed. Report parameters enable a report reader to choose values that help limit the data that is retrieved from the data source.

Back to top

Shared Data Sources and Datasets

To help performance and manageability, shared data sources and shared datasets can be defined on the report server and referenced by multiple reports.

Advantages of a shared data source:

  • Define once, refer to from multiple reports.

  • Update credentials in one place.

  • Update connection string to change database. For example, change from test to production database.

  • Disable to control access to the database for all reports that use it.

Advantages of a shared dataset, which are only available for shared data sources:

  • Provide optimized queries against shared data sources.

  • Provide queries that return most of the interesting data for report authors. For example, write one optimized query that returns most of the fields of interest to ad hoc report authors.

    Help control access to database by providing queries that return the information that most report authors want from a specific database.

You can update permissions and properties of shared data sources deployed to SQL Reporting report servers in the Edit Data Source dialog box available in the management portal. You can also create new shared data sources. For more information, see Create a shared data source (Azure SQL Reporting).

Back to top

Community Additions

ADD
Show:
© 2014 Microsoft