BusinessObjects Board

Syntax error DMB0007 using Where

building reports desktopintelligence
#1

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 :lol: , I get the message “Syntax Error DMB0007”. I checked that both variables and are numeric. :yesnod: When I use

=<Realisation(Real with Um1a0006)> Where (<MonthNum>=4)

or

=<Realisation(Real with Um1a0006)> Where (<ParMonth>=4)

there is no problemo :roll_eyes: .

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.

HenkK

HenkK :netherlands: (BOB member since 2004-03-02)

#2

Just say If = Then …well try it anyway.

Nick Daniels :uk: (BOB member since 2002-08-15)

#3

Nick,

I tried this:

=If (<ParMonth> = <MonthNum>) Then <Realisation(Real with Um1a0006)>

and I got a computation error … :frowning:
To avoid the possible numeric problems/conversions I created to objects with the derived monthname (character), but with the same result: a syntax error …

I don’t know anymore … :roll_eyes: :roll_eyes:

HenkK

HenkK :netherlands: (BOB member since 2004-03-02)

#4

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.

Andreas :de: (BOB member since 2002-06-20)

#5

Andreas,

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 :mrgreen: 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 :wah: . Now they are in one query and voila, it works! :yesnod:

Thanks,
HenkK

HenkK :netherlands: (BOB member since 2004-03-02)