Complex SQL Statement in Crystal or Store Procedure

[AlreadyLost]

I have an enhancement to my existing report that I have no clue how to write formula in Crystal. I asked my tech support and he suggest that I write a sql in store procedure and pull the store procedure from Crystal.

I never done store procedure before. Within crystal 10, I never pull store procedure before. I don't want to use store procedure unless I have too. Is it possible to do something in crystal?

My users wants to filter out all invoice amounts in the line item that have an ProgramCode of 9915:

Example data:
Inv.ID ProgramCode Description RelatedProgramCode Amt

IN123 1002 Payment 1002 $40

IN123 2400 Payment 2400 $60

IN123 9915 Adjustment 1002 $40

My report should only show ProgramCode 2400 with amount $60 as a result.

I already have in my report on the selection formula to exclude ProgramCode 9915. But my report still show Program Code 1002 with amount of $40.

Is it possible to filter ProgramCode of 9915 and all it's RelatedProgramCode in the report using Crystal?

I try to use this formula in selection formula, but I got an error "A boolean is require here".

{TB_FIN_INVOICE.INVOICE_DATE} >= {?Start Date} and
{TB_FIN_INVOICE.INVOICE_DATE} <= {?End Date}
and
(if {TB_INVOICE_LINE_ITEM.PE} = {TB_INVOICE_LINE_ITEM.RELATED_PE}
and {TB_INVOICE_LINE_ITEM.PE} <>{TB_INVOICE_LINE_ITEM.RELATED_PE}
then {TB_INVOICE_LINE_ITEM.PE})

 

[AlreadyLost]

Here is what I have for HazAmt:

RunningTotalName: HazAmt

Field to summarize: TB_INVOICE_LINE_ITEM.PE
I also try TB_INVOICE_LINE_ITEM.AMOUNT

Type of summary: Maximum

Evalute: Use a formula: if {TB_FIN_INVOICE_LINE_ITEM.PE} in ['1002', '2400', '4502']
then ({TB_FIN_INVOICE_LINE_ITEM.AMOUNT})
else 0.00

Reset: On Change of group -Group#1:TB_INVOICE.INVOICE_ID

Then I pull this field next to the running total of max of HazPE (per your suggestion)on the Group Footer#1.

Result when I run the report: that field is blank.

 

[lbass]

Why are you doing it differently than suggested?

If you the field is supposed to show an amount, then select that field, maximum, evaluate using a formula:

{TB_FIN_INVOICE_LINE_ITEM.PE} in ['1002', '2400', '4502']

Reset on change of group (invoice).

-LB

 

[AlreadyLost]

I love you ... thank you soo much.. I didn't meant to do it differently than suggested. I just hated this report (been working on it for 1 month+) and still very new in Crystal that when I read a post I am trying to figure out and understand what it is doing.

Thanks so much!!!