Report on Service Request and its Parameters

Version 1

    Details

    Is it possible to report off of the parameters that are defined for our Request Offerings?


     


    Resolution

    This will ultimately need to be done through either custom reporting (certainly an option, but up to the customer to design) or through SQL directly.


    The two key tables are dbo.ServiceReq (stores the Service Request data) and dbo.ServiceReqParam (contains the parameter names, and their respective values, per-request).

    The best way to demonstrate this is through a join:

    select ServiceReq.ServiceReqNumber, ServiceReq.ProfileFullName, ServiceReqParam.ParameterName, ServiceReqParam.ParameterValue
    from ServiceReq
    LEFT JOIN ServiceReqParam
    ON ServiceReq.RecID=ServiceReqParam.ParentLink_RecID
    where ServiceReq.ServiceReqNumber='10002'


    You can return more results by opening up your query:

    select ServiceReq.ServiceReqNumber, ServiceReq.ProfileFullName, ServiceReqParam.ParameterName, ServiceReqParam.ParameterValue
    from ServiceReq
    LEFT JOIN ServiceReqParam
    ON ServiceReq.RecID=ServiceReqParam.ParentLink_RecID
    ORDER BY Servicereq.ServiceReqNumber desc

    where ServiceReq.ServiceReqNumber='10002'
    ORDER BY Servicereq.ServiceReqNumber desc

    returns the following:


    This query is from the PrintServiceReqMain report and sorts the parameters based on how they are in the actual offering.

    SELECT ServiceReq.RecId
    ,ServiceReqTemplateParam.NAME
    ,ServiceReqTemplateParam.Description
    ,ServiceReqTemplateParam.DisplayType
    ,ServiceReqTemplateParam.DisplayName
    ,ServiceReqTemplateParam.RecurPeriod
    ,ServiceReqTemplateParam.LineBreak
    ,ServiceReqTemplateParam.ValidationList_RecID
    ,ServiceReqTemplateParam.ValidationConstraints
    ,ServiceReqTemplateParam.ConfigOptions
    ,ServiceReqTemplateParam.SequenceNum
    ,ServiceReqTemplateParam.VisibilityExpression
    ,ServiceReqTemplateParam.PriceExpression
    ,ServiceReqParam.ParameterValue
    ,ServiceReqParam.ParameterName
    ,ServiceReqParam.ParameterDisplayValue
    ,ServiceReqParam.DisplayType ParamDisplayType
    ,ServiceReqParam.ParameterPrice
    ,ServiceReqParam.RecurrenceType
    ,ServiceReqParam.Price
    ,ServiceReqParam.Price_Currency
    ,ServiceReqParam.Price_CurrencyValid
    ,ServiceReqParam.RecurrentPrice
    ,ServiceReqParam.RecurrentPrice_Currency
    ,ServiceReqParam.RecurrentPrice_CurrencyValid
    ,ServiceReqParam.PriceItemPriceLink_RecID
    ,ServiceReqParam.PriceItemPriceLink_Category
    ,ServiceReqParam.PriceItemRecurringPriceLink_RecID
    ,ServiceReqParam.PriceItemRecurringPriceLink_Category
    ,ServiceReqParam.PriceVarianceLink_RecID
    ,ServiceReqParam.PriceVarianceLink_Category
    ,ServiceReqParam.PriceVarianceRecurringPriceLink_RecID
    ,ServiceReqParam.PriceVarianceRecurringPriceLink_Category
    ,att.FileBytes
    ,val.DEFINITION [ValidationList_Definition]
    FROM ServiceReq
    INNER JOIN ServiceReqTemplateParam ON (ServiceReq.SvcReqTmplLink_RecID = ServiceReqTemplateParam.ParentLink_RecID)
    LEFT OUTER JOIN ServiceReqParam ON (
      ServiceReqParam.ParentLink_RecID = ServiceReq.RecId
      AND ServiceReqParam.ParameterName = ServiceReqTemplateParam.NAME
      )
    LEFT JOIN [FusionAttachments] att ON att.RecID = ServiceReqParam.ParameterValue
    AND ServiceReqParam.DisplayType = 'image'
    LEFT JOIN [Frs_def_validation_lists] val ON val.RecID = ServiceReqTemplateParam.ValidationList_RecID
    AND ServiceReqTemplateParam.ValidationList_RecID IS NOT NULL
    WHERE (ServiceReq.ServiceReqNumber = ('10165'))
    ORDER BY ServiceReqTemplateParam.SequenceNum
    ,ServiceReqTemplateParam.RecId