I need to report data from ROWS in Columns without Pivot tables

[RppDuck]

My rows have Ship dates and Ship quantities by Customer and Contract Number (Blanket Order). This is a manufacturing environment where fulfillment of orders is tracked for JIT (Just in time delivery.

A typical row will consist of the following information pulled from five tables in the query, but the query only has a Main table, and the other 4 just contain fields based on the primary keys; Customer and Contract.

Each ROW contains; Customer, Contract, Ship Date, Ship Qty, Customer Order Number, Mfg Part Number, Our Work Order number, and a Sequence Number for the Item.. 010, 020, etc..

The report needs to be formatted to show:

Header:

Customer CutomerNo ContractNo SequenceNo AllPreviouslyShipped ShippedTodayDate ShippingDate+1 ShippingDate+2 ShippingDate+3 ShippingDate+4 ShippingDate+5 ShippingNextWeek TotalStillDueToShip

I need to be able to enter a Parameter Discrete Value Date that looks at all ContractNo SequenceNo shipments prior to the date entered. The Items Shipping Today, then the schedule of items out 4 more days, than the next weeks total shipping schedule and then the total left due to ship after the current data(Schedule).. Today - through - Next Week has been satisfied.

Data to Consider:

ROW:
XYZ CORPORATION, CONTRACT1, SHIPDATE1, SHIP_QTY(5), CUSTOMER_ORDER(12345), MFG_PN(43R23), WO_NUM(123RD23), SEQ_NO(010).

Delivery in report:

Customer Data ... WO Data .. Prev. Shipped Today 10/16/2002 .. 10/20/2002.. LeftToShip
XYZ 200000 2100 10 .. 55 22056

Are there any ways to create cross-tab queries on the fly? etc.

Thanks

 

[willisa]

If it was me I assuming you have the same number of columns on each report, I would use formulas for the headers to get the approrpraite dates across the top. and then a formula for each field.

h1: 'today ' + date1
...

f1: if date = date1 in range then value else 0
f2: if date = date2 in range then value else 0
...

 

[RppDuck]

Forgive me for asking what may be an elementary question in CR. I am new to designing reports that I are not developed via views in a database.

Or, in other words, I haven’t used CR as in an IT reporting environment before.

Does the H1 in the formula you provided, connect to the F1 automatically, or is there some other connection code that I would need to make it work?

Thanks

 

[willisa]

All the h1(heading formula) was is the heading, you had actual dates in your example, so I figured your heading would likely change. As for the linkage. All you would need to do is make sure they are lined up with the corresponding field formula.

 

[RppDuck]

I thank you very much for your responses. I am getting errors on the formula:

if {tdssc030.sdat} = {?SDATE} in range then {tdssc030.shpqty} else 0

I created this based on:
if date = date1 in range then value else 0 (above-help)

The error I get is that {tdssc030.sdat} doesn't result in a string, number...

Would it be possible to help me with syntax?

I am not using a range for the dates I am using a single date and then making the date headers successfully with {?SDATE} + 1.

The tdssc030.shpqty is the shipped quantity for a specific date in the row.

Thanks

 

[willisa]

For starters you shouldn't have the words in range if you are using a discret value. You should have = only.

Also if you are using in range the correct syntax is as follows.
if date in (date1} then value else 0

Try on or the other depending on if you are testing a range of values of a single value.