Lets recap what happened in the last part of our series focused on SSRS report creating (Part 2 – Configuring Data source in SSRS). We learned how to create a connection to database via Data source connection manager. We used shared Data source with scope over whole Reporting services project. Then we added reference to our report. In this part of the series, we are going to focus on creation of dataset (data extraction from database). Dataset in SSRS is the most important part of the whole report. Data must have correct structure to be able to represent them in graphs and tables properly.

How to Configure Dataset in Reporting services (SSRS)

After the last part, your project should be in state where you have Data source set up in your report. If you do not have it yet, go to the previous article to do so otherwise the dataset will not work. Datasets are SQL queries which are launched against 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.

All set. You can take a look at other settings in dataset configuration. 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 to visualize a dta!! On the screenshot below in the middle you can see a empty table where we can link a data from our dataset. The project should now look like this:

Next article is on configuring table in report – Part 4 – Creating report in SSRS