Jump to content

Cross-Tab report with ExecuteSQL


This topic is 2251 days old. Please don't post here. Open a new topic instead.

Recommended Posts

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

 

Link to comment
Share on other sites

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

 

 

Link to comment
Share on other sites

This topic is 2251 days old. Please don't post here. Open a new topic instead.

Create an account or sign in to comment

You need to be a member in order to leave a comment

Create an account

Sign up for a new account in our community. It's easy!

Register a new account

Sign in

Already have an account? Sign in here.

Sign In Now
 Share

×
×
  • Create New...

Important Information

By using this site, you agree to our Terms of Use.