| Author |
Message |
KevV
Senior Member
Joined: 19 May 2011
Online Status: Offline
Posts: 106
|
  
Topic: Kind of Summing
Posted: 14 Jun 2011 at 12:14pm |
I have a report that has many orders that are grouped. Some of the orders have codes. I have created two columns in the Group Footer and want to show if those codes appear at any time in the group. What seems to happen is it only looks at the last record in the group, so even if there are orders with codes in each of the columns in the group it may only show one. I am not looking to count them just note wether or not one exists.
Thanks
KevV
|
 IP Logged |
|
|
|
sharona
Senior Member
Joined: 16 Oct 2008
Location: United States
Online Status: Offline
Posts: 255
|
Posted: 15 Jun 2011 at 2:56am |
what happens when you move the formula next to the field, does it show the code?
|
|
sharona
|
| IP Logged |
|
lockwelle
Moderator
Joined: 21 Dec 2007
Online Status: Offline
Posts: 4374
|
Posted: 15 Jun 2011 at 3:54am |
you will need a formula of some sort...or more simply use the Count function, and if it > 0 then display whatever value you want. something like: if count({table.field}, {group}) > 0 then "were in group" else "" //just for paranoia as a FYI, any code that is checking a column operates on the current record ONLY. When you are in the group footer, you are looking at the last record of the group. So if you wanted to know the count (for example since it is simple--yes you could use the built in function) you would place your formula in the detail section of the report as it is section that is hit by EVERY record. If you wanted an average of group sums, you would put your incrementing formula in the group footer. HTH
|
| IP Logged |
|
KevV
Senior Member
Joined: 19 May 2011
Online Status: Offline
Posts: 106
|
Posted: 15 Jun 2011 at 6:27am |
I have the detail section set up with the "format with multiple colums" so I can show all of the detail section on one line but if I turn it off I can put the formula next to the field and it does show correctly. I tried the formula but I am not sure what to use for the second {group}
if count({table.field}, {group}) > 0 then
"were in group"
I put in the code but it needs a field.
Thanks
KevV
|
| IP Logged |
|
lockwelle
Moderator
Joined: 21 Dec 2007
Online Status: Offline
Posts: 4374
|
Posted: 15 Jun 2011 at 9:03am |
usually the {group} is the same condition that you are using to group the report by. for example, if you are grouping by {table.employeeid}, you would put that there. it is usually available from the list of fields above the formula editor, and if it is not a valid value CR will let you know HTH
|
| IP Logged |
|
KevV
Senior Member
Joined: 19 May 2011
Online Status: Offline
Posts: 106
|
Posted: 15 Jun 2011 at 11:37am |
When I put that in the report the value either comes up as 1 or 0 wether there is something in the field or not. I changed the detail section so I can see all the records and there are some groups that don't have that code show up on any records but the formula still shows the same. I tried it in both the detail section and in the footer. The code I am checking for is a three digit letter code so is it going to count it or do I need to convert it?
Thanks
KevV
|
| IP Logged |
|
lockwelle
Moderator
Joined: 21 Dec 2007
Online Status: Offline
Posts: 4374
|
Posted: 15 Jun 2011 at 11:51am |
Sorry, I was thinking that it would be field was blank or filled in. For a specific code.... I would build a formula that would increment, though you could just make it a flag if you wanted. DBlank would create a formula like : if {table.field} = 'abc' then 1 else 0; then he would count the formula...at least that is what I remember HTH sorry for any confusion
|
| IP Logged |
|
DBlank
Moderator
Joined: 19 Dec 2008
Online Status: Offline
Posts: 9053
|
Posted: 15 Jun 2011 at 11:53am |
make a flag per thing you want to show in the group footer. I will use ABC and DEF to illustrate...
ABCflag
if table.code="ABC" then 1
DEFflag
if table.code="DEF" then 1
now in your group footer add 2 more formulas
if sum(ABCflag,groupfield)>0 then 'Includes ABC' else ''
if sum(DEFflag,groupfield)>0 then 'Includes DEF' else ''
edited:
beat me to the punch lockwelle  Edited by DBlank - 15 Jun 2011 at 11:56am
|
| IP Logged |
|
KevV
Senior Member
Joined: 19 May 2011
Online Status: Offline
Posts: 106
|
Posted: 15 Jun 2011 at 12:34pm |
I am guessing that the ABCflag and DEFflag are formulas. I create them and I get the 1 in each of the colums if I add them in the detail section. When I create the formulas for the footers I use the first formula I created and then I am not sure what to use for the group field. If I put the groupename ({table.field}) i get an error highlighting the first formula and saying a number field or currency amount field is required.
Thanks
KevV
|
| IP Logged |
|
DBlank
Moderator
Joined: 19 Dec 2008
Online Status: Offline
Posts: 9053
|
Posted: 15 Jun 2011 at 4:50pm |
it is whatever field you are using to group your orders (probably an orderid).
so these formulas will look like
ABCtext
if sum({@ ABCflag},{table.orderid})>0 then 'Includes ABC' else ''
DEFtext
if sum({@ DEFflag},{table.orderid})>0 then 'Includes DEF' else ''
|
| IP Logged |
|
|
|
