CASE WHEN in a detail filter

[Danielbryanuk]

Hi all

Using 8.3 Cognos report studio.

I was someone could tell what is wrong with the follwing case statement, used in a detail filter in a report:

"CASE

WHEN ?pID? between 73 and 84
THEN ([GMT Product Overview by Asset Class].[Product Overview Facts by Asset Class].[Year] = 2006)

WHEN ?pID? between 85 and 96
THEN ([GMT Product Overview by Asset Class].[Product Overview Facts by Asset Class].[Year] = 2007)

WHEN ?pID? between 97 and 108
THEN ([GMT Product Overview by Asset Class].[Product Overview Facts by Asset Class].[Year] = 2008)

ELSE ([GMT Product Overview by Asset Class].[Product Overview Facts by Asset Class].[Year] = 2009)
END"

I'm not sure if I should be using such a construct in a detail filter? Does the THEN or ELSE clause work in brackets?

Thanks for your help
Dan

 

[blom0344]

AFAIK the THEN and ELSE are supposed to return a value and not a boolean (true/false). The boolean part is supposed to be handled by the WHEN expression. What are you trying to accomplish with the filter?

I think the filter should be like:

(?pID? between 73 and 84 and ([GMT Product Overview by Asset Class].[Product Overview Facts by Asset Class].[Year] = 2006))
OR
(?pID? between 85 and 96 and ([GMT Product Overview by Asset Class].[Product Overview Facts by Asset Class].[Year] = 2007))
OR
(?pID? between 97 and 108 and ([GMT Product Overview by Asset Class].[Product Overview Facts by Asset Class].[Year] = 2008))
OR
(?pID? not between 73 and 108 and
([GMT Product Overview by Asset Class].[Product Overview Facts by Asset Class].[Year] = 2009))

Ties Blom

 

[Danielbryanuk]

Sorry Ties, should have been clearer, it was quite late when I posted!!

The objective is to filter the year of my selected data items depending on the value of pID, thus

when pID is between x and y
then filter data for year = z

I am therefore trying to use the THEN and ELSE clauses to return the filter expression. Is this wrong? Can you think of a workaround?

Thanks
Dan

 

[blom0344]

Did you read my reply? Did you try the filter example?



Ties Blom

 

[Danielbryanuk]

Not my day today, I didn't read your post correctly - it seems to work, (in that I don't have an error message), but it is taking a lot longer to run the report now.

I'll keep you posted if it works. Any ideas on improving the performance?

Thanks very much Ties

 

[blom0344]

Best performance is attained with cascading prompts. In your case you would need to establish a relationship between the pID range and the year in a seperate table or cognos view. This however is not nice to maintain, but performance is a hullovalot faster as only one part of the filter will be excecuted against the dbase..

The 'not between' operator is usually a real performance killer, as it kills index access to tables. This will be a problem with large facts

Ties Blom