BusinessObjects Board

Two objects in WHERE condition at Report level variable?

building reports desktopintelligence
#1

I want to create a report level variable based on the values of two other objects.

The formula for Variable I am trying to create looks like this.

=<Object1> Where (<Object2> = <Object3)

But BO gives a syntax error for . I think it expects a constant value as second operand.
Is there any work around or technique to achieve this?

Thanks,
-Manish

mkumar (BOB member since 2002-08-26)

#2

Please, take a look at the FAQ Reporter…

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

#3

Hmmm, that explains it well.

Here is my problem. I hope somebody could help me with the solution.

I want to show Current Day and Previous Day’s data in two separate columns in report. The data is stored in a single column in database.
The current date is entered by user via a prompt.
If the user selects 01/04/2004, the report should look like this.


Date               Current Data              Previous Data
01/04/2004          10                                 6
01/03/2004           6                                  8
01/02/2004           8                                  7

If the user selects 01\03\2004, the report should look like this.


Date               Current Data              Previous Data
01/03/2004           6                                  8
01/02/2004           8                                  7
01/01/2004           7                                 10

The database looks like this.


Date                 Data Value
01/04/2004           10                                
01/03/2004           6                                 
01/02/2004           8                                 
01/01/2004           7
31/12/2003           10

Any ideas how this can be achieved with a single data provider. Using two DPs will definitely reduce the performance.

mkumar (BOB member since 2002-08-26)

#4

Basic sketch of how I would approach this:

You’ll have to create two flags, one called (for example) Current Day Flag and another for Previous Day Flag. Those will be based on a comparison between the data from your data provider and the prompt… if the day = prompt date then 1 for current, if relative_date(day, +1) = prompt then previous day flag = 1. Then you can use the Where clause with the two flags.

Dave Rathbun :us: (BOB member since 2002-06-06)

#5

Using Plain SQL we can do something like this to get the desired result… See if you can make it for your use…


SELECT
	DATEID,
	DATEVALUE,
	(SELECT DATEVALUE FROM TABLE1 A WHERE A.DATEID + 1 = TABLE1.DATEID) 
FROM
	TABLE1
WHERE
	DATEID <= TO_DATE('03-JAN-2004')
AND	DATEID >= TO_DATE('03-JAN-2004') - 2

Note:
Plz check the exact syntax.

Sri

Sridharan :india: (BOB member since 2002-11-08)