Customizing Group Names

[ProgressiveJL]

Hi all,

I'm using CR 11 and trying to accomplish the following:

1. Get the distinct employee names from 2 different DB's for groupings.
- Jobs holds the employee name in characters 11-20 in the "reserved" field.
- Items holds the employee name in the "employee" field.
2. If the returned group name is larger than 10 then only display the next 10 characters as the group name.
3. Link these groups to 2 subreports (Jobs and Items respectively) to pull transaction data using the employee names.

What I have so far:
Using an SQL command in Database Expert I perform the following:
select reserved from jobs union all select employee from items

I group by "command.reserved" and get the distinct group names but since the Jobs DB appends the employee name in the "reserved" field at characters 11-20 I get a 20 character value. Characters 1-10 need to be discarded in order to get the employee name (eg. 0123456789George).

To summarize, I need to display employee transactions from 2 different databases. The employee name is stored in the "employee" field in Items and contained in characters 11-20 in the "reserved" field in Jobs.

Perhaps I'm not approaching this the best way and so any insight/suggestions are much appreciated as always. =)

Thanks in advance!

 

[synapsevampire]

You'll probably use a SUBSTR() function, but how could we possibly know since you didn't post the database type?

Sosomething like:

select reserved from jobs
union all
select substr(employee,11) from items

Most SQL databases support some variation of SUBSTRING.

-k

 

[ProgressiveJL]

Man... I tried that using the MID function... haha
thanks K! Works great!