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]') )
I would try replacing the "if" with "or" conditions that cover the possible scenarios.
(
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')
)
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]'))
Quote3.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?
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.
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. ::)
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.. :(