multiple recordset in data report

[mebhas]

i am working on a project with oracle database and vb data reports. i have figured out the connections, dynamic sql and the like and am ready to create a report and thats where i'm stuck.

i have to use four recordsets coz 3 recordsets differ with the change in row of the first recordset. i need to put that in a single data report.

my question here is how do i connect multiple recordsets to a single data report page?

[mebhas]

and also, i need to change the layout of the paper to landscape. seems it is not very flexible with that.

[mebhas]

about half a day gone and still no reply to my post. well, i, at least, solved the second part.... or so i think. i will be using crystal reports, as soon as i get my cd....

so far, i have no idea on how to work out the multiple recordsets as yet. hope crystal reports helps me out with that....

[dee-u]

Would you care to elaborate? Your problem is not clear to me...

[mebhas]

this is my first query that is in recordset called oraRS

mySQL = "select agent_id, flight_time, flight_date, sector_pair, flight_no from vofsc t1, rf t2, res t3 " & _
            "where flight_date = '" & inDate & "' and " & _
            "sector_pair = nvl('" & inSector & "', sector_pair) " & _
            "and flight_status <> 'XX' " & _
            "and t2.reservation_status = 'HK' " & _
            "and t1.unid = t2.unid and t2.pnr_no = t3.pnr_no " & _
            "group by agent_id, flight_time, flight_date, sector_pair, flight_no " & _
            "order by flight_date"

here are the other 3 sqls that are in gRS1, gRS2 and gRS3, respectively

'gRS1
mySQL = "select count(decode(nationality,'FOR', passenger_no)) for_pax, " & _
            "count(decode(nationality, 'IND', passenger_no, 'NEP', passenger_no)) nep_pax, " & _
            "count(decode(nvl(nationality, 'A'), 'A', passenger_no)) ntba_pax " & _
            "from rp where pnr_no in (select t1.pnr_no from rf t1, res t2 where unid in " & _
            "(select unid from vofsc where flight_date = '" & sDate & "' " & _
            " and sector_pair = '" & oraRS.Fields(3).Value & "' and flight_status <> 'XX' " & _
            "and flight_no = " & Val(oraRS.Fields(4).Value) & _
            " and t1.pnr_no = t2.pnr_no and t2.agent_id = '" & oraRS.Fields(0).Value & "' " & _
            "and t1.reservation_status in ('HK','KL','KK','RR')) " & _
            "and passenger_status = 'OK')"

'gRS2
mySQL = "select ttl_date, ttl_time, user_id from rf t1, res t2 where unid in " & _
            "(select unid from vofsc where flight_date = '" & sDate & "' " & _
            "and sector_pair = '" & oraRS.Fields(3).Value & "' and flight_status <> 'XX' " & _
            "and flight_no = " & Val(oraRS.Fields(4).Value) & _
            " and t1.pnr_no = t2.pnr_no and t2.agent_id = '" & oraRS.Fields(0).Value & "' " & _
            "and t1.reservation_status in ('HK','KL','KK','RR'))"

'gRS3
mySQL = "SELECT ALL COUNT(DECODE(NATIONALITY, 'FOR', TICKET_NO)) for_tkt, " & _
            "COUNT(DECODE(NATIONALITY, 'IND', TICKET_NO, 'NEP', TICKET_NO)) nep_tkt, " & _
            "COUNT(DECODE(NVL(NATIONALITY, 'A'), 'A', TICKET_NO)) ntba_tkt " & _
            "FROM VFI WHERE UNID IN (SELECT UNID FROM VOFSC WHERE FLIGHT_DATE = '" & sDate & "' " & _
            "AND SECTOR_PAIR = '" & oraRS.Fields(3).Value & "' AND FLIGHT_NO = " & Val(oraRS.Fields(4).Value) & _
            " AND FLIGHT_TIME = '" & oraRS.Fields(1).Value & "' " & _
            "AND SECTOR_STATUS = 'HK' " & _
            "AND RESERVATION_STATUS = 'OK' AND PASSENGER_STATUS = 'OK' " & _
            "AND AGENT_ID = '" & oraRS.Fields(0).Value & "')"

and here is the attachment of the data report i need to fill up with these 4 recordsets.

[mebhas]

ok, the landscape part is done. anybody has a view on how to do this? if it can be done. anybody know a good tut for CR?

help....

[Hack]

I have searched a number of times, but I've never found an free online tutorital for Crystal.

All of the Crystal tutorials/training is cost based.

If anyone has found something, it would be very helpful to a lot of people to post the link.

[brucevde]

I find Crystal is easy to use. It contains Wizards to help get someone started and the help file is pretty good.

There is no way to use multiple recordsets in Data Reports.

Crystal is much more powerful and does have the ability to use multiple recordsets. It allows more control over formatting, user formulas, user defined functions, sub-reports etc..