Selection Criteria

[simpelli]

I'm reporting against a transaction history table. The transactions record the various stages of a product sku as it goes from warehouse to shipping (planned, scheduled, picked, ship, etc) Any time something changes on the order master table, the transaction is recorded here, with the most current data in the order master. With all of these records, I need to get the maximum of the field "latest_delivery_date", which could change with each update.

The report needs to be grouped/ordered by requested_date , then sku. The requested_date is a formula field determined by certain criteria in the record:

{@ReqDate}=
If {table_A.criteria}= X then
{table_B.requested_date}
else
table_A.requested_date}

I have the grouping and sorting working per spec (along with other selection criteria) and I'm returning the data that I need. The criteria above is a specific transaction code of those returned. If I don't get X for the sku, I need to get the max of the latest_delivery_date of those returned.

Thanks,

-SI

 

[synapsevampire]

Perhaps if you provided example data and expected output I can better help as it's unclear what "If I don't get X for the sku, I need to get the max of the latest_delivery_date of those returned." means since your formula references getting a requested date if it's not X.

You can get the max of the date by dropping it in the Details->Right click it->Insert->Summary->Maximum->pick the date and select the grouping you want the max for.

You'll see that the max is now available to your formulas.

Now you can reference this in your formula, as in:

{@ReqDate}=
If {table_A.criteria}= X then
{table_B.requested_date}
else
Maximum (latest_delivery_date, {YourGroup})

Hopefully this at least gets the theory across, I really didn't grasp what you were after.

-k

 

[simpelli]

Data returned by my query may contain:
SKU trans_code req_date latest_delivery_date
1 A 7/1/2003 7/2/2003 12:15PM
1 B 7/1/2003 7/2/2003 12:15PM
1 C 7/1/2003 7/2/2003 1:15PM
1 X 7/1/2003 7/2/2003 3:15PM
2 A 7/1/2003 7/2/2003 12:15PM
2 B 7/1/2003 7/3/2003 8:00AM
2 C 7/1/2003 7/2/2003 2:15PM
3 A 7/1/2003 7/2/2003 2:15PM
3 B 7/1/2003 7/3/2003 2:00PM
3 X 7/1/2003 7/2/2003 12:15PM

Expected result:

{@ReqDate} - Group
7/1/2003
SKU latest_delivery_date
1 7/2/2003 3:15PM (trans_code=X)
2 7/3/2003 8:00AM
3 7/2/2003 12:15PM (trans_code=X)

I apologize - HUGE omission in my first post - I don't have the report available at the moment and am reconstructing from memory:
As mentioned, the main sku data is in the order_master table. When it reaches trans_code of X it is removed from order_master and only exists in history. The requested date is an input parameter, so I need to check the master table for requested dates that match, and return those, as well as those that match in history. trans_code only exists in history. My query is returning more data than I need. I need the record from history if trans code is X but if the sku it is still in order_master, I need the greater of latest_delivery_date from either the history record or order_master latest_delivery_date in master.

Hope I didn't make it more confusing....

 

[lbass]

Something like this might work:

Group on {order_master.sku}. Then create a formula {@x} and place it in the details section and suppress:

if {history.transcode} = "X" then 1 else 0

Then create a formula {@delivdate}:

if sum({@X},{order_master.sku}) = 0 then
maximum([maximum({order_master.delivdate},{order_master.sku}), maximum({history.delivdate},{order_master.sku})]) else
if sum({@X},{order_master.sku}) >= 1 then {#Xdate}

//where{#Xdate} is a running total using the running total editor: Select {history.delivdate}, maximum, evaluate using a formula:

{history.transcode} = "X"

Reset on change of group (sku). {@delivdate would need to be displayed in the group (sku) footer since it contains a running total that will only evaluate correctly at the group footer level. Then just suppress the details section.

-LB

 

[simpelli]

Thanks LB

It appears to have accomplished what I'm looking for. Going through validation now, but it looks good so far

-SI