crosstab sorting issue

[system]

I’ve got a small problem which I believe may not be possible in BI but thought I could get some feedback.

I have a crosstab report which needs to be sorted by the last fiscal year.
F2011 in this example below.

         F2009       F2010       F2011
city1      $11         $1           $3
city2       $5         $4           $4
city3       $3         $3           $1
city4      $10         $2           $5

I have created a 4th column with this formula.
=sum() where ( = 2011)
I could sort and hide and all is well but…
I don’t want to hard code my fiscal year. I would like it to be dynamic
So I created a demension called which is value of the user response prompt. Here is what I would expect to use as the formula.

=sum() where ( = )

Which doesn’t work.

Am I wasting my time trying to get this to work? What other methods could I try?

Thanks

---

mboaler (BOB member since 2009-11-05)

[system]

Welcome to B:bob:B! Please check this FAQ.

---

Jansi (BOB member since 2008-05-12)

[system]

Thanks for the quick reply but I still don’t see a valid way following the example in th FAQ.

If I create a flat report with the data in the crosstab I have this:

<location>  <fees> <fiscalyear> <year>  
city1              $4          2009          2011
city1              $3          2010          2011
city1              $7          2011          2011
city2              $1          2010          2011
city2              $2          2011          2011

I don’t see a way to create a second variable to get a 1 or 0 value.
If I follow the example in the FAQ using a greater than I still have to hard code a year into the formula which I’m trying to avoid.

Ideas?
Thanks

[/code]

---

mboaler (BOB member since 2009-11-05)