Need help with group selection

[RedHeadedStepITChild]

I need help with a group selection. I cannot seem to get this to work, and at this point I think I've been working the issue too long.

Background info on the report. Using Crystal XI and a DB2 database on AS400 v5r3.

The report shows Shop order information. It is grouped in the following manner:

GP1 - department
GP2 - customer order number
GP3 - release date
GP4 - shop order number
GP5 - child item description
GP6 - child item number
GP7 - item location
details


Groups 1 through 5 are for sorting purposes only. Nothing is shown here.

Here is an attempt at a visual of the report.

so# pItem pDesc rDate dDate component qReq loc lQty
-------------------------------------------------------------------
nnn nnn xxx date date nnn nnn
... ... ... .... .... ... ... nnn nnn


The location fields return multiple records and quantity on-hand.

OK - here is the problem.

I need to delete the complete shop order from the report if the sum of any components quantity on hand is <= 0.

The way I have been trying to go about this is doing a distinct count of items on the shop order (to be placed in the shop order group). Then if the sum of quantity on hand is > 1 then assign 1 to that item else 0. I then would like to be able to sum each result at the shop order group footer. if the distinct count of items on the shop order = the sum of the quantity on hand result then keep the shop order else drop it.

This will not work for me, because crystal is telling me that my formula assigning 1 or 0 cannot be summarized.

Does anybody have any other ideas? I hope I have given enough information - if not, just ask me and I'll answer any further questions.

Thanks in advance.



Jason

 

[Madawc]

To clarify, you have a number of "shop orders" that contain several "components". If any one of these components is zero or negative, you want to avoid showing the entire shop order. Otherwise you want to show all components?

You could get the right answer for the group footer of the "shop orders" group. You'd need to do this using variables, I think. Sum for a component and then add 1 to a count if the result is negative. Check at the end if this count is greater than zero.

But to show the "shop orders" that do qualify, I think you'd need to have a subreport in the group footer section, re-doing the work of accumulating and showing the components.

In Crystal-with-SQL, it would also be possible to get the same result using an SQL Component (Stored Procedure), writing details to a temporary table and then deleting. I'd suppose that's not relevant to your case.

Madawc Williams (East Anglia, UK). Using Crystal 10 & 11.5 with Windows XP

 

[lbass]

Try writng a SQL expression {%qty} that sums the qty on hand at the item level, e.g.,

(
select sum(`lqty`)
from table A
where A.`itemno` = table.`itemno` and
A.`shoporderno` = table.`shoporderno`
)

This assumes that the shop order is unique to the department/customer/releasedate. Adjust the punctuation to reflect that for your datasource.

Once you have this you can write a formula {@qtytest} like this:

if {%qty} > 0 then 1

Then use the group selection:

sum({@qtytest},{table.shoporderno}) =
distinctcount({table.itemno},{table.shoporderno})

-LB

 

[RedHeadedStepITChild]

lbass,

Thanks for the help.

I created the SQL expression and formula as suggested. For whatever reason the results of the summation of the @qtytest where not what was to be expected. I cannot figure out why it is off. I inserted a distinct cout of @qtytest and did a group selection to show shopOrder = distinctcount = 1 (if every item has positive quantity on hand, then the only result for @qtytest will = 1)

I'm not 100% sure why doing a formula based off the sql expression works and creating a formula based off a summary of the quantity on hand for the component doesn't - but the end result is what I want! Thanks again.

 

[lbass]

Please clarify whether the SQL expression is returning the correct value. If it isn't, you need to make sure you are building any selection criteria that apply to the report into the SQL expression.

If it is returning the correct value, then please post the actual formulas you created in the field explorer and in the group selection formula area.

-LB