Derived Field - Rows to Columns

[roseygirl]

I have read several responses to questions about getting rows to columns, but they just say "create a derived field." What exactly should the derived field look like? For example, I am trying to get the benefit plan an employee is enrolled in to show like this:
Medical Dental Vision
A Rose BCBS CIGNA Waive

I created a derived field to test plan type and display the plan name, but I still get three rows per employee and I only want one row. Help!

 

[dlblack]

Here is a good place to start with some questions.

http://www.srs-inc.com/support/HowDoI/start.htm

If you are using Oracle (SQL is slightly different), here is a tip out of the book that might help:

Example 3: Accumulators
Usually, ReportSmith displays one row for each of an employee’s accumulators and its
associated amount (see the first sample below). Instead, you want to display one column
for accumulator 52 and another for accumulator 45 (see the second sample below). To
do this, you would use the following derived field statements in separate derived field
calculations.
SUM (DISTINCT DECODE (" REPORTS"." V_ YTD_ ACCUMULATOR".
"SPECIALACCUMCODE", '52', "REPORTS"." V_ YTD_ ACCUMULATOR".
"SPECIALACCUMAMT", 0)
SUM (DISTINCT DECODE (" REPORTS".” V_ YTD_ ACCUMULATOR".
"SPECIALACCUMCODE", '45', "REPORTS"." V_ YTD_ ACCUMULATOR".
"SPECIALACCUMAMT", 0)

You will also have to select your table column as "Include only for use in query" and select "Group data before loading into report".

Hope this gives you some ideas.




David Black, CSQA
Sr. Systems Analyst
Teepak, LLC