Situation
In my report I retrieve data for months 1 to 4. The sum of this is shown in a column but I also need a column with the value for the last month in the range (in this case 4). So in 1st column months 1 till 4 and in 2nd column only month 4.
Problem
Because I’ve got data for 4 months I thought to use the Where, in my case like this:
=<Realisation(Real with Um1a0006)> Where (<MonthNum>=<ParMonth>)
Explanation: <Realisation(Real with Um1a0006)> is the measure, is a dimension with the month from the query, contains the user parameter with the month.
But maybe you guessed , I get the message “Syntax Error DMB0007”. I checked that both variables and are numeric. When I use
=<Realisation(Real with Um1a0006)> Where (<MonthNum>=4)
or
=<Realisation(Real with Um1a0006)> Where (<ParMonth>=4)
there is no problemo .
Any ideas for a solution or a workaround (a troublesome workaround would be to create a separate query for last months data).
My BO version is 6.5.1 full client with SQL Server database.
=If (<ParMonth> = <MonthNum>) Then <Realisation(Real with Um1a0006)>
and I got a computation error …
To avoid the possible numeric problems/conversions I created to objects with the derived monthname (character), but with the same result: a syntax error …
Avoid using the IF…THEN…ELSE solution if possible as you would have to have all referenced dimensions in your block/table. Instead, look at this Reporter FAQ entry.
It’s a good workaround, although at first it didn’t work for me (at first) (I got a “Variables are not compatible”). That is another way helps, not always direct but stimulating further digging and trial and error.
The reason it didn’t work was that the 2 objects I use are from different queries . Now they are in one query and voila, it works!