Creating a SSRS report using a mySQL data source

By:   |   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.

  1. Go to Windows start menu > Administrative tools >DataSources ODBC and click the add button.
     
    ODBC DS Administrator

  2. Select the MySQL ODBC driver and press Finish (This driver is installed with the connector specified in the requirements)
     
    Select the driver MySQL

  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
     
    Create the connection parameters

  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.
     
    Select the Report Server Project Wizard

  9. In the Welcome to the Report Wizard, press next.
     
    The Welcome Wizard

  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.
     
    Select the Data Source

  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.
     
    Specify the Connection Properties

  15. In the select Data Source Window, press Next.
     
    Select the Data Source

  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).
     
    Design the Query

  17. In the Query Designer, press the add table icon.
  18. In the Add Table Windows select the mytable and press Add.
     
    Add the MySQL table

  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
    

    Select the columns

  20. In the Design the Query, press Next.
     
    Verify the Query string

  21. In the Select the Report Type window, select tabular and then Next.
     
    Select the Report Type

  22. In the Design the Table Window, press Next.
     
    Design the Table

  23. In the Choose the Table Style Window, select the Slate style and press Next.
     
    Choose the Table Style

  24. In the Choose the Deployment Location window, press Next.
     
    Choose the Deployment Location

  25. In the Completing the Wizard, check the Preview report and press Finish.
     
    Completing the Wizard

  26. Now, we created a report using a mySQL table for a Reporting Services report.
     
    Show the report
Next Steps


sql server categories

sql server webinars

subscribe to mssqltips

sql server tutorials

sql server white papers

next tip



About the author
MSSQLTips author Daniel Calbimonte 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



Comments For This Article




Monday, November 15, 2021 - 7:10:53 PM - David Rucker Back To Top (89449)
If the SSRS is on a separate machine than the SQL Server backend database. Does the ports need to be opened between the SSRS and the MySQL DB, or between the SQL Server DB and the MySQL DB?

Saturday, May 9, 2020 - 7:24:13 PM - Harry Valdivieso Back To Top (85614)

And how I get the report to my networked printer?


Wednesday, August 22, 2018 - 10:59:03 AM - Richard Frenkel Back To Top (77270)

SSRS is 64 bit and ReportBuilder 3.0 is 32 bit. In order to get a DataSource that works with both you have to install both 64 and 32 bit ODBC Drivers and set up SYSTEM DSNs with identical names using both drivers. For example:

 

You then create a data source in SSRS as described in this post, for example DSN=HolocomDevRW. This will work with both SSRS and ReportBuilder. 

At least that is my experience, the DSN name seems to be the key and the application will pick the appropriate 32 or 64 bit system data source. 


Friday, May 19, 2017 - 12:55:20 PM - Pramod Back To Top (55885)

Hi Daniel

I followed these instructions to create a report and was able to successfully create report (connecting to MySQL DB). But as this report was created on my local machine, I want to keep it on the Report Server so it can be shared with other users. I tried this in two ways:

1) simply uploading this report on Report Server

2) Deployed the project on the report server.

But when I tried to run it from Report Server using Report Builder it gives error as:

An error has occurred during report processing. (rsReocessingAborted)

Details:

For more information about this error navigate to the report server on the local server machine, or enable remote errors
----------------------------
Cannot create a connection to data source 'MySQLCADUAT'. (rsErrorOpeningConnection)
----------------------------

Do you have any suggestions on what might be going wrong in my case?


Tuesday, March 7, 2017 - 12:09:15 AM - Rahul Kumar Back To Top (47436)

Hi Daniel,

Thanks for this nice tutorial. I am successfully able to connent MYSQL though ODBC, In Visual Studio I am able to create repots and preview them, But I have one problem when I try to deploy my code on server. It gives following error message :

An attempt has been made to use a data extension 'ODBC' that is either not registered for this report server or is not supported in this edition of Reporting Services.

Can you plesae quide me how to fix that.

 


Tuesday, January 26, 2016 - 11:44:35 AM - Saurabh Back To Top (40504)

Hi Daniel,

 

I ' ve typical requirement  where there is 2 database hosted in 2 different environment ,

one database is :- sql server  and other is in mysql database.

one new report which will be have some data from SQL server and some data from MYSQL , combining those tables from 2 database and that too in different environment. how to get the meaningfull information from this report.


Is that possible through SSRS ?

Looking forward for your update.

 

Thanks,

Saurabh

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 


Monday, February 16, 2015 - 3:16:13 AM - vasanth Back To Top (36244)

Hi Daniel,

 

 

Thanks Daniel, It's work very nice, i Created my first report  without problems.

 

 


Thursday, October 2, 2014 - 12:22:22 AM - Jose Henao Back To Top (34814)

<p>Hi Daniel:</p>

<p>I am accesing a Mysql database in the cloud (a server at Heroku, a cloud applications platform). Your tips Work fine. I can create reports without problems.</p>

<p>Very good tip. &nbsp;Thanks so much</p>


Wednesday, May 15, 2013 - 11:01:24 AM - Daniel Calbimonte Back To Top (23964)

Hello, have you read this forum  to solve the parameter problem?

http://social.msdn.microsoft.com/Forums/en-US/sqlreportingservices/thread/8dae66fb-3784-4277-8ccf-8a114862c591/


Friday, April 26, 2013 - 12:51:09 PM - Patricia Neilan Back To Top (23580)

I am also looking how to use parameters with SSRS and mysql, could you please give me some hints if you have them. [email protected]

 

Thanks!!

 


Friday, April 26, 2013 - 2:58:04 AM - siya ram Back To Top (23570)

How to pass Parameter at run time in my sql query....for SSRS report

for example:  select * from T where c1=@c1

plz give me answer by email

[email protected]


Sunday, November 25, 2012 - 8:33:48 PM - Marcwolf Back To Top (20525)

Great Article.

I have had some issues though.  Can you tell me what version of MySQL, ODBC (32/64) that you are using to connect.

I am using MySQL (64) 5.5.20 and have tried both ODBC 5.02.02w and 3.51.21

 

With the 3.5 ODBC I can import the tables into the query wizard however I cannot see any columns, with the 5.01 ODBC I cannot import the tables into the Query Wizard at all (I click and nothing happens)

 

Do you have any advice?  Many thanks

Dave


Wednesday, April 18, 2012 - 9:10:19 PM - Faheem Ahmad Back To Top (16994)

Excellent.

This is exactly what i am looking for.

Thank you very much.


Wednesday, April 18, 2012 - 9:23:30 AM - Karuppan Back To Top (16988)

This is very straight forward and very easy to understand. thanks for giving to us.















get free sql tips
agree to terms