CRXI Problem Displaying Formula Value in GroupFooter 2

[solmskid]

A Reporting Problem:
Using CR XI DataSource SQL
Report Group1 - Proj Id : Group2 - FY

Detail - Proj Id | FY1 Budget$ | FY2 Budget$....to FY5...

FY1 is a formula defined IF FiscalYear = 2007 THEN BUDGET$
FY2 - same - FiscalYear = 2008 ...to FY5 to 2011

Attempting to create report in following format:
All section suppressed except for Group1Footer,
Group1Footer: ProjId | FY1 | FY2 | FY3 | FY4 | FY5

The Problem : Only FY5 displays a value. I believe its a grouping thing but I've danced around it long enough to be completely confused.

Any Help Greatly Appreciated.

 

[synapsevampire]

Use Insert->Crosstab, place it in the report footer.

Place the fiscalyear in the column, and the budget in the summary field.

-k

 

[lbass]

...and Project ID as the rowfield.

-LB

 

[solmskid]

Thank you both for the quick response.
Forgot to mention I am not summing the budget$ for the fiscal year, just displaying the latest budget$ update for the fiscal year. The GF1 has a other fields on it related to the ProjId and I am not familiar enough with CrossTabs to get it to fit, plus the user wants the output in Excel format. I know, I should gave the user what I have in Excel and let them slice&dice all they want. Basically what I'm trying to do is pass Group2 data to Group1 so I can display the data in the Group1 footer. I suppose I could create a formula with a variable for each Fiscal Year that is populated in Group2, displayed in Group1 footer A and reset in Group1 footer B. Does that sound right?

 

[lbass]

Remove the inner group on fiscal year, and then create conditional formulas like:

if {table.fiscalyear} = 2006 then {table.budget}

Then insert maximums on them at the group level and suppress the detail section.

-LB

 

[solmskid]

I know this sounds complicated and I appreciate your patience....

your last tip is what I tried to do, but the updated budget$ is lower then the previous update in some cases so maximum doesn't apply. I have the data sorted by date/time of entry and am picking up the value as of that point in time. I get the correct value at the G2Footer but I can't get value to the G1Footer, it gets reset to 0.

thanks...

 

[lbass]

Then instead of conditional formulas, use running totals where you select {table.budget}, sum, evaluate using a formula:

{table.fiscalyear} = 2006 and
(
onfirstrecord or
{table.project} <> previous({table.project}) or
year({table.date}) <> year(previous({table.date}))
)

Reset on change of {table.projID}. This assumes that you have the report sorted in descending order by date and that you want the most recent budget per year per project ID.

-LB

 

[solmskid]

THANK YOU THANK YOU THANK YOU
Merci Beaucoup
Besten Danke
Mucho Gracias

sincerely