Combine three query together 1

[roseMay]

Now I have three querys in one report, they have same filter(where clause), but different group by and sum columns, and they are printed in seperated layout frames. I think I use too many querys, and every query has long where clause, the report will run slowly, how could I combine the three querys together? or how could I use just one query to get the same result set for summary?

If you need any sample, please ask me for it.

Thanks a lot!

 

[nagornyi]

Report queries don't need group clause and sum functions. Groupping performed in the data model and all summations can be done with summary columns on the report data model, no need to get sums in the SQL. So you can modify the query to return all column used by either layout, and add nessesary summary columns in the data model. Then you can use the same query in multimple layouts.

 

[roseMay]

I tried a lot, seperated the query to several groups, but I have not got the desired result until now, and here are the layout of the report, the first page is the detail listing, the second page is a sub total according to each facility, and the third page is the total for all facilities,

http://ca.pg.photos.yahoo.com/ph/rose_echo2002/album?.dir=/c577&.src=ph

This is the query:

Select oms.offender_id,
vhb.last_name,
vhb.first_name,
vhb.liv_unit_desc,
hc.description,
oms.comment_text,
lvl1.LIVING_UNIT_ID lvl1_unit_id,
lvl1.DESCRIPTION lvl1_desc,
lvl1.AGY_LOC_ID,
hc.description diet_order
From offender_medical_services oms,
v_header_block vhb,
hcpcs_codes hc,
(SELECT LIVING_UNIT_ID,
DESCRIPTION,
AGY_LOC_ID
FROM LIVING_UNITS
WHERE LIVING_UNIT_TYPE = (SELECT HOUSING_LEV_1_CODE
FROM AGENCY_LOCATIONS
WHERE AGENCY_LOCATIONS.AGY_LOC_ID = LIVING_UNITS.AGY_LOC_ID
AND AGENCY_LOCATIONS.AGENCY_LOCATION_TYPE = 'INST'
AND AGENCY_LOCATIONS.AGY_LOC_ID IN (SELECT AGY_LOC_ID
FROM CASELOAD_AGENCY_LOCATIONS
WHERE CASELOAD_ID = :CASELOAD) )) lvl1
Where oms.medical_service_code = hc.hcpcs_code
And oms.offender_id = vhb.root_offender_id
And oms.active_flag = 'Y'
And oms.end_date is null
And vhb.agy_loc_id = lvl1.agy_loc_id
And vhb.active_flag = 'Y'
And vhb.root_offender_id in (
select root_offender_id
from v_header_block
where living_unit_id in (select living_unit_id from living_units
start with living_unit_id = lvl1.living_unit_id
connect by prior living_unit_id = parent_living_unit_id )
)
&CF_DIET_FLAG
ORDER BY lvl1.AGY_LOC_ID, oms.offender_id

Please help me take a look, cause I tried a lot, there is still such a mess.

Thanks a lot!

 

[nagornyi]

The data model should have 2 groups:
the first is for Facility/Housing Location, the second is for Order.

The layout for the first report should have 2 repeating frames: the first one is for the Facility/Location group, with that information placed there. The inner repeating frame should be for Order group, with all the order information placed there.

The layout for the second report is also using the same 2 repeating frames, you just put less information for orders.

The third report layout uses single repeating frame and that one is for the Order group.

 

[roseMay]

The second page is a subtotal according to every Diet Order face every Facility, and the third page is total according to every Diet Order for all facilities, how to count it out? My result is always wrong.

 

[nagornyi]

OK, I see the point.
You will need to split Order group into two. Just drag the Order field from the order group above the group. That should create new group between exiting groups. What's left in the third group is all the order info except for order number (or whatever is used on the second in third report). The first and second reports will still use 2, not 3 repeating frames on the layout, but for the second report you reassign the inner repeating frame to the new group. The third report will use single repeating frame, and that is for the newly created group.
Now you create summary columns in that new group. The summary column for the second report would count any field from the third group and reset on the first group. The summary for the third report is the same, but resets at Report.

 

[roseMay]

I followed you to perform the steps, now the report is better than before, thank you so much! But in the third report, there is still some problem, I have no idea how to handle this,

http://ca.pg.photos.yahoo.com/ph/rose_echo2002/detail?.dir=c577&.dnm=ee37.jpg&.src=ph

Thanks again!

 

[nagornyi]

Yes, you are right...
I created some mock report and was able to reproduce the problem. Looks like there is no way to trick the report out of that breaking order by location for the third report. So the bottom line so far is that the first and the second reports can be combined with the same data model, but the third one requires a separate query. Hopefully that SQL can be simpler, as you need just the Order. Again, you don't need to do summation in the SQL, as you can do it in the data model.

 

[roseMay]

Finally we got a conclusion, Thanks a lot... *_*

 

[ameima]

I have a follow up in the same category, i hope nagornyi can help me with


i have a problem with having two sibling groups reporting to one parent.

G1 <- G2 <- G3A
G1 <- G2 <- G3B

I would like to show both of these G3 data next to each other. If i keep them in one group it will create a cartesian product fo A and B.

Normally (outside Ora Reports (9i)) i would create
Query Set QS =
Q1 (G1 <- G2)
Q2 = (Q1<-G3A )
Q3 = (Q1<-G3A )

Then
Parent repeating frame R_1 (g1)
Two sub repeating frames R1a (g1 for paging) and r2(g2)
then two sibling frames r3a and r3b both reporting to parent fram r2, each having their own source (g3a, resp 3b)

In Oracle i cant get this to work (Ora 9iDS Reportbuilder)
it works with just g3a fine, but once i add 3b the rows multiply. Meaning if there is 2 records for g3a, and 3 records for g3b (and the view below thus having 6 entries), the records will show 2*3 = 6 records, doubling, resp tripling the actual records

Thank You
Arjen