Monday, March 28, 2016

Dataset Setup in SSRS 2016

For SSRS 2016, Report builder is the primary tool to create a new dataset. If you have it installed, open it as you would any application. If not you can start it up directly from the SSRS browser menu. From the menu bar, select  '+ New' and then Dataset.

Starting SSRS Report Builder


From the SSRS browser portal:



Or, download it, starting with the download feature in SSRS:



When Report Builder first starts, it brings up a popup. If Report Builder was started from the SSRS menu item labeled 'Dataset' the popup's title is 'New Report or Dataset'.  Normally, you'll download and install Report Builder. Starting Report Builder as an installed application, the popup is labeled 'Getting Started'. Either way, once you get to this screen, select a data source. If this is the first time you have used a data source, find the 'Browser other data sources...' selection at the bottom to locate your new data source.

If you need to create a data source, see Data Source setup in SSRS 2016.

Getting Started:  Use the 'Browse other data sources...' option.



And then select your new data source:


With the data source selected, create a new Dataset. Click on the Create button at the bottom.


This opens up the Dataset builder.  On the left are all of your available tables, views and stored procedures.

Using the Dataset builder in Report Builder, add your tables, views and columns.


Test your query using the Run Query command at the top of the form:



You can also switch to T-SQL mode, using the Edit as Text command at the top:
(HINT: click on the Edit as Text button a second time to switch back to the standard mode.)


Saving the Dataset

Once you have the query setup and tested, save the Dataset as a file. Using the File tab, ...


 select Save (or Save as) and give it an appropriate name.



Once saved, adjust the view settings for your SSRS browser, and your new Dataset is displayed.





2 comments:

Karen McMahon said...

I have recently installed the released SQL Server 2016 and trying to create a shared dataset in report builder. I would like to use stored procs that have params in them. I set the default values for the params, but the fields are not appearing in the design window. Can you please help?

Andrew Peterson said...

The easiest is to first create the dataset using the SSRS web portal. On the menu, first select +New, then data set (or data source if that is preferred).