Monday, June 13, 2016

SSRS 2016 - Create a Shared DataSet

The creation of a shared dataset first starts with a shared data source. ( see SSRS 2016 - Create a Shared Data Source).  And like shared data sources, shared datasets are effectively objects of the report server that reside on the report server.

Once you have a shared data source, you use the Report Builder to create a new shared dataset.
Here, you can simply startup the Report Builder, or start from the SSRS web portal, which takes you to the New Report of Dataset form within Report Builder.  Be sure to get the latest Report Builder. You can find the link to the latest version here - SSRS 2016 - stand alone Report Builder gets an update.

Summary:

  1. Navigate to the New Report or Dataset form in Report Builder
    1. From the SSRS web portal
      1. Login into the portal (typically http[s]://[servername]/reports )
      2. Click on the menu item +New
      3. Select Dataset
    2. From within Report Builder
      1. Menu:  File | New 
      2. Popup: Choose New Dataset from the options on the left of the
        New Report or Dataset form
  2. Select your desired shared data source from the list
  3. Build your query in the Query Designer
    1. either as text
    2. or with the designer tools
  4. Save your shared dataset

Report Builder connected to your SSRS server

Report Builder will need to be connected to your desired SSRS server. You can verify the connection simply by reviewing the current report server listed at the bottom of the report builder screen. 



If you need to update or adjust this, follow the menu:  File | Options (button)



New Report or Dataset

Once you have navigated to the New Dataset section of the New Report or Dataset form, you are ready to select the desired shared data source. A shared dataset, as an object of the report server, requires a shared data source. Make your choice and the select the Create button.


Dataset Query Designer

This takes you to the dataset query designer.  You can switch to the "Edit as Text" form, or use the graphical query designers tools and options to build your query.  




Stored Procedure or Text (Select)

One of the great features of SSRS is the ability to run stored procedures. If you are using a stored procedure, locate the Command Type combo box, and choose Stored Procedure.



Saving

Once the query is all set and tested, use the menu commands File | Save (or Save as). This will bring up a Save as Dataset popup.  Add your desired dataset name, and click OK.  Your shared dataset is now ready to be used.

You can find the MSDN details on datasets here:  Report Embedded Datasets and Shared Datasets (Report Builder and SSRS)














No comments: