Page footer issue 1

[Scroller52]

hi, i'm using CR XI release 2 on a SQL DB

i'm trying to get a page footer to return the 'legal entity' where the PCAP column number = 1. this report is grouped on type, investor and a contact. when i try to just bring in 'legal entity' it will change depending on where i place the field GH, GF, PF etc. in the page footer section, it would always return legal entity B, i want it to return legal entity D since the PCAP column number = 1. any way to solve this?

Type Legal Entity PCAP Column Number Investor
Family D 1.00 Joe smith
Family A 2.00 Joe smith
Family C 3.00 Joe smith
Family B 4.00 Joe smith

 

[MrBillSC]

Create a formula called LegalEntity01
//formual LegalEntity01
If {table.PCap} = 1.00 then {table.LegalEntity} else " "

Place the formula LegalEntity01 in your detail section. You will probably want to suppress printing it.
Right click on the formula and select Insert > Summary.
Select Maximum and your summary location (group footer).

If placing it in the group footer doesn't work you will have to use variables.

MrBill

 

[lbass]

Create three formulas:

//{@reset} for the Page header:
whileprintingrecords;
stringvar legent := "";

//{@accum} for the detail section:
whileprintingrecords;
stringvar legent;
if {table.PCap} = 1 then
legent := {table.LegalEntity};

//{@display} for the page footer:
whileprintingrecords;
stringvar legent;

-LB

 

[Scroller52]

wow lbass, you are awesome! any time to explain what the formulas do?

 

[lbass]

This is a variable that is set to be blank in the page header (resetting the variable for each page). The second formula sets the value of the variable to the legal entity if the value of the other field is 1. The last formula displays the result.

-LB

 

[Scroller52]

This formula worked great, but i think it needs to be changed a little bit. since every investor may or may not have a 'pcap column number' that = 1, i want to bring back the legal entity that has the minimum column number per investor. how do i do that? i tried to play around with the minimum formula in the //accum formula that lbass suggested.

 

[lbass]

//{@accum} for the detail section:
whileprintingrecords;
stringvar legent;
if {table.PCap} = minimum({table.PCap},{table.investor}) then
legent := {table.LegalEntity};

Note that if you are using page headers/footers that are resetting, you would need to have new groups start on a new page. Otherwise you should be using group sections for the legal entity.

-LB

 

[Scroller52]

thanks! it worked out perfectly. and each group starts on a new page.