2 Replies Latest reply on May 1, 2018 6:00 AM by JDel

    ServiceReq Parameters

    JDel Rookie

      We have some custom SQL reports we use to report on ServiceReq pulling in parameters and template information into the report. Has anyone worked up similar reports in Xtraction?


      I can pull in parameters via join in the DME, but wanted to display them in columns with a display name rather than in rows (i.e. each SR has 10 rows of two columns (parameter name and parameter value), one for each parameter

        • 1. Re: ServiceReq Parameters
          GreggSmith SupportEmployee

          Yes, many times in many different situations where data is stored the same way.


          In the data model, if you simply map the parameter table, map the link, parameter name and parameter value fields, and then join the parameter table to the main table (or whatever it links to), this will provide generic access to all parameters through the use of the 2 fields (parameter name and parameter value).  However, if a given record (ticket, configuration item, etc.) has multiple parameters, that will result in multiple rows.  That describes the "rather than in rows" portion of your description.


          The pros to this mapping are that it is quick and easy to model once and you are done, and this one mapping exercise provides access to all parameter data.  The cons are that each parameter name/value pair results in a separate row/record and makes it difficult to filter on things like "where parameter 1 equals A and parameter 2 equals B".


          An alternate mapping of the same data will provide the "display them in columns" portion of your description.  You map the parameter table as before, but you map the separate multiple times - once for each parameter you want to add as a new column.  As an example of mapping one of these parameter tables, let's say you have a parameter "Shoe Size".  Map a new table with ID/Text/Alias of "SHOE_SIZE" and Table name of PARAMETER (or whatever the actual table name is).  Then only map the link field and the parameter value fields.  For the 2nd field mapping, the ID is "SHOE_SIZE", the Text is "Shoe Size", and the expression is SHOE_SIZE.Value (or whatever the actual field name is). Then, when you join this table to the main table, after adding the field join on the key field, add an additional SQL join with something like "AND SHOE_SIZE.NAME = 'Shoe Size'" (without the double-quotes).  This joins to the parameter table and filters down to just the one parameter (shoe size, in this example) and maps it as a new column in the view.  If there are 20 parameters, you map the parameter table 20 times, each time filtering down to a different specific parameter.


          The pros to this mapping is that it gives the user greater and easier flexibility in using the parameter values and allows you do display multiple parameter values in a single record list row.  The cons to this mapping is that each new parameter added to the system requires a new mapping in the data model to make that parameter available to the user.


          Personally, I tend to find uses for both mapping methods and tend to map them both in most situations - the general mapping as well as separate mappings for each name/value pair.


          I hope that helps.



          • 2. Re: ServiceReq Parameters
            JDel Rookie

            Thanks Gregg, I thought that multiple mapping would be the solution, wanted something a little less tedious. Let em get crunching....