Group Footer Summary 1

[mtownbound]

I have a group footer that tallies and displays the tickets for each month. I'm looking to roll up prior years and only expand the current year, but still keep the correct total. Any ideas?

Example:
Current
Aug-2009 10
Sep-2009 15
Jan-2010 11
Feb-2010 13
Total 49

Need
2009 25
Jan-2010 11
Feb-2010 13
Total 49


Thanks!!!

 

[fisheromacse]

how are the records being tallied? formulas, running totals from the wizard, etc? Please show the contents any relevant items.

 

[lbass]

Instead of grouping on date on change of month, group on a formula like this:

if year({table.date}) <> year(currentdate) then
date(year({table.date}),1,1) else
date(year({table.date},month({table.date}),1)

When inserting the group->options->customize groupname->use a formula->x+2 and enter:

if year({table.date}) <> year(currentdate) then
totext(date(year({table.date}),1,1),"yyyy") else
totext(date(year({table.date},month({table.date}),1),"MMM-yyyy")

-LB

 

[mtownbound]

Sorry about that. I'm using running total fields from the wizard.

Thanks again

 

[mtownbound]

Thanks lbass!! I'll try this and let you know what I get.

 

[mtownbound]

lbass, I know it's been forever but I was finally able to get back to this report. I used your formula, but I'm getting "A constant expression is required here" on the first {table.date} when I check for errors.

Also, don't know if it's normal behavior for the Grouping formula tab, but Report fields and database fields aren't available for selection in the explorer window.


Thanks again!!!

 

[lbass]

There is a missing paren:

if year({table.date}) <> year(currentdate) then
totext(date(year({table.date}),1,1),"yyyy") else
totext(date(year({table.date}[red])[/red],month({table.date}),1),"MMM-yyyy")

If this still doesn't work please post your exact formula.

-LB

 

[mtownbound]

Thanks again for the reply. Just to make sure, I'm entering this under "Use a formula as Group Sort Order". Here is my formula:

if year({Sheet1.Date Received}) <> year(currentdate) then
totext(date(year({Sheet1.Date Received}),1,1),"yyyy") else
totext(date(year({Sheet1.Date Received}),month({Sheet1.Date Received}),1),"MMM-yyyy")

Still getting the "A constant expression is required here" error.

 

[lbass]

No. This formula is for the group expert->options tab->customize groupname->use a formula->x+2.

-LB

 

[mtownbound]

Some progress.......sort of. The months are still listed seperately in the report, but now just the year is showing up in the Groups window. So now I have:

2009
2009
2009
2009
2009
1/2010
2/2010
3/2010
4/2010
etc.



Thanks again!!

 

[lbass]

There were two steps to my suggestion. The first was to group on the formula:

if year({table.date}) <> year(currentdate) then
date(year({table.date}),1,1) else
date(year({table.date},month({table.date}),1)

The second step was to customize the groupname with the formula I just fixed for you.

You have taken the first step--and the customize group name must be used for that group, so you'll have to do that again.

-LB

 

[mtownbound]

Finally got it to work!!! Thanks again!!!!!!!!!!!