Derived Field

[jchriste]

I am trying to take a report that has multiple rows per employee because of education, e.g., 3 degrees and show the data in a single row for each person, how do I do this?

 

[sa113]

Need to know how to do the same thing...I know you have to assign an alias and however what if the tables I am using both have multiple rows and need an outer join as well? Example, I would like to create a report that lists the review ratings for each employee in each year on one row.

 

[DebChattin]

Don't know if you got answers on this, but this is what you do. You need a derived field for every row of data. For example, if you have 3 rows with 3 different deduction codes you write a derived field for each deduction code. Then you write a fourth derived field adding up all the three other derived fields you wrote. The fourth derived field will give you all the data you need. Hope this makes sense to you.

 

[sa113]

Thanks for the tip however do you know how I would have all three in their own colmns? I would like one row and 3 columns with each code instead of 3 rows per employee.

Thanks

 

[DebChattin]

I am not sure if this is what you mean, but if you write a derived field for each row, you will get a column for each row, since the results of the derived field show up in colums.

 

[JMontv]

We are also trying to create reports that will return a single row of data when multiple rows are returned (as in the perfomance review example listed above). Does anyone have examples of how this was done? Any help would be appreciated.

 

[DebChattin]

This is what I have learned in getting only on row of data. You should write a derived field for each row of your data. Let's say you have a row of data for Date A, a row of data for Date B and a row of data for Date C.

Write a derived field for Date A, a derived field for Date B, and a derived field for Date C. Then you write a 4th derived field that adds Date A, Date B and Date C. The results will be your row of data.
Make sense? Let me know.

 

[Summerclouds]

In order to get one row of data you need to create aggregate (ie SUM/MAX)derived fields for each column you want to retreive. From there you need to query on the fields that are unique in the education table/deduction table etc. Then turn on database grouping.