Zero Value Group 1

[rewob65]

I have the follwoing data:-

Details Sep06 4
Details Oct06 6
Details Nov)6 0
Group 0 (being the final data not a total)

The numbers are the stock level for a stock line. What I want to do is supress the line at group level if the final stock figure is zero, how do I do this>

Thanks

 

[lbass]

You should be able to use a section suppression formula on the GF like this:

{table.stockqty} = 0

...since the GF will pick up the last value in the details section.

-LB

 

[rewob65]

This is brilliant. The main problem I have is when the group total is in fact a formuala, this does not work.

Can I supress lines where say the formula give say zero or even say the formula is less than a number.


Thanks

 

[lbass]

Please show the content of the formula you are referring to. You stated in your original post that you wanted to suppress based on the last value in the details section, not on the group total, so please clarify what you are doing.

-LB

 

[rewob65]

Sorry for being unclear, I have say two groups of numbers say:-

D 6 3 50%
D 5 2 40%
D 3 2 60%

G 14 7 50%

Obviously there are may grouped fields the formula is the percentage of the third and second row. At the group level I would only want to display items above a precentage of say 60%

Thanks

 

[lbass]

Again, please show the content of the formula used for percentage in the group footer. From your example, I cannot tell what you are doing. And then are you asking a second question? About how to display only those groups with a percentage above 60%?

-LB

 

[rewob65]

I am sorry. I should have reposted as I do know my question was more complex than i thought. the formula I used was:-


if Sum ({HIST__Q4Contract_Poo.DELVDelivered_Qty}, {IASE__I4Contract_Hea.ACCTCustomer_account}) =0 then 0 else


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

*100

this gives a percentage of the rejects. I am only interest in rejects above a percent.

Sorry and thanks

 

[lbass]

You should be able to go to report->selection formula->GROUP and enter:

Sum ({HIST__Q4Contract_Poo.DELVDelivered_Qty}, {IASE__I4Contract_Hea.ACCTCustomer_account}) > 0 and
Sum ({HIST__Q4Contract_Poo.REJTRejects}, {IASE__I4Contract_Hea.ACCTCustomer_account}) %
Sum ({HIST__Q4Contract_Poo.DELVDelivered_Qty}, {IASE__I4Contract_Hea.ACCTCustomer_account}) > 60

-LB

 

[rewob65]

Thanks for that, I will check it out on Monday. I take it your % should be / . Also is there anyway to sort by this column?

 

[lbass]

No, use '%' --then you don't need to add the '*100'.

You should always post your CR version. In order to sort, you would have to return the summaries to the report directly, rather than in formulas. How you do this depends upon your version--in a SQL expression in versions less than 9.0 or in a command.

Note also that you are posting in the wrong forum. In the future, try forum149 or forum767.

-LB

 

[rewob65]

I am using crystal XI, I have valued your help over the past week, as this has been a problem on so many of my reports, and cannot find courses that run to this level.

 

[lbass]

On what field are the two tables HIST__Q4Contract_Poo and IASE__I4Contract_Hea linked? What is your datasource?

-LB

 

[rewob65]

{IASE__I4Contract_Hea.CON_Contract_no} {HIST__Q4Contract_Poo.CON_Contract}

are the linked fields

 

[lbass]

You didn't mention your datasource, but try setting up a command (database->database expert->your datasource->add command something like this:

Select sum(HIST__Q4Contract_Poo.`DELVDelivered_Qty`) as delqty, sum(HIST__Q4Contract_Poo.`REJTRejects`) as rejqty, IASE__I4Contract_Hea.`ACCTCustomer_account`
From IASE__I4Contract_Hea inner join HIST__Q4Contract_Poo on
IASE__I4Contract_Hea.`CON_Contract_no` = HIST__Q4Contract_Poo.`CON_Contract`
Group by IASE__I4Contract_Hea.`ACCTCustomer_account`

Then link this command to IASE__I4Contract_Hea on the account field. Then create a formula {@rejpct}:

if {command.delqty} > 0 then
{command.rejqty} % {command.delqty}

Place {@rejpct} in the detail section and insert a maximum on it (you have to have an inserted summary to do a group sort). And then go to report->group sort and select maximum of {@rejpct} as your group sort field.

The syntax/punctuation of the command will be specific to your datasource, so if you're unsure, check how the Show SQL Query is set up under 'Database'.

-LB

 

[rewob65]

Thanks for that I have tried and failed but see what you mean as a data source. this is a database called "Wwear

 

[rewob65]

This was the Sql query:-


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"."REF_Contract_referce",
"IASE__I4Contract_Hea"."SDESSales_rep_descr",
"IASE__I4Contract_Hea"."BDESBusiness_descrip"

FROM (((("wwear"."IASE__I4Contract_Hea" "IASE__I4Contract_Hea" INNER
JOIN "WWEAR"."HIST__Q4Contract_Poo" "HIST__Q4Contract_Poo" ON
"IASE__I4Contract_Hea"."CON_Contract_no"="HIST__Q4Contract_Poo"."CON_Contract")
INNER JOIN "WWEAR"."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 "WWEAR"."DROP__C5Linen_Counte" "DROP__C5Linen_Counte" ON
"HIST__Q4Contract_Poo"."LCNTLinen_Product"="DROP__C5Linen_Counte"."LCNTLINEN_COUNTER")
INNER JOIN "WWEAR"."IASE__I1Product_Grou" "IASE__I1Product_Grou" ON
"DROP__C5Linen_Counte"."PG__Product_Group"="IASE__I1Product_Grou"."PG__Product_group")
INNER JOIN "WWEAR"."DROP__E1Sales_Analys" "DROP__E1Sales_Analys" ON
"DROP__C5Linen_Counte"."ANALSales_analysis"="DROP__E1Sales_Analys"."ANALSales_analysis"

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

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



The problem seems to be that delqty is not defined, but that may just be me!