MultiOrders MultiValues 2

[CrystaLv]

Result of my SQL is

OrderNo Result

123 NC
123 Completed

122 NC

112 NC
112 NC

So I can have several similar Order Numbers but i need to select only those with "Completed" as a Result.
I will be writing it in CR 9.

Thanks

 

[lbass]

Are "NC" and "Completed" the only possible results for that field? Do you want to show all records for any group that contains "Completed" or only those rows with "Completed"?

-LB

 

[CrystaLv]

No, there are tons of values in this field but as long as one = 'Completed' - whole thing is belong on the Report.

I will want to aknowlege orderNo - 123 as Completed and possibly count Orders like 2+1 for the 123.

OrderNo Result Orders

123 NC 1
123 Completed 2

So result will be
OrderNo Orders

123 3

Thanks

 

[synapsevampire]

Real SQl would do this best, in Crystal you might create a formula of:

if {table.result} = "Completed" then
1
else
0

Group by the Orderno and in the suppress for the Group and details place:

sum({@MySumFormula},{table.group}) = 0

Now the report will only display those that meet your criteria, and you can use a conventional right click field->Insert->Summary-Count to show a count of the orders in the group footer.

-k

 

[CrystaLv]

Thanks, it make sense at least fo now before thay came up with other specs

 

[CrystaLv]

Today specs changed and now I have to show those <>"Completed"

CusID OrderNo Result
1 123 NC
1 123 Completed
1 123 NA
1 111 NA
1 111 Completed

2 124 NA
2 124 NC

So result will be :
CusID NotCompleted
2 1


Thanks

 

[CrystaLv]

OK, here is what my problem grew into.

Contract Order Stage
1 12 Complete
1 12 NC
1 13 Complete
1 13 NA
1 14 NC


2 10 <NULL>
2 11 <NULL>

3 20 <NULL>

Output expected:
Contract CompleteOrd NonComplete
1 2 1
2 0 2
3 0 1

Complete order is the one that has Stage Complete for it.

Thanks for your time

 

[CrystaLv]

HI! It all works except when Stage isnull it wouldn't pick up. Like it does not exist. Thanks

 

[CrystaLv]

I think I got it...THANKS!!!!!!!!!!

 

[CrystaLv]

I am working with live data and everything worked until one more record was entered.


these 2 orders have to be grouped by Contract but it is not happening.
Is there way? So I am using combination of Order and Stage to determine Complete Orders. It works fine. I am filtering Complete Orders out by having
sum({@MySumFormula},{table.group}) > 0

I have following displayed :
Contract Order Stage

1 14 NC
1 15 NC

2 10 <NULL>
2 11 <NULL>

3 20 <NULL>
______________________________________________

Now I need to get it to a :

Contract Incomplete

1 2


2 2


3 1

Thanks so much

 

[lbass]

Group on contract and use a formula like:

if isnull({table.stage}) or
{table.stage} <> "Complete" then 1

Then insert a sum (not a count) on this formula and suppress the detail section. Drag the groupname into the group footer next to the summary.

-LB

 

[CrystaLv]

AAAAAAAA! it did it!!! Oh, thank so much, I would never had it done without your guidance and help. Thanks