| Author |
Message |
lsalih
Groupie
Joined: 27 Sep 2007
Location: United States
Online Status: Offline
Posts: 44
|
  
Topic: Highlight records with specific charges + extra
Posted: 06 Jul 2011 at 5:46am |
Hi -
I have a table called Charge. I already wrote a report that only displays the records with specific charges, lets say (charge 123, and charge 345). The user wantes for the records with those two charges to have some kind of flag set when the user has extra achrges with the two ones that we already filtered (charges 123, charges 345).
so I need to create a formula to write either more charges or highlight the record when the user has additional charges beside the two ones we mentioned.
Please help.
|
 IP Logged |
|
|
|
DBlank
Moderator
Joined: 19 Dec 2008
Online Status: Offline
Posts: 9053
|
Posted: 06 Jul 2011 at 6:39am |
so I assume you are suppressing records that are not in 123 or 345
If you group on the user
write a formula to flag records that are NOT in 123,345 as
if table.field in (123,345) then 0 else 1
sum this at the group level adn now any user that has any charge other than 123,345 will have a value >0.
you can use that to hightlight or flag
if SUM(formula,user)>0 then highlight
|
| IP Logged |
|
lsalih
Groupie
Joined: 27 Sep 2007
Location: United States
Online Status: Offline
Posts: 44
|
Posted: 06 Jul 2011 at 7:36am |
Hello -
In my report, I already have record selection set to charges 123, charges 345. So when I added the formula if fieldname in 123, 345, then 0 else 1, all results were 0.
I do not have the report grouped by name, so I just said to highlight when the formula = 1. However it did not work.
Edited by lsalih - 06 Jul 2011 at 7:40am
|
| IP Logged |
|
DBlank
Moderator
Joined: 19 Dec 2008
Online Status: Offline
Posts: 9053
|
Posted: 06 Jul 2011 at 7:39am |
you cannot use records selection to exclude data. When you do you won't be able to check if other data exists because you just removed ir from the report.
ratehr than selecting date based on the citireia you can suppress data based ont he criteria. This leaves teh other stuff avialable to check against.
You have to group otherwise it does not know if there are more records specifically for that user.
|
| IP Logged |
|
lsalih
Groupie
Joined: 27 Sep 2007
Location: United States
Online Status: Offline
Posts: 44
|
Posted: 06 Jul 2011 at 9:16am |
Here is what I did:
1) removed record selection
2) Selected Secrtion Expert => Details => Surpress => not({CHARGES.CHCODE} in ["chrg123", "chrg345"])
3)Grouped by name
4) Created a formula called (Charges), I wrote: if ({CHARGES.CHCODE} in ["chrg123","chrg345"]) then 0 else 1
5) Added sum of (charges) in group footer
Still the report did not show other entries of the same person with different charges!
|
| IP Logged |
|
DBlank
Moderator
Joined: 19 Dec 2008
Online Status: Offline
Posts: 9053
|
Posted: 06 Jul 2011 at 9:23am |
1. to verify that your set up is working as expected, for item #5 if you look in the group footer at your SUM do you get a 0 for the sum for all users that have only 123 and 345 and a number >0 for all the others?
2. Exacly what do you want it to do to hightlight/ flag? Do you want the user name to change color? Do you want a field to say 'Other Charges!', something else?
Edited by DBlank - 06 Jul 2011 at 9:24am
|
| IP Logged |
|
lsalih
Groupie
Joined: 27 Sep 2007
Location: United States
Online Status: Offline
Posts: 44
|
Posted: 06 Jul 2011 at 2:10pm |
. to verify that your set up is working as expected, for item #5 if you look in the group footer at your SUM do you get a 0 for the sum for all users that have only 123 and 345 and a number >0 for all the others?
*** When I do sum of charges, all I get all ZEROs! I guess it is checking on the conditional statement, which all true based on the suppressed records. Because the condition is true, then the result is 0.
2. Exacly what do you want it to do to hightlight/ flag? Do you want the user name to change color? Do you want a field to say 'Other Charges!', something else?
All I need a flag to show that there are more charges beside those to charges. A highligh or just a formula to drag/drop into report should work.
BTW, the charge field is string. I even tried to change tonumber(filedname) so I could count number of charges but that I was not able to do as well!
Lava
|
| IP Logged |
|
DBlank
Moderator
Joined: 19 Dec 2008
Online Status: Offline
Posts: 9053
|
Posted: 07 Jul 2011 at 3:54am |
Originally posted by lsalih
When I do sum of charges, all I get all ZEROs! I guess it is checking on the conditional statement, which all true based on the suppressed records. Because the condition is true, then the result is 0.
Suppresing records do not exclude them from SUMS. Do you have NULLs to deal with?
If so try this for your counting formula
if isnull({CHARGES.CHCODE}) or NOT({CHARGES.CHCODE} in ["chrg123","chrg345"]) then 1 else 0
places this in your detial section
temporaily unsupress all the details
you should get a 1 if it is not 123 or 345 and a 0 if it is.
|
| IP Logged |
|
lsalih
Groupie
Joined: 27 Sep 2007
Location: United States
Online Status: Offline
Posts: 44
|
Posted: 07 Jul 2011 at 10:47am |
1) when I use to under suppress formula:
if isnull({CHARGES.CHCODE}) or NOT({CHARGES.CHCODE} in ["chrg123","chrg345"]) then 1 else 0
to suppress records, I get boolean is expected here. The suppress does not like above formula.
2) I followed your steps:
1) used
not({CHARGES.CHCODE} in ["chrg123", "chrg345"])
formula to suppress to display only those two charges.
3) Grouped report by name
4) Created a formula called charges, I used your formula:
if isnull({CHARGES.CHCODE}) or NOT({CHARGES.CHCODE} in ["chrg123","chrg345"]) then 1 else 0
Then I sum(charges) in group footer.
I am not sure if I have all steps correct?
Please advice.
|
| IP Logged |
|
DBlank
Moderator
Joined: 19 Dec 2008
Online Status: Offline
Posts: 9053
|
Posted: 07 Jul 2011 at 11:35am |
1. Sorry I was suggesting you remove you suppression formula temporarily for testing purposes, not replace it. Sometimes if you can see all your rows it is easier to trouble shoot.
For the steps.
1. correct (but you may want to remove it temporarily to test the formula for step 4)
2. not listed in your post
3. correct grouping
4. create formula, call it FLAG
if isnull({CHARGES.CHCODE}) or NOT({CHARGES.CHCODE} in ["chrg123","chrg345"]) then 1 else 0
5. Place FLAG on the detail row. it should show a 1 when ever that row has NULL in your charge field or is NOT = to chrg123 or chrg345 and it should have a 0 if the row DOES have charge field = chrg123 or chrg345
****If this does not work step 6 and beyond will not work and this has to be fixed here****
6. insert a summary (Sigma sign)
pick the FLAG formula field as the field to summarize
type is a SUM
summary location is group#1
you should now have a field in your group footer that shows totals for your 0/1 FLAG formula.
Does all of this work as I have described?
Edited by DBlank - 07 Jul 2011 at 11:35am
|
| IP Logged |
|
|
|
