Announcement

Collapse
No announcement yet.

Reporting: Passing Parameters from Report to Custom SQL query

Collapse
X
  • Filter
  • Time
  • Show
Clear All
new posts

  • Reporting: Passing Parameters from Report to Custom SQL query

    Is it possible to write a custom SQL query and pass parameters from the report to it?
    Due to the complexity of the query I can not use the wizard, so I lose the ability to use AutoSearch. Is there a way round this?

    My query is:

    SELECT
    ll.learningCentre_id as id,
    learning_centre.`name` as centre,
    COUNT(ll.id) as totalLearners,
    SUM(ll.isCompletedInTime) as completed,
    ROUND(SUM(ll.isCompletedInTime)/COUNT(ll.id)*100) as percent
    FROM (
    SELECT
    learner.id,
    learner.first_name,
    learner.last_name,
    ilp_programme.programme_id,
    booking.learningCentre_id,
    MIN(IF(ilp_course.`completed_date` IS NOT NULL, IF(ilp_course.`completed_date` <= [$end] and ilp_course.`status` = 'completed', 1, 0), 0)) as isCompletedInTime

    FROM learner
    INNER JOIN booking_group ON booking_group.learner_id = learner.id
    INNER JOIN booking ON booking.`bookingGroup_id` = booking_group.id AND booking.`learner_id` = learner.id
    INNER JOIN ilp_programme ON ilp_programme.learner_id = learner.id AND ilp_programme.bookingGroup_id = booking_group.id
    INNER JOIN ilp_course ON ilp_course.ilpProgramme_id = ilp_programme.id

    WHERE booking_group.`start` = [$start]
    AND booking_group.`status` NOT IN (8,16)
    AND booking_group.`target` = 'new'

    GROUP BY learner.id, ilp_programme.programme_id
    ) as ll
    LEFT JOIN learning_centre on ll.learningCentre_id = learning_centre.id
    GROUP BY ll.learningCentre_id

    As you can see I want to be able to pass in $start and $end from the report.

    Cheers.

  • #2
    I am sorry that Navicat does not support your mentioned feature. This request is already in our development list. Thanks for your advice.

    Comment


    • #3
      Is there any update on this? This is a critical need for me, being able to pass a parameter to a complex SQL statement which the "query builder" can not build, I can achieve this in the original query using a variable with ease.

      Comment


      • #4
        Hi igalloway,

        Your idea is already in our development list. However, I am very sorry that we could not provide any time frame for the possibility of implementing your requested feature. I will contact you if any progress is made.

        Comment


        • #5
          Please, put this feature in the top of the list and priority high. Our company need it so much!

          Comment


          • #6
            Has this ever been implemented, as I couldn't find anything here. Usually it should, as its more than 2 years old, but the only info about passing parameters was found on queries, using [$xxx],that did not work.

            I also have a custom Mysql-Query, that I use, and would like to pass a date, that the report is done correctly.
            Trying the wizard usually quits with SQL errors, so I have to do my own queries. Using lates version.

            Thanks
            Patrick

            Comment


            • #7
              Hi futureweb,

              I am sorry that the report feature does not support setting parameters for custom SQL at this moment.

              Comment

              Working...
              X