If Statement in Filter

[kenrisen]

Hi all cognos gurus,

I need to apply security level data in my framework manager file when i
use IF statement in my filter and run the report after publish the     
package, I got an error.                                               
                                                                       
I have to use conditional statement for my security (I attached my     
filter).                                                               
For example,                                                           
                                                                       
If year = 2012                                                         
then (Regional='1' and sales office in ('2','3','4'))                   
else (Regional ='1' and sales office in ('5','6','7'))                 
                                                                       
if i use that filter and i run the report sometimes i got 'XQE-GEN-0018 Query     
Service internal error has occurred'                                   
                                                                       
The report will run well when i delete the if statement                 
for example, Regional='1' and sales office in ('2','3','4')             
                                                                       
So I think I need to separate the if statement become two filter       
filter 1 = Regional='1' and sales office in ('2','3','4')               
filter 2 = Regional ='1' and sales office in ('5','6','7')             
                                                                       
can we map a cognos group to two filter?                               
for example,                                                           
If year = 2012                                                         
then (filter1)                                                         
else (filter2)

My real filter
IF ( substring ( [Trading Domestic - Profitability Figures].[Fiscal year/period].[Fiscal year/period].[Fiscal year/period].[Fiscal year/period - Fiscal year (Key)]   ,4,4) ='2012' ) THEN
    ([Trading Domestic - Profitability Figures].[Sales Region].[Sales Region].[Sales Region].[Sales Region]  contains '11' and
                [Trading Domestic - Profitability Figures].[Distribution Channel].[Distribution Channel].[Distribution Channel].[Distribution Channel]  contains '06' and
                [Trading Domestic - Profitability Figures].[Sales Office].[Sales Office].[Sales Office].[Sales Office]  in
        ('[0SALES_OFF].[S001]', '[0SALES_OFF].[S002]', '[0SALES_OFF].[S003]', '[0SALES_OFF].[S004]', '[0SALES_OFF].[S005]') )
ELSE
                ([Trading Domestic - Profitability Figures].[Sales Region].[Sales Region].[Sales Region].[Sales Region]  contains '11' and
                [Trading Domestic - Profitability Figures].[Distribution Channel].[Distribution Channel].[Distribution Channel].[Distribution Channel]  contains '06' and
                [Trading Domestic - Profitability Figures].[Sales Office].[Sales Office].[Sales Office].[Sales Office]  in
                ('[0SALES_OFF].[S001]', '[0SALES_OFF].[S002]', '[0SALES_OFF].[S003]', '[0SALES_OFF].[S004]', '[0SALES_OFF].[S005]', '[0SALES_OFF].[S006]', '[0SALES_OFF].[S007]') )
                                                     

[Lynn]

I would try replacing the "if" with "or" conditions that cover the possible scenarios.

Code Select Expand


(
  year = 2012 and Regional='1' and sales office in ('2','3','4')
)
or
(
  (year <> 2012 and Regional ='1' and sales office in ('5','6','7')       
)


[kenrisen]

Thx Lynn..but i still get the wrong result in my report..:(

When i use OR statement like you said , the result in my report is still the same with if statement.

I Attached the printscreen:
1.Right result (without any filter)

2.Right result (with a filter without IF statement or OR statement)
filter :
[Trading Domestic - Profitability Figures].[Sales Office].[Sales Office].[Sales Office].[Sales Office]  in ('[0SALES_OFF].[S001]', '[0SALES_OFF].[S002]', '[0SALES_OFF].[S003]', '[0SALES_OFF].[S004]', '[0SALES_OFF].[S005]')

3.Wrong result (using IF statement or OR statement)
filter using OR Statement:
(substring ( [Trading Domestic - Profitability Figures].[Fiscal year/period].[Fiscal year/period].[Fiscal year/period].[Fiscal year/period - Fiscal year (Key)]   ,4,4)= '2012' and [Trading Domestic - Profitability Figures].[Sales Office].[Sales Office].[Sales Office].[Sales Office]  in ('[0SALES_OFF].[S001]', '[0SALES_OFF].[S002]', '[0SALES_OFF].[S003]', '[0SALES_OFF].[S004]', '[0SALES_OFF].[S005]'))
OR
(substring ( [Trading Domestic - Profitability Figures].[Fiscal year/period].[Fiscal year/period].[Fiscal year/period].[Fiscal year/period - Fiscal year (Key)]   ,4,4) <> '2012' and [Trading Domestic - Profitability Figures].[Sales Office].[Sales Office].[Sales Office].[Sales Office]  in
('[0SALES_OFF].[S001]', '[0SALES_OFF].[S002]', '[0SALES_OFF].[S003]', '[0SALES_OFF].[S004]', '[0SALES_OFF].[S005]', '[0SALES_OFF].[S006]', '[0SALES_OFF].[S007]'))

[PRIT AMRIT]

3.Wrong result (using IF statement or OR statement)
filter using OR Statement:
(substring ( [Trading Domestic - Profitability Figures].[Fiscal year/period].[Fiscal year/period].[Fiscal year/period].[Fiscal year/period - Fiscal year (Key)]   ,4,4)= '2012' and [Trading Domestic - Profitability Figures].[Sales Office].[Sales Office].[Sales Office].[Sales Office]  in ('[0SALES_OFF].[S001]', '[0SALES_OFF].[S002]', '[0SALES_OFF].[S003]', '[0SALES_OFF].[S004]', '[0SALES_OFF].[S005]'))

Just another testing --- Separate the above condition and put two filters in your report querty i.e.

substring ( [Trading Domestic - Profitability Figures].[Fiscal year/period].[Fiscal year/period].[Fiscal year/period].[Fiscal year/period - Fiscal year (Key)]   ,4,4)= '2012'

[Trading Domestic - Profitability Figures].[Sales Office].[Sales Office].[Sales Office].[Sales Office]  in ('[0SALES_OFF].[S001]', '[0SALES_OFF].[S002]', '[0SALES_OFF].[S003]', '[0SALES_OFF].[S004]', '[0SALES_OFF].[S005]'))[/quote]

see if you are getting the correct result?

[kenrisen]

hi PRIT

If I separate the condition and put them to my report query the result is correct but I think,I cannot use that way coz I need to map the filter with cognos group (security level data like in Framework manager).

I have to do that because I want to restrict the data based on Cognos Group.

If I put the filter in report query, how to map the cognos group with the filter? and I am afraid it will impact to cognos report performance because framework will populate all data from SAP BW Cube first without filtering.

[PRIT AMRIT]

I understand you concern. I just wanted to narrow down and see what could be causing wrong result. As it is difficult to guess in such scenarios without having access FM model. ::)

[kenrisen]

Hi PRIT..

maybe if you want see my FM model i can send you the model..:)

I already tried using if statement, case, and OR

but still the result is not as expected.. :(