Using running total in detail section 1

[eriktoronto]

I need to do a calculation based on the report data and use that calculation in the report detail section. Is it possible?

The tables are:
Group 1: Building
Group 2: Floor

The report runs for a building, looks at each floor, and looks at each space on the floor (to pick up office sq ft)

In the detail section, it reads each space record and sums the sq ft and groups by floor. On each floor record, it picks up the floor area sq ft number (stored on the floor record which is group 2).

Floor Space ID
001 100 100 sqft
001 200 200 sqft
001 300 100 sqft
Floor 001: 400 sqft Floor Record: 500 sqft

002 100 150 sqft
002 200 200 sqft
002 300 100 sqft
Floor 002: 450 sqft Floor Record: 500 sqft

Report Footer:
Building Total sum: 850 sqft
Floor Record Running Total: 1000 sqft
Ratio: 0.85
(Ratio = Building Total / Floor Record running total)

Is it possible to use the Ratio field that is displayed in the report footer section (Building Total / the running total that is evaluated for each group 2 floor record that is read) and multiply this value by the detail record (space sq ft)

For example, on floor 001, space id 100, we would want to multiply the detail record sqft value of 100 sqft by the report footer Ratio value (0.85).

=========================================================

So far I have not been able to access the Ratio formula value in the detail section of this report. As it is using a running total, when I try to access the Ratio formula in the detail section, the value is a moving target/work in progress as it is being generated as the report is processing.

The final value is not generated until the report footer section.

Is there any way that a formula that is calculated in the report footer that uses a running total based on a group value (group 2) can be accessed in the detail section of a report? With the final correct value? I don't think it will work as the Ratio formula is not finished its calculations until the report footer section (once processing has completed)

Thanks

 

[lbass]

How does the "Floor Record" display if placed in the detail section?

-LB

 

[eriktoronto]

The floor record is displaying correctly on the detail level. It displays 500 sqft. For example:

Floor Space ID Space SqFt Floor Record
001 100 100 sqft 500 sqft
001 200 200 sqft 500 sqft
001 300 100 sqft 500 sqft
Floor 001: 400 sqft Floor Record: 500 sqft

002 100 150 sqft 500 sqft
002 200 200 sqft 500 sqft
002 300 100 sqft 500 sqft
Floor 002: 450 sqft Floor Record: 500 sqft


But if I add the floor record to the detail section and add a sum for group 2, the sum would be 1,500 sqft (rather than the correct 500 sqft) since the floor record would sum (evaulate) for every detail space record.

So that is why I added a running total that would only evaluate at group #2 (floor record group).

 

[lbass]

Can you please identify the fields you are using with the convention {table.field}. I'm wondering whether you could capture the sum of the floor record in a SQL expression.

Otherwise, you could always capture the ratio or the sum of the floor record in a subreport placed in the report header in which you create a shared variable to pass the value to the main report for use in the detail records.

-LB

 

[eriktoronto]

The fields are:

{Space.SpaceID}
{Space.SquareFt}

{Floor.FloorID}
{Floor.SquareFt}

{Building.BuildingID}

Table Joins:

{Building} one to many {Floor} one to many {Space}

The sub-report idea (in the report header) sounds intriguing. As I assume that it would process prior to the rest of the report. Therefore, if I do my calculations in the sub-report, as you mentioned, I could pass them to the main report.

It sounds liek it could possibly work as I have had no success at all to date trying to get the Ratio in the detail section (my real example is more complex as it does additional calculations based on space and floor totals but I simplified it above to make it easier to document).

Thanks for the suggestion LBass! I have enevr used a sub-report in a report header but it sounds like an intriguing idea.

 

[lbass]

You could try a SQL expression {%floorsum} like this:

(
select sum(`SquareFt`)
from Floor A, Building B
where A.`BuildingID` = B.`BuildingID` and
B.`BuildingID` = Building.`BuildingID` and
A.`FloorID` = Floor.`FloorID`
)

If you have other record selection criteria that would impact the sum, you would have to build them into the where clause, since the SQL expression will directly access the database.

You could then use a formula like this for the ratio:

sum({space.squareft},{building.buildingID})/{%floorsum}

If you want to go with the subreport idea, I think you could just link the building and floor tables and then do a simple sum of floor.squareft and set it to a shared variable. Place a formula like this in the sub report footer:

whileprintingrecords;
shared floortotal := sum({floor.squarefoot});

Since you want the value per building, I would place this in the group header for building and link the sub to the main report on the building group field (building ID?). You can then use a formula like this in the detail section for the ratio:

whileprintingrecords;
shared floortotal;
sum({space.squareft},{building.buildingID})/floortotal

-LB

 

[eriktoronto]

Thank you very much lbass. That information is very helpful, and I will try those ideas. Thanks again and have a good weekend!

 

[eriktoronto]

Hi lbass,

I tried the "sub-report in the header" idea, and it worked perfectly!

And the sub-report actually processes faster than expected, as I did a "non-linked" sub-report.. since it is not linked to the main report it runs faster than a linked sub-report would.

Thanks again as this solution worked perfectly (and I had been all out of ideas)

 

[lbass]

Well, the summary value you get for the building will be based on the last building in the report, unless you set up an array in the subreport.

-LB

 

[eriktoronto]

Thanks lbass.. the reason everything worked so far is that they have only been expecting one ratio.. they have been testing the report on either one single building, or a complex/campus (a group of related buildings which share a ratio, as at the moment the ratio is calculated in the report footer based on all building(s) in the report).

I will have to check if they want the ability to run this report for more than one unrelated building that does not share a ratio value, in which case I will need to modify the report as you mentioned.

Thanks again

 

[lbass]

Okay--your original post showed a group on Building, which was why I was concerned.

-LB

 

[eriktoronto]

yes, at the moment they do have the ability to select all buildings (not just those which are related), so I will either need to restrict the parameters, or make the changes you had suggested ... you are correct that the report will work incorrectly if they choose to select multiple unrelated buildings so even though it's working now based on their selections, I will need to fix it in case they do decide to try multiple values

thanks for reading in such detail, your time and expertise on this issue is much appreciated