CA Service Management

Expand all | Collapse all

Catalog WEBI Report

Jump to Best Answer
  • 1.  Catalog WEBI Report

    Posted 06-21-2016 11:47 AM

    Hi you all,

              i have two questions:

    1. How do i add the SC request form information to a WEBI report? what are the variables i should use in order to get those values? i have several offerings with custom Forms.

    2. What is the variable i should use in webi report to get the related ticket (sdm ticket no.) using the SLCM universe?

     

    I have been looking for it with no luck...hope you can help me.

     

    Thanks in advance!

     

    Regards.



  • 2.  Re: Catalog WEBI Report

    Posted 06-22-2016 02:28 AM

    Hi Hernan.

    For your question number 2, I might have an answer.
    Although there is no out-of-the-box solution for this.

    The mdb holds a table in which the link between an SC-request and an SDM-ticket is stored.
    You can execute the next query to retrieve the related data:
    SELECT persid,ref_num,request_item_id,request_id FROM usm_link_ticket_request_item

    Next, when you create a WEBI report, you can hit the 'sql' icon to open the sql-query that has been generated.
    You could then change that query to 'join' with the above mentioned table?

     

    Thanks and kind regards, Louis.



  • 3.  Re: Catalog WEBI Report
    Best Answer

    Posted 06-23-2016 10:09 AM

    Hi Hernan.

    I then further exploited on your first question:
    1a. How do I add the SC request form information to a WEBI report?
    1b. What are the variables I should use in order to get those values?
    1c. I have several offerings with custom Forms.

     

    And although I do not have a full blown answer for the WEBI report, I do have some information on the tables in the mdb database, involved in your question.
    Perhaps this might give you a (good?) starting point for this.

     

    For your question on how to retrieve combined form values, I split this into two sections.
    1. For an existing request_id.
    2. For an existong offering/service.

     

    AD1. For already created/existing requests:
    First it is to retrieve the subscription_detail_id of the request:
    SELECT * FROM usm_subscription_detail
    where request_id=10017 and subscription_type=5

    From the result use the value of the column named 'id' in the next query:
    SELECT subscription_detail_id,form_elem_name,form_elem_value,is_visible
    FROM  usm_request_item_form
    where subscription_detail_id=<your id value>

    Combined:
    SELECT subscription_detail_id,form_elem_name,form_elem_value,is_visible
    FROM  usm_request_item_form
    where subscription_detail_id
    in (SELECT id FROM usm_subscription_detail where request_id=10004 and subscription_type=5)

    Note: request_id is the input variable in this query.

    This should show all fields(form_elem_name)&values for that request_id.

    .........................................

     

    AD2. For an existing offering/service:
    Query the offering for existing form components:
    select distinct rd.text_1
    from usm_offering o,usm_offering_ratedef_inclusion i,usm_rate_definition rd
    where o.offering_name = 'Order new Team Site'
      and i.parent_id = o.offering_id
      and rd.rate_plan_id = i.rate_plan_id
      and rd.item_type = 14
    order by rd.text_1

    Query the form components for the offering, copy value of column text_1 into:
    SELECT form_entity_id,form_entity_name,form_entity_type,form_entity_path,form_comp_id,attr_name,attr_value
    FROM usm_form_entities,usm_form_component_attributes
    where form_comp_id = '34145' (or when multiple rows/values got returned "in ('34145','34158')")
      and usm_form_component_attributes.form_comp_id = usm_form_entities.form_entity_id
      
    Combined query:
    select fe.form_entity_id,fe.form_entity_name,fe.form_entity_type,fe.form_entity_path,fca.form_comp_id,fca.attr_name,attr_value
    from usm_form_entities fe, usm_form_component_attributes fca
    where fca.form_comp_id in (
    select distinct rd.text_1
    from usm_offering o,usm_offering_ratedef_inclusion i,usm_rate_definition rd
    where o.offering_name = 'Order new Team Site'
      and i.parent_id = o.offering_id
      and rd.rate_plan_id = i.rate_plan_id
      and rd.item_type = 14)
    and fca.form_comp_id = fe.form_entity_id

    Note: The input variable here is o.offering_name = 'Order new Team Site'.

    This should show all form-fields for that offering/service.

     

    Thanks and kind regards, Louis.



  • 4.  Re: Catalog WEBI Report

    Posted 06-27-2016 11:44 AM

    Hi Louis! Thank you so much for taking the time to answer my questions! i will do it, now i gotta try it out i have tried some things and are working fine.

     

    Regards.