locked
Count number of times a Request Offering has been used RRS feed

  • Question

  • Hi,

    I'm trying to generate a report/list of how many times a Request Offerings on the Self-Service Portal, has been used. However I can't seem to find the relevant information in either Console, SDK or SQL DB itself.

    Has anyone of you accomplished a similar task?

    Looking forward to hear from you guys.

    /Daniel

     

    Monday, October 17, 2016 12:33 PM

Answers

  • Hi,

    There is a relationship between the work item and the Request Offering that is set every time something is created from the Request Offering. This relationship is called Work Item relates to Request Offering and using this you should be able to get the statistics you are looking for.

    Regards
    //Anders


    Anders Asp | Lumagate | www.lumagate.com | Sweden | My blog: www.scsm.se

    Wednesday, October 26, 2016 10:42 AM
  • Here you go :)

    select count(WI.id) AS [Total], ROInfo.DisplayName AS [Request Offering]from WorkItemDimvw WI
    right join WorkItemRelatesToRequestOfferingFactvw ROFact on WI.WorkItemDimKey = ROFact.WorkItemDimKey
    left join RequestOfferingDimvw ROInfo on ROFact.WorkItemRelatesToRequestOffering_RequestOfferingDimKey = roinfo.RequestOfferingDimKey
    Group by ROInfo.DisplayName
    Order by ROInfo.DisplayName

    Friday, October 28, 2016 12:32 AM

All replies

  • Hi Daniel

    As far as I know there is no such information present in SCSM. But everytime a Request Offering is used, an Incident or a Service Request is generated.

    You could for example tag your templates used with each request offering to be able to report on how many Work Items were created from a given Request Offering.

    Hope this helps!


    Cheers,
    Stefan

    Blog: http://blog.jhnr.ch | Twitter: @JohnerStefan

    Tuesday, October 18, 2016 9:47 PM
  • Hi,

    There is a relationship between the work item and the Request Offering that is set every time something is created from the Request Offering. This relationship is called Work Item relates to Request Offering and using this you should be able to get the statistics you are looking for.

    Regards
    //Anders


    Anders Asp | Lumagate | www.lumagate.com | Sweden | My blog: www.scsm.se

    Wednesday, October 26, 2016 10:42 AM
  • Here you go :)

    select count(WI.id) AS [Total], ROInfo.DisplayName AS [Request Offering]from WorkItemDimvw WI
    right join WorkItemRelatesToRequestOfferingFactvw ROFact on WI.WorkItemDimKey = ROFact.WorkItemDimKey
    left join RequestOfferingDimvw ROInfo on ROFact.WorkItemRelatesToRequestOffering_RequestOfferingDimKey = roinfo.RequestOfferingDimKey
    Group by ROInfo.DisplayName
    Order by ROInfo.DisplayName

    Friday, October 28, 2016 12:32 AM