Home  >  Blog  >   SSRS

How To Create A Report Dataset Reporting Services - SSRS

Rating: 4
  
 
5075

Creating A SSRS Report Dataset

A dataset is usually specific to a report and will be based on a query to the database. Datasets are added to a report by right-clicking on Datasets in the Report Data panel.

e.g. If the report needs to show a summary of sales broken down by month, there must be a dataset able to supply the month and the total sales value for that month.

If you would like to become an SSRS certified professional, then visit Mindmajix - A Global online training platform: "SSRS Training and Certification Course". This course will help you to achieve excellence in this domain.

The query for this dataset looks at the Sales.SalesOrderHeader table in the AdventureWorks2012 database and gets the month and some of the sales totals for that month. As we are only interested in grouping by month and not every single date when an order was placed, the MONTH function is used to convert the OrderDate into just the month number.

Note that it is not necessary to specify the database name in the query text, as this was already set by the shared data source.

Database name in the query text

                                         (Related Page: Data Sources And Data Sets In Reporting Services - SSRS)

Try it

1. In the Report Data panel, right-click on Datasets and choose Add Dataset…

2. In the Dataset Properties dialog, name your dataset SalesSummary and change the selection from Use a shared dataset to Use a dataset embedded in my report.

3. Select the AdventureWorks data source. Note that this is the Report Data Source, which references the shared data source of the same name provided by the project.

Frequently Asked SSRS Interview Questions & Answers

MindMajix Youtube Channel

4. Enter the SELECT query as shown:

SELECT
        MONTH(OrderDate) AS Month,
        SUM(TotalDue) AS Total
FROM
        Sales.SalesOrderHeader
GROUP BY
        MONTH(OrderDate)
ORDER BY
        Month

5. Click OK

Note that the dataset SalesSummary is now shown under Datasets in the Report Data panel. If the dataset is not already expanded, click the + button next to it, and you will see the two fields Month and Total.

Report Data

Get FREE Teradata Sample Resumes! Download & Edit, Get Noticed by Top Employers!   Download Now!

List of Related Microsoft Certification Courses:

 SSIS Power BI
 SSAS SQL Server
 SCCM SQL Server DBA
 SharePoint BizTalk Server
 Team Foundation Server BizTalk Server Administrator

 

 

Join our newsletter
inbox

Stay updated with our newsletter, packed with Tutorials, Interview Questions, How-to's, Tips & Tricks, Latest Trends & Updates, and more ➤ Straight to your inbox!

Course Schedule
NameDates
SSRS TrainingMar 23 to Apr 07View Details
SSRS TrainingMar 26 to Apr 10View Details
SSRS TrainingMar 30 to Apr 14View Details
SSRS TrainingApr 02 to Apr 17View Details
Last updated: 03 Apr 2023
About Author

Ravindra Savaram is a Technical Lead at Mindmajix.com. His passion lies in writing articles on the most popular IT platforms including Machine learning, DevOps, Data Science, Artificial Intelligence, RPA, Deep Learning, and so on. You can stay up to date on all these technologies by following him on LinkedIn and Twitter.

read more
Recommended Courses

1 / 15