Max component number

building reports desktopintelligence
1

Hi All,

I have a report with 3 fields,

Name ( name of Employee)
Component Number ( Department Number)
Project Start date( date when the person joined the department)

Now when I run the query, I get 3 components for the same employee.

At present it looks like this…

Name Comp Num Start Date

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

2

Hi Ashish!

What will happen if you use Max() function for the date object in the universe and use this object in the report??? Please let me know.

Thanks
Ravi.

______________________________________________________ Get Your Private, Free Email at http://www.hotmail.com

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

3

I get a #ERROR in the date column.
Apart from that I have to make the date object as a measure object.

Ashish

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

4

Ashish,

Why don’t you add it as a condition to the universe and use it in your query ?
Condition would look like : ( I assume the table name as EMP_DEPT)

EMP_DEPT.START_DATE = (select max(start_date)
from EMP_DEPT
where Name = EMP_DEPT.Name
)
Regards

Jay

Ashish Wrote:

have a report with 3 fields,

Name ( name of Employee)
Component Number ( Department Number)
Project Start date( date when the person joined the department)

Now when I run the query, I get 3 components for the same employee.

At present it looks like this…

Name Comp Num Start Date

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

5

Ramakanth,

This really helped me.
But this hold good when the object is a measure or a date. What if I need the highest degree of a person…viz…

Name Degree

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

6

Ramakanth,

I tried doing this with another report…but I am unable to see the list of objects in the window for "Synchronize your calculation : For each value of one or more objects ". What do I do ?

Ashish

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

7

I suggest you create a new object (numeric) in your universe called “Qualification rank”, based on a decode statement (if your using oracle). Decode(Type_of_degree,‘DOCTORATE’,2,‘MASTERS’,4,‘DEGREE’,6,8)

Leave room for growth (if your CEO get joins a masonic club you might want to weight that accordingly) !

You can set this object as a “detail” object (I just love finding uses for those green triangles) to “hide” it from normal view, and your away (you can even use it as a sort squence when printing out all qualifications)

Simon

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

8

Thats a good sugestion.
But the problem is that I am accessing universe owned by some other department who wouldnt allow me to change in the universe.
So I should be doing it only as a user from the reporter module. Can I define a UDO that way and do that ? I actually tried creating a UDO but the decode doesnt have enough parameters to do that.

Please help.

Ashish

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