Learn more about SQL Server tools



solving sql server problems for millions of dbas and developers since 2006 join MSSQLTips for free SQL Server tips













































   Got a SQL tip?
            We want to know!

Creating a SSRS report using a mySQL data source

MSSQLTips author Daniel Calbimonte By:   |   Read Comments (7)   |   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.
     
    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


Last Update: 4/18/2012


About the author
MSSQLTips author Daniel Calbimonte
Daniel Calbimonte is a Microsoft SQL Server MVP, Microsoft Certified Trainer and Microsoft Certified IT Professional.

View all my tips
Related Resources


print tip Print  
Become a paid author





join MSSQLTips for free SQL Server tips     



Learn more about SQL Server tools
Post a comment or let the author know this tip helped you.

       All comments are reviewed, so stay on subject or we may delete your comment.

*Name   *Email Notify for updates



       Note: your email address is not published. Required fields are marked with an asterisk (*)


Get free SQL tips:

*Enter Code refresh code     



Thursday, October 02, 2014 - 12:22:22 AM - Jose Henao Read The Tip

<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 Read The Tip

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 Read The Tip

I am also looking how to use parameters with SSRS and mysql, could you please give me some hints if you have them. pgamezg@gmail.com

 

Thanks!!

 


Friday, April 26, 2013 - 2:58:04 AM - siya ram Read The Tip

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

siya.ram@hanusoftware.com


Sunday, November 25, 2012 - 8:33:48 PM - Marcwolf Read The Tip

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 Read The Tip

Excellent.

This is exactly what i am looking for.

Thank you very much.


Wednesday, April 18, 2012 - 9:23:30 AM - Karuppan Read The Tip

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




 
Sponsor Information







Copyright (c) 2006-2014 Edgewood Solutions, LLC All rights reserved
privacy | disclaimer | copyright | advertise | about
authors | contribute | feedback | giveaways | free t-shirt | user groups | community | events | first timer?
Some names and products listed are the registered trademarks of their respective owners.