Ubiquitous data-A tiered series to the report Service (III)

You search for data in a bunch of EXCEL tables, access reports, and notes that are printed by the manager. you need the description from this data source, the metrics from that data source, and the filtering information from another data source. What should you do with this ubiquitous data?

Take two consecutive deep breaths. This seriesArticleAgain to save you! This article discusses how to connect to different data sources and capture data. If you have not read the first two articles in this series, see:

• First understanding of SSRs-step series to the report Service (1)
• SSRs basics-step series to the report Service (2)

PS: Don't forget to take a deep breath and breathe out.

 

Data, data, data

The report aims to display data. Although the data may come from different data sources, the end user will only see the data that you finally provide. You need two data-related report items to achieve this goal. They are: data source and dataset)

 

Data Source)

The data source contains the location information of the data to be obtained. This information may be a connection string, such as the SQL Server database or Analysis Service database. If it is a Web service, it may also be a URL. The data source provides the data source connection information and security authentication information for the report service.

The Report Server contains two types of data sources:

• A shared data source is an abstract link connecting external data sources. Shared data sources can be used in multiple reports. Therefore, they are uploaded to the Report Server separately.
• The embedded data source is embedded into the report as shown in its name. Therefore, embedded data sources cannot be shared among multiple reports or accessed outside the report.

In many cases, it is common to change the data source information. For example, your development environment is changed from a test environment to a production environment, or the database server name is changed due to the implementation of the new naming rules of the company. Because this type of thing often happens, we recommend that you use shared data sources in all reports. The following describes how to create a shared data source.

 

Create a shared data source

Open Business Intelligence Development Studio (bids). After creating a report service project, you can view the shared data source directory in solution manager. Right-click the Directory and choose add new data source, as shown in Figure 3-1:

Figure 3-1. Share Data sources

Next, the data source wizard appears. We can see the data source name, type, and connection string in this window. This property window is 3-2.

Figure 3-2. Data Source sharing properties Screen

You can name your data source as a report author to identify a name that is not related to a specific report environment. When you create an SQL server data connection. Using the database name directly is a good practice. For Reporting Services 2008 R2, you can connect to the Data source:

• Microsoft SQL Server
• Microsoft SQL azure
• Microsoft SQL Server parallel data warehouse
• OLE DB
• Microsoft SQL Server Analysis Services
• Oracle
• ODBC
• XML
• Report Server Model
• Microsoft Sharepoint list
• SAP NetWeaver bi
• Epochs
• Teradata

Click "edit" to create a connection string. Enter the required information and click OK to view the generated connection string. Click OK to view the created shared data source in solution manager.

 

Classify a shared data source as a report data source

After creating a shared data source that can be shared by all reports, you also need to create a report data source to tell which shared data source the report uses. This step can be implemented in the report data pane of bids, as shown in 3-3. Right-click the data source and choose add data source.

Note: If you do not see the report data window, you can choose View> report data or press the shortcut key CTRL + ALT + D.

Figure 3-3. Add a data source in the report data pane

 

Because you have already created a shared data source, now you can select the shared data source you just created under "use shared data source reference", as shown in 3-4.

Figure 3-4. Data Source Properties window

 

Now we can start capturing data.

 

Dataset

When we know where to capture the database, we also need to tell the report service what data to capture. This step can be achieved through datasets in the report service. Similar to Data Source Creation, you can create two types of datasets:

• Shared datasets are only available in SQL Server 2008 R2 and later versions. query statements are abstracted for use in multiple reports.
• Embedded datasets are stored in a single report, which will be used in this article.

Create an embedded dataset. In the report data pane of bids, right-click the dataset and choose add dataset, as shown in Figure 3-5.

Figure 3-5. Add a dataset in the report data pane

 

After opening the dataset, you can enter the appropriate name, data source, and query statement on the dataset properties screen. Remember that you can have multiple data sources in a report. Therefore, the name must be detailed so that you can know which dataset to use. Of course, do not name the details so that you do not have to rename the dataset when adding additional fields to the dataset in the future. Such names include:SalesbycustomerOrSubunitproperties.

In reporting services 2008 R2, select "use datasets embedded in my reports" to enable you to use the data source you just created. Finally, you need a query statement to capture data. For SQL Server as a data source, you can use this article or stored procedure options. The stored procedure option allows you to call the stored procedure of a database. The Stored Procedure makes it easy to reuse data among multiple reports. For the following options and text, you can write a query statement to capture data. Table 3-1 is a statement based on different data source types.

Data Source Type	Example query statement
Microsoft SQL Server	Select C. accountnumber , Soh. orderdate , Sum (SOH. totaldue) as totaldue From sales. salesorderheader Soh Inner join sales. Customer C on soh. customerid = C. customerid Where soh. orderdate between @ startdate and @ enddate Group by C. accountnumber, soh. orderdate
Microsoft SQL Server Analysis Services	Select non empty {[Measures]. [sales amount], [measures]. [tax AMT]} On Columns , Non empty {([dim customer]. [first name]. [first name]. allmembers * [Dim customer]. [last name]. [last name]. allmembers)} on rows From [adventure works DW]
XML	<Query> <Method Name = "dictionarylist" Namespace = "http://services.aonaware.com/webservices"> </Method> <Elementpath ignorenamespaces = "true"> * </elementpath> </Query>

Table 1. query statements based on different data source types

 

A complete dataset attribute query window is shown in Figure 3-6.

Figure 3-6. dataset attribute query window

 

In the menu pane of the dataset Properties window, you can see four other options: fields, options, filters, and parameters. Click the field option to make sure that your query statement is correct and that the field is available. If a problem occurs in this step, the field option will prompt an error message or no field is displayed. You can set parameters for dataset adjustment, including sorting rules, case-sensitive, accent-sensitive, Kana-type, and halfwidth. The filter option allows you to restrict the rows that are displayed in the report by using the following constraints: =, like, top N, and. Finally, the parameter options allow you to modify the query that is sent to the data source. The query parameters are based on the data source protocol, and the parameter values can be based on the user input data when the report is running. The parameter window is displayed in Figure 3-7. We will discuss user input parameters in subsequent articles.

Figure 3-7. dataset attribute parameters window

 

After clicking OK to create a dataset, we can see it in the report data pane. Click "+" to expand the tree menu to view the available fields, as shown in Figure 3-8. You will use it in the next article about tablix report items.

Figure 3-8. Data sources and datasets in the report data after the final completion

 

What about next?

So far, we have connected to the data source and know the report elements we need. We can also use the shared information created by this report in other reports. For reports required by Big Boss, you also need to know which data is displayed, grouping rows and columns, sorting by certain naming rules, and displaying data in a specific format. In the next article in this seriesTablix GuideLearned.

-----------------------------------------------------------------------

Original article: Data everywhere-Level 3 in the stairway to reporting services

Translated by: careyson