| Author |
Message |
bwsanders
Senior Member
Joined: 05 Sep 2012
Location: United States
Online Status: Offline
Posts: 177
|
  
Topic: sum group / suppress formula
Posted: 02 May 2013 at 4:44am |
|
i have a report that i'm mking and exporting to excel(whole seperate issue :) ). it will have "dynamic" columns which is proving to be tricky. what i am trying to accomplish is this. an excel spreadsheet with the first column being for employee names, the remaining columns will be for each month of the year like so
employee | Jan | Feb | March | May |
jack |
jill |
billy |
bob |
each of the employee rows will show their earnings for each month. using the suppress field formula i am able to accomplish this if i run the report for any given month. here is the formula i use the format field suppress option
If cstr(month({CheckDate}),0,"")+ "/" +cstr(year({CheckDate}),0,"") = cstr("number of month")+ "/" +cstr(year(CurrentDate),0,"")
Then False Else True
however, if i run the report for a span of months instead of the earnings showing under the respective column they group under the first column and show a total for that span of months.
any thoughts? is there something that i'm missing? any help or a point in the right direction would be very appreciated.
Thank you!
|
 IP Logged |
|
|
|
DBlank
Moderator
Joined: 19 Dec 2008
Online Status: Offline
Posts: 9053
|
Posted: 02 May 2013 at 4:54am |
|
why not just use a crosstab?
|
| IP Logged |
|
bwsanders
Senior Member
Joined: 05 Sep 2012
Location: United States
Online Status: Offline
Posts: 177
|
Posted: 02 May 2013 at 6:48am |
|
I'm not familiar with crosstab. still bit of a newb i guess. i can do some googling.
|
| IP Logged |
|
DBlank
Moderator
Joined: 19 Dec 2008
Online Status: Offline
Posts: 9053
|
Posted: 02 May 2013 at 7:00am |
insert a cross tab in the report header
set rows to your employee (i use a formula field with employee name and ID to make sure it is unique and you do not group two emplioyees with the same name)
insert the CheckDate field as your colmn, click on group options and set it to monthly
I do not know what you are summarizing but insert that field into the summarized field and set it to a count to sum or whatever you are doing
|
| IP Logged |
|
bwsanders
Senior Member
Joined: 05 Sep 2012
Location: United States
Online Status: Offline
Posts: 177
|
Posted: 02 May 2013 at 7:58am |
|
hmmm, i'm trying to figure this out. it looks like it might just work. my columns i need to be month names. i was using just text to span the months out over columns. the rows will be employee names and the sum field will be the amount they were paid for each given month.
having never used the cross tab before i'm having a bit of trouble getting used to it and getting it to work.
|
| IP Logged |
|
DBlank
Moderator
Joined: 19 Dec 2008
Online Status: Offline
Posts: 9053
|
Posted: 02 May 2013 at 8:33am |
to make it the month name right click on the date in the CT column header
selet format field
select date and Time tab
select customize
select date tab
under month select either Mar or March
under day and month select None Edited by DBlank - 02 May 2013 at 8:34am
|
| IP Logged |
|
bwsanders
Senior Member
Joined: 05 Sep 2012
Location: United States
Online Status: Offline
Posts: 177
|
Posted: 02 May 2013 at 9:05am |
|
NICE! i have got it go into excel pretty nicely. i lose the format on the text when going into excel but that's not the end of the world. i was hoping to be able to use the can grow on the fields as to avoid the excel "####" issue.
thank you for your help!!
|
| IP Logged |
|
DBlank
Moderator
Joined: 19 Dec 2008
Online Status: Offline
Posts: 9053
|
Posted: 02 May 2013 at 10:15am |
you might try and convert the label to a string to not lose it when you export it.
right click on the month label
select format field
Common tab
click on the fomrual for 'Display string' and use
monthname(month(currentfieldvalue))
|
| IP Logged |
|
bwsanders
Senior Member
Joined: 05 Sep 2012
Location: United States
Online Status: Offline
Posts: 177
|
Posted: 02 May 2013 at 10:26am |
|
yeah i tried that and still loses it in excel. if i export it as raw i get the field format but loose the grid lines in excel. if i choose export excel(data) i get the grid lines but formatting.
|
| IP Logged |
|
bwsanders
Senior Member
Joined: 05 Sep 2012
Location: United States
Online Status: Offline
Posts: 177
|
Posted: 06 May 2013 at 3:37am |
|
i got the report working great. cross tab are def tricky but very cool once tweaked.
thank you for your help.
|
| IP Logged |
|
|
|
