SSRS Report Builder Part 4.4 - Importing Datasets from SQL Query Files

Introduction

In this tutorial, we will explore how to import datasets from SQL query files into SSRS Report Builder. You will learn how to create and utilize SQL query files using Notepad and SQL Server Management Studio (SSMS). This guide will provide step-by-step instructions to help you effectively manage your datasets in Report Builder.

Chapter 1: Preparing Your Environment

To follow along with this tutorial, ensure you have the following:

• The oscarwinningfilms.sql file, which you can download from the video description.
• The Weizel Movies Database installed. If you need assistance, refer to the linked video in the description for setup instructions.

Steps to Set Up Your Report Builder

1. Extract the downloaded SQL file:

   • Right-click on the downloaded file.
   • Choose "Extract All" to obtain the oscarwinningfilms.sql file.

2. Open Report Builder:

   • Start a new report in SSRS Report Builder.

3. Create a connection to the Weizel Movies Database:

   • Right-click on the Data Sources folder in the Report Data window.
   • Select "Add Data Source."
   • Name the data source "Movies."
   • Choose "Embedded Connection" and select "Microsoft SQL Server."
   • Click the "Build" button to help create your connection string.
   • For the server name, use localhost\sql2017 or your SQL Server instance name.
   • Select the Movies database from the dropdown and click OK.

Chapter 2: Importing an SQL Query File

Now that your environment is set up, you can import the SQL query file to create a dataset.

Steps to Import the Query File

1. Right-click on the Movies data source.
2. Select "Add Dataset."
3. Name the dataset "Oscar Winners."
4. Click the "Import" button at the bottom of the window.
5. Navigate to the extracted oscarwinningfilms.sql file on your desktop and double-click it.
6. Click OK to import the query. The dataset will be populated with the columns from the query.

Chapter 3: Displaying the Results

After importing the dataset, it’s important to visualize the results in your report.

Steps to Create a Table

1. Clean up the report by deleting any placeholder text boxes.
2. Right-click in the page footer and choose to remove it.
3. Insert a basic table:
   • Right-click in the report and select "Insert Table."
4. Bind the dataset columns to the table:
   • Drag and drop relevant fields such as Title, Release Date, Oscar Nominations, and Wins into the table.
5. Format the table:
   • Change the font if needed to avoid rendering issues.
   • Adjust column widths for better readability.
   • Format the Release Date to a sensible date format:
     • Select the Release Date column, click the dialog box launcher in the Number group, and choose a date format.
   • Style the header row by changing its background color and making the font bold.
6. Run the report to display the results.

Chapter 4: Creating a Query File Using Notepad

If you frequently use similar queries, creating your own SQL query files can save time.

Steps to Create a Query File

1. In Report Builder, create a new dataset:
   • Right-click on the Movies data source and select "Add Dataset."
2. Open the Query Designer:
   • Select the "Query Designer" button.
3. Choose columns from the Film, Director, and Genre tables:
   • Select Title, Release Date, Oscar Nominations, and Oscar Wins, and add a filter to show films with at least one Oscar win.
4. Copy the generated SQL query:
   • Use Ctrl + C to copy the SQL statement from the query text box.
5. Open Notepad and paste the query:
   • Save the file with the .sql extension (e.g., my_query.sql).
6. Import the new query file back into Report Builder:
   • Right-click on the Movies data source, select "Add Dataset," and use the Import button to choose your new SQL file.

Chapter 5: Creating a Query in SQL Server Management Studio

For a more robust query-building experience, consider using SSMS.

Steps to Create a Query in SSMS

1. Open SQL Server Management Studio and connect to your SQL Server instance.
2. Select the Movies database and create a new query:
   • Click on "New Query" or press Ctrl + N.
3. Use the Query Designer:
   • Right-click in the query window and select "Design Query in Editor."
   • Choose the Film, Director, Genre, and any additional tables you need.
4. Select the desired columns and set filters and sorting.
5. Click OK to generate the SQL query.
6. Save the query file to your desktop with a .sql extension.

Chapter 6: Creating a Dataset Using an SSMS Query

You can now use the query created in SSMS to generate a dataset in Report Builder.

Steps to Import SSMS Query

1. In Report Builder, right-click on the Movies data source and select "Add Dataset."
2. Name the dataset (e.g., "SSMS Query").
3. Click the Import button and select the SSMS query file you saved.
4. Click OK to populate the dataset with the query results.

Chapter 7: Editing a Query in SQL Server Management Studio

You can also edit existing queries in SSMS for further refinement.

Steps to Edit a Query

1. Open your SQL file in SSMS.
2. Select the entire query text and right-click to choose "Design Query in Editor."
3. Make necessary changes, such as adding or modifying tables and columns.
4. Save your changes and re-import the updated query file into Report Builder.

Conclusion

In this tutorial, we covered how to import SQL query files into SSRS Report Builder, create your own query files using Notepad and SQL Server Management Studio, and manage your datasets effectively. By utilizing these techniques, you can streamline your reporting process and enhance your data management capabilities. For further exploration, consider experimenting with more complex queries and report designs. Happy reporting!