SSRS Tutorial Part 3 – How to Configure Dataset in SSRS – Reporting Services

Last modified date:

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.

[1] Setting up dataset in SSRS

[2] Inserting SQL query into dataset definition

 

 

 

 

 

 

 

 

 

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:

Hotový Dataset v SSRS

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

My name is Jan Zedníček and I work as a BI Developer at Kentico Software in Brno. Mostly you can see me there working in the office, but I also work partly as a freelancer. I have been working as a freelancer for many companies for more than 5 years, but Kentico is the matter of my heart. I used to work as a financial controller at companies like Aero Vodochody or Sberbank and I also used to be a bond program manager in Unicapital Investment group. When I am not at work, I like playing volleyball, chess, doing a workout in the gym and I enjoy tasting of best quality rums. I am trying to summarize all my knowledge on this website not to forget them (because of the rum effect, you know =) and to put them forward to anyone. Don´t worry about asking for help or writing some comments.

Category: SSRS - Reporting Services

About Ing. Jan Zedníček - BI Developer, Finance controller

My name is Jan Zedníček and I work as a BI Developer at Kentico Software in Brno. Mostly you can see me there working in the office, but I also work partly as a freelancer. I have been working as a freelancer for many companies for more than 5 years, but Kentico is the matter of my heart. I used to work as a financial controller at companies like Aero Vodochody or Sberbank and I also used to be a bond program manager in Unicapital Investment group. When I am not at work, I like playing volleyball, chess, doing a workout in the gym and I enjoy tasting of best quality rums. I am trying to summarize all my knowledge on this website not to forget them (because of the rum effect, you know =) and to put them forward to anyone. Don´t worry about asking for help or writing some comments.

Leave a Reply

Your email address will not be published.

This site uses Akismet to reduce spam. Learn how your comment data is processed.