Oracle Analytics Publisher (BIP)

Products Banner

Not Able to Apply Multiple Values to Prompt in Formula

Received Response
31
Views
1
Comments
  • Dashboard has 3 reports with a prompt
  • Report 1 returns values for the agency selected in the prompt
  • Report 2 is benchmark values (all hard coded; not impacted by prompt)
  • Report 3 returns percentages comparing the selected agency against all agencies (ex. selected agency's premium is 1.5% of premium for all agencies)
  • Reports 1 and 3 are union reports using 2 different subject areas


  • The dashboard works as expected currently, however the user requested the ability to select multiple agencies in the prompt
  • Not a problem for Report 1 because the agency is filtered on the report level without manual calculations
  • Not a problem for Report 2 because everything is hard coded
  • However Report 3 returns zeroes for all of the fields because of the % calculation and formatting errors


For example the formula for "Sales Under 40" is:


FILTER(FILTER("Premium EAS"."Total Collected Premium" USING ("Policy"."Owner Issue Age" <= 40)) USING ("AppSigned Agent"."AppSigned Agency Name" IN ('@{BM_AGENCY}'))) / FILTER("Premium EAS"."Total Collected Premium" USING ("Policy"."Owner Issue Age" <= 40))


If I remove the quotes around BM_AGENCY, I get the error below. But by keeping the quotes around BM_AGENCY, it can't read multiple values passed, it tries to read them all as one string.


State: HY000. Code: 27045. [nQSError: 27045] Nonexistent column: "(Selected Agency in Prompt)". (HY000)



HELPPPPPPPP

Answers

  • Bhaskar Konar
    Bhaskar Konar ✭✭✭✭✭

    Hi @User_8IP5M,

    Can you please try the following formula and see if there's any betterment?

    FILTER(FILTER("Premium EAS"."Total Collected Premium" USING ("Policy"."Owner Issue Age" <= 40)) USING ("AppSigned Agent"."AppSigned Agency Name" IN ( @{BM_AGENCY}['@']{'Sample Agency'} ))) / FILTER("Premium EAS"."Total Collected Premium" USING ("Policy"."Owner Issue Age" <= 40))
    

    Hope this help.

    Cheers,