Tek-Tips is the largest IT community on the Internet today!

Members share and learn making Tek-Tips Forums the best source of peer-reviewed technical information on the Internet!

Register Log in
  • Congratulations Mike Lewis on being selected by the Tek-Tips community for having the most helpful posts in the forums last week. Way to Go!

Possible to sort main report data and subreport data together?

Status
Not open for further replies.
tkschief

tkschief

MIS
Apr 20, 2006
28
US
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
 
Sort by date Sort by votes
In your report example, what report section is each row appearing in?

-LB
 
Upvote 0 Downvote
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
 
Upvote 0 Downvote
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
 
Upvote 0 Downvote
I don't know how right offhand, but I will look into it. Thanks!
 
Upvote 0 Downvote
Status
Not open for further replies.

Similar threads

mmck1964
  • Locked
  • Question
Using "OR" to select data
Replies
4
Views
318
mmck1964
mmck1964
jchewsmith
  • Locked
  • Question
Report filter to run report yeartodate through yesterday
Replies
0
Views
213
jchewsmith
jchewsmith
D
  • Locked
  • Question
Running total from a subreport only showing last value
Replies
4
Views
409
doodledump
D
john432
  • Locked
  • Question
Crystal Report
Replies
2
Views
204
john432
john432
john432
  • Locked
  • Question
Crystal Report Help 1
Replies
2
Views
207
john432
john432

Part and Inventory Search

Sponsor

Back
Top