Possible to sort main report data and subreport data together?

[tkschief]

I'm using CR 11 and in my main report event data is being returned for specific events and then sorted by the event date for each case group. I then have a subreport that brings in another event, event date and two more fields which I have put in the group footer. The data being returned with the subreport of course is not sorted into the main report event data because it is in the group footer. Is there a way for me to have both the main report and subreport data be together so it can be sorted by the event date? I was not able to combine the two queries to get the correct data but I may be missing something. I've included the queries below. Thanks for any suggestions or pointing me in the right direction!

SQL query for main report:
SELECT T_CASE.CASE_NUM,
T_CASE.CASE_TITLE COLLATE SQL_Latin1_General_CP1_CI_AS AS [CASE TITLE],
T_CASE.RECEIVED_DATE AS [CASE RECEIVED DATE],
T_EVENT.EVENT_ID,
L_EVENT_TYPE.EVENT_TYPE_NAME AS EVENT,
T_EVENT.IS_DELETED AS Deleted,
T_EVENT.EVENT_DATE_TIME,
T_CASE.IS_DELETED,
T_CASE.IS_EXPUNGED,
T_CASE.CASE_ID
FROM T_EVENT LEFT OUTER JOIN
T_CASE ON T_EVENT.CASE_ID = T_CASE.CASE_ID LEFT OUTER JOIN
L_EVENT_TYPE ON T_EVENT.EVENT_TYPE_ID = L_EVENT_TYPE.EVENT_TYPE_ID
WHERE (T_CASE.CASE_NBR_PREFIX = 'TS') AND (T_CASE.RECEIVED_DATE > CONVERT(DATETIME, '2011-08-01 00:00:00', 102)) AND
T_EVENT.IS_DELETED = 0) AND
(L_EVENT_TYPE.EVENT_TYPE_NAME COLLATE SQL_Latin1_General_CP1_CI_AS = 'order receive' OR
L_EVENT_TYPE.EVENT_TYPE_NAME COLLATE SQL_Latin1_General_CP1_CI_AS = 'received referral' OR
L_EVENT_TYPE.EVENT_TYPE_NAME COLLATE SQL_Latin1_General_CP1_CI_AS = 'locate date' OR
L_EVENT_TYPE.EVENT_TYPE_NAME COLLATE SQL_Latin1_General_CP1_CI_AS = 'deadline service w/in 90 days' OR
L_EVENT_TYPE.EVENT_TYPE_NAME COLLATE SQL_Latin1_General_CP1_CI_AS = 'deadline order w/in 6 months' OR
L_EVENT_TYPE.EVENT_TYPE_NAME COLLATE SQL_Latin1_General_CP1_CI_AS = 'deadline order w/in 12 months') AND
(T_CASE.IS_DELETED = 0) AND (T_CASE.IS_EXPUNGED = 0)
ORDER BY T_CASE.CASE_NUM

SubReport SQL Query:
SELECT CRIMES.T_CASE.CASE_NUM, CRIMES.T_CASE.RECEIVED_DATE AS [CASE RECEIVED DATE],
CRIMES.L_EVENT_TYPE.EVENT_TYPE_NAME AS EVENT,
CRIMES.T_EVENT.EVENT_DATE_TIME,
CRIMES.T_EVENT_SERVICE.SERVED_DATE, CRIMES.T_PERSON.FULL_NAME,
CRIMES.T_EVENT_SERVICE.EVENT_ID,
CRIMES.T_CASE.CASE_ID
FROM CRIMES.T_PERSON INNER JOIN
CRIMES.T_CASE_PARTICIPANT ON CRIMES.T_PERSON.PERSON_ID = CRIMES.T_CASE_PARTICIPANT.PERSON_ID LEFT OUTER JOIN
CRIMES.L_CASE_PARTICIPANT_ROLE ON
CRIMES.T_CASE_PARTICIPANT.CASE_PARTICIPANT_ROLE_ID = CRIMES.L_CASE_PARTICIPANT_ROLE.CASE_PARTICIPANT_ROLE_ID LEFT OUTER JOIN
CRIMES.T_EVENT_SERVICE ON
CRIMES.T_CASE_PARTICIPANT.CASE_PARTICIPANT_ID = CRIMES.T_EVENT_SERVICE.CASE_PARTICIPANT_ID RIGHT OUTER JOIN
CRIMES.T_EVENT INNER JOIN
CRIMES.T_CASE ON CRIMES.T_EVENT.CASE_ID = CRIMES.T_CASE.CASE_ID ON
CRIMES.T_EVENT_SERVICE.EVENT_ID = CRIMES.T_EVENT.EVENT_ID LEFT OUTER JOIN
CRIMES.L_EVENT_TYPE ON CRIMES.T_EVENT.EVENT_TYPE_ID = CRIMES.L_EVENT_TYPE.EVENT_TYPE_ID
WHERE (CRIMES.T_CASE.CASE_NBR_PREFIX = 'TS') AND (CRIMES.T_CASE.RECEIVED_DATE > CONVERT(DATETIME, '2011-08-01 00:00:00', 102)) AND
(CRIMES.T_EVENT.IS_DELETED = 0) AND
(CRIMES.L_EVENT_TYPE.EVENT_TYPE_NAME COLLATE SQL_Latin1_General_CP1_CI_AS = 'pleadings sent to process server') AND
(CRIMES.T_CASE.IS_DELETED = 0) AND (CRIMES.T_CASE.IS_EXPUNGED = 0) AND (CRIMES.L_CASE_PARTICIPANT_ROLE.CASE_PARTICIPANT_ROLE_NAME) AND
(CRIMES.T_EVENT_SERVICE.SERVED_DATE IS NOT NULL) AND (CRIMES.T_EVENT_SERVICE.IS_DELETED = 0) AND
(CRIMES.T_CASE_PARTICIPANT.IS_DELETED = 0) AND (CRIMES.T_PERSON.IS_DELETED = 0)

REPORT EXAMPLE NOW:

TEST CASE TITLE
TS-2011-12345
Locate Date 07/29/11
Received Referral 08/30/11
Deadline Order 01/29/12
Pleadings Sent to Server 09/14/11 LN, FN, MN 09/14/11

Note: Pleadings Sent to Server is the data that is coming from the subreport and my office would like it to be merged and sorted into the other event data.

REPORT EXAMPLE GOAL:

TEST CASE TITLE
TS-2011-12345
Locate Date 07/29/11
Received Referral 08/30/11
Pleadings Sent to Server 09/14/11 LN, FN, MN 09/14/11
Deadline Order 01/29/12

 

[lbass]

In your report example, what report section is each row appearing in?

-LB

 

[tkschief]

Group header #1 = TEST CASE TITLE

Details section:
TS-2011-12345
Locate Date 07/29/11
Received Referral 08/30/11
Deadline Order 01/29/12

Group Footer #1
Pleadings Sent to Server 09/14/11 LN, FN, MN 09/14/11

 

[lbass]

Do you know how to create a union query in a command which you would then use as your sole datasource? That would be the way to go.

-LB

 

[tkschief]

I don't know how right offhand, but I will look into it. Thanks!