I'm designing a new dashboard, and I'm having trouble finding information for my specific issue.

There are three values that the users are prompted for: Federal Fiscal Year, Type, and Contracts.

The particular prompt I'm looking at right now is for Type. I looked into bins, but my understanding is since my groups have overlapping members, I can't use bins. So my current approach is to have a dashboard variable prompt where the user picks the group they want to look at.The options are:

• Educational Institutions and Affiliates
• Educational Institutions
• All Types

For reference, institutions can be one of three types:

• Educational Institution
• Affiliate
• Institution

I want their input to both decide the filtering for Type, and to assign an output variable called pv_inst_type_out that will be used in the Title View.

The logic for the filter application is:

When pv_inst_type_in = Educational Institutions and Affiliates then show type in Educational Institution, Affiliate

When pv_inst_type_in = Educational Institutions then show type in Educational Institution

When pv_inst_type_in = All Types then show all types

The logic for assigning the output variable is:

When pv_inst_type_in = Educational Institutions and Affiliates then pv_inst_type_out = 1

When pv_inst_type_in = Educational Institutions then pv_inst_type_out = 2

When pv_inst_type_in = All Types then pv_inst_type_out = 3

I was able to test to confirm that my input variables are being set by adding a column to look at their values. The values in that column change according to the prompt selection, as they should. To choose the proper filter and assign the output variable, I think I need to use case statements, but I don't know if they should go in a column formula, a column formula in a filter, or both. I'm having issues getting statements in either location to work properly. If I don't get a syntax error, I do get a SQLExecDirectW error when I try to view the results, whether I run it from the dashboard or not.

I'm working within some pretty tight constraints--I am new to this role and have only some experience with OBIEE and SQL, we have no one in the department with advanced OBIEE experience, we have no one in the department currently who can change the RPD or set up repository variables, and since these are very high-level analyses, changing how the groupings are organized or assessed isn't an option. The analyses themselves are very straightforward, but working in the organization that the stakeholders are comfortable with and used to is proving to be more difficult. As mentioned before, I haven't found anything looking at this particular issue. The closest I've found is that Rittman Mead blog about presentation variables, which offers some insight, but is so fundamentally different in its goals that it can't really be used as a logical template for this task.

Does anyone have any advice?

Thank you in advance.