cancel
Showing results for 
Search instead for 
Did you mean: 
cancel
Showing results for 
Search instead for 
Did you mean: 

Parameterized queries

Parameterized queries

This Product Idea comes from the January TC meetings...  Please provide additional details or Business use case if you would like to see specific functionality

 

 

  1. Use Case:
    1. Admin/user defines a query to share will all users
    2. The query is definition is return a list of items related to a specific item
      1. Specific item may be define by item id or a field unique to the item
    3. Prompt for parameter before query is run to limit impact on server
    4. Administrator/Query owner would define the parameter(s) that would be in the query
    5. Provide user interaction from item views, reports, default query for relationship field and charts
    6. One prompt for all query inputs
    7. Input dialog enforces field definition
      1. Boolean would prompt for a yes/no or true/false
4 Comments

I think this would be very helpful.  The query ability that is native to all of the dashboards - "All Documents", "All Change Requests", etc. - if very useful, but it would be very convenient if I could run a query against a particular document, for example, that could be run repeatedly and be used, for instance, for creating monthly reports or for gathering data on a regular basis.

We do DO-178 projects here and I must develop a PTC manual that contains instructions for creating reports and all other functions in PTC.  It  would be easier for me to document how to access a particular query and run it than describing how to use the "where" functionality to create a report - fewer steps.

Newbie

Yes, to this idea. Our users would like to see this in the form of launching a query from a current item. Information (relationship, field, etc.) would be the parameter that was sent to the query. Similar to a QBR field, but without the limitations of a QBR field (like editing items in the list displayed). In addition, adding the ability to send the same contextual information via a URL where a query and its associated parameters could be included in email links and documentation.

Newbie

YES to parameterized queries, please.

A couple more use cases for you:

1) Query: "Production Releases Between 2 Dates"

  • Parameter 1: Name of the system (our service catalog contains over 100 applications)
  • Parameter 2: Start date
  • Parameter 3: End date
  • RETURNS: All production releases for SYSTEM_X between START_DATE and END_DATE
  • Comments: Ideally, the user would select the query, and Integrity would immediately prompt them for all 3 mandatory parameters.  This is much more efficient that having the user select some placeholder query and then forcing them to modify the definition to use the desired system and the correct dates and then re-run the updated query.

2) Query: "Customer Search"

  • Parameter 1: First Name
  • Parameter 2: Last Name
  • Parameter 3: Phone #
  • Parameter 4: ZIP Code
  • RETURNS: All Customer items that match the 1 to 4 optional parameters entered.  If no parameters are entered, all Customer items are returned.  If only [Last Name] is entered, for example, the query returns all matches on just that field.
  • Comments: Our Integrity database currently has approximately 40,000 Customer items, with new Customers added every day.  Without the ability to use a parameterized query, our Service Desk agents are required to run the "All Customers" query, wait for the results, and then apply "Where..." filter #1 for the last name, wait for the reduced result set to run and load, then apply "Where..." filter #2 for the first name, wait for the reduced results set to run and load, etc., until they either find the match or determine a new Customer item is required.  This is terribly slow and inefficient.  The ability to have the system prompt them for the search criteria BEFORE any Customer query is executed, after which it then builds the appropriate SQL with the requisite "WHERE" clauses present on the first run, significantly reduces overall load on the server for all users while also speeding each agent's ability to determine if the Customer on the phone already has an entry in the system.
Community Manager
Status changed to: Acknowledged