Only select transactions for a specific year 1

[blueboyz]

Peachtree Quantum 2012
Pervasive SQL Database
Crystal Reports 2008

I have a report that lists total quantity of Sales Orders and Purchase Orders by Item ID by month:

Item OnHand JAN FEB MAR APR JUN
A 3 0 0 98 0 0 Sales Order
0 0 27 50 0 Purchase Order
3 3 -68 -18 -18 Available

My record selection is:
If {?Item ID} = "ALL" then TRUE else {LineItem.ItemID} = {?Item ID} and {JrnlHdr.JrnlKey_Journal} in [10, 11]
and (YEAR({JrnlHdr.ShipByDate}) = {?YEAR} or
YEAR({JrnlHdr.GoodThruDate}) = {?YEAR})

The problem is that 0's appear for all months for the Purchase Order
row. If I use "and" instead of "or" in the record selection the report only displays the column titles and no data.

I need to select sales orders and purchase orders for a specific year because some sales orders and purchase orders may remain open into another year. For example, a sales order with a ship by date of 11/15/11 may not be fulfilled until some time in the year 2012. If the ship by date is in a different year than the year requested by the report, the sales order quantity should not appear on the report.

How can use a record selection or formula to only select the sales orders with ship by dates and purchases with good thru dates that contain the year the user enters in the {?YEAR} parameter?

 

[Madawc]

It's not clear what you're doing, but it might be better to think about suppressing the display of the line rather than record selection. You can only select or reject records based on what's in a single detail line, not the properties of a group of them.

Right-click on the section and choose Format Section. Then choose the formula icon (x+2 and a pencil) for suppression.
Add a test, e.g.

@grand_Total = 0

Madawc Williams (East Anglia, UK). Using Crystal 2008 with SQL and Windows XP

 

[lbass]

Change your record selection to:

(
(
{?Item ID} <> "ALL" and
{LineItem.ItemID} = {?Item ID}
) or
{?Item ID} = "ALL"
) and
{JrnlHdr.JrnlKey_Journal} in [10, 11] and
(
YEAR({JrnlHdr.ShipByDate}) = {?YEAR} [red]and[/red]
YEAR({JrnlHdr.GoodThruDate}) = {?YEAR}
)

The parens do matter and your original if/then should have been set off in parens--plus you need an "and".

-LB

 

[blueboyz]

Thank you for your assistance. I tried the record selection formula that lbass recommended but the report returned 0 records.

I changed the record selection formula to:

If {?Item ID} = "ALL" then TRUE else {LineItem.ItemID} = {?Item ID} and
{JrnlHdr.JrnlKey_Journal} in [10, 11]

I also changed the individual formulas for each period to:
{@PO P-1}
If {JrnlHdr.JrnlKey_Journal} = 10 and month({JrnlHdr.GoodThruDate}) = 1 and year({JrnlHdr.GoodThruDate}) = {?YEAR}
then ({JrnlRow.QtyOrdered} - {JrnlRow.QtyReceived})

{@SO P-1}
If {JrnlHdr.JrnlKey_Journal} = 11 and month({JrnlHdr.ShipByDate}) = 1 and year({JrnlHdr.ShipByDate}) = {?YEAR}
then ({JrnlRow.QtyOrdered} - {JrnlRow.QtyReceived})

These formulas gave me the desired report outcome.

 

[lbass]

My formula should have worked, so I wonder how you implemented it.

-LB

 

[blueboyz]

lbass:

I remarked the lines of my record selection and then I pasted your formula. Next, I saved the changes, then saved the report.

I refreshed the report and chose to prompt for new parameters.
I entered a single item id and hit the right arrow key to move the item to the selected values box. Next, I entered 2011 (and on a second try, entered 2012) for the Year parameter and clicked ok.

The report returned no records, just the column headings.

So, I deleted your formula and changed my record selection to just the item id and the year as listed in the post above. I edited each period's formula for Sales Orders and Purchase Orders with the formula listed in the post above.

When I ran the report this time, the quantities populated for both sales orders and purchase orders in each period.

I have seen your posts and you have helped me on numerous ocassions. I trust your knowledge and expertise. I'd be happy to send you the report if you want to look at it. You would need Peachtree in order to produce data for the report.

 

[lbass]

Okay, I think you must have left joins and then by selecting on the left joined tables you were limiting your data. Removing those clauses from the criteria was what corrected it I guess.

-LB

 

[blueboyz]

My tables do use "left joins". Thank you for your insight.