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


              • #8
                Any movement on this issue? It's nice that the Viewer can be used by others in my business to run reports I create in Navicat, but not being able to input parameters like a date range greatly reduces the value of what I can make available. Don't know what's involved technically to add this functionality, but seems like it's a real deficiency to have it available for simple queries built with the Wizard and not for more useful queries not possible with the Wizard.

                Thanks,
                Scott

                Comment


                • #9
                  Ho Scott,

                  This request has been forwarded to our development team. Honestly speaking, it is unlikely to be provided in the near future. However, for sure we will contact you if any progress is made.

                  Comment


                  • #10
                    Hi Mayho,

                    I appreciate your honesty, but surprised that this functionality hasn't already been added let alone that it's not even being considered. The utility of Navicat is significantly reduced without it.

                    Thanks,
                    Scott

                    Comment

                    Working...
                    X