Subreport: Sorting Data

[JD5241]

I'm not sure if this is something anyone is going to be able to help me with, it might be too shop-specific, but I thought I'd give it a shot to help a coworker out. We're using XI and SQL database.

I'll explain the logic, then what we've done in Crystal.

I have two orders, I'll call them original order and new order.
A doctor creates an original order for a patient, that order has a unique order#. Within one year, something breaks and the patient comes back in to have whatever material replaced. A new order is created, with its own unique order#. Since the issue is related to the original order, a Base_Order# is assigned to the new order that is the same number as the original order. This references the new order back to the original order. Nothing changes on the original order, the reference only goes one way:

Original order: Order# 123, NO Base_Order#
New order: Order# 456, Base_Order# 123

Where it starts to get complicated:
The doctor who creates the new order may not be the same doctor that created the original order, and the original order has to be within one year in the past by the end date in the date range.
We need to use the Base_Order# on the new order to pull the names of the doctors who created the original orders via Subreport, and put them in order on the report.

What we've been able to do so far:

New Dr NewOrder# Base_Order# Orig.Dr Orig.Order#
Booey, B 456 123 Griffin, P 123
Davis, S 432 234 Allen, T 234

NOTE: The Orig.Dr and Orig.Order# are coming from the subreport, the rest is on the main report.

What we'd like to see:

Orig.Dr Base_Order#
Allen, T 234
Griffin, P 123

Basically, we want the original doctor's name and the Base_Order# listed by doctor.
I have the subreport linked to the main report by Order#(subrpt) = Base_Order# (main), and {@Start Date} and {@End Date} being passed but not linked to any fields in the subreport. I'm using a formula in the subreport to pull original orders up to one year in the past from the End Date, table.date in {@End Date - 1 Year} to {?PM-@End Date}.

This is everything that I thought should give me the result we want, but we've been unable to get the original doctors to sort in order. I've tried doing it in reverse, getting the Base_Order# with a subreport, but it's still listing the doctors who created the new order.

My coworker made a post about sorting subreports a few days ago, in which we discovered subreports only sort by the field it's linked to the main report with, which gave us a little more direction, but now we're stuck again.

I realize this whole thing might be a little nebulous, so please let me know if you need more info... if anyone has any ideas, I'd sure appreciate them.

"I have no idea what's going on." -Towelie

 

[synapsevampire]

Since your intended output is a subset of what you're able to create, it seems that you're done.

Rather than chatting about this, some more technical information would prove more useful.

You probably don't need a subreport anyway, just add the same table in again and join it by the origdr field.

So what helps people understand is to post your data (not what's in your report so far, especially if it doesn't do what you need, and then the desired output, you're fairly close here.

Anyway, try a self join and eliminate the subreport, they're slow and limiting and tend to overcomplicate things.

-k

 

[JD5241]

I wish I were done!

We're using individual table structures set up within a business view (basically fields in a BV separated into categories)... while I can add aliases of these table structures in the Database Expert, since the fields in each table are actually from within the same BV, they're not linked in the usual sense, so there aren't really any links I can change.
I've tried using a record selection formula in the report to return the data I want, but it doesn't help return any data:
if table.order# = table_alias.base_order# then Yes else No

The fact that I can't change the links between table structures within the BV is what is forcing me to use a subreport. But I'm either getting jumbled data that makes no sense, no data at all, or the subreport hangs and locks XI up.

Thanks for your help!

"I have no idea what's going on." -Towelie

 

[lbass]

If you want to show the data in order of the originating doctor, then use that in your main report, and use the subreport for your new doctor info. Clearly, only one or the other can drive the order.

-LB