Sorting data from two subreports in the main report 1

[DJWheezyWeez]

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?

 

[CoSpringsGuy]

Seems like a job for an SQL Command to me

_____________________________________
Crystal Reports 2011 and XI
Intersystems Cache 2012 ODBC connection

 

[kenhamady]

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

http://www.kenhamady.com/

 

[DJWheezyWeez]

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.

 

[kray4660]

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.

 

[DJWheezyWeez]

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.

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")}