Group Sort on calculated percentage 1

[rewob65]

Hi I am using Crystal XI, and can group sort on:-

Sum ({HIST__Q4Contract_Poo.REJTRejects}, {IASE__I4Contract_Hea.ACCTCustomer_account})

and also on this.

Sum ({HIST__Q4Contract_Poo.DELVDelivered_Qty}, {IASE__I4Contract_Hea.ACCTCustomer_account})

I am interested in sorting on this:-

Sum ({HIST__Q4Contract_Poo.REJTRejects}, {IASE__I4Contract_Hea.ACCTCustomer_account})
/
Sum ({HIST__Q4Contract_Poo.DELVDelivered_Qty}, {IASE__I4Contract_Hea.ACCTCustomer_account})

which is purely one figure divided by the other. I have no idea how I can sort this last formula in decending order, any ideas?

Many Thanks

 

[lbass]

Create a SQL expression {%ratio} like this, assuming the two tables are linked on what I'll call "linkfield". Substitute the correct field:

(
select sum(`REJTRejects`)
from HIST__Q4Contract_Poo A, IASE__I4Contract_Hea B
where A.`linkfield` = B.`linkfield` and
B.`ACCTCustomer_account` = IASE__I4Contract_Hea.`ACCTCustomer_account`
)/
(
select sum(`DELVDelivered_Qty`)
from HIST__Q4Contract_Poo A, IASE__I4Contract_Hea B
where A.`linkfield` = B.`linkfield` and
B.`ACCTCustomer_account` = IASE__I4Contract_Hea.`ACCTCustomer_account`
) * 100

The punctuation would be specific to your datasource, so you also need to correct that. Look at database->show SQL query if you are unsure how to punctuate table and field names.

Place the SQL expression in the detail section and insert a maximum on it (this is necessary to activate the group sort). Then go to report->group sort and choose maximum of %ratio as your sort field.

-LB

 

[rewob65]

This is my current sql expression but try what I may I cannot may it work. This is my format, I tired changing all you ' with " but no good. I appreciate this is more than the call of duty, but if I can solve this I can relax


SELECT "HIST__Q4Contract_Poo"."CON_Contract",
"HIST__Q4Contract_Poo"."LOQTLIST_OUR_QTY",
"HIST__Q4Contract_Poo"."INC_INCREASE",
"HIST__Q4Contract_Poo"."DEC_DECREASE",
"HIST__Q4Contract_Poo"."DELVDelivered_Qty",
"HIST__Q4Contract_Poo"."NRNDNew_Rounding",
"HIST__Q3CONTRACT_POO"."DTE_Date", "HIST__Q3CONTRACT_POO"."DDTEDUE_DATE",
"DROP__C5Linen_Counte"."DES_DESCRIPTION",
"HIST__Q3CONTRACT_POO"."LNO_List_No",
"HIST__Q3CONTRACT_POO"."INV_1_INVOICED",
"HIST__Q3CONTRACT_POO"."RTE_Route", "HIST__Q3CONTRACT_POO"."DROPDrop",
"HIST__Q4Contract_Poo"."CPRIClean_Price",
"HIST__Q4Contract_Poo"."TPCKTotal_Packs",
"HIST__Q4Contract_Poo"."REJTRejects", "HIST__Q4Contract_Poo"."SHRTShorts",
"HIST__Q4Contract_Poo"."ORNDOld_Rounding",
"IASE__I4Contract_Hea"."ACCTCustomer_account",
"DROP__C5Linen_Counte"."LCNTLINEN_COUNTER",
"IASE__I1Product_Grou"."PG__Product_group",
"DROP__C5Linen_Counte"."ANALSales_analysis",
"DROP__E1Sales_Analys"."DES_Description",
"IASE__I4Contract_Hea"."SDESSales_rep_descr",
"IASE__I4Contract_Hea"."BDESBusiness_descrip",
"CUST__CUCUSTOMER_DET"."NME_Customer_Name"

FROM ((((("IASE__I4Contract_Hea" "IASE__I4Contract_Hea" INNER JOIN
"HIST__Q4Contract_Poo" "HIST__Q4Contract_Poo" ON
"IASE__I4Contract_Hea"."CON_Contract_no"="HIST__Q4Contract_Poo"."CON_Contract")
INNER JOIN "CUST__CUCUSTOMER_DET" "CUST__CUCUSTOMER_DET" ON
"IASE__I4Contract_Hea"."ACCTCustomer_account"="CUST__CUCUSTOMER_DET"."ACC_Customer_Account")
INNER JOIN "HIST__Q3Contract_Poo" "HIST__Q3CONTRACT_POO" ON
("HIST__Q4Contract_Poo"."CON_Contract"="HIST__Q3CONTRACT_POO"."CON_Contract")
AND
("HIST__Q4Contract_Poo"."LNO_List_No"="HIST__Q3CONTRACT_POO"."LNO_List_No"))
INNER JOIN "DROP__C5Linen_Counte" "DROP__C5Linen_Counte" ON
"HIST__Q4Contract_Poo"."LCNTLinen_Product"="DROP__C5Linen_Counte"."LCNTLINEN_COUNTER")
INNER JOIN "IASE__I1Product_Grou" "IASE__I1Product_Grou" ON
"DROP__C5Linen_Counte"."PG__Product_Group"="IASE__I1Product_Grou"."PG__Product_group")
INNER JOIN "DROP__E1Sales_Analys" "DROP__E1Sales_Analys" ON
"DROP__C5Linen_Counte"."ANALSales_analysis"="DROP__E1Sales_Analys"."ANALSales_analysis"

WHERE NOT ("DROP__C5Linen_Counte"."DES_DESCRIPTION"='Bags' OR
"DROP__C5Linen_Counte"."DES_DESCRIPTION"='Cages' OR
"DROP__C5Linen_Counte"."DES_DESCRIPTION"='Cages orange') AND
("HIST__Q3CONTRACT_POO"."DDTEDUE_DATE">={d '2008-07-15'} AND
"HIST__Q3CONTRACT_POO"."DDTEDUE_DATE"<={d '2008-07-21'})

ORDER BY "IASE__I4Contract_Hea"."ACCTCustomer_account",
"DROP__C5Linen_Counte"."LCNTLINEN_COUNTER"

 

[rewob65]

I have tried all day to soleve this is this the linked field, which you refer in all probablity ( I am aware you do not know my database) or am I going down the wrong track?


where A."IASE__I4Contract_Hea"."CON_Contract_no" = B."HIST__Q4Contract_Poo"."CON_Contract

 

[CaptainD]

What database are you using?

I usually work with MS SQL Server or MS Access and when creating SQL espressions, the table and field names do not have ' ' or " " marks around them.

Reading your SQL I did notice some double entries at your inner joins. Is that correct?

FROM ((((("IASE__I4Contract_Hea" "IASE__I4Contract_Hea" INNER JOIN
"HIST__Q4Contract_Poo" "HIST__Q4Contract_Poo" ON
"IASE__I4Contract_Hea"."CON_Contract_no"="HIST__Q4Contract_Poo"."CON_Contract")
INNER JOIN "CUST__CUCUSTOMER_DET" "CUST__CUCUSTOMER_DET" ON
"IASE__I4Contract_Hea"."ACCTCustomer_account"="CUST__CUCUSTOMER_DET"."ACC_Customer_Account")
INNER JOIN "HIST__Q3Contract_Poo" "HIST__Q3CONTRACT_POO" ON
("HIST__Q4Contract_Poo"."CON_Contract"="HIST__Q3CONTRACT_POO"."CON_Contract")
AND
("HIST__Q4Contract_Poo"."LNO_List_No"="HIST__Q3CONTRACT_POO"."LNO_List_No"))
INNER JOIN "DROP__C5Linen_Counte" "DROP__C5Linen_Counte" ON
"HIST__Q4Contract_Poo"."LCNTLinen_Product"="DROP__C5Linen_Counte"."LCNTLINEN_COUNTER")
INNER JOIN "IASE__I1Product_Grou" "IASE__I1Product_Grou" ON
"DROP__C5Linen_Counte"."PG__Product_Group"="IASE__I1Product_Grou"."PG__Product_group")
INNER JOIN "DROP__E1Sales_Analys" "DROP__E1Sales_Analys" ON
"DROP__C5Linen_Counte"."ANALSales_analysis"="DROP__E1Sales_Analys"."ANALSales_analysis"

This is not answering your question but it might make your SQL expression easier to read.

With the 2 database formats I listed you can also alias your table names which make the SQL expressions easier to read. (Yours might)

Example:

SELECT a.FirstName, a.LastName, b.PhoneNumber FROM tblPersonnel a INNER JOIN tblPhoneNumbers b ON a.PersonnelID = b.PersonnelID

Note the a and b following the table names.

 

[lbass]

What you showed is NOT a SQL expression, but I'm guessing it is your SQL query from database->show SQL query. To create a SQL expression, go to the field explorer->SQL expression->new. Note that you cannot return multiple fields in a SQL expression.

Yes, that would have been the correct linking field, but now the problem is that you are using so many tables (and the additional equal joins are further limiting the data), and you have a selection formula that needs to be built into the SQL expression (since it accesses the database directly), making it very complex.

-LB

 

[rewob65]

I am using pervasive 9, but I think it appears I do not have the skills to do this. Broadly I am sorting by a second pass record and there appears to be no easy way around it.

I will have to export to Excel and sort from there which does not take long. I had been aiming to get crystal report server to kick it out to users.

Than you for all the help