Lets rewind what happened in the last part of our series focused on SSRS report creating (Part 2 – Configuring Data source in SSRS). We saw how to create a connection to database via Data source connection manager. We used shared Data source with scope over whole BI project. Then we added reference to our report. In this part of the series, we are going to focus on creation of dataset from which will our report extract its data. Dataset in SSRS is the most important part of the whole report. Data must have correct structure so we can later represent them in graphs and tables.

How to Configure Dataset in SSRS

After the last part, your project should be in state where you have Data sourced set up in your report. If you do not have it yet, head on to the previous part to do so. In opposite case, the datasets will not work. Datasets are SQL queries which are launched above SQL database which is defined by data source. Prior further reading, check your project – it should look like this:

Configuring Dataset in SSRS

As I mentioned before, dataset is basically SQL query to the database which is defined by data source. I already have one query prepared, it consists of data on sales in following structure – year, month, product, product category, product subcategory. Dataset also contains number of transactions and average profit. SQL query looks like this:

SELECT
  [b].[CalendarYear] AS [Year],
  [b].[MonthNumberOfYear] AS [Month],
  [c].[EnglishProductName] AS [Product],
  [e].[EnglishProductCategoryName] AS [ProductCategory],
  [d].[EnglishProductSubcategoryName] AS [ProductSubCategory],
  SUM([SalesAmount]) AS [Revenues],
  COUNT(*) AS [NumberOfSales],
  SUM([SalesAmount])/COUNT(*) AS [AvgRevenue]
FROM [AdventureWorksDW2014].[dbo].[FactInternetSales] [a]
     JOIN [AdventureWorksDW2014].[dbo].[DimDate] [b]
       ON [a].[OrderDateKey] = [b].[DateKey]
     LEFT JOIN [AdventureWorksDW2014].[dbo].[DimProduct] [c]
       ON [a].[ProductKey]=[c].[ProductKey]
     LEFT JOIN [AdventureWorksDW2014].[dbo].[DimProductSubcategory] [d]
       ON [c].[ProductSubcategoryKey]=[d].[ProductSubcategoryKey]
     LEFT JOIN [AdventureWorksDW2014].[dbo].[DimProductCategory] [e]
       ON [d].[ProductCategoryKey]=[e].[ProductCategoryKey]
GROUP BY
  [b].[CalendarYear],
  [b].[MonthNumberOfYear],
  [c].[EnglishProductName],
  [e].[EnglishProductCategoryName],
  [d].[EnglishProductSubcategoryName];

Dataset can be set up in two ways. First way is individual dataset for report. The second way is shared dataset that can be used in multiple reports. It made sense to set up shared data source while configuring Data source. It is predictable that one SQL instance/database will be usable for multiple reports in one project. This will however not be the case.

So it only makes sense to set up individual Dataset in SSRS. Important part of naming and inserting SQL query as Dataset comes next. It is suitable for each dataset to have its own View in its database. It is for simple reason. If you have many reports, you will occasionally get lost.

And it is done. You can take a look at other settings above dataset. But apart from that, there is nothing else left to do. We will get to more advanced settings (as parameter settings) in one of the next parts of our series.

You project is now ready for data visualization in report. List of the fields to which we will link in tables and graphs can be seen lower. The project should now look like this:

Next part will focus on processing exact report – Part 4 – Creating report in SSRS