How do I group service requests with their parameters in a SQL query?

Version 1

    Details

    This will provide an example query that shows the joins needed to display a Service request along with the display name of the parameter and its value.  With proper grouping defined, this can be used to build a SSRS report with the desired information.


    Resolution

     

    ServiceReq.Subject should contain the name of the request offering.

    This is the SaaS function to pull the parameters into a notification: $(ForEachChild("ServiceReq#", RecId, "ServiceReqParam#.", "'' + [ServiceReqTemplateParam#]DisplayName + ': ' + ParameterValue + '\r\n'"))

    The query is below (CHange the subject to match what you want to look for)::

    SELECT 
      ServiceReq.ServiceReqNumber
    , ServiceReqTemplateParam.DisplayName
    , ServiceReqParam.ParameterName
    , ServiceReqParam.ParameterValue
    FROM ServiceReq WITH (NOLOCK)
    LEFT OUTER JOIN ServiceReqParam WITH (NOLOCK) on ServiceReq.RecId = ServiceReqParam.ParentLink_RecId
    LEFT OUTER JOIN ServiceReqTemplate WITH (NOLOCK) on ServiceReq.SvcReqTmplLink_RecId = ServiceReqTemplate.RecId
    LEFT OUTER JOIN ServiceReqTemplateParam WITH (NOLOCK) on (ServiceReqTemplate.Recid = ServiceReqTemplateParam.ParentLink_RecId AND ServiceReqTemplateParam.Name = ServiceReqParam.ParameterName)
    WHERE ServiceReq.Subject = 'Tablet Request'


    Example Results: