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

Version 1


    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.



    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)::

    , 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: