Filtering a sub-set of report results

Question

Hi All,

I'm working on a project on Oracle Business Intelligence 12.2.1.4.0.

I'm trying to create a Budget V's Actual report where the requirements are to show Actual, Budget and Encumbrance (Per ACTUAL_FLAG_CODE Column which is either A, E, or B)  and would like to be able to filter on Budgets that are a certain "Budget Name" but still include Actuals and Encumbrance (so I can't set a report filter/PV as this would filter on ONLY Budget name). I have a presentation variable set up in a prompt for Budget Name @{P_BUDGET} And am trying to filter out where the Budget name is not @{P_BUDGET}

I've tried a few different column formulae and have run out of ideas, so I'm looking for some help. I have the following:

CASE WHEN "Fact - XXFSS GL Line Details"."ACTUAL_FLAG_CODE" = 'A' THEN SUM("Fact - XXFSS GL Line Details"."AMOUNT") WHEN "Fact - XXFSS GL Line Details"."ACTUAL_FLAG_CODE" = 'B' THEN FILTER(SUM("Fact - XXFSS GL Line Details"."AMOUNT") USING ("Fact - XXFSS GL Line Details"."BUDGET_NAME" IN ('@{P_BUDGET}'))) WHEN "Fact - XXFSS GL Line Details"."ACTUAL_FLAG_CODE" = 'E' THEN SUM("Fact - XXFSS GL Line Details"."AMOUNT") END

This doesn't cause any errors, however when I set the Budget Name in the prompt as "CURRENT BUDGET" for example, I can still see other Budgets IE: ORIGINAL BUDGET and the full amounts. If OBI wasn't reading the PV I'd expect to see an error, which I don't get, problem is, the filter doesn't seem to operate so I'm stumped.

(I don't have a report filter to say Budget Name is prompted or =@{P_BUDGET} as I would assume this will filter the whole dataset on Budgets.)

Thanks all,

Lee

Lee Gledhill · Answer

OK I've fixed this now by simply adding a filter for 'Is Prompted' OR 'IS NULL'. Sometimes you get so wrapped up in one idea you can't see the wood for the trees.

I'm still intrigued as to why this (Thoroughly messy I know) formula doesn't operate?