filtering a crosstab rpt- define/complex/formula/boolean/conditional 1

[fghNoVaDC]

My Business Objects report is comprised of many crosstabs. For many of the crosstab tables, a simple filter placed on a particular "Value" will accurately display the desired report table. However, several crosstab tables require conditional/complex filters. I think these are set up similar to the simple filters by navigating through the menu path: Format/Filters/Define/Formula? However I do know how to construct the formula with proper syntax &/or boolean requirements. My raw data is large in volume and has numerous columns. I am seeking to report the cell in the column <Resource Amount YTD> for all rows except where certain column/cell criteria or conditions exist. Any insight as to whether I'm on the right path, or how I can correctly report the desired reported result, or a place to reference, or learn, etc. would be greatly appreciated. The gentleman at Knightsbridge seems to be very knowledgable. Please send questions if I can provide any additional details or further clarify in any way. Thank you in advance for any and all assitance with this sticking point.
Specifics are as follow:
I am seeking to report the cell in the column <Resource Amount YTD> for all rows except where a row has "ALLOC_EXP" or "ALLO_EXP" in the <Proj. Activity ID> column AND "MODIF" in the <Proj. Type Code> column AND "ELA" in the <Analysis Type> column, or where a data row has "ALLOC_EXP" or "ALLO_EXP" in the <Proj. Activity ID> column AND "NSCON" in the <Proj. Type Code> column AND "ELA" in the <Analysis Type> column AND "CONST" in the <Resource Category> column. (-and a couple easier conditional filters, which I'm sure I could write once the prior is determined.)
In the afore mentioned menu path: Format/Filters/(Project Activity ID)Define/Formula, I have tried to use the following formula: =If(<Project Activity ID>InList("ALLOC_EXP","ALLO_EXP"))Then(<Resource Amount YTD>Where(<Project Type Code>="MODIF"And<Analysis Type>="ELA"+<Resource Amount YTD>Where(<Project Type Code>="NSCON"And<Analysis Type>="ELA"And<Resource Category>="CONST"))Else<Resource Amount YTD>. Can I even use an If/Then/Else in this area? Many questions, any initial advise?? Again, many thanx!
FH, frank_heisler@yahoo.com, (703)-624-5273

 

[blom0344]

Filtering in BO has a mind of it's own. The actual action is on the underlying dataset.
That is the reason one cannot filter on any object that is the outcome of an aggregate/min/max.

The use of the 'where' clause is something special to. Do not make the mistake to think in terms of SQL. In SQL the where clause can consist of very complex statement. In BO it is down to:

example:

=Sum(<Amount>) Where <Year> = 2005

You cannot use anything other than a constant expression after the where clause.

AFAIK mixing the 'IF THEN ELSE' with the WHERE construction will not work in your example, but you can use nested IF THEN ELSE constructions:

= If (<Bedrijfsnummer als detail>=33) Then "SEH" Else If (<Bedrijfsnummer als detail> = 11) Then "SEGH" Else If (<Bedrijfsnummer als detail>= 30)  Then "SBX" Else If (<Bedrijfsnummer als detail>=38) Then "HSF" Else ""

Ties Blom
Information analyst