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.