Create reports for SQL Reporting
Updated: May 9, 2014
|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.|
Get started authoring and publishing reports to SQL Reporting report servers.
You can use either Report Builder 3.0 or Report Designer in SQL Server Data Tools to create reports:
Report Builder is considered easier to learn and use, but SQL Server Data Tools has additional capability that you might need if you are managing a large project. For example, you might want to deploy multiple reports and shared data sources in as few steps as possible.
Reports can use embedded or shared data source connection information. Embedded data source information is stored inside the report and is always bound to the report. In contrast, shared data sources are independent objects. As such, binding does not always occur automatically. When using shared data sources, which we recommend you do, create and use them in the following ways:
When authoring reports in SQL Server Data Tools, you can create the shared data sources as part of the project. From the report server project, deploy reports and shared data sources together to bind the shared data source information to the report.
When authoring in Report Builder, we recommend that you first create the shared data source on SQL Reporting, and then choose it as the data source for your report, thus automatically binding the shared data source to the report.
For more information, see Create a shared data source (Azure SQL Reporting) and Configure a Report to use a SQL Reporting Data Source in Report Builder.
Report authors need the following information in order to access data for a report:
SQL database login, user name, and password—A SQL Database administrator must create a login that provides at least read-only access to database views or tables, and run permissions on stored procedures or functions that retrieve data from the database.
Create embedded or shared data sources—A report author specifies the data source connection information used in the report. We recommend creating shared data sources that can be managed on the server. You can create a shared data source in advance. You will need one shared data source per database. You might also need to create additional shared data source for the same database if you want to vary the credential type. For example, for some datasets, you might want prompted credentials, while stored credentials might be adequate for other datasets.
Queries—A SQL Database administrator might provide queries that return the data you need in a report. Tell the administrator which fields you want to work with, and ask that he or she avoids aggregating the data in the query in a way that limits your ability to organize that data in a report. For example, you do not get accurate results if you add a set of values in a report that are already averaged in a query. When writing a query, find the balance between returning just the data that is needed in a report and returning the data of interest that can then be grouped and sorted in the report as needed.
You might request a set of queries that implement the cascading parameters feature, where a report reader can select a series of parameters that provide successive filtering. For more information, see the lesson Adding Cascading Parameters in the parameter tutorial in Tutorials (SSRS).
Internally, database credentials for accessing data sources are saved separately from the report definition on the report server. The data source definition includes the type of credentials to use but does not include passwords. Passwords are stored securely on the report server, independently from the report definition. Using shared data sources helps to manage the credentials in one location.
Alternatively, a report author can set data source credentials to Prompt. After report readers log in to the SQL Reporting portal, they must then type a SQL Database user name and password for each data source when they run the report.
Choose SQL Server Data Tools if you want to deploy or manage multiple reports and shared data sources in a single operation. SQL Server Data Tools lets you create multiple reports and data sources under a single solution. You can deploy the solution to bulk upload multiple reports and data sources at one time.
If project requirements include rapid deployment, or repeatable deployment (for example, you plan to create and retire service instances on a frequent basis), using a solution to organize your reports gives you a way to publish a lot of reports quickly. On the Azure platform, it’s common for businesses to create services and databases that are used on a temporary basis. If report hosting needs are fluid, you can build and tear down a reporting service quickly, once it’s no longer needed.
Remember that in Azure SQL Reporting, you cannot download, save, or upload the report server database that contains all of your reports, items, accounts, and permissions. Your best bet for bulk management of multiple reports is to use a solution and SQL Server Data Tools.
If you are familiar with creating Reporting Services reports, you will notice report design feature parity between the reports you create for Reporting Services and those you create for SQL Reporting. To understand differences, review the following topics before you start.
A high level view of designing a report includes the following steps:
Create data sources with connection strings to your Azure SQL Database databases. You must work with the database administrator of each database to get credentials and access from BI Development Studio on your client computer.
In the query designer that is associated with the data source, write a dataset query to specify which data to retrieve. You can also develop the query in a tool other than BI Development Studio and paste the query into the query designer. You create a dataset for each query result set that you want to use in a report.
For each dataset, add a data region such as a table or chart to display the data.
Preview the report. You must provide credentials for each data source in the report.
Publish the report to your SQL Reporting report server.
View the report in a Microsoft Reports application that you write that includes a Report Viewer control that points to the published report. Alternatively, you can log in to your SQL Reporting subscription, and view the report in a browser.
Books Online in MSDN library provides articles about all aspects of report authoring, including data sources and datasets, report layout, tables and matrices, and data visualization. For more information, see Designing Reports in Report Designer and Report Builder 3.0 (SSRS).
If you choose Report Builder 3.0, you can upload reports using the management portal.
For links to resources about learning to author reports, see SQL Reporting Overview.
For sample reports and embedded reporting samples, see SQL Reporting Samples.