SSRS – How to Create a Report With a Shared Data Source
Creating a report with a shared data source
You now have a Shared Data Source in your project, but no dataset or report to display any information. Remember that a dataset will usually be specific to a report, so it’s easiest to create the dataset right after you create the report.
Reports are created inside a project, which is in turn inside a solution. If you only have one project, the solution might not be shown in the Solution Explorer. If you want to always see the solution, in SSDT go to the menu bar and choose Tools > Options. In the window that opens, find Projects and Solutions > General, and select Always show solution.
When you create reports inside your project, SSDT offers the Report Wizard which can automate some of the steps, and also the manual way of creating a report. In this tutorial, we will not be using the Report Wizard, so reports will be created by right-clicking on the Reports folder and choosing Add > New Item. If you accidentally choose Add New Report and open the Report Wizard, just click Cancel to close it.
When a report is created or opened, a Report Data panel opens on the left side of the screen. This panel is concerned with the currently open report, whereas if you make changes in the Solution Explorer, you could affect all the reports in your project. One of the first things you will do in the Report Data panel is to set up a reference to the project data source. Then you will create a dataset with a query that is specific to the current report.
THE REPORT DATA PANEL
1. In the Solution Explorer, right-click on Reports and choose Add > New Item, (Avoid using Add New Report for now, as this will launch the wizard instead of letting you specify details yourself).
2. In the Add New Item dialog, choose Report (not Report Wizard) and call it Summary Sales.rdl, then click Add.
3. SSDT will now display an empty report, and the Report Data panel should have appeared on the left side of the screen. If this panel is not visible, use CTRL-ALT-D to display it.
4. In the Report Data panel, right-click on Data Sources and choose Add Data Source…
5. Name the data source AdventureWorks, and change the selection from Embedded connection to Use shared data source reference.
6. Select AdventureWorks from the drop down list. This is the project data source you configured previously.
7. Click OK and note that the new AdventureWorks data source has appeared in the Report Data panel. Note also that it has an icon containing an arrow. This shows that it is a reference to the project data source.