In this article, I am going to explain ODBC drivers. We will go through the process to set up the ODBC driver for MySQL and use it to create an SSRS report. First, let me talk about ODBC drivers.
The ODBC, also known as Open DataBase Connectivity, is provided by Microsoft that allows us to connect the application to the SQL Server or other database servers like MySQL, Oracle, or PostgreSQL. Every database platform provides its setup of ODBC drivers that can be used to connect Windows or Linux applications to connect to the database.
When we install the MySQL server, the ODBC driver for MySQL is installed automatically. In the installation process, you can choose the different version of MySQL from the Select Products and Features screen. I have already installed it on my computer, so it is greyed out.
You can download the driver from here.
Once the driver is installed, let us configure the ODBC data source for MySQL. The Windows ODBC data sources (64-bit) is used to install and manage the ODBC drivers of various databases. We are going to use it. Open Control Panel Open Administrative Tools Open ODBC Data source (64-bit) See the following image:
A dialog box ODBC Data Source Administrator dialog box opens. On the User DSN tab, you can view the pre-configured ODBC data sources. We want to create a new System DSN; hence click on System DSN tab and click on the Add… button.
A dialog box Create New Data Source opens. In the list, you can see all see the list of various ODBC drivers that are installed on the computer. We want to set up MySQL ODBC Data source, hence choose MySQL ODBC 8.0 ANSI Driver or MySQL ODBC 8.0 Unicode Driver and click on Finish.
A dialog box MySQL Connector/ODBC Data Source configuration opens. In the Data Source name and description text box, provide the desired name and the description of the Data source.
If you are using TCP/IP protocol to connect to MySQL, then click on TCP/IP Server and provide the name and the port number of MySQL Server. If you are using Named Pipe protocol, then select Named Pipe and enter the appropriate value in the text box.
Enter the username and password in the User and Password text box to authenticate to the server. Select the desired database name from Databases drop-down box. Click OK to save the ODBC connection properties and close the dialog box. See the following image:
On ODBC Data Source Administrator (64-bit) screen, you can see that the MySQL for SSRS has been created. Click OK to close the dialog box. See the following image:
Once the ODBC Data Source is configured, let us create an SSRS report to test it. To create an SSRS report, you must download SQL Server data tools from this location. Once it is downloaded and installed, open it and click on File Hover on New Click on New Project. See the following image:
A dialog box, New Project, click on Reporting Services from the left pane and click on Report Server Project Wizard. Provide the desired name in Name textbox. See the following image:
Create a new report wizard opens. The first screen gives information about the wizard and the tasks that can be performed by it. You can review the details and click on Next. See the following image:
On Select Data Source Screen, provide the desired name of the data source in the Name text box. Choose ODBC from the Type drop-down box and enter DSN=MySQL in connection string text box. Click on Next. See the following image:
On Design the Query screen, enter the following query. It populates the list of movies, their category, release year, rental details.
|
1 2 3 4 5 6 7 8 9 |
select title as 'Movie Name',c.name as movie category', description 'Movie description', release_year as 'Movie release year', rental_rate 'Rental Rate' , rental_duration 'Rental Duration' , special_features 'Special features' from film a inner join film_category b on a.film_id=b.film_id inner join category c on b.category_id=c.category_id |
Click on Next. See the following image:
On Select, the Report Type screen, select the type of report. You can create a matrix report that summarizes the output of the query. We want to populate the details of the movie titles; hence choose Tabular and click Next. See the following image:
On the Design the Table screen, you can select the columns that you want to display on the report. We require the following columns from the query output
- Movie_name
- Movie_category
- Movie_description
- Movie_release_year
- Rental_rate
- Rental_description
- Special_features
Select all the fields from the available fields list and click on details. All the selected fields will be moved to the Displayed field list. Click on Next. See the following image:
On Completing the Wizard screen, provide the desired report name in the report name text box. Review the report details in Report Summary textbox and click on Finish. See the following image:
Once the report is created, in the Design window, it looks like the following image.
To view it correctly, click on Preview. In Preview, it looks like the following image:
You can format the report according to your requirement.
Summary
In this article, we have learned about the ODBC driver and how to configure it to connect it to the MySQL Server. I have also demonstrated the usage of the ODBC driver to create an SSRS Report to display data from MySQL Server.
He has expertise in database design, performance tuning, backup and recovery, HA and DR setup, database migrations and upgrades. He has completed the B.Tech from Ganpat University. He can be reached on nisargupadhyay87@outlook.com
- Copy SQL Databases between Windows 10 and CentOS using SQL Server data tools - October 19, 2022
- Changing the location of FILESTREAM data files in SQL Database - October 14, 2022
- Manage SQL Databases in CentOS: Manage filegroups of user databases - October 5, 2022