Data source specifies a database connection.
Can be either shared or embedded. Best practise is to use shared data source.
Shared Data sources means that every report can use it. You can use this connection and do not have to create a new one for every single report.
On the other hand, If you don't create a shared data source, you can create for every new report an embedded connection. It is a connection that only works with the given report. Every new report will need its own connection. Problem is, if you deploy a project like this to services you will have to eventually change every single embedded connection ,If for example database name will change.
Data sets stand for SQL Statement or Stored procedure. Same as Data source can be either shared or embedded. Best practise is to use shared data set.
After you have established database connection [Data source] you will have to create Data set where you can specify the table name and columns you want to retrieve for you report.
Exercise 2: How to create a Shared Data Sources and Shared Dataset [Good practice]
Example 1:
Embedded connection. This is not good practice. Everything I will create is going to have to same suffix: embedded.
Step1:
I have skipped shared Data Source and datasets. Fist I create a report and for this report its own database connection and query. Any other report won't be able to share it.
Step 2:
Right click on Data Source to create an database connection
Give it a name so later on you will be able to recognize it.
Type. I use SQL Server
Build. I am going to connect to database Northwind.
Step 3:
General tab
use: SELECT @@SERVERNAME to get server name or use local or localhost.
Specify a database name you need to connect to and test connection.
Step 4:
Credentials
I use Windows Authentication.
Click OK to close this window.
Step 5:
To get the table and columns you need for the report>
1. right click on the Dataset > Add Dataset
2. Give dataset a name.
3. I haven't created a shared dataset, so now I have to create an embedded one just for this report
4. I use embedded data source, database connection I have created in the previous step.
5. Write the query or use a stored procedure, view...
If any error occurs in this step is most likely connected to Credentials. Go back to the previously created data source and check the credentials.
If everything worked good you should be able to see the columns.
Step 6:
Add new Report
The main problem using this approach is, if you decide to add more reports for this project, you will have to build over and over again an embedded database connection and embedded dataset for table and columns. If you deploy the project and any changes will be needed it has to be done for every single connection. That is why this approach is not the best practice.
Step 7:
Solution: Convert them to shared data source and shared dataset by right click on the
Data source > Convert to Shared Data Source
Embedded connection. This is not good practice. Everything I will create is going to have the same
Exercise 2:
How to create a Shared Data Sources and Shared Dataset
Step 1:
Add new report
SSRS servers to build the reports from tables, views or stored procedure. Main points is to have reusable data sources and datasets which can be used by the reports.
Step 2:
Create a Shared Data Source
2.1 Add New Data Source
2.2 Specify a meaningful name
2.3 Click Build
Specify server name, database, test connection.
Step 3:
Credentials
On the credentials tab choose Windows Authentication (Integrated security)
Step 4:
Add New Dataset
Give the dataset a name and specify the columns you want to retrieve for the report.
Step 5:
On the Report Data pane, right click on the Data Source >
1. Add Data Source: you are going to create a Reference, pointer to the Shared Data Source. You are not creating a new one, just referencing the already created shared one.
2. Name it.
3. Use shared data source reference.
Credentials should be greyed out in this step, If you want to change them go to the original Shared Data Source.
Step 6:
Add Reference to Shared Dataset by right clicking on Dataset > Add new dataset
Check: Use a shared dataset
Click on the dataset you want to use [can be more in that window]
Click OK to close it.
Step 7:
Check the fields
