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!

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

Sorting data from two subreports in the main report 1

Status
Not open for further replies.

DJWheezyWeez

Technical User
Jun 5, 2008
265
US
Using all of my years of Crystal knowledge, I'm fairly certain this isn't possible but I thought I'd ask for ideas. I have two subreports that I need to sort by date together, essentially intermingling the data. The only way I can imagine doing this is creating potentially 100s of variables to hold each line of data then do a lot of "if then"s to order them based on date though I'm unsure if it will work the way I think it will in my head.

Am I forgetting some simple way of doing this or is this impossible to do?
 
Seems like a job for an SQL Command to me

_____________________________________
Crystal Reports 2011 and XI
Intersystems Cache 2012 ODBC connection

 
You are correct. There is no practical way to do this with subreports. Instead write a command with a UNION query and append one set of records to the other. That is one of the main reasons I end up using commands.

Ken Hamady, On-site Custom Crystal Reports Training & Consulting
Public classes and individual training.
Guides to Formulas, Parameters, Subreports, Cross-tabs, VB, Tips and Tricks
 
Thanks so much for the help. Using UNION has been working out almost perfectly. The problem I'm having is that I need to include two fields, kjobcode and event-qty, but those fields only exist on one of the tables. Is there a way to create a dummy field of null values for kjobcode and 0 for the event-qty? I've googled this and it sounds like it's possible but I must using the incorrect syntax.
 
Normally with most SQLs, you just put in a text field (i.e, 'SomeValue') or a number (i.e, 0) in place of the missing field. I normally alias the 'feild' to make it easier. You normally just have to make sure it is the same data type.
 
Thanks everyone for the help. The problem I was having was using double quotes instead of single quotes. For anyone else that may have a similar problem, my code is below.

Code:
SELECT 
"sfeventcds"."start-time", 
"sfeventcds"."kco", 
"sfeventcds"."PlantCode", 
"sfeventcds"."mach-id", 
"sfeventcds"."shift-code", 
"sfeventcds"."stat-code", 
"sfeventcds"."elapse-time", 
"sfeventcds"."op-code", 
"sfstatcode"."description",
"sfeventcds"."event-qty" as "Quantity",
"sfeventcds"."kjobcode" as "JobCode"
 FROM   {oj "SFDC"."PUB"."sfeventcds" "sfeventcds" LEFT OUTER JOIN "SFDC"."PUB"."sfstatcode" "sfstatcode" ON (("sfeventcds"."kco"="sfstatcode"."kco") AND ("sfeventcds"."PlantCode"="sfstatcode"."PlantCode")) AND ("sfeventcds"."stat-code"="sfstatcode"."stat-code")}

UNION SELECT 
"sfopevent"."start-time", 
"sfopevent"."kco", 
"sfopevent"."PlantCode", 
"sfopevent"."mach-id", 
"sfopevent"."shift-code", 
"sfopevent"."stat-code", 
"sfopevent"."elapse-time", 
"sfopevent"."op-code", 
"sfstatcode"."description",
0.00 as "Quantity",
'' as "JobCode"
 FROM   {oj "SFDC"."PUB"."sfopevent" "sfopevent" LEFT OUTER JOIN "SFDC"."PUB"."sfstatcode" "sfstatcode" ON (("sfopevent"."kco"="sfstatcode"."kco") AND ("sfopevent"."PlantCode"="sfstatcode"."PlantCode")) AND ("sfopevent"."stat-code"="sfstatcode"."stat-code")}
 
Status
Not open for further replies.

Part and Inventory Search

Sponsor

Back
Top