Show data for different years

[cristle01]

I need to show data in two columns, Grouped by Year, Month, Day and Hour
2008 2007 Variance
Group Header 1: Total 500 400 100
Group Header 2 Month
Jan 300 150 150
Feb 200 250 (50)
I have it sort of working by using Parameters and Running Totals but it seems
very convuluted. Also I 'm trying not to Group on a formula so that the SQL gets pushed down to the database.
I don't want to use a cross tab as I need to drill down and there's to much detail for a Cross Tab

I can't group by year because data will appear as follows
2008 2007 Variance
Group Header 1 500 500
Group Header 1 400 (400)

I'm using Crystal Reports XIR2.
Any help would be much appreciated.

Cristle01

 

[lbass]

If you don't have row inflation, all you need are two formulas for the year summaries:

//{@2008}:
if year({table.date} = 2008 then {table.amt} //or 1 for a count

//{@2007}:
if year({table.date} = 2007 then {table.amt} //or 1 for a count

Then you can insert summaries on them at each group level. For the difference, you would need a formula like this:

sum({@2008},{table.date},"monthly")-sum({@2007},{table.date},"monthly")

You would create a separate formula for each group level where you change the group field.

-LB

 

[cristle01]

LBASS,
Thanks for getting back to me.
I've been away and just getting back to work.

If I create the formula in the report footer
if year(table.date) = 2008 then sum(table.amount)
the formula sums ALL values, not just those for 2008.

I can get the correct sum for each year using a running total and place it in
the report footer but I need to be able to express the values in each group footer as a percentage of the sum in the report footer. Using the Running Total lets me down at this point.

 

[dgillz]

Please re-read LBass' post, he did not tell you to use a Sum() in the formula, he told you use his formula and insert a sum() ON his formula.

Software Sales, Training, Implementation and Support for Macola, eSynergy, and Crystal Reports

http://www.gainfocus.biz

"What version of URGENT!!! are you using?

 

[cristle01]

yes I did notice that and that won't (doesn't) work
But the sum needs to be in the report footer and in order to
add the values I need to use sum

 

[lbass]

Please explain WHY you think this won't work, since it should work just fine. However, are being unclear about where you want what calculations. Please show a sample of the percentage calculations you want, indicating the section. If you wanted the difference between 2007 and 2008 at the month group level, as a percentage of the report total then the calculation would look like this, using my earlier formulas:

(sum({@2008},{table.date},"monthly")-sum({@2007},{table.date},"monthly")) % sum({table.amt})

-LB

 

[cristle01]

Ok,
Thanks LB and dgillz,
I've created a new report and it appears to be working.

Thank you both.