We have a range of reports structure the following way for which we need to add a cascading prompt with List of Values based on a first level : Product Family and second level : Order Type.
All our existing reports have a summary query (Created as a table in Data Foundation as free hand SQL ) and a Detail query (Create as a table in Data Foundation as free hand SQL and link by an hyperlink in Crystal report).
Each of the report have about 4 existing prompts.
We cant seem to be able to add at this stage any cascading prompt to the existing Report. Could you please advise how this could be done ?
Also your documentation mentions that :
You cannot create a cascading list of values that is based on a Business View which in turn depends on another list of values (for example a dynamic prompt used in the Data Foundation or Business Element). Ifthis list of values is used for a parameter, then the parameter prompting will not work.
Example of Summary Query: FULMOR010_SUMMARY
SELECT (SERV.LAST_UPD_AGE1440) AS AVG_AGE, --Avg time to close service orders
(SERV.LAST_UPD_AGE1440) AS MED_AGE, – Median time to close service orders
trunc(SERV.LAST_UPD_DATE, ‘HH’) + trunc(to_number(to_char(SERV.LAST_UPD_DATE, ‘MI’)) / 15) / 96 AS JOIN_DATE_TIME_SUM,
SPEC.HLR_PROFILE_ID AS HLR_PROFILE_ID,
SERV.SERV_ORD_STTS_DESC AS SERV_ORD_STTS_DESC,
– The following parameters are included in the select query because
– selecting the columns from the table directly will break the ‘WHERE EXISTS’ clause
‘{?Orderable Item}’ AS CFS_SPEC_DESC,
‘{?Amdocs Process Type}’ AS PROV_ORD_ACT_TYPE_DESC,
SPEC.PROD_SPEC_NAME AS PROD_SPEC_NAME ,
OFF.LOB_DESC AS PRODUCT_FAMILY
FROM EAM_SERV_ORD SERV
JOIN EAM_ORCH_SERV_ORD ORCH ON ORCH.ORCH_SERV_ORD_ID = SERV.ORCH_SERV_ORD_ID
JOIN EAM_PROD_ORD PROD ON PROD.PROD_ORD_ID = ORCH.PROD_ORD_ID
JOIN EAM_ORD_LINE_ITEM LINE ON LINE.PROD_ORD_ID = PROD.PROD_ORD_ID
JOIN EAM_PROD_OFF OFF ON OFF.PROD_OFF_ID = LINE.PROD_OFF_ID
JOIN EAM_OLI_PROD_OFF OLI_OFF ON OLI_OFF.PROD_OFF_ID=OFF.PROD_OFF_ID
JOIN EAM_PROD_SPEC SPEC ON SPEC.PROD_SPEC_ID = OLI_OFF.PROD_SPEC_ID
– This report is for Completed service orders
WHERE SERV.SERV_ORD_STTS_DESC = ‘Complete’
– Report displays data for the last 12 complete hours
AND SERV.LAST_UPD_DATE BETWEEN TRUNC(cast(systimestamp at time zone ‘GMT’ as DATE),‘HH’) - 0.6 AND (cast(systimestamp at time zone ‘GMT’ as DATE))
– If a product order has multiple order line items, only 1 order line item will be returned (with plan_ind = 1)
AND OFF.PLAN_IND = 1
AND OFF.LOB_DESC = ‘{?Product Family}’
– Mandatory Prompt
AND SPEC.PROD_SPEC_NAME = ‘{?Order Type }’
– Optional Prompt
AND (SPEC.HLR_PROFILE_ID = ‘{?HLR ID}’ OR ‘{?HLR ID}’ is null)
– Where exists clause to ensure the FIRST occurance is returned rather than ALL occurances to improve performance
AND EXISTS (SELECT /+ no_unnest ordered use_nl(h)/ PROV.PROV_ORD_ACT_TYPE_DESC,
CFS.CFS_SPEC_DESC
FROM EAM_PROV_ORD PROV
JOIN EAM_CFS_SPEC CFS ON CFS.CFS_SPEC_ID = PROV.CFS_SPEC_ID
WHERE PROV.SERV_ORD_ID = SERV.SERV_ORD_ID AND PROV.PEI_NAME = SERV.PEI_NAME
– changed the above line from PROV.SERV_ORD_PEI_NAME to PROV.PEI_NAME for ALDM 4.2 changes
– Mandatory Prompt
AND CFS.CFS_SPEC_DESC = ‘{?Orderable Item}’
– Mandatory Prompt
AND PROV.PROV_ORD_ACT_TYPE_DESC = ‘{?Amdocs Process Type}’
)
–GROUP BY SPEC.HLR_PROFILE_ID,
– SERV.SERV_ORD_STTS_DESC,
– SPEC.PROD_SPEC_NAME,
– trunc(SERV.LAST_UPD_DATE, ‘HH’) + trunc(to_number(to_char(SERV.LAST_UPD_DATE, ‘MI’)) / 15) / 96
Example of Detail Query: FULMOR010_DETAIL
SELECT TRUNC(SERV.LAST_UPD_DATE, ‘HH’) AS JOIN_DATE_TIME_DET, – To join with last 12 hours table
SERV.LAST_UPD_DATE, --Service Order Completion Date/Time
SPEC.HLR_PROFILE_ID, --HLR ID
SERV.SERV_ORD_ID, --Service Order No
SERV.SERV_ORD_STTS_DESC, --Service Order Status
SERV.LAST_UPD_AGE * 1440 AS AGE, --Completion time(M)
SPEC.PROD_SPEC_NAME, --Order Type
SERV.CRE_DATE, --Service Order Creation Date/Time
PROV_OUT.PROV_ORD_ACT_TYPE_DESC, --Amdocs Process Type
CFS_OUT.CFS_SPEC_DESC, --Orderable item
– included for Bug 5848
LINE.ORD_LINE_ITEM_ID – Order line item ID
FROM EAM_SERV_ORD SERV
JOIN EAM_ORCH_SERV_ORD ORCH ON ORCH.ORCH_SERV_ORD_ID = SERV.ORCH_SERV_ORD_ID
JOIN EAM_PROD_ORD PROD ON PROD.PROD_ORD_ID = ORCH.PROD_ORD_ID
JOIN EAM_ORD_LINE_ITEM LINE ON LINE.PROD_ORD_ID = PROD.PROD_ORD_ID
JOIN EAM_PROD_OFF OFF ON OFF.PROD_OFF_ID = LINE.PROD_OFF_ID
JOIN EAM_OLI_PROD_OFF OLI_OFF ON OLI_OFF.PROD_OFF_ID = OFF.PROD_OFF_ID
JOIN EAM_PROD_SPEC SPEC ON SPEC.PROD_SPEC_ID = OLI_OFF.PROD_SPEC_ID
JOIN EAM_PROV_ORD PROV_OUT ON PROV_OUT.SERV_ORD_ID = SERV.SERV_ORD_ID AND PROV_OUT.PEI_NAME=SERV.PEI_NAME
– changed the above line from PROV_OUT.SERV_ORD_PEI_NAME to PROV_OUT.PEI_NAME for ALDM 4.2 changes
JOIN EAM_CFS_SPEC CFS_OUT ON CFS_OUT.CFS_SPEC_ID = PROV_OUT.CFS_SPEC_ID
– This report is for Completed service orders
WHERE SERV.SERV_ORD_STTS_DESC = ‘Complete’
– Report displays data for the last 12 complete hours
AND SERV.LAST_UPD_DATE BETWEEN TRUNC(cast(systimestamp at time zone ‘GMT’ as DATE),‘HH’) - 0.6 AND TRUNC(cast(systimestamp at time zone ‘GMT’ as DATE),‘HH’)
– If a product order has multiple order line items, only 1 order line item will be returned (with plan_ind = 1)
AND OFF.PLAN_IND = 1
AND EXISTS (SELECT /+ no_unnest ordered use_nl(h)/ PROV_IN.PROV_ORD_ACT_TYPE_DESC,
CFS_IN.CFS_SPEC_DESC
FROM EAM_PROV_ORD PROV_IN
JOIN EAM_CFS_SPEC CFS_IN ON CFS_IN.CFS_SPEC_ID = PROV_IN.CFS_SPEC_ID
WHERE PROV_IN.SERV_ORD_ID = SERV.SERV_ORD_ID AND PROV_IN.PEI_NAME = SERV.PEI_NAME
– changed the above line from PROV_IN.SERV_ORD_PEI_NAME to PROV_IN.PEI_NAME for ALDM 4.2 changes
– Passed from the summary report
AND CFS_IN.CFS_SPEC_DESC = ‘{?Orderable Item for Subreport}’
–Passed from summary report
AND PROV_IN.PROV_ORD_ACT_TYPE_DESC = ‘{?Amdocs Process Type for Subreport}’
)
– Passed from summary report
AND SPEC.PROD_SPEC_NAME = ‘{?Order Type for Subreport}’
– Passed from summary report
AND (’{?HLR ID for Subreport}’ I
gogo (BOB member since 2005-06-03)