Ranking

[pblazeppd]

Version: XI
Database: SQL

Tables: ApartmentName <left outer join> ApartmentUnits <left outer join> PCalls

Total#Units (Which is not Joined--Just a sum of ApartmentName.#ofUnits

ApartmentName
Fields: Business#, BusinessName, #ofUnits

ApartmentUnits
Fields: Unit#, Business#, Address

PCalls
Fields: Call#, Case#, Address,CALL_ENTRY_DATE

Example of Output:
(REPORT Header Section)
Total CFS for this Bulletin: 350
Total Number of Apartment Complexes: 195
Total Number of Apartment Units: 19,365
Average Apartment Community Size: 99.16
City Wide Ratio: 0.3763
(End of Header Section)

ApartmentName Address #ofUnits CFS INC RATIO LEVEL RANK
London Bell 123 Main 100 10 3 0.10 -0.27 ?
Chase 1010 Jack 689 100 43 0.14 -0.23 ?

Grouping:
Group Header#1 <ApartmentName.BusinessName>
Group Header#2 <ApartmentUnits.Address>

Details: Suppressed
Call#, Report#, Address, @CountofCFS, @CountofINC

Formulas:
@CountofCFS --if {PCalls.CALL_ENTRY_DATE} in YearToDate then 1
@CountofINC --if ({PCalls.CALL_ENTRY_DATE}) in YearToDate and ({PCalls.REPORT_NO})<>" " then 1
@CityRatio --Sum ({@Count of CFS})/{TotalUnits.SumOf# of Units}

Formating:
CityRatio--NumberVar CITY
#ofUnits--NumberVar UNITS
CFS--NumberVar CFS
RATIO--NumberVar
WhilePrintingRecords;
NumberVar CFS;
NumberVar UNIT;
NumberVar Ratio:= (CFS/UNIT);
ToText((CFS/UNIT),4)
LEVEL--NumberVar
WhilePrintingRecords;
NumberVar CITY;
NumberVar RATIO;
ToText((RATIO - CITY),4)
RANK?????

What I want to do is RANK the Apartments by the LEVEL. Is this possible???

I hope this is enough information and detail...

Phil

 

[lbass]

Can't tell what you are setting your variables equal to.

-LB

 

[pblazeppd]

Sorry,

They are set to the currentfield values at the group header #1 level.

under format field, suppress...
CFS
WhilePrintingRecords;
NumberVar CFS:=currentfieldvalue;
false;

INC
WhilePrintingRecords;
NumberVar INC:=currentfieldvalue;
false;

Then in the Report Header Section;

CityRatio
WhilePrintingRecords;
NumberVar RATIO:=currentfieldvalue;
false;

Hope this clarifies things...

Phil

 

[lbass]

You are trying to do this in a crosstab? And no, that doesn't clarify anything, since I don't know what the summaries are in the crosstab. What does CFS stand for? To me, it means Chronic Fatigue Syndrome. And INC?

-LB

 

[pblazeppd]

No, it is not a crosstab...CFS and INC are just labels that stand for Calls for Service and Incidents...CFS is the sum of the CALL_ENTRY_DATE since every call is assigned a date the same with INC, but if there is a REPORT_NO then it is counted...

Phil

 

[lbass]

I am not following why you would be trying to set variables equal to some value in a formatting formula area. Because you are doing this, I cannot really tell the values you are setting the variables to--whether these are summaries or what.

However, I can tell you that in order to rank these values, you need to be able to sort by them, which means you need to return the summaries to the reports directly through a command.

-LB