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!
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.
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_1from usm_offering o,usm_offering_ratedef_inclusion i,usm_rate_definition rdwhere 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 = 14order 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 fcawhere fca.form_comp_id in (select distinct rd.text_1from usm_offering o,usm_offering_ratedef_inclusion i,usm_rate_definition rdwhere 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.
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.