Not Able to Apply Multiple Values to Prompt in Formula

User_8IP5M

• 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

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,