highlight summary values 1

[swiss2007]

Hi,
I have 2 groupings country and Year.
I have only one date parameter and I have to calculate yeartodate summary values and the previous months greater than the month parameter should be highlighted in green.

sample data like this

Country
Year:2010

YeartoDate Jan Feb Mar Apr May Jun Jul Aug Sep Oct Nov Dec
Sum 275 125 150
(= Jan+Feb)

Country
Year:2009

YeartoDate Jan Feb Mar Apr May Jun Jul Aug Sep Oct Nov Dec
Sum 350 100 250 200 175 300 350 120 75 125 45 90 50
(= Jan+Feb)

output
------

Country
Year:2010

YeartoDate Jan Feb Mar Apr May Jun Jul Aug Sep Oct Nov Dec
Sum 275 125 150
(= Jan+Feb)

Country
Year:2009

YeartoDate Jan Feb Mar Apr May Jun Jul Aug Sep Oct Nov Dec
Sum 350 100 250 200 175 300 350 120 75 125 45 90 50
(= Jan+Feb) [-------Green--->>>>>>>>>>>>>>>>>>
these values should be highlighted

Any ideas, Thanks for your time.

 

[lbass]

if year({table.date}) <> year(maximum({table.date})) and
month({table.date}) > {?month} then
crGreen else
crNoColor

-LB

 

[swiss2007]

one more req,
my date format is like 200909,200910.
year to date is calculated as
at year group level
if {table.date}) > {?month}
then yeartodatetotal = sum(values)
how to calculate % diff i.e 2010
(2010 year to date/ 2009 year to date)-1.
and my values are placed in group headers.

 

[lbass]

Your formula wouldn't work for year to date. Are you saying your "date" field is really a number representing year and month only? Or is it a string?

-LB

 

[swiss2007]

Date field is a number containing year and month only stored in string format but in formulas I convert it to number

 

[lbass]

Why would you convert it to a number? How are you extracting the month then? Can I assume the month parameter is a number (datatype)?

-LB

 

[swiss2007]

To extract month from the database I use mid(table.date,5,2)
and to compare with date parm I use tonumber
yes assume month parm is number

 

[lbass]

Create formulas like this:

if val(mid(table.date,5,2)) <= {?month} then
{table.amt}

Place this in the detail section and insert a sum on it at the year group level. Then drag the result into the group header and suppress the detail section.

-LB

 

[swiss2007]

Yes LB this gives me the summary values at year level
how do i get the percentage diff of 2010 and 2009
as it's the same field that populates the year

 

[lbass]

Create a formula like this and place it in the year group header:

whileprintingrecords;
numbervar minyrytd;
numbervar maxyrytd;
stringvar diff := "";
if {@yrgroup} = minimum({@yrgroup}) then
minyrytd := sum({@ytdformula},{@yrgroup});
if {@yrgroup} = maximum({@yrgroup}) then
maxyrytd := sum({@ytdformula},{@yrgroup});
if {@yrgroup} = maximum({@yrgroup}) then
diff := totext((maxyrytd-minyrytd)% minyrytd,2)+"%" else
diff := "";

-LB

 

[swiss2007]

took some time to test. perfect lb