create a filter object using the max function

building reports desktopintelligence
1

Hello,
Do you know of a way of creating a filter object which will include a
function such as max,
That is to retrieve only records of let’s say activities that their date
field is the most recent.
When I tried to do so I got a message that grouping is not allowed in
conditions.

Thanks,

Ronen Baran
MIS, Tecnomatix
Tel: 972-9-9594856 Fax: 972-9-9544402

Listserv Archives (BOB member since 2002-06-25)

2

BusinessObjects allows you to use complex calculations in query conditions:

 - drag the date object to the query conditions in the query panel
 - as operator, select Equal to
 - as operand, select Calculation
 - you're now prompted to select an object you want to use in the

calculation: select date
- select Maximum from the available functions
- since you want to obtain a single result row per activity, select
Globally in the next step
- finally, select the object(s) for which you want to perform the
calculation for each value: for example ‘activity’ if you want to calculate
the maximum date for each activity

BO translates the calculation into a correlated subselect, which is exactly
what you need.

good luck,
mirko

Do you know of a way of creating a filter object which will include a
function such as max,
That is to retrieve only records of let’s say activities that their date
field is the most recent.
When I tried to do so I got a message that grouping is not allowed in
conditions.

Listserv Archives (BOB member since 2002-06-25)

3

Ronen,

I had a similar problem, but I was able to solve it by using a combination
of objects and native SQL. Here’s an example of a filter that gets the most
recent snapshot generation id using the max function. You should be able to
do something similar to this with your date field. Put something like this
into the Where section of the filter; it will parse ok.

@Select(Student Snapshot\snapshot generation id) = (select
max(informix.snpt_generation.snpt_gnrt_id) from informix.snpt_generation)

Do you know of a way of creating a filter object which will include a
function such as max,
That is to retrieve only records of let’s say activities that their date
field is the most recent.
When I tried to do so I got a message that grouping is not allowed in
conditions.

Dennis Edgecombe
Washington State University
dennis_edgecombe@wsu.edu

Listserv Archives (BOB member since 2002-06-25)