Opposite of the Complex SQL Post

[AlreadyLost]

Hi,

I had posted a complex sql problem to filter out a PE. Now I have another report that the users want to show the PE.

Here is my same example:

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 9915 Adjustment 1002 $40


This is the formula that I use to filter out 9915 but Now I need it in a new report. So I use the old report and modify the formula. How do I modify this formula so that it will bring in 9915 and it's relatedProgramCode?

if {table.programcode} = 9915 then 1

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

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

 

[lbass]

Change the group selection formula to:

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

-LB

 

[AlreadyLost]

Will this formula still stay the same?
If {TB_FIN_INVOICE_LINE_ITEM.PE} = '9915'
then 1

 

[AlreadyLost]

Sorry.. dumb question.

 

[AlreadyLost]

One more last question. I need to also bring in ProgramCode 9903 with RelatedProgramCode 9903.

So I created a running total that evaluate on use a formula:

{TB_FIN_INVOICE_LINE_ITEM.RELATED_PE} in ["9903"] but nothing show up for this field. Please advice.

 

[lbass]

You can't use running totals for this. Change your original formula to:

If {TB_FIN_INVOICE_LINE_ITEM.PE} in['9915','9903']
then 1

Then when you use the group selection, any groups containing either of these code will appear.

-LB

 

[AlreadyLost]

Understood what you say. If you can I cannot use a running total for this field, I need to display this line on my group footer with the amount.

I did everything you say for the report. The report brings in everything that has a ProgramCode of 9915 with whatever the relatedProgramCode is, it will display the amount that I created with Running Total.

(Will use 9915)
Now, my users wanted to bring in ProgramCode of 9915 with RelatedProgramCode 9915.

What I did was: create a running total of "OverPaymentAmount""like I would for all the other RelatedProgramCode and drag it into the GroupFooter of my report. This RelatedProgramCode does not display the amount for this particular ProgramCode 9915.

Since you can I cannot use running total, what should I do?

I have all running total on my groupfooter.. this new field "OverpaymentAmount" will also need to be display on my groupfooter.

 

[lbass]

I am not saying you cannot display running totals in your group footer. I meant that you cannot use a running total as a basis for group selection. If you want a running total that uses code 9903 to appear, you have to make sure the groups containing that code appear. Right now, they will not because you have a group selection just for the presence of 9915. The only thing you have to do to allow both to show is change the initial formula by adding the 9903 code, as in my previous post. This should allow the appropriate groups to appear. If your running total is still not appearing, then comment out the group selection and check your running total by placing it in the detail section--so you can see what is going on. You might have some nulls or something.

-LB

 

[AlreadyLost]

Don't I need to create a formula field with some kind of formula so I can drag this field to my groupfooter?

 

[lbass]

What field? I have no idea what you are talking about. Please spend some time formulating your question so that all can follow. If you cannot explain as fully as you would like, then please provide sample data.

-LB

 

[AlreadyLost]

I think I found out why the problem but don't know how to resolve it..

it seems like if I only have one invoice line item ProgramCode 9915 and RelatedProgramCode 9915, it will not appear on my report. But if I have more than one invoice line item, ProgramCode 9915 and RelatedProgramCode 1002, I will get both amount on my report. Here is an example:

GroupFooter (Invoice_line_item.Invoice ID)

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 9915 Adjustment 1002 $40
IN123 9915 OverPayment 9915 $100

The above example invoice with RunningTotal1002, RunningTotal9915, I will get the data. But with the below example,I will not get RunningTotal9915 to display on my report.

Example data:
Inv.ID ProgramCode Description RelatedProgramCode Amt

IN123 1002 Payment 1002 $40

IN123 9915 OverPayment 9915 $60

Result:
Invoice 123 did not appear on my report. I did check the database and there is invoice 123 ProgramCode 9915 with RelatedProgramCode 9915. I also check whether RelatedProgramCode is null or blank (answer is no, it has data).

My OverPaymentAmount field detail:
RunningTotalName: OverPaymentAmount
Field summarized: Invoice_Line_Item.Amount
Type of Summary: max
Evaluate - use formula: {TB_FIN_INVOICE_LINE_ITEM.RELATED_PE} = '9915'
Reset: On change of group: Invoice_line_item.invoice_id

my formula field for @FilterPEOut: If {TB_FIN_INVOICE_LINE_ITEM.PE}in ['9915']
then 1

GroupSelectionFormula:
sum({@FilterPEOut}, {TB_FIN_INVOICE_LINE_ITEM.RELATED_PE})>0

 

[AlreadyLost]

Please disregard. I found the problem cause it does not have facility id associated with it.. Thanks so much with your help...