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})

 

[synapsevampire]

Tis part of your record selection doesn't make sense:

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}

There is no THEN just a field, the record selection does filtering so a comparison should be involved.

I think that you want:

(
{TB_FIN_INVOICE.INVOICE_DATE} >= {?Start Date}
and
{TB_FIN_INVOICE.INVOICE_DATE} <= {?End Date}
)
and
(
{table.RelatedProgramCode} = 2400
)

Adjust the field name.

-k

 

[lbass]

You could use your date criteria only in the record selection formula. Then insert a group on {table.relatedprogramcode} and then create a formula in the formula expert {@9915}:

if {table.programcode} = 9915 then 1

Then go to report->selection formula->GROUP and enter:

sum({@9915},{table.relatedprogramcode}) = 0

Note that if you then want to do calculations that cross groups, you will need to use running totals, since non-group selected records would contribute to inserted summaries.

-LB

 

[AlreadyLost]

Thanks for the help.

synapsevampire, my record selection does not make sense because I was try an if statement to filter out PE 9915 and all it's related PE. I cannot use this (
{table.RelatedProgramCode} = 2400) because I could have different RelatedProgramCode in my report besides 2400. Sorry that I did not clarify what I needed.

lbass,
I try your suggestion also and it works but I don't understand how or why it works. What does this do?
" insert a group on {table.relatedprogramcode} and then create a formula in the formula expert {@9915}:

if {table.programcode} = 9915 then 1"

 

[AlreadyLost]

After the suggestion from lbass, I ran the report and this whole invoice is gone. The invoice should be in my report.

My invoice ID is at the Group footer.

 

[AlreadyLost]

I also have a field call Max@ProgramCode and Max@Amount on my group footer(grouping by invoice ID).

 

[lbass]

Please explain exactly what you did in trying to implement my suggestion.

-LB

 

[AlreadyLost]

Here is what I did:
1. I insert group on {table.relatedprogramcode}.
2. Went to "Formula Field" and right click new.
3. Name this field @PE. Then added this formula as suggested: If {TB_INVOICE_LINE_ITEM.PE} = '9915'
then 1
4. Then go to report->selection formula->GROUP and enter:
sum({@PE},{table.relatedprogramcode}) = 0
5. I then suppress the group header, group footer for {table.relatedprogramcode}
6. Run the report.

 

[lbass]

When you respond again, please use your exact code, not my generic version. Please copy your record selection formula into the post. Go to report->selection formula->RECORD and copy the statement. Also, please describe your current group structure.

-LB

 

[AlreadyLost]

Sorry,

Here is the formula from report>selection formula>Record:

{TB_INVOICE.INVOICE_DATE} >= {?Start Date} and
{TB_INVOICE.INVOICE_DATE} <= {?End Date}

Here is the formula from report>selection formula>Group:

sum({@PEOut}, {TB_INVOICE_LINE_ITEM.RELATED_PE})=0

Here is @PEOut formula: If {TB_INVOICE_LINE_ITEM.PE} = '9915'
then 1

Group structure:
Group Header #1: TB_INVOICE.INVOICE_NO - suppress
Group Header #2: TB_FACILITY.FACILITY_ID - suppress
Group Header #3: TB_INVOICE_LINE_ITEM.RELATED_PE - suppress

Group Footer #3: TB_INVOICE_LINE_ITEM.RELATED_PE - suppress
Group Footer #2: TB_FACILITY.FACILITY_ID - suppress
Group Footer #1: TB_INVOICE.INVOICE_NO

In Group Footer#1: I have to following field:

1.Group #2 Name
2. Group #1 Name
3. Facility Name
4. Max @HazPE - if {TB_INVOICE_LINE_ITEM.PE} in ["1002","2400","4502"]
then {TB_INVOICE_LINE_ITEM.PE}

5. Max @HazAmt - if {TB_INVOICE_LINE_ITEM.PE} in ['1002', '2400', '4502']
then ({TB_INVOICE_LINE_ITEM.AMOUNT})
else 0.00

 

[lbass]

This should work perfectly--it should eliminate all relatedprogramcode groups that contain the program code 9915. This would only eliminate invoices if all groups of related program codes contained 9915. You should comment out the group selection formula and check your detail level data (place the codes in the detail section to check). Note that your maximums will not work correctly if you are using group selection, as they will take into account codes containing 9915. You should use running totals instead.

-LB

 

[AlreadyLost]

My detail section is suppress.

 

[lbass]

Unsuppress it and use it to check your data.

-LB

 

[AlreadyLost]

I have this formula in my detail suppress x2 ({@HazPE} = "").

I remove it and create a test field with this formula:
sum({@PEOut}, {TB_FIN_INVOICE_LINE_ITEM.RELATED_PE})=0

And did a search on the Invoice ID and still this invoice ID is not found.

 

[lbass]

What do you mean "This invoice ID is not found"? Don't you have more than one invoice? If a particular invoice isn't showing up even after removing the suppression and group selection criteria, then it doesn't meet your record selection criteria.

You need to comment out the group selection, too. Place the fields for program code and program related code on the line so that you can see whether any of your invoices meet the group selection criterion.

If you still don't see the invoice you expect to see, remove your record selection, too, so that you can why this invoice isn't showing up.

-LB

 

[AlreadyLost]

lbass,

After analyzing your suggestion:
"You could use your date criteria only in the record selection formula. Then insert a group on {table.relatedprogramcode} and then create a formula in the formula expert {@9915}:

if {table.programcode} = 9915 then 1

Then go to report->selection formula->GROUP and enter:

sum({@9915},{table.relatedprogramcode}) = 0"

If ProgramCode is 9915 (Credit Adjustment to ProgramCode 1002)with amount $40. The RelatedProgramCode would be 1002.
Will your suggestion remove both amount of $40 dollars?
Meaning, I do not want Line with ProgramCode 1002 and ProgramCode 9915 on my report. See below same sample and result what the output should look like.

Example data:
Inv.ID ProgramCode Description RelatedProgramCode Amt

IN123 1002 Payment 1002 $40

IN123 2400 Payment 2400 $60

IN123 9915 Adjustment 1002 $40


Result:
Inv.ID ProgramCode Description RelatedProgramCode Amt

IN123 2400 Payment 2400 $60


--In the meanwhile, I will check my report again base on your last post.

 

[AlreadyLost]

When I use running total, the data does not come out correct, only max works. What is the work around if I needed to use max base on your comment here:
"Note that your maximums will not work correctly if you are using group selection, as they will take into account codes containing 9915. You should use running totals instead."

Here is my Max @HazPE formula again - if {TB_INVOICE_LINE_ITEM.PE} in ["1002","2400","4502"]
then {TB_INVOICE_LINE_ITEM.PE}

My suppress detail I also have this next to suppress x2 {@HazPE} = ""

 

[lbass]

Yes, to answer your first question.

I meant for you to use maximum in your running total. Select {TB_INVOICE_LINE_ITEM.PE}, maximum, evaluate using a formula:

{TB_INVOICE_LINE_ITEM.PE} in ["1002","2400","4502"]

Reset on Change of Group (Invoice #).

You detail suppression formula will suppress all detail rows except those with {TB_INVOICE_LINE_ITEM.PE} in ["1002","2400","4502"].

-LB

 

[AlreadyLost]

okay.. I got that to work.

Now my PE is showing up but the amount is incorrect. It is not showing the amount of $60.

My amount field is max@HazAmt with this formula:
(if {TB_FIN_INVOICE_LINE_ITEM.PE} in ['1002', '2400', '4502']
then ({TB_FIN_INVOICE_LINE_ITEM.AMOUNT})
else 0.00

By the way, thanks so much for helping. I really appreciated. I was about to give up on this report.

 

[lbass]

You have to use a running total for that also. Use maximum of the amount field, evaluate using a formula (as before), reset on change of group (invoice).

-LB