none
sql Server reporting service with mysql RRS feed

  • Question

  • Dear partners,

    How can we use mysql with sql server reporting service? If we have 2-3 sql command , how can we add the result of these commands in the same report ? Thanks a lot.

    Sunday, April 9, 2017 7:19 AM

All replies

  • you can odbc data source which explain in following link

    https://www.mssqltips.com/sqlservertip/2615/creating-a-ssrs-report-using-a-mysql-data-source/


    Please Mark it as Answered if it answered your question OR mark it as Helpful if it help you to solve your problem.

    Sunday, April 9, 2017 8:11 AM
  • What do you mean by commands? Do you mean results sets? You can union these together if they are have the same columns and datatypes.

    If you mean parameters you can add multiple parameters in the parameter section.

    Sunday, April 9, 2017 11:31 AM
  • I have command as listed as below:

    1. SELECT GROUP_CONCAT( CONCAT( u.firstname,  " ", u.lastname ) ) ,ic.fullname FROM mdl_course ic JOIN mdl_context con ON con.instanceid = ic.id JOIN mdl_role_assignments ra ON con.id = ra.contextid AND con.contextlevel = 50 JOIN mdl_role r ON ra.roleid = r.id JOIN mdl_user u ON u.id = ra.userid WHERE r.id = 3 GROUP BY ic.id

    2. SELECT COUNT(l.userid) AS 'DistinctUserLogins', DATE_FORMAT(FROM_UNIXTIME(l.timecreated), '%M') AS 'Month' FROM prefix_logstore_standard_log l
    WHERE l.action = 'loggedin' AND YEAR(FROM_UNIXTIME(l.timecreated)) = '2017'  GROUP BY MONTH(FROM_UNIXTIME(l.timecreated))

    3.SELECT COUNT(userid) AS hits, cmid, module,course, b.fullname
    FROM mdl_log a, mdl_course b
    WHERE a.course = b.id and FROM_UNIXTIME(time) >= DATE_ADD(LAST_DAY(DATE_SUB(NOW(), INTERVAL 1 MONTH)), INTERVAL 1 DAY) and FROM_UNIXTIME(time) <= LAST_DAY(NOW())
    GROUP BY a.cmid
    ORDER BY a.module ASC

    And I would like to show the result in the same sheet , how can we finish it ? They don't have same columns between the three sql command.

    Thanks a lot.

    Sunday, April 9, 2017 12:50 PM
  • These are 3 different data sets.

    You will need to add them separately to your Report.

    In this report I have 2 data sets. Each of them provide the data to a different report part.

    You will need to do something similar.

    Sunday, April 9, 2017 12:53 PM
  • But I remember it just only can add one dataset , or can we  add morn than one dataset ? Thanks a lot
    Sunday, April 9, 2017 3:05 PM
  • Yes, You can add as many data sets as you want.

    Just right click on Data sets then click on add dataset.

    • Proposed as answer by Henry Jiang Monday, April 10, 2017 6:42 AM
    Sunday, April 9, 2017 4:06 PM
  • Yes, you can add multiple report items to the same report - for example 2 or more tables, matrixes or charts.
    • Proposed as answer by Henry Jiang Monday, April 10, 2017 6:42 AM
    Monday, April 10, 2017 12:21 AM
  • Hi,

    According to your description, we cannot merge all three queries in only one dataset, even using stored procedure. But we can add multiple datasets by clicking “Add Dataset” in the “Report Data” menu and input the commands in the Query. Then all the results can be rendered in the same sheet. 

    Best Regards,
    Henry
    Monday, April 10, 2017 6:42 AM