A colleague and I have wrestled for a while now with trying to enhance a prompt embedded within an Oracle Analytical Function.
We would like to offer our uses the ability to compute Moving Averages over 3, 6 or 12 month period. As Oracle considers the current month part of the set, our prompts are 2, 5 or 11. This is aesthetically not very pleasing and we would like the prompt to show 3, 6 or 12 with 1 being subtracted by Business Objects. We have tried a number of ideas, so far without success. Can anyone on the forum come up with a solution?
Here is what we have so far
SELECT
SUM(COUNT (DISTINCT EVENT.SYSTEM_EVENT_NUMBER)) OVER (PARTITION BY nvl(BO_FINDING_GROUP_VW.BFG_FINDING_GROUP_NAME,BO_SYMPTOM_GROUP_VW.BSG_SYMPTOM_GROUP_NAME) ORDER BY TO_DATE(TO_CHAR(EVENT.EVENT_DATE_TIME,'YYYYMM'),'YYYYMM')
RANGE INTERVAL '@Prompt('Enter Monthly Period','N',{'2','5','11'},MONO,CONSTRAINED)' MONTH PRECEDING),
TIA
We would like to offer our uses the ability to compute Moving Averages over 3, 6 or 12 month period. As Oracle considers the current month part of the set, our prompts are 2, 5 or 11. This is aesthetically not very pleasing and we would like the prompt to show 3, 6 or 12 with 1 being subtracted by Business Objects. We have tried a number of ideas, so far without success. Can anyone on the forum come up with a solution?
Here is what we have so far
SELECT
SUM(COUNT (DISTINCT EVENT.SYSTEM_EVENT_NUMBER)) OVER (PARTITION BY nvl(BO_FINDING_GROUP_VW.BFG_FINDING_GROUP_NAME,BO_SYMPTOM_GROUP_VW.BSG_SYMPTOM_GROUP_NAME) ORDER BY TO_DATE(TO_CHAR(EVENT.EVENT_DATE_TIME,'YYYYMM'),'YYYYMM')
RANGE INTERVAL '@Prompt('Enter Monthly Period','N',{'2','5','11'},MONO,CONSTRAINED)' MONTH PRECEDING),
TIA