By: Daniel Calbimonte | Comments (14) | Related: > Reporting Services Data Sources
Problem
SQL Server Reporting Services is a great tool to create reports from a SQL Server database, but you can also use SSRS to create reports from a mySQL database. In this example I will show you how to create a report using SQL Server Reporting Services from a mySQL table.
Solution
In this example, the database used to import data is the test database in MySQL (this database is installed by default).
To follow the steps it is necessary to have the following requirements already installed.
Requirements
- The MySQL Database installed .
- The MySQL Connector Installed (this will install a MySQL ODBC driver).
Let's create a table myTable in the mysql database with a column myColumn and after that, insert some data in the table:
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");
Now, let's start creating the ODBC connection.
- Go to Windows start menu > Administrative tools >DataSources ODBC and click the add button.
- Select the MySQL ODBC driver and press Finish (This driver is installed with the connector specified in the requirements)
- Specify the Data Source Name. e.g. mySQL conn
- Specify the TCP/IP Server. It can be the IP or the localhost if the machine used is the local machine.
- Specify the user, in this case root and the password (ask the mysql administrator if you do not know the user database password)
- Select the mySQL database
- Congratulations! You have an ODBC connection. Now let's use it in SSRS.
- Open the SQL Server Business Intelligence Development Studio and create a new Report Server Project Wizard.
- In the Welcome to the Report Wizard, press next.
- This is the most important part, in the Select the Data Source, choose New data source.
- In the name specify a name for the connection (any name can be used. In this example the mySQLDS name is used).
- In the Type combo box, select ODBC.
- In the connection string click the Edit button.
- In this step we are going to call the ODBC connection created in step 6. Select the Use user or system data source name and select the mySQL conn data source and press OK.
- In the select Data Source Window, press Next.
- In this step we are going to create the query to access the mySQL table named myTable created at the beginning of this article. Press the Query Builder button (you could write a query directly, but I want to show you the visual tools to generate queries).
- In the Query Designer, press the add table icon.
- In the Add Table Windows select the mytable and press Add.
- In the mytable, check mycolumn and press OK. You have generated the following query to be displayed in the report:
select mycolumn from mytable
- In the Design the Query, press Next.
- In the Select the Report Type window, select tabular and then Next.
- In the Design the Table Window, press Next.
- In the Choose the Table Style Window, select the Slate style and press Next.
- In the Choose the Deployment Location window, press Next.
- In the Completing the Wizard, check the Preview report and press Finish.
- Now, we created a report using a mySQL table for a Reporting Services report.
Next Steps
- You can use SSRS to create reports with any database, it can be Oracle, DB2, Sybase, etc.
- Review the following tips and other resources:
About the author
Daniel Calbimonte is a Microsoft SQL Server MVP, Microsoft Certified Trainer and 6-time Microsoft Certified IT Professional. Daniel started his career in 2001 and has worked with SQL Server 6.0 to 2022. Daniel is a DBA as well as specializes in Business Intelligence (SSIS, SSAS, SSRS) technologies.This author pledges the content of this article is based on professional experience and not AI generated.
View all my tips
