Cross-Tab report with ExecuteSQL

[David Zakary]

I'm working on a cross-tab report using ExecuteSQL. I'd like to try to do it as a single eSQL statement but I'm not sure if its possible or not. I'm still a bit of an eSQL newbie.

Background, the database manages work done in a condominium building. Each apartment in the building is identified by a floor and a location in the building; 1-9 for the floors and N, NE, SE, W for the location. For the report, the floor is on the vertical axis and location is the horizontal axis. Each cell in the report is a sum of apartments where the work has been completed.

I have the first part of the statement working, I can grab a total number of apartments completed on each floor. What I need is the total for each location, on each floor. 

So far the SELECT statement looks like this. I didn't include the eSQL and the delimiter bits. It returns the floor # and the total number of apartments completed correctly. 

"SELECT 
    a.\"apt_loc_floor\",
    COUNT (a.\"zz_ID\")
    
FROM \"rep_Apartments\" a
INNER JOIN \"accounts\" b ON a.\"zz_ID\" = b.\"zz_fk_apartment\"
WHERE b.\"ac_conversion_date\" is not null
GROUP BY a.\"apt_loc_floor\"
ORDER BY a.\"apt_loc_floor\"

I know it could be done via several eSQL statements but I'd like to try to do it with as few as possible. Any suggestions?

Also, the second field for the location is "apt_loc_location"

Dave Zakary

 

[beverly]

hey David! just off the bat, I wonder if UNION would help (several SQL queries linked). Can you post a screen shot (of Excel) as this might look?

once you get the values (perhaps pipe delimited), you could use Virtual List/Table to get the values in the report. contact me off list if needed.

beverly