condition for text control based on results of group footer calculation

[cbrydon]

Hello,

I have a report where I'd like to set the condition of a text control based on the results of a calculation in the groupfooterband just below the text control.
The text control outlined in red should have its text color changed to red if its value is greater than the value of the text box outlined in blue.

The expression in the text control outlined in red is {((FacilityAllAssets.Asset_WOCosts.CostPerAsset)+(FacilityAllAssets.CurrentYearDepreciation) + (decimal)FacilityAllAssets.faa_fsum.FillupTotal)/(FacilityAllAssets.CurrentAge)}

The expression in the text control outlined in blue is {Avg(((FacilityAllAssets.Asset_WOCosts.CostPerAsset) + (FacilityAllAssets.CurrentYearDepreciation) + (decimal)FacilityAllAssets.faa_fsum.FillupTotal)/(FacilityAllAssets.CurrentAge))}

ReportQuestion.png (14.96 KiB) Viewed 645 times

I've tried using the following expression in the Highlight Condition of the text control outlined in red.
((FacilityAllAssets.Asset_WOCosts.CostPerAsset)+(FacilityAllAssets.CurrentYearDepreciation) + (decimal)FacilityAllAssets.faa_fsum.FillupTotal)/(FacilityAllAssets.CurrentAge) >
Totals.Avg(GroupFooterBand1,((FacilityAllAssets.Asset_WOCosts.CostPerAsset)+(FacilityAllAssets.CurrentYearDepreciation) + (decimal)FacilityAllAssets.faa_fsum.FillupTotal)/(FacilityAllAssets.CurrentAge))

The above expression results in all text being colored red because Totals.Avg(GroupFooterBand1,((FacilityAllAssets.Asset_WOCosts.CostPerAsset)+(FacilityAllAssets.CurrentYearDepreciation) + (decimal)FacilityAllAssets.faa_fsum.FillupTotal)/(FacilityAllAssets.CurrentAge)) seems to always returns 0. I must be missing something simple in the expression.

ReportQuestionResults.png (18.91 KiB) Viewed 645 times

I have tried running the report in both Single Pass and Double Pass mode and get the same results for both.

Unfortunately, I am unable to provide a report file or sample data for this question.

I hope you can provide some guidance on an expression I can use.

Thank you,
Carl

[Kirill Klimenkov]

Hello.

You could create a Data Transformation with the same grouping as in GroupHeaderBand and a column with an expression:

Code: Select all

Avg(((FacilityAllAssets.Asset_WOCosts.CostPerAsset) + (FacilityAllAssets.CurrentYearDepreciation) + (decimal)FacilityAllAssets.faa_fsum.FillupTotal)/(FacilityAllAssets.CurrentAge))

You could then use this column in the Condition. Please check the attached report template.

Thank you.

[cbrydon]

Thank you for this Kirill. I'll have a look at the report and will let you know if I can get it to work.

[Lech Kulikowski]

Hello,

You are welcome.