SQL Server Reporting Services is a powerful tool that allows you to create reports from a SQL Server database. However, did you know that you can also use SSRS to create reports from a MySQL database? In this article, we will walk through the steps of creating a report using SQL Server Reporting Services from a MySQL table.

Requirements

Before we begin, make sure you have the following requirements installed:

• MySQL Database
• MySQL Connector (this will install a MySQL ODBC driver)

Creating the MySQL Table

First, let’s create a table in the MySQL database and insert some data into it:

use mysql;
create table myTable(myColumn varchar(20));
insert into myTable(myColumn) values("John");
insert into myTable(myColumn) values("Jane");
insert into myTable(myColumn) values("Arnold");
insert into myTable(myColumn) values("Jessica");

Creating the ODBC Connection

Next, we need to create an ODBC connection to the MySQL database:

1. Go to the Windows start menu, navigate to Administrative tools, and click on DataSources ODBC.
2. Click the “Add” button.
3. Select the MySQL ODBC driver and click “Finish”.
4. Specify a Data Source Name (e.g. mySQL conn).
5. Specify the TCP/IP Server (it can be the IP or “localhost” if the machine used is the local machine).
6. Specify the user and password for the MySQL database.
7. Select the MySQL database.

Using the ODBC Connection in SSRS

Now that we have the ODBC connection set up, let’s use it in SQL Server Business Intelligence Development Studio to create a report:

1. Open the SQL Server Business Intelligence Development Studio and create a new Report Server Project Wizard.
2. In the “Welcome to the Report Wizard” window, click “Next”.
3. In the “Select the Data Source” window, choose “New data source”.
4. Specify a name for the connection (e.g. mySQLDS) and select “ODBC” as the type.
5. In the connection string, select the ODBC connection created earlier.
6. In the “Select Data Source” window, click “Next”.
7. In the “Query Builder” window, use the visual tools to generate a query to access the MySQL table.
8. In the “Design the Query” window, click “Next”.
9. In the “Select the Report Type” window, choose “Tabular” and click “Next”.
10. In the “Design the Table” window, click “Next”.
11. In the “Choose the Table Style” window, select the “Slate” style and click “Next”.
12. In the “Choose the Deployment Location” window, click “Next”.
13. In the “Completing the Wizard” window, check the “Preview report” option and click “Finish”.

And there you have it! You have successfully created a report using a MySQL table for a Reporting Services report.

SQL Server Reporting Services provides a powerful and flexible platform for creating reports, and with the ability to connect to a MySQL database, you can leverage the full potential of SSRS for your reporting needs.