Allow Listing report Search Filter "Advanced pass-through SQL" to use "Query at runtime" prompts. Hot

by Paul Thompson on July 01, 2015

Advanced SQL (Pass-through SQL) WHERE clauses in reports do not support "query at run-time". Allow me to prompt for multiple values at report run-time and use those in an Advanced SQL WHERE clause.

  • Allow prompting the user for string values to be passed to Advanced "pass-through" SQL report WHERE clauses. Using an example from the Reports Guide for "Finding Items Submitted by Members of a Group". Here is the example from the Guide: @WHERE tablename.TS_SUBMITTER IN ( SELECT TS_ID FROM TS_USERS WHERE TS_USERS.TS_ID IN ( SELECT TS_USERID FROM TS_MEMBERS WHERE TS_MEMBERS.TS_GROUPID IN ( SELECT TS_ID FROM TS_GROUPS WHERE TS_GROUPS.TS_NAME = 'Technical Support' ) ) ) Here is the example using Query At Runtime for the name of the group instead of the hard-coded value "Technical Support": @WHERE tablename.TS_SUBMITTER IN ( SELECT TS_ID FROM TS_USERS WHERE TS_USERS.TS_ID IN ( SELECT TS_USERID FROM TS_MEMBERS WHERE TS_MEMBERS.TS_GROUPID IN ( SELECT TS_ID FROM TS_GROUPS WHERE TS_GROUPS.TS_NAME like '%{Enter group name or partial name}%' ) ) ) That report would prompt the user "Enter group name or partial name". The response would be substituted into the WHERE clause where it is surrounded by "%" wildcards.

    Ideas

    Status
    Target Release
    SBM 11.6
  • Please login to view any attachments.

  • Delivered in SBM 11.6
    David J. Easter Commented by David J. Easter June 24, 2019
    #1 Reviewer  -  

    Delivered in SBM 11.6

    The team is investigating if this can be done through Advanced Reporting. But if not, we'll consider placing it in the product as part of the 2HCY'17 release.
    David J. Easter Commented by David J. Easter February 15, 2017
    #1 Reviewer  -  

    The team is investigating if this can be done through Advanced Reporting. But if not, we'll consider placing it in the product as part of the 2HCY'17 release.

    Hi,

    very good idea and I came across this here and there.
    You have my vote!

    Brian
    Brian Rosenberger Commented by Brian Rosenberger March 10, 2016
    Top 50 Reviewer  -  

    Hi,

    very good idea and I came across this here and there.
    You have my vote!

    Brian

    Another good use case would be a (Current User) keyword. That way you can pull attributes from the current user and join them together, such as using the Current User's associated Contact Record to determine their company.
    Matthew Frauenhoffer Commented by Matthew Frauenhoffer February 11, 2016
    Top 50 Reviewer  -  

    Another good use case would be a (Current User) keyword. That way you can pull attributes from the current user and join them together, such as using the Current User's associated Contact Record to determine their company.

    Similar to (or perhaps in combination with) this idea, it would be great to have the option to combine Advanced SQL _with_ normal Search Filter, with an option to AND/OR the two together. This would eliminate the need to rewrite what in many cases is 90%+ of the logic in one report just to add a single more advanced SQL filter.
    Jeff Malin Commented by Jeff Malin December 16, 2015
    Top 50 Reviewer  -  

    Similar to (or perhaps in combination with) this idea, it would be great to have the option to combine Advanced SQL _with_ normal Search Filter, with an option to AND/OR the two together. This would eliminate the need to rewrite what in many cases is 90%+ of the logic in one report just to add a single more advanced SQL filter.

     
    View all user comments
     

PrintEmail

Recent Tweets