SQL queries in Custom Reports

[BrianShoe]

Hey folks -
I've found another good reason to permit use of SQL statements in creating Custom Reports. This relates to NeilZ's observation (in the Membership category) that none of the officer fields are available in the Custom Report builder.

After considerable experimenting, and going crosseyed looking at the Visual Foxpro online documentation, I've successfully built the SQL query that will extract what I want: a list of all officers and committee members as of a specific date.

To do this required JOINing four tables: SKSERV, ME, SKREF, and SKCODES. The entire SQL SELECT statement is unbearably ugly, but it works.

If you're interested, here it is:

select T2.lastname, T2.firstname, T1.skill_no, T3.desc, T4.descript, begin, end from skserv as T1 inner join me as T2 on T1.mail_no = T2.mail_no and T1.pers_no = T2.pers_no inner join skref as T3 on T1.skill_no = T3.skill_no inner join skcodes as T4 on T1.role = T4.code where T1.skill_no > 2000 and T1.skill_no < 3000 and end > DATE() order by T1.skill_no, T2.lastname to file FPUUOFFC.txt

(This gives current year officers, since it tests "end > DATE()"; if I want a previous year, I need to test like:
begin < ctod("09/01/2003") and end > ctod("06/01/2004")

Problem is, I can only run this in the Database Manager - and can only dump the result to an ASCII file (not even CSV!). Yes, that file can be pulled into Excel (if I insert delimiters to make sure things end up in the proper columns), and from there into a table in Word or whatever. Painful, with multiple steps, but all of this can be done.

The point is that the output of the SQL SELECT is a table - and if it's a table, it can go directly into a report . . . can't it?

Is there something I'm missing about the Custom Report writer the way it is?

Does anyone else have thoughts on this?