Creating a SSRS report using a mySQL data source

By: Daniel Calbimonte   |   Read Comments (6)   |   Related Tips: > 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.

1. Go to Windows start menu > Administrative tools >DataSources ODBC and click the add button.
    
   
   
   
2. Select the MySQL ODBC driver and press Finish (This driver is installed with the connector specified in the requirements)
    
   
   
   
3. Specify the Data Source Name. e.g. mySQL conn
4. Specify the TCP/IP Server. It can be the IP or the localhost if the machine used is the local machine.
5. Specify the user, in this case root and the password (ask the mysql administrator if you do not know the user database password)
6. Select the mySQL database
    
   
   
   
7. Congratulations! You have an ODBC connection. Now let's use it in SSRS.
8. Open the SQL Server Business Intelligence Development Studio and create a new Report Server Project Wizard.
    
   
   
   
9. In the Welcome to the Report Wizard, press next.
    
   
   
   
10. This is the most important part, in the Select the Data Source, choose New data source.
11. In the name specify a name for the connection (any name can be used. In this example the mySQLDS name is used).
12. In the Type combo box, select ODBC.
13. In the connection string click the Edit button.
     
    
    
    
14. 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.
     
    
    
    
15. In the select Data Source Window, press Next.
     
    
    
    
16. 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).
     
    
    
    
17. In the Query Designer, press the add table icon.
18. In the Add Table Windows select the mytable and press Add.
     
    
    
    
19. In the mytable, check mycolumn and press OK. You have generated the following query to be displayed in the report:

    select mycolumn
    from
    mytable
    

    
    
    
    
    
20. In the Design the Query, press Next.
     
    
    
    
21. In the Select the Report Type window, select tabular and then Next.
     
    
    
    
22. In the Design the Table Window, press Next.
     
    
    
    
23. In the Choose the Table Style Window, select the Slate style and press Next.
     
    
    
    
24. In the Choose the Deployment Location window, press Next.
     
    
    
    
25. In the Completing the Wizard, check the Preview report and press Finish.
     
    
    
    
26. 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:
  • MySQL Manuals
  • Using SQL Server 2008 Reporting Services (SSRS) with MySQL.

About the author

Daniel is a Microsoft SQL Server MVP, Microsoft Certified Trainer and Microsoft Certified IT Professional.

View all my tips

We Recommend

Print

Tweet

Become a paid author

Comments and Feedback:

Post a Comment or Question

Keep it clean and stay on the subject or we may delete your comment.
Your email address is not published. Required fields are marked with an asterisk (*)

*Name

*Email

Notify for updates Signup for our newsletter

Comments *** NOTE *** - If you want to include code from SQL Server Management Studio (SSMS) in your post, please copy the code from SSMS and paste the code into a text editor like NotePad before copying the code below to remove the SSMS formatting.
*Enter Code