Group by subtotal or running total percentage 1

[ngkatsaras]

[tt]
I have a "shipment completion report" as follows:

Order # Qty Ordered Qty Shipped %complete

100 20 18 90.00%
101 10 7 70.00%
102 10 4 40.00%
103 20 14 70.00%
------------ ----------- ---------
60 43 71.67%

How can I group based upon %complete? As follows:

Order # Qty Ordered Qty Shipped %complete

102 10 4 40.00%
Count of orders 40% = 1

101 10 7 70.00%
103 20 14 70.00%
Count of orders 70% = 2

100 20 18 90.00%
Count of orders 90% = 1

The next thing I need is to count the orders that fall within a certain percentage of completion:

75-100% = 1
50-74% = 3
<50 = 1

Actually if I could group based upon the above criterial that would be ideal. I just need three groups and a count of the orders in each group.

The orders shown above are actually groups, the detail of each group is shown below:

Order # Item Qty Ordered Qty Shipped %complete

100 20 18 90.00%
Pencil 5 5 100.00%
Pen 10 10 100.00%
Eraser 5 3 60.00%

101 10 7 70.00%
Pencil 5 5 100.00%
Pen 5 2 40.00%

So what I need to do is group based upon either subtotals or running totals, actually the percentage of one subtotal divided by another subtotal and the groups need to be within a range of numbers.

I really appreciate any help.

Sincerely,

NK
[/tt]

 

[lbass]

What version of CR are you using? Is SQL expression an option in the field explorer?

-LB

 

[ngkatsaras]

Hi LB,

Thanks for your prompt reply.
We are running CR version 8.5.0.217
This version comes as a bundle with our ERP package and they don't support a higher version.

Yes, we have SQL expression as an option in the field explorer.

Thanks in advance, I was hoping you would come to my rescue, I have seen some of the magic you have worked for others here!

Sincerely,

NK

 

[Madawc]

Grouping is done on the basis of values for a single record. It can't be done on the basis of running totals because they are derived from more than one record.

Assuming "%complete" is based just on "Qty Ordered" and "Qty Shipped" for a single record, you could group on that. But not on a subtotal, though you can sort your groups on the basis of group values, see "Top N".

Your version is 8.5; I'm not sure what the other numbers mean but they don't indicate any major change in function. See here for the differences between the version.

You might also look at crosstabs. Though I've a feeling 8.5 did not allow calculated figures to be used.

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

 

[lbass]

Create two SQL expressions:

[{%qtyordered:}]
(select sum(A.`QtyOrdered`) from table A where
A.`Order ID` = table.`Order ID`)

[{%qtyshipped}:]
(select sum(A.`QtyShipped`) from table A where
A.`Order ID` = table.`Order ID`)

Substitute your exact field names for QtyOrdered, QtyShipped, and Order ID, and replace "table" with your table name, leaving "A" as is (alias table name). If you have other record selection criteria, these might need to be built into the expression.

Then create a formula {@percentshipped}:

if {%qtyordered} <> 0 then
{%qtyshipped} % {%qtyordered}

Place this in your detail section and insert a maximum on it. Then you can use this for a topN/sort. You can also group on a formula like:

if round({@percentshipped}) in 75 to 100 then "75 to 100%" else
if round({@percentshipped}) in 50 to 74 then "50 to 74%" else
"< 50%"

-LB

 

[ngkatsaras]

Thanks, LB, that looks promising, but I haven't been able to get it to work.

These are my real field names:

OELINHST_SQL.'ord_no'
OELINHST_SQL.'qty_ordered'
OELINHST_SQL.'qty_to_ship'

Here is my best guess as to what to do (I know I'm doing something wrong, I tried a lot of variations):

(select sum(A.`qty_ordered`) from A where
A.`ord_no` = OELINHST_SQL.`ord_no`)

I'm getting "Error in compling SQL Expression : Syntax error found here." right after "sum"

In CR, I usually see the double quote, are we using single quotes because it's an SQL expression?

I haven't tried the formula grouping at the end yet, but I will see if I can get anywhere with that.

Thanks very much!

Nick

 

[lbass]

First, go to database->show SQL query and see how the punctuation appears for your datasource, and then use the same punctuation in the SQL expression. Note that you need to include your table name before "A" in the first line:

(select sum(A.`qty_ordered`) from OELINHST_SQL A where
A.`ord_no` = OELINHST_SQL.`ord_no`)

Also note that the position of the cursor for error messages related to SQL expressions is usually not indicative of the location of the error.

-LB

 

[ngkatsaras]

Thanks LB, but I'm still getting that same error message:

[tt]
(SELECT SUM(A."qty_ordered") FROM OELINHST_SQL A WHERE A."ord_no" = OELINHST_SQL."ord_no")

Here is my SQL query:

SELECT
OEHDRHST_SQL."ord_no", OEHDRHST_SQL."ord_dt", OEHDRHST_SQL."oe_po_no", OEHDRHST_SQL."cus_no", OEHDRHST_SQL."inv_no", OEHDRHST_SQL."inv_dt",
OELINHST_SQL."item_no", OELINHST_SQL."qty_ordered", OELINHST_SQL."qty_to_ship", OELINHST_SQL."promise_dt", OELINHST_SQL."picked_dt"
FROM
{ oj "DATA"."dbo"."OEHDRHST_SQL" OEHDRHST_SQL INNER JOIN "DATA"."dbo"."OELINHST_SQL" OELINHST_SQL ON
OEHDRHST_SQL."ord_type" = OELINHST_SQL."ord_type" AND
OEHDRHST_SQL."ord_no" = OELINHST_SQL."ord_no"}
WHERE
OEHDRHST_SQL."inv_dt" <> 0 AND
OEHDRHST_SQL."cus_no" <> 'EMP100'
ORDER BY
OEHDRHST_SQL."ord_no" ASC
[/tt]

Thanks,

Nick

 

[lbass]

This is where it always gets sticky trying to help with SQL expressions, as the syntax is specific to your datasource. Maybe try:

(select sum(A."qty_ordered") from "DATA"."dbo"."OEHDRHST_SQL" A where
A."ord_no" = "DATA"."dbo"."OEHDRHST_SQL"."ord_no")

-LB

 

[ngkatsaras]

I tried that but still the same error...

 

[lbass]

I'm sorry, but I don't know how else to help, since I can't test. I think this is a punctuation/syntax issue specific to your database/connectivity. Maybe if you identify your datasource and your connectivity, someone else will be able to assist. Be sure to specify that you are using 8.5, as in later versions the SQL expressions have different functionality and you would have to do something like this in a command instead.

You might need to start a new thread to get a response from another reader.

-LB

 

[ngkatsaras]

The datasource is MS SQL Server 2000, is that what you need to know, or something more detailed? I would be happy to provide you with whatever info would be helpful.