Hi All,
I have a multi-select prompt which has academic_periods as values - for eg:- Fall 2018, Fall 2019, Spring 2018, Summer 2018, etc. In a query I want the filter to filter down on Academic_Periods of 'Fall & Summer' together when Fall term is selected in the prompt and otherwise just the same term from the prompt. 

For eg:- If I choose Fall 2018 in the prompt, I want the query to select data from (Fall 2018, Fall 2018-80-> (i.e Summer 2017)). The filter that I have in place is -

if (?Academic Period Prompt? like '%10')
then ([ACADEMIC_PERIOD] in (to_char(?Academic Period Prompt?-80),?Academic Period Prompt?))
else ([ACADEMIC_PERIOD] in ?Academic Period Prompt?

It seems to be working fine when one Fall term is selected. But when I choose 'Fall 2018' and 'Fall 2019', it only filters out correctly for one fall term and not the other.

Would you know why this is happening? Don't filters with formula work with multi-select prompts in filter?

Thank You!

------------------------------
Nikhila Kuchibhotla
------------------------------

#CognosAnalyticswithWatson

The usual approach is to try to identify all the elements of the problem space and, by isolation, identify the source of the problem.

The fact that the statement executes allows you to look into query information (if you are in FM) or the show generated SQL (if you are in report studio) to see the SQL.   If you look at that you could get more information which could help you narrow down the problem.

My first guess would be to look at the to_char bit of your filter.

Syntax such as this might be better.

in (?Academic Period Prompt? +'-80', ?Academic Period Prompt?)

I was able to create a prompt filter in a query subject using the sample model with this filter expression. I fed the prompt with 'Hibernator' and it filtered my query subject by the product names 'Hibernator' and 'Hibernator Extreme' .  You will notice the space just before 'Extreme' in the expression.

[Business View].[Copy of Products with prompt].[PRODUCT_NAME] in ( ?prompt? +' Extreme', ?prompt? )

Hope that helps.

------------------------------
IAN HENDERSON
------------------------------

Original Message:
Sent: Mon February 03, 2020 12:14 PM
From: Nikhila Kuchibhotla
Subject: Filter on multi-select prompt values

Hi All,
I have a multi-select prompt which has academic_periods as values - for eg:- Fall 2018, Fall 2019, Spring 2018, Summer 2018, etc. In a query I want the filter to filter down on Academic_Periods of 'Fall & Summer' together when Fall term is selected in the prompt and otherwise just the same term from the prompt. 

For eg:- If I choose Fall 2018 in the prompt, I want the query to select data from (Fall 2018, Fall 2018-80-> (i.e Summer 2017)). The filter that I have in place is -

if (?Academic Period Prompt? like '%10')
then ([ACADEMIC_PERIOD] in (to_char(?Academic Period Prompt?-80),?Academic Period Prompt?))
else ([ACADEMIC_PERIOD] in ?Academic Period Prompt?

It seems to be working fine when one Fall term is selected. But when I choose 'Fall 2018' and 'Fall 2019', it only filters out correctly for one fall term and not the other.

Would you know why this is happening? Don't filters with formula work with multi-select prompts in filter?

Thank You!

------------------------------
Nikhila Kuchibhotla
------------------------------
#CognosAnalyticswithWatson

Hi Ian,
Sorry, I should have mentioned that it is not concatenate that I am trying to do, but to actually have a mathematical formula. The numerical code for Fall 2018 is 201910 and the numerical code for Summer 2018 is 201830. From selecting Fall 2018 in the prompt, I want the data from a query to include data linked with Fall 2018 and Summer 2018 and thus the calculation of (201910-80=201830). With the formula in place, it seems to be correctly calculating when I choose one Fall term, but when I choose fall 2018 and Fall 2019, I need it to pick both summer terms associated with each of the Fall terms, but it only selects one of the two and proceeds with the formula.

Thank You!

------------------------------
Nikhila Kuchibhotla
------------------------------

Original Message:
Sent: Tue February 04, 2020 08:34 AM
From: IAN HENDERSON
Subject: Filter on multi-select prompt values

The usual approach is to try to identify all the elements of the problem space and, by isolation, identify the source of the problem.

The fact that the statement executes allows you to look into query information (if you are in FM) or the show generated SQL (if you are in report studio) to see the SQL.   If you look at that you could get more information which could help you narrow down the problem.

My first guess would be to look at the to_char bit of your filter.

Syntax such as this might be better.

in (?Academic Period Prompt? +'-80', ?Academic Period Prompt?)

I was able to create a prompt filter in a query subject using the sample model with this filter expression. I fed the prompt with 'Hibernator' and it filtered my query subject by the product names 'Hibernator' and 'Hibernator Extreme' .  You will notice the space just before 'Extreme' in the expression.

[Business View].[Copy of Products with prompt].[PRODUCT_NAME] in ( ?prompt? +' Extreme', ?prompt? )

Hope that helps.

------------------------------
IAN HENDERSON

Original Message:
Sent: Mon February 03, 2020 12:14 PM
From: Nikhila Kuchibhotla
Subject: Filter on multi-select prompt values

Hi All,
I have a multi-select prompt which has academic_periods as values - for eg:- Fall 2018, Fall 2019, Spring 2018, Summer 2018, etc. In a query I want the filter to filter down on Academic_Periods of 'Fall & Summer' together when Fall term is selected in the prompt and otherwise just the same term from the prompt. 

For eg:- If I choose Fall 2018 in the prompt, I want the query to select data from (Fall 2018, Fall 2018-80-> (i.e Summer 2017)). The filter that I have in place is -

if (?Academic Period Prompt? like '%10')
then ([ACADEMIC_PERIOD] in (to_char(?Academic Period Prompt?-80),?Academic Period Prompt?))
else ([ACADEMIC_PERIOD] in ?Academic Period Prompt?

It seems to be working fine when one Fall term is selected. But when I choose 'Fall 2018' and 'Fall 2019', it only filters out correctly for one fall term and not the other.

Would you know why this is happening? Don't filters with formula work with multi-select prompts in filter?

Thank You!

------------------------------
Nikhila Kuchibhotla
------------------------------
#CognosAnalyticswithWatson